【读书笔记】Inside Microsoft SQL Server 2005 – T-SQL Programming

来源：互联网发布：warframe淘宝编辑：程序博客网时间：2024/05/29 21:35

<Inside Microsoft SQL Server 2005 – T-SQL Programming>

Author: Itzik Ben-Gan; Dejan Sarka; Roger Wolter

By Alva Chien

Docu. Version

Comment

Date

First release

10.10.06

1. SQL Server has limited support for pattern matching through the LIKE predicate and PATINDEX function. ANSI supports regular expressions through an operator called SIMILAR TO, but SQL Server do not support.

2. LIKE wildcard table:

Wildcard character

Description

Example

Any string of zero or more characters.

WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.

_ (underscore)

Any single character.

WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).

[ ]

Any single character within the specified range ([a-f]) or set ([abcdef]).

WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.

[^]

Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

WHERE au_lname LIKE 'de[^|]%'all author last names starting with de and where the following letter is not l.

3. Sometimes, you can use some functions to instead of LIKE predicate. The following two queries are the same, search for customer whose ID starts with the letter A, and if you check the execution plan, the LIKE query uses the index efficiently.

SELECT CustomerID, CompanyName, Country

FROM dbo.Customers

WHERE LEFT(CustomerID, 1) = N'A';

SELECT CustomerID, CompanyName, Country

FROM dbo.Customers

WHERE CustomerID LIKE N'A%';

4. Check a string representing a serial number or not, using the LIKE predicate, you can define it like the code below. The first one is inefficient and slow down insertions into the table.

CHECK(sn LIKE REPLICATE('[0-9]', LEN(sn)))

CHECK(sn NOT LIKE '%[^0-9]')

5. Define a check for validate IP address.

ALTER TABLE dbo.IPs ADD CONSTRAINT CHK_IP_valid CHECK

(

-- 3 periods and no empty octets

ip LIKE '_%._%._%._%'

AND

-- not 4 periods or more

ip NOT LIKE '%.%.%.%.%'

AND

-- no characters other than digits and periods

ip NOT LIKE '%[^0-9.]%'

AND

-- not more than 3 digits per octet

ip NOT LIKE '%[0-9][0-9][0-9][0-9]%'

AND

-- NOT 300 - 999

ip NOT LIKE '%[3-9][0-9][0-9]%'

AND

-- NOT 260 - 299

ip NOT LIKE '%2[6-9][0-9]%'

AND

-- NOT 256 - 259

ip NOT LIKE '%25[6-9]%');

6. Using SUBSTRING function to get the sub-string of a total string. The CAST function can cast one type into the other type, such as CAST(SUBSTRING(ip, s1, l1) AS TINYINT).

7. SQL Server default use case-insensitive character-based data. Regardless of the sensitivity of the data based on the effective collation, its binary representation is always case sensitive. For example:

SELECT CustomerID, CompanyName, Country

FROM dbo.Customers

WHERE CustomerID COLLATE Latin1_General_CS_AS = N'ALFKI'

AND CustomerID = N'ALFKI';

8. Create a table, detect the table with same name exist or not, like the codes below, check the CustomerData is exist or not, if exist, delete first.

IF OBJECT_ID('dbo.CustomerData') IS NOT NULL

DROP TABLE dbo.CustomerData;

CREATE TABLE dbo.CustomerData

(

custid INT NOT NULL PRIMARY KEY,

txt_data VARCHAR(MAX) NULL,

ntxt_data NVARCHAR(MAX) NULL,

binary_data VARBINARY(MAX) NULL

);

9. To avoid the low performance of updating the LOB, such as dynamic MAX binary type. SQL Server enhances the UPDATE statement by providing you with a WRITE method for dynamic MAX columns. The WRITE method allows you to modify only a section within the string and not override the whole thing. Logically, the WRITE method is similar to the STUFF function. It accepts three arguments: @expression, @offset, and @length. The @expression argument replaces @length units (characters/bytes) starting from @offset position in the target value. The codes below operate on the txt_data column value for customer 102. It replaces the string '102' located at offset 9 (zero-based) with the string 'one hundred and two', resulting in the string 'Customer one hundred and two text data':

INSERT INTO dbo.CustomerData(custid, txt_data)

VALUES(102, 'Customer 102 text data');

UPDATE dbo.CustomerData

SET txt_data.WRITE('one hundred and two', 9, 3)

WHERE custid = 102;

10. Some functionality of WRITE method:

a. If @expression is NULL, @length is ignored, and the value is truncated at the @offset position. For example, the following code truncates the string at the 28th position, resulting in the string 'Customer one hundred and two':

UPDATE dbo.CustomerData

SET txt_data.WRITE(NULL, 28, 0)

WHERE custid = 102;

b. If @length is NULL, the string is truncated at the @offset position, and @expression is appended at the end. For example, the following code truncates the string at the ninth position, and appends '102' at the end, resulting in the string 'Customer 102':

UPDATE dbo.CustomerData

SET txt_data.WRITE('102', 9, NULL)

WHERE custid = 102;

c. If @offset is NULL and @length is 0, @expression is simply appended at the end. For example, the following code appends the string ' is discontinued' at the end, resulting in the string 'Customer 102 is discontinued':

UPDATE dbo.CustomerData

SET txt_data.WRITE(' is discontinued', NULL, 0)

WHERE custid = 102;

d. If @expression is an empty string, no data is inserted; rather, you just remove a substring at the @offset position in the size of @length. For example, the following code removes 4 characters at the ninth position:

-- Removing 4 characters beginning at position 9

UPDATE dbo.CustomerData

SET txt_data.WRITE('', 9, 4)

WHERE custid = 102;

11. SQL Server supports the BULK engine to load file data as a rowset or as a single LOB value. For example, the following code returns the data from a file called shippers.txt as a row set, based on the format file shippers.fmt:

SELECT ShipperID, CompanyName, Phone

FROM OPENROWSET(BULK 'c:/temp/shippers.txt',

FORMATFILE = 'c:/temp/shippers.fmt') AS S;

12. The format file is the same format file you're familiar with when working with bcp.exe or BULK INSERT. Besides FORMATFILE, you can also specify other bulk options: CODEPAGE, ERRORFILE, FIRSTROW, LASTROW, MAXERRORS and ROWS_PER_BATCH. In such an INSERT statement, you can control load options using table hints, including KEEPIDENTITY, KEEP-DEFAULTS, IGNORE_CONSTRAINTS, IGNORE_TRIGGERS and TABLOCK. The following code is an example for loading the content of a file called shipper.txt into the target Shipper table, using the shippers.fmt format file:

INSERT INTO dbo.Shippers WITH (IGNORE_CONSTRAINTS)

SELECT ShipperID, CompanyName, Phone

FROM OPENROWSET(BULK 'c:/temp/shippers.txt',

FORMATFILE = 'c:/temp/shippers.fmt') AS S;

13. It is also support to use OPENROWSET function and specify the BULK option, the source file name, and one of three options for the type of data: SINGLE_CLOB for regular character data, SINGLE_NCLOB for Unicode data, and SINGLE_BLOB for binary data. The following code show a simply usage.

UPDATE dbo.CustomerData

SET txt_data = (SELECT txt_data FROM OPENROWSET(

BULK 'c:/temp/textfile101.txt', SINGLE_CLOB) AS F(txt_data)),

ntxt_data = (SELECT ntxt_data FROM OPENROWSET(

BULK 'c:/temp/unicodefile101.txt', SINGLE_NCLOB) AS F(ntxt_data)),

binary_data = (SELECT binary_data FROM OPENROWSET(

BULK 'c:/temp/binaryfile101.jpg', SINGLE_BLOB) AS F(binary_data))

WHERE custid = 101;

14. CASE expression, the result type is determined by the highest datatype in precedence among the possible result expressions, the following code is a wrong example, it will report a conversion error in runtime, for ‘abc’ can not convert to DECIMAL, DECIMAL is the highest precedence datatype (to solve this problem, can convert all return type to a common type, such as VARCHAR or SQL_VARIANT).

SELECT

CASE

WHEN 1 > 1 THEN 10

WHEN 1 = 1 THEN 'abc'

WHEN 1 < 1 THEN 10.

END;

15. To add to the confusing of implicit conversion, SQL_VARIANTs compare differently from regular datatypes. When comparing regular datatypes, the lower in precedence is implicitly converted to the higher, for example, DECIMAL is considered lower than FLOAT, 12.0 will great than 10E. However, with SQL_VARIANTs, if the “datatype family” is higher in the datatype hierarchy that in itself is sufficient to determine that the value is greater, regardless of whether it really is. So, CAST(12.0 AS SQL_VARIANT) is smaller than CAST(10E AS SQL_VARIANT).

16. When both side of the expression have the same datatype, the expression is considered a Search Argument (SARG), and the optimizer can consider the potential of using an index on col1, assuming that one exists, based on the selectivity of the query. If you perform manipulation on the base column (f(col1) <operator> <scalar_expression>), it is no longer considered a SARG.

17. Some concepts of CLR-based UDT:

a. In SQL Server you can create CLR-based User-Defined Types (UDT) using a .Net language.

b. Domain-Key normal form (DKNF): a relation is in Domain/Key Normal Form if every constraint on the relation is a logical consequence of the definition of keys and domains.

c. A relation is a set.

d. An UDT must also support conversions to and from character string to facilitate data input and presentation. Microsoft requires every UDT to implement default accessor and mutator specific methods for this: ToString and Parse. The most important attribute is Microsoft.SqlServer.SqlUserDefinedTypeAttribute, which defines the behavior of the type itself, this attribute is required.

e. Deploying the UDT with CLR-Based, the sample codes show the sequence.

-- Enable the CLR

USE master;

EXEC sp_configure 'clr enabled', 1;

RECONFIGURE;

-- Create Database

CREATE DATABASE Clrtest;

USE Clrtest;

-- Create assembly

CREATE ASSEMBLY ComplexNumberCS

FROM ' /ComplexNumberCS/ComplexNumberCS/bin/Debug/ComplexNumberCS.dll'

WITH PERMISSION_SET = SAFE;

-- Create CLR-based type

CREATE TYPE dbo.ComplexNumberCS

EXTERNAL NAME ComplexNumberCS.[ComplexNumberCS];

-- Create table

CREATE TABLE dbo.CNUsage

(

id INT IDENTITY(1,1) NOT NULL,

cn ComplexNumberCS NULL

);

-- Correct values

INSERT INTO dbo.CNUsage(cn) VALUES('(2,3i)');

INSERT INTO dbo.CNUsage(cn) VALUES('(1,7i)');

-- Now an incorrect valueINSERT INTO dbo.CNUsage(cn) VALUES('(1i,7)');

18. SQL Server 2005 supports a XML data type, the following codes show how to create, and display the XML type data.

-- Create the test table

CREATE TABLE dbo.VisioDocs

(

id INT NOT NULL,

doc XML NOT NULL

);

-- Insert the data

INSERT INTO dbo.VisioDocs (id, doc)

SELECT 1, *

FROM OPENROWSET(BULK 'C:/VisioFiles/ProductORM.vdx',

SINGLE_BLOB) AS x;

INSERT INTO dbo.VisioDocs (id, doc)

SELECT 2, *

FROM OPENROWSET(BULK 'C:/VisioFiles/ProductER.vdx',

SINGLE_BLOB) AS x;

INSERT INTO dbo.VisioDocs (id, doc)

SELECT 3, *

FROM OPENROWSET(BULK 'C:/VisioFiles/ProductUML.vdx',

SINGLE_BLOB) AS x;

INSERT INTO dbo.VisioDocs (id, doc)

SELECT 4, *

FROM OPENROWSET(BULK 'C:/VisioFiles/CustomerER.vdx',

SINGLE_BLOB) AS x;

-- Selection case 1

SELECT id,

doc.value('declare namespace VI=

"http://schemas.microsoft.com/visio/2003/core";

(/VI:VisioDocument/VI:DocumentProperties/VI:Company)[1]',

'NVARCHAR(50)') AS company

FROM dbo.VisioDocs;

-- Selection case 2

SELECT id, 'ER DB Model' AS templatetype

FROM dbo.VisioDocs

WHERE doc.value(

'declare namespace VI="http://schemas.microsoft.com/visio/2003/core";

(/VI:VisioDocument/VI:DocumentProperties/VI:Template)[1]',

'nvarchar(100)') LIKE N'%DBMODL_U.VST%';

-- Selection case 3

SELECT doc.query('

declare namespace VI="http://schemas.microsoft.com/visio/2003/core";

for $v in /VI:VisioDocument/VI:DocumentSettings

return $v') AS settings

FROM dbo.VisioDocs;

19. SQL Server supports two types of temporary tables: local and global. Temporary tables are created in tempdb, regardless of the database context of your session. Temporary table names are prefixed with a number symbol (#), a temporary table is owned by the creating session and visible only to it. And, SQL Server allows different sessions to create a temporary table with the same name, because system automatic add unique numeric suffix to table name, though it can refer it by the name you created it.

20. Global temporary tables differ from local ones mainly in their scope and visibility. They are accessible by all sessions. Global temporary table name prefixed with two number symbols (##), and like local temporary tables, they are also created in tempdb.

21. Table variables are probably among the least understood T-SQL elements, it can not be altered after created, the SELECT INTO and INSERT EXEC statements also can not be used. There is a simply example as below:

DECLARE @T1 TABLE(col1 INT);

INSERT @T1 VALUES(1);

SELECT * FROM @T1;

22. You should use table expressions in cases where you need a temporary object mainly for simplification. You should also use table expressions when you need to access the temporary object only once or a very small number of times and you don’t need to index interim result sets.

SELECT O.OrderID, O.EmployeeID, O.CustomerID, O.OrderDate

FROM dbo.Orders AS O

JOIN (SELECT EmployeeID, MAX(OrderID) AS MaxOid

FROM dbo.Orders

GROUP BY EmployeeID) AS D

ON O.OrderID = D.MaxOid;

23. A sample to using cursor, before running the code of the cursor, makes sure to turn off the ‘Discard results after execution’.

SET NOCOUNT ON;

USE tempdb;

IF OBJECT_ID('dbo.T1') IS NOT NULL

DROP TABLE dbo.T1;

SELECT n AS keycol, CAST('a' AS CHAR(200)) AS filler

INTO dbo.T1

FROM dbo.Nums;

CREATE UNIQUE CLUSTERED INDEX idx_keycol ON dbo.T1(keycol);

DBCC DROPCLEANBUFFERS;

DECLARE @keycol AS INT, @filler AS CHAR(200);

DECLARE C CURSOR FAST_FORWARD FOR SELECT keycol, filler FROM dbo.T1;

OPEN C

FETCH NEXT FROM C INTO @keycol, @filler;

WHILE @@fetch_status = 0

BEGIN

-- Process data here

FETCH NEXT FROM C INTO @keycol, @filler;

END

CLOSE C;

DEALLOCATE C;

24. We can also deal each row data individual.

DECLARE @keycol AS INT, @filler AS CHAR(200);

SELECT @keycol = keycol, @filler = filler

FROM (SELECT TOP (1) keycol, filler

FROM dbo.T1

ORDER BY keycol) AS D;

WHILE @@rowcount = 1

BEGIN

-- Process data here

-- Get next row

SELECT @keycol = keycol, @filler = filler

FROM (SELECT TOP (1) keycol, filler

FROM dbo.T1

WHERE keycol > @keycol

ORDER BY keycol) AS D;

END

25. Microsoft SQL Server provides you with two commands that invoke code strings that you construct dynamically—EXEC (short for EXECUTE) and sp_executesql. Note that the EXEC command has two uses; one to invoke a stored procedure: EXEC <procedure name and arguments>, and the other to invoke dynamic code: EXEC(<string>). And sp_executesql is preferable because it has an interface (input/output parameters) and EXEC doesn’t.

26. It is a good practice to always construct the code into a variable, where such limitations don’t apply, and then provide the variable name as input to the EXEC command.

27. The syntax for using sp_executesql is as follows:

EXEC sp_executesql

@stmt = <statement>, -- similar to proc's body

@params = <params>, -- similar to proc's params declaration

<params assignment> -- like in a procedure call

28. A simple example to show how to use the sp_executesql:

DECLARE @i AS INT;

SET @i = 10248;

DECLARE @sql AS NVARCHAR(46);

SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = @oid;';

EXEC sp_executesql

@stmt = @sql,

@params = N'@oid AS INT',

@oid = @i;

29. Concept: Common table expression [CTE]; user-defined function [UDF].

30. The following code showing how to create a view.

SET NOCOUNT ON;

USE Northwind;

IF OBJECT_ID('dbo.VCustsWithOrders') IS NOT NULL

DROP VIEW dbo.VCustsWithOrders;

CREATE VIEW dbo.VCustsWithOrders

SELECT CustomerID, CompanyName, ContactName, ContactTitle,

Address, City, Region, PostalCode, Country, Phone, Fax

FROM Customers AS C

WHERE EXISTS

(SELECT * FROM dbo.Orders AS O

WHERE O.CustomerID = C.CustomerID);

31. Though the use of * is generally a bad practice, you can use it safely with the EXISTS predicate, the optimizer knows that the EXISTS predicate does not refer to a particular attribute from the row. The following code shows that the system didn’t evaluate the expression and run without error, otherwise, it raise an exception.

IF EXISTS(SELECT 1/0) PRINT 'no error';

32. If need use ORDER BY in the view definition, the TOP or FOR XML is specified.

33. After applying such schema changes, it is a good practice to refresh the view’s metadata information using the sp_refreshview stored procedure so that the changes will be reflected in the view.

34. A sample shows how to create a view.

IF OBJECT_ID('dbo.VTrends') IS NOT NULL

DROP VIEW dbo.VTrends;

CREATE VIEW dbo.VTrends

SELECT

CONVERT(VARCHAR(6), MIN(mnth), 112) AS start_range,

CONVERT(VARCHAR(6), MAX(mnth), 112) AS end_range,

CASE sgn

WHEN -1 THEN 'down'

WHEN 0 THEN 'same'

WHEN 1 THEN 'up'

ELSE 'unknown'

END AS trend

FROM dbo.VGrp

GROUP BY sgn, grp

35. When you create or alter a view, you can specify options that will control the view’s behavior and functionality. The options ENCRYPTION, SCHEMABINDING, and VIEW_METADATA are specified in the view’s header, and the CHECK OPTION is specified after the query.

36. Both T-SQL and CLR UDFs can be created with a new EXECUTE AS clause, which lets you define the security context of the execution of the function. It is a good practice to create all your UDFs with both SCHEMABINDING and RETURNS NULL ON NULL INPUT options when it is the desired behavior.

37. Scalar UDFs return a single (scalar) value. Scalar UDFs must have a BEGIN/END block defining, must be schema qualified when invoked (unless invoked as stored procedures with EXEC, as in EXEC myFunction 3, 4), do not allow omitting optional parameters when invoked; rather, you must at least specify the DEFAULT keyword for those. There comes the example:

CREATE FUNCTION dbo.fn_ConcatOrders

(@cid AS NCHAR(5)) RETURNS VARCHAR(8000)

BEGIN

DECLARE @orders AS VARCHAR(8000);

SET @orders = '';

SELECT @orders = @orders + CAST(OrderID AS VARCHAR(10)) + ';'

FROM dbo.Orders

WHERE CustomerID = @cid;

RETURN @orders;

END

38. Scalar UDFs can be used in DEFAULT constraints, the only limitation is that a UDF cannot accept columns from the table as inputs when used in a DEFAULT constraint. The following example shows how to define a function to get default ID key:

CREATE TABLE dbo.T1

(

keycol INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY CHECK (keycol > 0),

datacol VARCHAR(10) NOT NULL

);

IF OBJECT_ID('dbo.fn_T1_getkey') IS NOT NULL

DROP FUNCTION dbo.fn_T1_getkey;

CREATE FUNCTION dbo.fn_T1_getkey() RETURNS INT

BEGIN

RETURN

CASE

WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1

ELSE (SELECT MIN(keycol + 1)

FROM dbo.T1 AS A

WHERE NOT EXISTS

(SELECT *

FROM dbo.T1 AS B

WHERE B.keycol = A.keycol + 1))

END;

END

ALTER TABLE dbo.T1 ADD DEFAULT(dbo.fn_T1_getkey()) FOR keycol;

39. UDFs used in CHECK constraints are allowed to refer to columns from the table as inputs. And the UDFs defined in UNIQUE or PRIMARY KEY constraint must be defined with SCHEMABINDING option.

40. Check a database is existed or not.

IF DB_ID('CLRUtilities') IS NOT NULL

DROP DATABASE CLRUtilities;

CREATE DATABASE CLRUtilities;

41. Table-valued UDFs are UDFs that return a table and are typically specified in the FROM clause of an outer query. Inline table-valued UDFs are similar to views expect it support the input parameters. Here comes an example.

CREATE FUNCTION dbo.fn_GetCustOrders

(@cid AS NCHAR(5)) RETURNS TABLE

RETURN

SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,

ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,

ShipRegion, ShipPostalCode, ShipCountry

FROM dbo.Orders

WHERE CustomerID = @cid;

42. Like views, inline UDFs can be a target of a modification statement. Any DML permission on the function to users can be assigned.

BEGIN TRAN

SELECT OrderID, ShipVia FROM fn_GetCustOrders(N'ALFKI') AS O;

UPDATE fn_GetCustOrders(N'ALFKI') SET ShipVia = 2;

SELECT OrderID, ShipVia FROM fn_GetCustOrders(N'ALFKI') AS O;

43. Split array code, useful codes. A key point is that you must create a table name Nums first before using this code:

CREATE FUNCTION dbo.fn_SplitTSQL

(@string NVARCHAR(MAX), @separator NCHAR(1) = N',') RETURNS TABLE

RETURN

SELECT

n - LEN(REPLACE(LEFT(s, n), @separator, '')) + 1 AS pos,

SUBSTRING(s, n,

CHARINDEX(@separator, s + @separator, n) - n) AS element

FROM (SELECT @string AS s) AS D

JOIN dbo.Nums

ON n <= LEN(s)

AND SUBSTRING(@separator + s, n, 1) = @separator;

44. Nondeterministic functions are functions that are not guaranteed to return the same output when invoked multiple times with the same input, such as RAND, GETDATE, and NEWID.

45. Unlike UDFs, stored procedures are allowed to have side effects, that is, they are allowed to change data in tables, and even the schema of objects. An example as below:

IF OBJECT_ID('dbo.usp_GetSortedShippers') IS NOT NULL

DROP PROC dbo.usp_GetSortedShippers;

-- Stored procedure usp_GetSortedShippers

-- Returns shippers sorted by requested sort column

CREATE PROC dbo.usp_GetSortedShippers

@colname AS sysname = NULL

DECLARE @msg AS NVARCHAR(500);

-- Input validation

IF @colname IS NULL

BEGIN

SET @msg = N'A value must be supplied for parameter @colname.';

RAISERROR(@msg, 16, 1);

RETURN;

END

IF @colname NOT IN(N'ShipperID', N'CompanyName', N'Phone')

BEGIN

SET @msg =

N'Valid values for @colname are: '

+ N'N''ShipperID'', N''CompanyName'', N''Phone''.';

RAISERROR(@msg, 16, 1);

RETURN;

END

-- Return shippers sorted by requested sort column

IF @colname = N'ShipperID'

SELECT ShipperID, CompanyName, Phone

FROM dbo.Shippers

ORDER BY ShipperID;

ELSE IF @colname = N'CompanyName'

SELECT ShipperID, CompanyName, Phone

FROM dbo.Shippers

ORDER BY CompanyName;

ELSE IF @colname = N'Phone'

SELECT ShipperID, CompanyName, Phone

FROM dbo.Shippers

ORDER BY Phone;

46. Stored procedures like the interfaces, so, it will be a good solution to hind the access on the table directly, instead of add the execution of procedures.

DENY SELECT ON dbo.Shippers TO user1;

GRANT EXECUTE ON dbo.usp_GetSortedShippers TO user1;

47. Special stored procedures means a stored procedure created with a name beginning with sp_in the master database, and they has special behavior. Temporary procedures can be defined by prefixing their name with a single number symbol – local temporary or a double one – global temporary (# or ##).

48. The SET NOCOUNT ON option tells SQL Server not to produce the message saying how many rows were affected for data manipulation language (DML) statements.

49. There are two formats for assigning values to parameters when invoking a stored procedure: unnamed and named. In the unnamed format, you just specify the inputs by declaration order, the parameters with default values and defined in the end of the parameter list can be omitted, like C++. And you also can specify the DEFAULT keyword to that parameter with default value.

50. @@rowcount contains the number of rows affected by the query. @@error contains the error value of the query.

51. A stored procedure can generate other types of outputs as well, including the output of PRINT and RAISERROR commands. Both would be received by the client through the client interface’s structures – for example, the Errors collection in ADO.

52. A stored procedure will reuse a previously cached execution plan by default. You can observe the fact that an execution plan was reused by querying the sys.syscacheobjects system view (or master.dbo.syscacheobjects in SQL Server 2000), which contains information about execution plans, here is an example for usp_GetOrders:

SELECT cacheobjtype, objtype, usecounts, sql

FROM sys.syscacheobjects

WHERE sql NOT LIKE '%cache%'

AND sql LIKE '%usp_GetOrders%';

53. The RECOMPILE option used in stored procedure definition tells SQL Server to create a new execution plan every time it is invoked. SQL Server 2005 supports statement-level recompile, SQL Server 2000 supports at whole procedure level.

54. By default, the SET option CONCAT_NULL_YIELDS_NULL is turned ON, meaning that when you concatenate a NULL with any string, you get a NULL as a result.

55. When you create the stored procedure, you can specify an EXECUTE AS clause with one of the following options: CALLER (default), SELF, OWNER, ‘user_name’.

56. An example to support dynamic pivot in stored procedure:

CREATE PROC dbo.usp_pivot

@schema_name AS sysname = N'dbo', -- schema of table/view

@object_name AS sysname = NULL, -- name of table/view

@on_rows AS sysname = NULL, -- group by column

@on_cols AS sysname = NULL, -- rotation column

@agg_func AS NVARCHAR(12) = N'MAX', -- aggregate function

@agg_col AS sysname = NULL -- aggregate column

DECLARE

@object AS NVARCHAR(600),

@sql AS NVARCHAR(MAX),

@cols AS NVARCHAR(MAX),

@newline AS NVARCHAR(2),

@msg AS NVARCHAR(500);

SET @newline = NCHAR(13) + NCHAR(10);

SET @object = QUOTENAME(@schema_name) + N'.' + QUOTENAME(@object_name);

-- Check for missing input

IF @schema_name IS NULL

OR @object_name IS NULL

OR @on_rows IS NULL

OR @on_cols IS NULL

OR @agg_func IS NULL

OR @agg_col IS NULL

BEGIN

SET @msg = N'Missing input parameters: '

+ CASE WHEN @schema_name IS NULL THEN N'@schema_name;' ELSE N'' END

+ CASE WHEN @object_name IS NULL THEN N'@object_name;' ELSE N'' END

+ CASE WHEN @on_rows IS NULL THEN N'@on_rows;' ELSE N'' END

+ CASE WHEN @on_cols IS NULL THEN N'@on_cols;' ELSE N'' END

+ CASE WHEN @agg_func IS NULL THEN N'@agg_func;' ELSE N'' END

+ CASE WHEN @agg_col IS NULL THEN N'@agg_col;' ELSE N'' END

RAISERROR(@msg, 16, 1);

RETURN;

END

-- Allow only existing table or view name as input object

IF COALESCE(OBJECT_ID(@object, N'U'),

OBJECT_ID(@object, N'V')) IS NULL

BEGIN

SET @msg = N'%s is not an existing table or view in the database.';

RAISERROR(@msg, 16, 1, @object);

RETURN;

END

-- Verify that column names specified in @on_rows, @on_cols, @agg_col exist

IF COLUMNPROPERTY(OBJECT_ID(@object), @on_rows, 'ColumnId') IS NULL

OR COLUMNPROPERTY(OBJECT_ID(@object), @on_cols, 'ColumnId') IS NULL

OR COLUMNPROPERTY(OBJECT_ID(@object), @agg_col, 'ColumnId') IS NULL

BEGIN

SET @msg = N'%s, %s and %s must'

+ N' be existing column names in %s.';

RAISERROR(@msg, 16, 1, @on_rows, @on_cols, @agg_col, @object);

RETURN;

END

-- Verify that @agg_func is in a known list of functions

-- Add to list as needed and adjust @agg_func size accordingly

IF @agg_func NOT IN

(N'AVG', N'COUNT', N'COUNT_BIG', N'SUM', N'MIN', N'MAX',

N'STDEV', N'STDEVP', N'VAR', N'VARP')

BEGIN

SET @msg = N'%s is an unsupported aggregate function.';

RAISERROR(@msg, 16, 1, @agg_func);

RETURN;

END

-- Construct column list

SET @sql =

N'SET @result = ' + @newline +

N' STUFF(' + @newline +

N' (SELECT N'','' + '

+ N'QUOTENAME(pivot_col) AS [text()]' + @newline +

N' FROM (SELECT DISTINCT('

+ QUOTENAME(@on_cols) + N') AS pivot_col' + @newline +

N' FROM ' + @object + N') AS DistinctCols' + @newline +

N' ORDER BY pivot_col' + @newline +

N' FOR XML PATH('''')),' + @newline +

N' 1, 1, N'''');'

EXEC sp_executesql

@stmt = @sql,

@params = N'@result AS NVARCHAR(MAX) OUTPUT',

@result = @cols OUTPUT;

-- Check @cols for possible SQL injection attempt

IF UPPER(@cols) LIKE UPPER(N'%0x%')

OR UPPER(@cols) LIKE UPPER(N'%;%')

OR UPPER(@cols) LIKE UPPER(N'%''%')

OR UPPER(@cols) LIKE UPPER(N'%--%')

OR UPPER(@cols) LIKE UPPER(N'%/*%*/%')

OR UPPER(@cols) LIKE UPPER(N'%EXEC%')

OR UPPER(@cols) LIKE UPPER(N'%xp_%')

OR UPPER(@cols) LIKE UPPER(N'%sp_%')

OR UPPER(@cols) LIKE UPPER(N'%SELECT%')

OR UPPER(@cols) LIKE UPPER(N'%INSERT%')

OR UPPER(@cols) LIKE UPPER(N'%UPDATE%')

OR UPPER(@cols) LIKE UPPER(N'%DELETE%')

OR UPPER(@cols) LIKE UPPER(N'%TRUNCATE%')

OR UPPER(@cols) LIKE UPPER(N'%CREATE%')

OR UPPER(@cols) LIKE UPPER(N'%ALTER%')

OR UPPER(@cols) LIKE UPPER(N'%DROP%')

-- look for other possible strings used in SQL injection here

BEGIN

SET @msg = N'Possible SQL injection attempt.';

RAISERROR(@msg, 16, 1);

RETURN;

END

-- Create the PIVOT query

SET @sql =

N'SELECT *' + @newline +

N'FROM' + @newline +

N' ( SELECT ' + @newline +

N' ' + QUOTENAME(@on_rows) + N',' + @newline +

N' ' + QUOTENAME(@on_cols) + N' AS pivot_col,' + @newline +

N' ' + QUOTENAME(@agg_col) + N' AS agg_col' + @newline +

N' FROM ' + @object + @newline +

N' ) AS PivotInput' + @newline +

N' PIVOT' + @newline +

N' ( ' + @agg_func + N'(agg_col)' + @newline +

N' FOR pivot_col' + @newline +

N' IN(' + @cols + N')' + @newline +

N' ) AS PivotOutput;';

EXEC sp_executesql @sql;

57. Triggers allow you to automate the process of reacting to statement issued by users and application (AFTER triggers) or substituting the original statement with your own code (INSTEAD OF triggers).

58. AFTER trigger fire after the firing statement has already taken place. AFTER triggers are fired per statement, not per row, and multiple AFTER triggers can created on each object for each statement.

59. With DML triggers, you can access the old and new image of the affected rows through special tables called inserted and deleted. The inserted table contains the new image of the affected rows, and deleted table contains the old image. An example:

CREATE TRIGGER trg_T1_i ON T1 FOR INSERT

DECLARE @rc AS INT;

SET @rc = @@rowcount;

IF @rc = 0 RETURN;

DECLARE @keycol AS INT, @datacol AS VARCHAR(10);

IF @rc = 1 -- single row

BEGIN

SELECT @keycol = keycol, @datacol = datacol FROM inserted;

PRINT 'Handling keycol: '

+ CAST(@keycol AS VARCHAR(10))

+ ', datacol: ' + @datacol;

END

ELSE -- multi row

BEGIN

SELECT * INTO #I FROM inserted;

CREATE UNIQUE CLUSTERED INDEX idx_keycol ON #I(keycol);

SELECT @keycol = keycol, @datacol = datacol

FROM (SELECT TOP (1) keycol, datacol

FROM #I

ORDER BY keycol) AS D;

WHILE @@rowcount > 0

BEGIN

PRINT 'Handling keycol: '

+ CAST(@keycol AS VARCHAR(10))

+ ', datacol: ' + @datacol;

SELECT @keycol = keycol, @datacol = datacol

FROM (SELECT TOP (1) keycol, datacol

FROM #I

WHERE keycol > @keycol

ORDER BY keycol) AS D;

END

60. You can disable a trigger completely using an ALTER TABLE DISABLE TRIGGER command.

61. Logically, context info is a VARBINARY (128) variable owned by this session, you can use CONTEXT_INFO function to get it or using SET CONTEXT_INFO to set its value.

62. The UPDATE predicate accepts a column name as input and return TRUE if the input column was specified in the SET clause of the firing UPDATE statement. The COLUMNS_UPDATED function returns a binary string with a bit of each column.

63. INSTEAD OF triggers fire instead of the original modification that was issued against the target object. The original statement will never reaches the target object, rather, the trigger replace it. An example:

CREATE TRIGGER trg_T1_i ON T1 AFTER INSERT

DECLARE @msg AS VARCHAR(100);

SET @msg = 'Key: '

+ CAST((SELECT keycol FROM inserted) AS VARCHAR(10)) + ' inserted.';

PRINT @msg;

64. Transactions allow you to define a unit of activity that will be considered atomic – all or nothing. The aforementioned aspects of transaction are known as ACID (Atomicity, Consistency, Isolation, and Durability). Using BEGIN TRAN/COMMIT TRAN block to define a transaction.

65. SQL Server doesn’t automatically roll back a transaction as a result of any failure. If you want all errors to cause a rollback of a transaction, set the XACT_ABORT session option to ON.

66. Locks can be obtained on resources at different granularity levels of data. The smallest granularity of data is the row level. If a row of a heap is locked, the locked resource is a row identifier (RID). If a row in an index is locked, the locked resource is a key. SQL Server automatically chooses which resource type to lock (ROWLOCK, PAGLOCK, and TABLOCK). The lock mode determines how resource can be accessed by concurrent transactions, such as exclusive (X) lock, shared (S) lock, update (U) lock.

67. To troubleshoot blocking scenarios, SQL Server 2005 gives you a whole array of new dynamic management view (DMV) and functions (DMF). For example, the sys.dm_tran_locks view gives you information about locks. Query the sys.dm_exec_connections view to obtain information about the connections involved in the conflict (connect_time, last_read, last_write, most_recent_sql_handle, etc.).

-- Lock info

SELECT

request_session_id AS spid,

resource_type AS restype,

resource_database_id AS dbid,

resource_description AS res,

resource_associated_entity_id AS resid,

request_mode AS mode,

request_status AS status

FROM sys.dm_tran_locks;

68. The sys.dm_exec_connections view contains a binary handle that you can provide to the function sys.dm_exec_sql_text to get the code text of the last request. In SQL Server 2000, you use the DBCC INPUTBUFFER command or the fn_get_sql function to get the code buffer of a session.

69. Isolation levels allow you to control the consistency level that you will get when manipulating data. SQL Server 2000 support 4 levels (read uncommitted, read committed, repeatable read, and serializable), SQL Server 2005 add 2 levels (snapshot and read committed snapshot).

70. The END