#mssql2008

Originally posted on April 11, 2013 by Daniel James

Quick query to determine which columns of a table must be assigned values on INSERT:

SELECT OBJECT_NAME(c.object_id), c.name FROM sys.COLUMNS c LEFT JOIN sys.default_constraints dc ON (c.default_object_id = dc.object_id) WHERE c.is_nullable = 0 AND dc.object_id IS NULL AND c.is_identity = 0 AND c.object_id = OBJECT_ID('Table Name') ORDER BY 1, 2

Originally posted on November 8, 2012 by Daniel James

Originally posted on November 5, 2012 by Daniel James

Here’s a neat tidbit I found recently while working deep in some ETL scripts written in T-SQL: You can use RAISERROR with a severity of 0 as you would use PRINT in order to print out informational messages, and RAISERROR has a nifty option to write the message out immediately to the client. (Unlike PRINT, which waits for various things before printing messages.)

For example, the following each print out This is a very informational message.

PRINT 'This is a very informational message.'   RAISERROR('This is a very informational message.', 0, 0) WITH NOWAIT

Originally posted on February 24, 2012 by Daniel James

Have you ever found yourself working in T-SQL and thought, “Query parameters are great for individual values, but what I really want is a way to pass an array–or even a table–into my query.” I have, and I was pleased to discover that starting in SQL Server 2008, a feature called Table-Valued Parameters (TVP) exists.

With TVP, it becomes possible to pass in a table variable to a query or stored procedure, so long as a proper table type has been defined for it. Table types are simply stored table schemas that may be used to create a table variable. For example, the following two T-SQL statements produce a table variable with the same schema.

DECLARE @table1 TABLE ( tableId INT NOT NULL, VALUE nvarchar(64) )   CREATE TYPE tableType AS TABLE ( tableId INT NOT NULL, VALUE nvarchar(64) )   DECLARE @table2 tableType -- Has the same columns, tableId and value, as @table1

When using TVPs in stored procedures, the table type must be used as the type for the parameter because the verbose TABLE (...) syntax is not allowed.

CREATE PROCEDURE proc1 (@TABLE tableType) AS BEGIN SELECT tableId, VALUE FROM @TABLE END

This is all great if you are working entirely in T-SQL because it’s possible to build and pass around table variables with ease. However, in other programming languages that communicate with SQL Server, it is a little less apparent how to work with TVPs. However, in .NET, this is made possible by passing DataTables into query parameters.

/* Build the DataTable */ DataTable table = new DataTable(); table.Columns.Add("tableId", typeof(int)); table.Columns.Add("value", typeof(string));   /* Populate the DataTable with some rows */ DataRow row = table.NewRow(); row["tableId"] = 1, row["value"] = "foobar"; table.Rows.Add(row);   SqlCommand command; // Set up as normal command.CommandText = "proc1";   SqlParameter tableParam = command.Parameters.Add("@table", SqlDbType.Structured); // Inform the command that the parameter is a TVP, or "structured" type tableParam.Value = table; tableParam.TypeName = "tableType"; // Set the parameter's type name to the table type declared in T-SQL earlier /* ... Execute command as desired ... */