Sql Server: What I Have Learned in December 2010

Dedicated to my inspiration Pinal Dave

 Performance   & Tuning

1.      Which Statement Triggered the Recompile Event

2.      Performance Counter to Count Stored Procedure Re-compilations

3.      Avoid LIKE Operator for Exact Searches

4.      Covering Index, A performance booster for query

5.      Does Your Sql Server Machine Need More RAM? Buffer Cache Hit Ratio Can Inform You.

Sql Server Internals:

1.      Negative Side Effects of Altering Tables

2.      Why We Can't Add New Column in Between Existing Columns

 Sql Server Security

1.      Find and Kill Specific User Session

2.      Hide Database Name from Specific User in SSMS

 Sql Server Integration Services

1.      Merge Join Problem

2.      FILESTREAM Data Export and Import By Using SSIS Package

Sql Server Tips & Tricks

1.      Generate Conditional Table Data Script

2.      Sequence to Create Error Free Objects Script for Whole Database

3.      Compare two strings on basis of how they sound

4.      ORDER BY column name Or ORDER BY column number

5.      Cautions Must Be Taken for WHERE Clause

6.      Common Misconceptions Regarding IDENTITY Columns

7.      Insert Explicit Value for Identity Column in Table

8.      Clean Query Plans and Buffer Pool before Query Performance Comparison

9.      The Magic of GO Command

10.  (Msg 101000) Cannot Create Index on View Because it Contains the DISTINCT Keyword

11.  Table Valued Function

Sql Server: Hide Database Name from Specific User in SSMS

Recently,I have tried my best to find out a way to hide name of databases from Sql Server Management Studio, which I don’t want to show to a specific user. But unfortunately I can’t find any proper way to achieve my goal.

You can hide all databases from a specific user by using following statement

USE MASTER

DENY VIEW ANY DATABASE TO [TargetUserNameHere];

And same way by using GRANT, you can allow your user to view ALL databases.  Optimum solution I have found is to hide database objects (even name of TABLES, VIEW, SPs & FUNCTIONS) ONLY, by using following statement

USE YourDatbaseNameHere

GO

DENY VIEW DEFINITION TO [TargetUserNameHere]

or you can use

DENY VIEW DEFINITION ON DATABASE:: YourDatbaseNameHere TO [TargetUserNameHere]

So is there any way to hide a specific database name & definition (NoT ALL DATABASES) from a specific user? 

Sql Server Performance: Which Statement Triggered the Recompile Event

In last post “Performance Counter to Count Stored Procedure Re-compilations” we have discussed about a useful counter of system performance counters i.e. SQL Re-Compilations/sec. In this post, we have discussed that if nonzero values are consistently occurring for this counter, we should seriously search for the culprit stored procedures and then statement with in that stored procedure.

Let me explain, how Sql Profiler can help us to find statement in a stored procedure which actually triggered the recompile event.

Open Sql Server Profiler and start a new trace but with following events

·         SP:Starting

·         SP:StmtStarting

·         SP:Recompile

·         SP:Completed

You can select these events by selecting check box “Show all events” (as mentioned in screen shot. Click “Run” button to start trace.

Meanwhile execute following query as an example

CREATE PROCEDURE proc_testRecompilation

AS

    CREATE TABLE #t ( a INT )

    SELECT  *

    FROM    #t

GO

EXEC proc_testRecompilation

Now shift your focus to Sql Profiler to examine trace, which must resembling following screen shot.

Watch closely the statement which appeared before and after the SP:Recompile. This is the statement in stored procedure which actually triggered recompilation. Once we have detected the culprit statement, we must find out the reason behind this happening. In our example statement triggered recompilation because of "Recompilations Due to Interleaving Data Definition Language (DDL) and Data Manipulation Language (DML) Operations". What are other reasons behind recompilation occurring read previous post.

Note: In our example you will only see the recompile event on the first execution of the procedure, or if you drop and re-create the procedure each time you execute the script.

Sql Server:Performance Counter to Count Stored Procedure Re-compilations

In an ideal situation a stored procedure is compiled once and forth coming queries related to this stored procedure are satisfied with already created query plan. The following actions may cause recompilations of a stored procedure plan:

• Use of a WITH RECOMPILE clause in the CREATE PROCEDURE or EXECUTE statement.
• Schema changes to any of the referenced objects, including adding or dropping constraints, defaults, or rules.
• Running sp_recompile for a table referenced by the procedure.
• Restoring the database containing the procedure or any of the objects the procedure references (if you are performing cross-database operations).
• Sufficient server activity causing the plan to be aged out of cache
• A sufficient percentage of data changes in a table that is referenced by the stored procedure.
• The procedure interleaves Data Definition Language (DDL) and Data Manipulation Language (DML) operations.

These recompilations of stored procedure add overhead on the processor. We should closely monitor the occurrence of recompilation of these stored procedures.  Performance monitor counter Sql Re-Compilations/sec is very helpful counter for this recompilation monitoring task.

Recommended value for SQL Re-Compilations/sec is close to ZERO. If nonzero values are consistently occurring for this counter, we should seriously search for the culprit stored procedures. Sql Profiler is a nice tool for this task.

How to monitor Sql Re-Compilation/sec counter values.

Step 1

On database server machine  --  > click on RUN -- > type Perfmon -- > delete existing counters by clicking on cross button on top.

Step2

           Click on + button to add new counter. Add counter Form select “SQL Server: SQL Statistics”. Now select “SQL Re-Compilations/sec” from counters list. Click “Add “button . Click “Close” button to view graphical view of performance monitor.

Sql Sqerver : Generate Conditional Table Data Script

In earlier posts “Sql Server 2005:Generate Tables Data Script ” and “Sql Server 2008:Generate Data Script” we have discussed methods to generate table data script for schema based script for Sql Server 2005 and specific table’s data script for Sql Server 2008 respectively.

On request of few blog readers following script will generate table data script based on your given conditions. This script is useful to generate script for only required records of a targeted table data and not the whole data for a table.

Following are four steps to achieve our goal.

1.       Create function to get all columns name of targeted table

2.       Create function to get values for all columns of targeted table

3.       Create a store procedure, to group our queries for future use.

4.       Execute store procedure with following parameters

a.       Schema Name

b.      Table Name

c.       Condition with WHERE clause

--------------------------------------------------

--STEP (1)

--------------------------------------------------

CREATE FUNCTION [dbo].[fnc_GetColumnsByCommas]

    (

      -- Add the parameters for the function

      @schemaName VARCHAR(50),

      @tableName VARCHAR(50)

    )

RETURNS VARCHAR(4000)

AS BEGIN

    DECLARE @column VARCHAR(2000),

        @columnS VARCHAR(4000),

        @i INT

    SET @i = 0

    SET @column = ''

    SET @columnS = ''

    DECLARE Cur_Columns CURSOR STATIC

        FOR SELECT  sys.columns.name

            FROM    sys.schemas

                    INNER JOIN sys.objects ON sys.schemas.schema_id = sys.objects.schema_id

                    INNER JOIN sys.columns ON sys.columns.object_id = sys.objects.object_id

                    INNER JOIN sys.types ON sys.columns.user_type_id = sys.types.user_type_id

            WHERE   sys.types.name <> 'timestamp'

                    AND sys.objects.type = 'U'

                    AND sys.objects.name = @tableName

                    AND sys.schemas.name = @schemaName

            ORDER BY sys.columns.column_id

    OPEN Cur_Columns

    FETCH FIRST FROM Cur_Columns INTO @column

    WHILE @@FETCH_STATUS = 0

        BEGIN

            IF @i = 0

                SET @columnS = '[' + @column + ']'

            ELSE

                SET @columnS = @columnS + ',' + '[' + @column + ']'

            SET @i = @i + 1

            FETCH NEXT FROM Cur_Columns INTO @column

        END

    CLOSE Cur_Columns

    DEALLOCATE Cur_Columns

 -- Return the result of the function

    RETURN @columns

   END 

--------------------------------------------------

--STEP (2)

--------------------------------------------------

CREATE FUNCTION [dbo].[fnc_GetColumnsForValueByCommas]

    (

      -- Add the parameters for the function here

      @schemaName VARCHAR(50),

      @tableName VARCHAR(50)

    )

RETURNS VARCHAR(4000)

AS BEGIN

 

    DECLARE @column VARCHAR(4000),

        @typeName VARCHAR(500),

        @columnS VARCHAR(2000),

        @ColStart VARCHAR(50),

        @ColEnd VARCHAR(50),

        @i INT

    SET @i = 0

    SET @column = ''

    SET @columnS = ''

    DECLARE Cur_Columns CURSOR STATIC

        FOR SELECT  sys.columns.name,

                    sys.types.name

            FROM    sys.schemas

                    INNER JOIN sys.objects ON sys.schemas.schema_id = sys.objects.schema_id

                    INNER JOIN sys.columns ON sys.columns.object_id = sys.objects.object_id

                    INNER JOIN sys.types ON sys.columns.user_type_id = sys.types.user_type_id

            WHERE   sys.types.name <> 'timestamp'

                    AND sys.objects.type = 'U'

                    AND sys.objects.name = @tableName

                    AND sys.schemas.name = @schemaName

            ORDER BY sys.columns.column_id

    OPEN Cur_Columns

    FETCH FIRST FROM Cur_Columns INTO @column, @typeName

    WHILE @@FETCH_STATUS = 0

        BEGIN

            IF @typeName = 'text'

                OR @typeName = 'uniqueidentifier'

                OR @typeName = 'varbinary'

                OR @typeName = 'smalldatetime'

                OR @typeName = 'char'

                OR @typeName = 'datetime'

                OR @typeName = 'varchar'

                OR @typeName = 'date'

                OR @typeName = 'time'

                BEGIN

                    SET @ColStart = ' ISNULL(CHAR(39) + CAST ( '  

                    SET @ColEnd = ' AS VARCHAR(MAX))+ CHAR(39),''NULL'') '

                END

            ELSE

                IF @typeName = 'nvarchar'

                    OR @typeName = 'ntext'

                    OR @typeName = 'nchar'

                    BEGIN

                        SET @ColStart = 'ISNULL( ''N''+CHAR(39)+ CAST ( ' 

                        SET @ColEnd = ' AS NVARCHAR(MAX)) + CHAR(39),''NULL'') '

                    END

                ELSE

                    BEGIN 

                        SET @ColStart = 'ISNULL(CAST ( ' 

                        SET @ColEnd = ' AS VARCHAR(MAX)), ''NULL'') '

                    END

            IF @i = 0

                SET @columnS = @ColStart + @column + @ColEnd

            ELSE

                SET @columnS = @columnS + '+'',''+' + @ColStart + @column

                    + @ColEnd

            SET @i = @i + 1

            FETCH NEXT FROM Cur_Columns INTO @column, @typeName

        END

    CLOSE Cur_Columns

    DEALLOCATE Cur_Columns

-- Return the result of the function

    RETURN @columns

   END 

--------------------------------------------------

--STEP (3)

--------------------------------------------------

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[proc_CreateInsertStatmentsBySchema]

    (

      -- Add the parameters for the function here

      @SchemaName VARCHAR(50),

      @TableName VARCHAR(50),

      @Condition VARCHAR(2000)

    )

AS

    BEGIN

        DECLARE @tbID INT,

            @tbNAME VARCHAR(50),

            @tbColumn VARCHAR(4000),

            @tbColumnforVal VARCHAR(4000),

            @SQLstr VARCHAR(4000),

            @label VARCHAR(500),

            @Count INT

        SELECT  @tbNAME = '[' + sys.schemas.name + '].[' + sys.objects.name

                + ']',

                @tbColumn = dbo.fnc_GetColumnsByCommas(@schemaName,

                                                       sys.objects.name),

                @tbColumnforVal = dbo.fnc_GetColumnsForValueByCommas(@schemaName, sys.objects.name)

        FROM    sys.schemas

                INNER JOIN sys.objects ON sys.schemas.schema_id = sys.objects.schema_id

        WHERE   sys.objects.type = 'U'

                AND sys.schemas.name = @schemaName

                AND sys.objects.name = @TableName

        SET @SQLstr = 'SELECT ' + CHAR(39) + 'INSERT INTO ' + @tbNAME + '  ('

            + @tbColumn + ')  VALUES ( ''+' + @tbColumnforVal

            + ' +'')'' FROM ' + @tbNAME + @Condition

        EXEC ( @SQLstr

            )

    END

--------------------------------------------------

STEP (4)Execute store procedure with “Result to Text” option

--------------------------------------------------  

    Use AdventureWorks

    EXEC [dbo].[proc_CreateInsertStatmentsBySchema] 'HumanResources',

        'Department', ' WHERE DepartmentID <10'