Archives

Table Value Constructors were introduced in SQL Server 2008 and enables you to (not only) insert more records with one INSERT statement.

“Standard” option to insert more records is following:

INSERT INTO Continents (Name) VALUES ('Asia')
INSERT INTO Continents (Name) VALUES ('Africa')

With Table Value Constructors you can do this:

INSERT INTO Continents (Name)
VALUES ('Asia'),('Africa')

I must admin that I like it but there’s more. Sometimes you need list of constants in temporary set to be used just within your batch – I was always doing something similar to this (if there was only few values):

SELECT 'Africa' 
UNION ALL 
SELECT 'Asia'

With Table Value Constructors you can do this:

SELECT * 
    FROM (VALUES ('Asia'), ('Africa')) AS Continents (Name)

You can even use subquery within VALUES command. Check full reference and limitations of Table Value Constructors on Technet.

Jakub Dvorak @ www.sqltreeo.com

It is known that you cannot get inserted identity values using OUTPUT clause when inserting to view which has instead of trigger on it. I would like to offer another workaround I was used recently.

I am talking about fact that you will not succeed with this:

    -- inserting records via vw_Customer which has instead of trigger on itself. 
    -- Underlying Customer table has Id column defined as IDENTITY.
    INSERT INTO vw_Customer ( Name , Value_FilledBy_Instead_Of_Trigger )
        OUTPUT inserted.Id, inserted.Name, inserted.Value_FilledBy_Instead_Of_Trigger
        INTO @someTable
        SELECT Name, Value
            FROM someOtherTable

Result is that you don’t have identity values in output @someTable. This is behavior described e.g. here or discussed on Microsoft Connect here.

I used workaround with process-keyed table and process-keyed view which encapsulates reading/inserting of keyed records. It is similar workaround as using #table but I dislike #table management within triggers.

Check this commented code:

/*
-- drops 
DROP TABLE Customer 
DROP VIEW vw_Customer
DROP TABLE PK_Customer_TriggerScope
DROP VIEW vw_Customer_TriggerScope
*/

-- create customer table
CREATE TABLE Customer (id INT IDENTITY (1,1) NOT NULL, Name VARCHAR(20) NOT NULL, Value_FilledBy_Instead_Of_Trigger VARCHAR(20) NOT NULL)
GO

-- create customer view
CREATE VIEW vw_Customer
AS
SELECT * FROM Customer
GO

-- create process keyed table for usage in instead of trigger
CREATE TABLE PK_Customer_TriggerScope ( SPID INT, Id INT, Name VARCHAR(20), Value_FilledBy_Instead_Of_Trigger VARCHAR(20) )
GO

-- add default constraint for process keyed table to ensure that SPID is filled/filtered automatically
ALTER TABLE PK_Customer_TriggerScope ADD  CONSTRAINT DF_PK_Customer_TriggerScope_SPID  DEFAULT (@@spid) FOR [SPID]
GO

-- create view on process keyed table to encapsulate records process keying 
CREATE VIEW vw_Customer_TriggerScope
AS 
SELECT * FROM PK_Customer_TriggerScope WHERE SPID = @@SPID
GO

-- create instead of insert trigger on customer view
CREATE TRIGGER tg_IOI_vw_Customer ON vw_Customer INSTEAD OF INSERT
AS
BEGIN

    -- flush records keyed for this process 
    DELETE FROM vw_Customer_TriggerScope
    
    -- do customer inserts and fill PK_Customer_TriggerScope with output keyed for this process
    -- unfortunately you cannot use view in output clause so PK_Customer_TriggerScope must be use directly
    INSERT INTO Customer ( Name , Value_FilledBy_Instead_Of_Trigger )
        OUTPUT @@SPID, inserted.Id, inserted.Name, inserted.Value_FilledBy_Instead_Of_Trigger
        INTO PK_Customer_TriggerScope 
        SELECT Name, 'Filled by IOI'
            FROM inserted

END
GO

-- Insert few customers and try to get identity 
INSERT INTO vw_Customer ( Name )
    SELECT TOP 10 name FROM msdb.sys.objects -- use sys.objects as list of customers

-- get inserted records from process keyed table 
-- it is ensured that now it contains only customer records inserted within instead of trigger
SELECT * FROM vw_Customer_TriggerScope

Jakub Dvorak @ www.sqltreeo.com

I was using user-defined tables types (UDTT) quite intensively despite of their current disadvantages especially their forced READONLY behavior when used as stored procedures parameter. Another disadvantage which I didn’t realize impact for is that UDTT cannot be altered. I hit maintenance nightmare when they became spread across my T-SQL code.

What I did is that I encapsulated  few business objects as UDTT and started to use them consistently in my T-SQL stored procedures and functions (directly in code and as input parameters as well). It works great, your code looks “strongly-typed” and it is definitely big step forward in T-SQL programming. But whenever I wanted to add or alter field in my UDTT, I had to drop/create all my procedures or functions which this UDTT depends on. It looks peaceful but it’s totally annoying because you have no built-in option to script references for UDTT and you have to do it manually or through “script database” task. If you are in development phase when everything changes from minute to minute, it’s unusable.

I wrote helper stored procedures which take UDTT name as only parameter and returns DROP and CREATE scripts of all references. It basically do what I’ve mentioned, it scripts all UDTT’s references and saves my time.

First is ap_FindReferences which finds all references and returns them as single result set. It would be enough to display this result set as text in SSMS (CTRL+T) but it’s not because SSMS cannot display strings long more than few thousands characters in results pane. That’s why there is second procedure ap_WriteReferences which do quite nasty job of writing result from ap_FindReferences into .sql file you can display in SSMS.

Usage is following:

EXEC ap_WriteReferences '<your user-defined table type>'

…result is text file defined within ap_WriteReferences with scripted references to your UDDT.

Good option is to bind this command to keyboard shortcut in SSMS.

Here are those two procedures (some notes follow):

-- Find all referencing objects to user-defined table type in @fullObjectName parameter
-- and generate DROP scripts and CREATE scripts for them
CREATE PROC ap_FindReferences (@fullObjectName VARCHAR(200))
AS
BEGIN
    SET NOCOUNT ON

    IF (TYPE_ID (@fullObjectName) IS NULL)
    BEGIN
        RAISERROR ('User-defined table type ''%s'' does not exists. Include full object name with schema.', 16,1, @fullObjectName)
        RETURN
    END;

    WITH sources
    AS
    (
        SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(m.object_id)) RowId, definition
        FROM sys.sql_expression_dependencies d
        JOIN sys.sql_modules m ON m.object_id = d.referencing_id
        JOIN sys.objects o ON o.object_id = m.object_id
        WHERE referenced_id = TYPE_ID(@fullObjectName)
    )

    SELECT 

        'DROP ' +
            CASE OBJECTPROPERTY(referencing_id, 'IsProcedure')
            WHEN 1 THEN 'PROC '
            ELSE
                CASE
                    WHEN OBJECTPROPERTY(referencing_id, 'IsScalarFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsInlineFunction') = 1 THEN 'FUNCTION '
                    ELSE ''
                END
            END
        + SCHEMA_NAME(o.schema_id) + '.' +
        + OBJECT_NAME(m.object_id)    

    FROM sys.sql_expression_dependencies d
    JOIN sys.sql_modules m ON m.object_id = d.referencing_id
    JOIN sys.objects o ON o.object_id = m.object_id
    WHERE referenced_id = TYPE_ID(@fullObjectName)
    UNION  ALL
    SELECT  'GO'
    UNION  ALL
    SELECT
        CASE
            WHEN number = RowId    THEN DEFINITION
            ELSE 'GO'
        END
     FROM sources s
    JOIN (SELECT DISTINCT number FROM master.dbo.spt_values) n ON n.number BETWEEN RowId AND RowId+1

END
GO

-- Invokes ap_FindReferences procedure and writes scripted result to .sql file 
CREATE PROC ap_WriteReferences
@typeToFind VARCHAR(200)
AS
BEGIN

    DECLARE @sqlCmd VARCHAR(500)
    DECLARE @database VARCHAR(200) = 'test'
    DECLARE @outputFile VARCHAR(500) = 'c:\refences.sql'

    SET @sqlCmd = 'sqlcmd.exe -d '+@database+' -q "EXEC ap_FindReferences '''+ @typeToFind +'''" -o '+ @outputFile +' -h-1 -y0'

    EXEC xp_cmdshell @sqlCmd

END

Please pay attention to these notes if you are going to use it:

• you must change your database name and file name in ap_WriteReferences procedure
• it uses sys.sql_modules view to find references, hence it may not have most current information. It worked for me so far but still…
• please use it carefully, it drops objects and it is intended to dedicated development databases only (and at your own risk )
• it works only on SQL Server 2008 and higher
• extended properties are not scripted, it will be forgotten related objects are dropped

If you know any better way how to avoid this maintenance issue with UDDT, surely let me know! Also let me know here if you want me to include it as feature to my “pocket” SSMS productivity add-in.

Red Gate’s SQL Source Control (RGSSC) is very decent solution source control solution for database development. I am personally using it and despite it is still young product it is very helpful for me and rest of team members. I am not going to review this tool but I am going to look closer how you can improve your experience with it using SQL Treeo SSMS Add-In.

RGSSC is Management Studio Add-In which enables developer to track changes over SQL database and commit/update changes to source control repository (TFS, SVN and others) directly from SQL Server Management Studio (SSMS). For me, using SSMS for database development is still better way comparing to Visual Studio 2010 Database Projects (VS.NET) because I simply not got used to many drawbacks which VS.NET provides from database development point of view. But I will return back to this later.

RGSSC was quite life saver for me because you have quite few options of source control if you’re developing over dedicated local database. To make my life more comfortable I’ve developed SQL Treeo Add-In which allows me to create complex hierarchy of folders directly under my database bound to SVN. This folder (or groups) hierarchy is shared across all team members because this database is under source control. You can see short sample video here.

So all team members see the same picture of logical modules directly under our development database (green folders are created using SQL Treeo  Add-In):

Logical groups for objects within SSMS standard nodes can be created for all basic SQL objects – tables, views, stored procedures, functions and even for databases.

Jakub Dvorak

Many years back when ASP 2.0 (not ASP.NET) was used as primary server language for web application on Microsoft platform, it was common that business logic was implemented using stored procedures on database side. Nowadays, this approach is used less and less especially because of.NET capabilities to act as full-featured application layer. Big step forward were also arise of various ORM tools (such as NHibernate) which did awful of creating CRUD operations completely for developer. I am against any generalization but I am not far from truth if I say that most of newly developed .NET software today uses some kind of object-relational mapping technique at least for very basic SQL operations such as insert, update and delete.

NHibernate is good option but there are still some concept disadvantages:

• Your domain object model must be very well designed and carefully mapped to relational data. ORM creates pressure to developer to sometimes “align” object model to relational model just because mapping could be done easier.
• It brings performance drawbacks especially during application startup
• You can easily misuse ORM without proper technical background of your ORM tool (such as nhibernate).
• You are limited what you can do with your SQL code. It applies to DDL and DML code as well.

If you are facing to complex SQL query you can use things like NHibernate criterias or  NHibernate Linq provider which may provide you way to build that kind of queries.

There are basically three scenarios where NHibernate is not helpful:

• Even more complex data retrieval query which you want to fine tune using e.g. SQL Server query hints.
• Any DML batch operation which affects more rows at one time
• Any complex application logic which require “batch approach” to your data and cannot be done “row-by-row” using your domain model manipulation and its mapping to relational data

For those scenarios, you must simply go lower in your application stack and deal with it directly in database. For first scenario you could use database views, for second and third you could use stored procedures mapped as named query in NHibernate.

Imagine even more complex application logic which must be implemented as stored procedures just because of performance – if you deploy your best developer skills such as encapsulation or reuse, you can deal with many stored procedures and functions (you can organize them with SQL Treeo Add-In in your SQL Server Management Studio).

I’ve identified very bad developer habit “do-it-at-application-layer-at-all-costs” which is that developer does even very complex SQL tasks using manipulating object model and ORM. Just not to dirty his code with some direct SQL. It inevitably leads to many row-by-tow operations and inefficient queries made by confused OR mapping components.

Head first – sometimes few stored procedures, functions or views at proper places could make better job than do-it-at-application-layer-at-all-costs.

Jakub Dvořák

I faced problem that I had many lookup tables like ProductType, ClientType etc. and wanted to refer to their values in code to base some logic on them. This is quite common.

I am talking about something like following:

SELECT @ClientType = ClientType, -- other columns
    FROM Client
    WHERE Id = @ClientId

IF (@clientType = 'SOMETYPE')
    -- do something

It is quite ok, but practice problem happened if you spread string constants representing lookup values over your T-SQL routines. This means that you have your ‘SOMETYPE’ constant defined on many places which is always not good for many reasons. If you would be in C# or other type-safe language you could afford to use constant static variables representing each lookup value and define their string values only at one place.

Such pseudo-hybrid code would look like this:

SELECT @ClientType = ClientType, -- other columns
    FROM Client
    WHERE Id = @ClientId

IF (@clientType = ClientType.SOMETYPE)
    -- do something

This is of course syntactically not correct in T-SQL but using CLR static variable might be good option.  There are few solutions how to implement “static” variables in T-SQL. Among better solutions belongs usage of UDF described e.g. here or very limited solution with CONTEXT_INFO described here.

I would like to describe other solution which uses SQL CLR User-Defined Type as static class for string constants. I am not going to describe what is CLR UDT and how to develop and deploy them, you can read it msdn.

First step is to write and deploy following UDT:

[Serializable()]
[SqlUserDefinedType(Format.UserDefined, MaxByteSize = -1)]
public struct ClientType : IBinarySerialize, INullable
{
    /// <summary>
    /// TypeA value
    /// 
    public static string TypeA { get { return "TYPEA"; } }

    ///
    /// TypeB value
    /// 
    public static string TypeB { get { return "TYPEB"; } }

    public void Read(BinaryReader r)
    {
        throw new NotImplementedException("Don't persist this UDT, it holds only constants.");
    }

    public void Write(BinaryWriter w)
    {
        throw new NotImplementedException("Don't persist this UDT, it holds only constants.");
    }

    public bool IsNull
    {
        get { throw new NotImplementedException(); }
    }

    public static ClientType Null
    {
        get
        {
            throw new NotImplementedException("Shouldn't have null value.");
        }
    }

    public override string ToString()
    {
        throw new NotImplementedException("Don't persist this UDT, it holds only constants.");
    }
    public static ClientType Parse(SqlString s)
    {
        throw new NotImplementedException("Don't persist this UDT, it holds only constants.");
    }

}

Second step is to use this UDT in your code:

SELECT @ClientType = ClientType, -- other columns
    FROM Client
    WHERE Id = @ClientId

IF (@clientType = ClientType::TypeA)
    -- do something

Now you have “strongly typed” lookup value used in your T-SQL code. Trick is that you can call static members of UDT from T-SQL by using “::” and completely avoid using string constants for lookups throughout your SQL code.

Jakub Dvorak

SSMS is abbreviation of SQL Server Management Studio, according to Microsoft description it is “an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SQL Server Management Studio combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels”.

Microsoft enables developers to enhance or alter features of their development tools (SSMS in this case) by creating “add-in” (or addin). Add-In is software component which is installed on same computer as SSMS and is fully integrated in dev. environment. Fully integrated means that your add-in is loaded when SSMS is starting and begin to do its job. SSMS addins are written in .NET and utilize SSMS internal object model (or “services”) to achieve their goal. One big issue on SSMS addins development is that mentioned object model (or its interfaces) sometimes dramatically differs from one SQL version to the other. It really depends on addin logic if “version inconsistent” interfaces will be hit.

Who is interested in more deeper implementation view, read e.g. this article.

Jakub Dvorak