LINQ : Query A Simple Array

Query String Array:
public static void ExecuteBasicStringArray()
{
    //create string array
    string[] bikes = new string[] { “Yamaha”, “Honda”, “Ducati”, “Suzuki” };


  //build LINQ query
    var queryStrings =
        from bike in bikes
        where bike == “Honda” || bike == “Yamaha”
        select bike;


  //view results of query
    foreach (var b in queryStrings)
    {
        Console.WriteLine(b);
    }

    Console.ReadLine();

}
Query Integer Array:
public static void ExecuteBasicIntArray()
{
   //create integer array
    int[] numbers = { 46, 99, 69, 35 };


  //build LINQ query
    var queryNums =
        from num in numbers
        where num < 99
        select num;


  //view results of query
    foreach (var n in queryNums)
    {
        Console.WriteLine(n);
    }

    Console.ReadLine();

}

LINQ : An Introduction

LINQ is an acronym for “Language
Integrated Query.” It’s been around since Visual Studio 2008 but
in the works far in advance of that; however, many companies are just
now using it for the first time. It is a programming model
developed by Microsoft to allow developers to query several types of
objects as if they were querying a SQL database. For example,
developers can now query for a textbox on a form with the ease of
querying for a row in a database table – often, with less overhead.

The syntax is similar to SQL except
that the clauses are ordered differently. For example, note the
following LINQ query:
var queryProduct =
from p in products
select p.ProductName;

Common objects queryable by LINQ are
enumerable classes, arrays, datasets, and XML documents.

SQL Server : Using Quotes In Linked Server Queries

Doing an OpenQuery across a linked server can get ugly very fast and it is often best to avoid them.
The basic syntax of an OpenQuery is :
SELECT * FROM OPENQUERY(YourLinkedServerName, ‘SELECT FIELD1, FIELD2 FROM YOURTABLE’)
From a coding perspective, this gets ugly if you have to incorporate string parameters; thus, utilizing many quotes.  For Example:
SELECT * FROM OPENQUERY(YourLinkedServerName, ‘SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = ”ID105” ‘)
Microsoft made this easier with the built-in stored procedure that allows you to avoid multi-layered quotes:  sp_executesql
The above can become:
DECLARE @PARAM char(5)
SELECT  @PARAM = ‘CA105’
EXEC YourLinkedServer.master.dbo.sp_executesql
N’SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = @p1′,
N’@p1 char(5)’,
@PARAM
But, what if your WHERE clause becomes:
WHERE FIELD2 in (”ID105”,”ID107”,”ID109”,”ID112”,”ID116”,
”ID119”,”ID400”,”ID404”,”ID101”,”ID102”,”ID115”,”ID106”,”ID111”,”ID117”,”ID412”,”654Z”,”ID103”,”ID121”,”ID401”,”ID403”,”ID407”,”ID108”,”ID113”,”ID114”,”ID120”,”ID123”,”ID405”,”ID409”,”ID100”,”ID110”,”ID124”,”ID408”,”ID104”,”ID118”,”ID406”,
”ID413”)
There are multiple ways to go about this but here is one cheap method for dealing with the quotes:
DECLARE @TSQL NVARCHAR(2000);
DECLARE @CODES NVARCHAR(500);
DECLARE @OPENQUERY NVARCHAR(100);
DECLARE @LINKED_SQL NVARCHAR(2700)
SET @CODES = ”’ID105”,”ID107”,”ID109”,”ID112”,”ID116”,
”ID119”,”ID400”,”ID404”,”ID101”,”ID102”,”ID115”,”ID106”,”ID111”,”ID117”,”ID412”,”654Z ”,”ID103”,”ID121”,”ID401”,”ID403”,”ID407”,”ID108”,”ID113”,”ID114”,”ID120”,”ID123”,”ID405”,”ID409”,”ID100”,”ID110”,”ID124”,”ID408”,”ID104”,”ID118”,”ID406”,
”ID413”’;
SELECT @TSQL = ‘SELECT … WHERE FIELD2 IN (‘+ @CODES + ‘)’  
— CLEAN UP THE QUOTES FOR THE OPENQUERY
—————————————————–                 
SET @TSQL = REPLACE(@TSQL, ””,”””)  ;
SET @TSQL = @TSQL + ”’)’;
SET @OPENQUERY = ‘SELECT * FROM OPENQUERY(GHDB, ”’;
SET @LINKED_SQL = (@OPENQUERY+@TSQL)
EXEC sp_executesql @LINKED_SQL

SQL: Set Up Email Alert When Partition Size Reaches Threshold

-- =============================================
-- Query to determine if a partition's size may
-- be reaching a critical maximum
-- Note: Currently uses SP_SEND_DBMAIL instead of XP_SENDMAIL
-- =============================================

DECLARE @DbName VARCHAR(25)
DECLARE @sql VARCHAR(100)
DECLARE @ThresholdCountMsg INT
DECLARE @Subject VARCHAR(100)

SET @DbName = DB_NAME(); --get name of current database to use in email alert

SET @Subject = 'Partitioning Threshold Alert On ' + @DbName + '!'

SET @ThresholdCountMsg = 14000000;  -- set partition row count

IF OBJECT_ID('tempdb..##yourPartitionSizeCheck') IS NOT NULL
BEGIN
DROP TABLE ##yourPartitionSizeCheck
END;
 

--use global temp table for email alert

CREATE TABLE ##yourPartitionSizeCheck
(
table_name varchar(20),
partition_number int,
row_count int
);

 

INSERT INTO ##yourPartitionSizeCheck

SELECT b.name AS 'table_name',
partition_number,
a.[rows] AS 'row_count'
FROM sys.partitions AS a INNER JOIN
sys.objects AS b ON a.object_id = b.object_id
INNER JOIN sys.schemas AS c On c.schema_id = b.schema_id              
WHERE LOWER(b.name) = 'yourPartitionedTableName'     
and a.index_id < 2
and a.[rows] > @ThresholdCountMsg; 

IF @@ROWCOUNT > 0

EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'YourSqlmail',
@recipients = 'your.email@yourcompany.com',
@subject = @Subject,
@body = 'The following partitions may be approaching performance degradation: ',   

@execute_query_database = @DbName,       

@query = 'SELECT table_name, partition_number, row_count FROM ##yourPartitionSizeCheck';