SQL Server Deep Dives

Bulk Insert data to specific columns

noreply@blogger.com (Unknown) — Thu, 19 Apr 2012 12:12:00 +0000

Bulk insert will fail if we try to insert data to specific columns in a table, e.g. we have a Candidate table where we store the list of candidates and has an Identity column CandidateID

CREATE TABLE Candidate

(CandidateID INT IDENTITY(1,1) PRIMARY KEY,

CandidateName VARCHAR(60),

DOB DATETIME

)

Suppose our source file has only columns "CandidateName" and "DOB", If we do BULK INSERT from source file to this candidate table as,

BULK INSERT Candidate

FROM 'F:\Candidates.csv'

WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

We will end up in an error,

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CandidateID).

An easy method to avoid this error is to create a view against the destination table with columns that we can provide as input and BULK INSERT to the created view.

CREATE VIEW vw_Candidate

(

CandidateName VARCHAR(60),

DOB DATETIME

)

BULK INSERT vw_Candidate

FROM 'F:\Candidates.csv'

WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

Now, the BULK INSERT is successful and our base table has the data.

Thank!

Adding dynamic connection in SSIS

noreply@blogger.com (Unknown) — Wed, 04 Apr 2012 15:01:00 +0000

I have seen many post regarding "Adding dynamic connection in SSIS" in various SQL Sever forums, the question is like

Hi

Whats the best way to edit the connection of a slowly changing dimension. I want to point to a new connection.

CheersI

The best way is to use XML configuration file to change the connection dynamically, using this method saves the SSIS configuration in an XML files, and later we have the flexibility to edit this xml file Programmatically (from the Application) or manually. The steps are as follows

· Right click on package designer, choose "Package Configuration and Organizer"

· Click Add, this will open up a wizard, Click next.

· In Select configuration type window, choose" XML Configuration file" as your "configuration type". Specify a location to save this configuration file in "Configuration File Name" and click next

· In "Select Properties to Export Window", expand "Connection Managers", Choose Servername, (initialCatloge, only if you need to edit database as well) in both source and destination and click next button.

· Specify a name for this configuration and click finish to complete the wizard

Check the XML configuration file created above, you can customize the connection by editing this config file.

Auditing Password Changes in SQL Server 2005

noreply@blogger.com (Unknown) — Tue, 03 May 2011 15:52:00 +0000

Here on this article, I will demonstrate how to track SQL login Password changes on SQL Server 2005, on SQL Server 2008 we can rely on newly introduced feature called SQL Server Auditing to achieve this. Since SQL Server 2005 does not have Auditing feature, so let’s see how to track these changes in SQL Server 2005.

By creating custom trace on Event Class: Audit Login Change Password Event Class we can track password changes to the SQL Logins. By default this event class is not a part of default trace.

This is how it’s done:

1. Create a stored procedure to create custom trace on Event Class: Audit Login Change Password Event Class.

2. Mark the above created stored procedure as Startup procedure

1. Creating Stored Procedure to Create Custom Trace.

Here I am using two system stored procedure

1. sp_trace_Create :- This is used to create new trace definition.

2. sp_trace_setevent:- Once the trace is defined, we need to add events to the trace that we created in above step. This stored procedure is used to Add or Remove Events to a trace. Remember this may be executed only on existing trace having status ‘stopped’.

Script

USE [master]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[usp_password_change_trace]

BEGIN

DECLARE @TraceID INT,

@FileName NVARCHAR(245) ,

@ErrorMessage NVARCHAR(4000),

@ErrorSeverity INT,

@ErrorState INT,

@maxfilesize BIGINT

BEGIN TRAN

BEGIN TRY

-- Specify the location and file name to which the trace will be written

SELECT @filename = 'D:\SQLTrace\Password_Change_Trace_'

+ CONVERT ( CHAR(8), GETDATE(), 112 )

+ '_' +REPLACE(CONVERT (CHAR(18), GETDATE(), 108), ':', '')

,@maxfilesize = 50

-- Add Event Class to the trace.

EXEC sp_trace_setevent @TraceID -- TraceID

@EventID = 107, -- 107 : Audit Login Change Password Event, Occurs

when a SQL Server login password is changed.

Passwords are not recorded.

@columnID, -- Columns to be addedd

@On = 1 -- 1 = Add, 0 = Remove

EXEC sp_trace_setevent @TraceID, 107, 6, 1

-- Adds Column NTDomainName: Windows domain to which the user belongs.

EXEC sp_trace_setevent @TraceID, 107, 7, 1

-- Adds Column HostName : Name of the computer on which the client is running.

EXEC sp_trace_setevent @TraceID, 107, 8, 1

/* Adds ApplicationName : Name of the client application that created

the connection to an instance of SQL Server. This column is populated with

the values passed by the application rather than the displayed name of the

program.*/

EXEC sp_trace_setevent @TraceID, 107, 10,1

-- Add Column LoginName : Name of the login of the user.

EXEC sp_trace_setevent @TraceID, 107, 11,1

-- Add Column StartTime : Time at which the event started, if available.

EXEC sp_trace_setevent @TraceID, 107, 14,1

/* Add Column EventSubClass: 1=Password self changed, 2=Password changed ,3=Password self reset 4=Password reset,5=Password unlocked,6=Password must change*/

EXEC sp_trace_setevent @TraceID, 107, 21,1

-- Add Column ServerName : Name of the instance of SQL Server being traced.

EXEC sp_trace_setevent @TraceID, 107, 26,1

-- Add Column ObjectTyoe : Value representing the type of the object involved in the event.

EXEC sp_trace_setevent @TraceID, 107, 28,1

-- Add Column ObjectName : Name of the object being referenced.

EXEC sp_trace_setevent @TraceID, 107, 34,1

-- Add Column DatabaseName : Name of the database in which the user statement is running.

EXEC sp_trace_setevent @TraceID, 107, 35,1

-- Adds Column DBUserName : SQL Server database user name of the client.

EXEC sp_trace_setevent @TraceID, 107, 40,1

/* Adds Column LoginSid : Security identification number (SID) of the logged-in user. You can find this information in the sys.server_principals catalog view. Each SID is unique for each login in the server.*/

EXEC sp_trace_setevent @TraceID, 107, 41,1

/* Add Column TargetLoginName: For actions that target a login (for example, adding a new login), the name of the targeted login. */

EXEC sp_trace_setevent @TraceID, 107, 42,1

/* Add Column IsSystem : Indicates whether the event occurred on a system process or a user process. 1 = system, 0 = user. */

EXEC sp_trace_setevent @TraceID, 107, 60,1

/* Adds Column SessionLoginName :Login name of the user who originated the session.

For example, if you connect to SQL Server using Login1 and execute

a statement as Login2, SessionLoginName shows Login1 and LoginName

shows Login2. This column displays both SQL Server and Windows logins.*/

EXEC sp_trace_setevent @TraceID, 107, 64,1       

      COMMIT TRAN

      END TRY

      BEGIN CATCH

                        IF @@TRANCOUNT > 0

                        ROLLBACK

                        SELECT  @ErrorMessage = ERROR_MESSAGE(),

                                    @ErrorSeverity = ERROR_SEVERITY(),

                                    @ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage,  @ErrorSeverity, @ErrorState);

      END CATCH

END

Execute the following query to check the trace we created

SELECT * FROM ::fn_trace_getinfo(default)

SELECT * FROM sys.fn_trace_getinfo

The column property with ‘5’ shows the current status of the trace

The value can be 0 = Stopped or 1 = Started

2. Mark the stored procedure to run while SQL Server Starts.

By using sp_procoption you can set a stored procedure for auto execution, this makes the stored procedure to execute automatically whenever the SQL SERVER instance starts.

Before that check the “Scan for Startup Procs” is enabled by using sp_configure.

-- Check run value "scan for startup procs"

sp_configure 'scan for startup procs'

If it is disabled enable it

sp_configure 'scan for startup procs', 1

RECONFIGURE WITH OVERRIDE

-- make the sp to run at startup

EXEC sp_procoption @procname = 'dbo.usp_password_change_trace', @optionname = 'startup', @optionvalue = 'true'

After making any password change, you can test this by two ways – By opening the trace file created or by using the function fn_trace_gettable

To Modify the state of the trace

By using sp_trace_setstatus, you can modify the current state of the trace.

-- To run the trace.

sp_trace_setstatus @traceID, 1

-- To stop the trace.

sp_trace_setstatus @traceID, 0

-- To delete the trace, you need to first stop if the trace running.

-- if trace is running, stop.

sp_trace_setstatus @traceID, 0

-- Delete its definition

sp_trace_setstatus @traceID, 2

Top SQL Server performance killers..!

noreply@blogger.com (Unknown) — Fri, 08 Apr 2011 10:23:00 +0000

Here are some areas that degrades SQL Server Performance. These are not in any order.

Improper Database and SQL Server Settings

tempdb is one of the busiest system database, It is a place for temporary storage such as when you create temp tables, table variables, cursors, joins and operations like sorting, row versioning etc. All these operations can hurt IO, CPU performance. So it is necessary to adequately design the storage structure of tempdb.

Another important problem can be with incorrect storage design for data files, log files, SQL program files and OS files. They should be placed on appropriate RAID levels.

Also you should properly set the configuration settings for SQL Server like Memory, Query time out, parallelism etc, Database settings like Managing Statistics, Auto growth of db files etc.

Database Design

Bad logical database design results in bad physical database design, and generally results in poor database performance. Both the logical and physical design must be right before you can expect to get good performance out of your database.

Database should be adequately normalized and de-normalized to improve the performance and reduce blocking. Normalized database reduces redundant data in turn reduces storage requirements. Row size should be well planned so that more rows can be fit on an 8KB data page.

Over normalized tables also as bad as under normalized database, this results in increased number of complexity of joins to retrieve the data.

Index and Statistics

One of the common and biggest performance problems on SQL Server is poor or missing indexes and outdated statistics. When a query executes, Query optimizer will search for the useful indexes, in the absence of proper index for a query may result in blocking, dead lock, a high amount of stress in Disk, memory and CPU because SQL Server needs to process or retrieve much more data while executing the query.

Coming to statistics, Statistics are the information about the distribution of data in columns or indexes. SQL Server query optimizer relies on this statistics to generate more efficient query plan to retrieve or update the data. A good updated statistics helps to create high quality plan. By default SQL Server automatically creates and updates statistics. An outdated statistics gives the query optimizer inaccurate information about data distribution like the number of rows returned etc, this creates inefficient plans to execute the query.

Excessive Fragmentation

SQL Server manages the indexes automatically whenever an INSERT, UPDATE, DELETE occur on the underlying data this results in page split, fragmentation. There are two types of fragmentation External fragmentation – the leaf level of index is fragmented in a non-orderly fashion. The other one is Internal Fragmentation - the index pages are not being used to their maximum volume. A property called fill factor of an index decides how much space to be left on index pages, severe internal fragmentation can lead to increased index size and cause additional reads to be performed to return needed data. These extra reads can lead to degradation in query performance. You can overcome the fragmentation by rebuilding or reorganizing the index.

Blocks and Dead Locks

When a modification occurs in a transaction, SQL Server Isolates it from other concurrent transactions, this prevents the transactions to see intermediate states of the data, i.e. A transaction either sees the data either before state or after state of the data. This is the default property of SQL Server, and is done by using a mechanism called locks. Because of this isolation Blocking occurs in a database

A deadlock occurs when two resources attempt to escalate or expand locked resources and conflict with one another. The query engine determines which process is the least costly to roll back and chooses it as the deadlock victim.

Thus the execution time of a query is adversely affected by the amount of blocking and deadlock it faces.

Poor Query Design

The effectiveness of index depends on the way how you write queries like use of functions on a column of a where clause e.g Year(SaleDate) = 2010, this will result in index scan rather than an index seek. Another problem is retrieving more data from the table than that is required. You must ensure that the queries are fine tuned and it can make use of the indexes effectively.

T-SQL is a set based language meaning it operates on sets of data rather than rows. It performs better when deals with set of data. Non-set based approach make use of cursors, loops etc rather than more efficient Joins and subqueries. These Non-Set based approach kills the performance. If you are forced to use cursor if you are forced to deal with cursors, be sure to use efficient cursor types such as fast-forward only.

Poor Execution plans and Re-Compliation

When a query comes for execution optimizer checks for query plan available to execute the query, if it is missing it will generate a fair plan and usually this plan will be cached in memory for future use once it is created. There is fair amount of CPU expense while creating the Query plan. If a plan is designed so that you cannot plug variables to it, SQL Server creates a query plan every time the query is resubmitted with different value. This eats up the CPU cycles, so it is important to submit your queries in a form that SQL Server can cache and reuse it.

One of the standard ways of ensuring a reusable execution plan, independent of variable values used in a query, is to use a stored procedure. Using stored procedure to execute a set of SQL queries allows SQL Server to create parameterized execution plan.

No of Read and Write against tables

noreply@blogger.com (Unknown) — Thu, 24 Mar 2011 08:27:00 +0000

Is it possible to find the number of reads and writes performed against each tables in a database? We can depend on sys.dm_db_index_usage_stats DMV to achieve this partially. The columns user_lookups, user_scans, user_seeks, user_updates are counters and gets incremented for each individual seek, scan or update on the specific index on a table when a query is executed.

Here is the query,

SELECT S.name,

SUM(user_lookups + user_scans + user_seeks) AS Reads,

SUM(user_updates) AS Updates

FROM sys.dm_db_index_usage_stats v

INNER JOIN sys.objects S ON S.object_id = v.object_id

WHERE type = 'U'

AND database_id = DB_ID()

GROUP BY S.name

The Reads column displays the number of times sql server accessed the indexes on that table and, the Updates column represent the number of time sql server performed update/write operation on the indexes of a table.

For example – I have a table, Employee

CREATE TABLE Employee

(

ID INT PRIMARY KEY,

EmpName NVARCHAR(30),

DeptID TINYINT

)

CREATE INDEX IX_NCL_Employee_EmpName

ON Employee(EmpName)

CREATE INDEX IX_NCL_Employee_DeptID

ON Employee(DeptID)

Our Employee table has three indexes, One Clustered Index on column ID and the rest are non-clustered index on EmpName and DeptID. For each insert against the Employee table SQL Server needs to do update the underlying indexes on the table also, in our case it is three. If we are doing an update on ID column then SQL Server needs to update the three indexes, because when you update a clustered index all the non-clustered indexes are also get updated. What if we update DeptID of that table? This time SQL Server needs to update two indexes, index on DeptID and the Clustered Index.

Well, there are some limitations with this query, the major one is that the counters reset when sql server starts. Another limitation is, when we drop any indexes on that table, we will lost reads/update information performed against those indexes. So this method is a temporary solution only!

Missing Index Feature - Limitation

noreply@blogger.com (Unknown) — Wed, 29 Sep 2010 05:59:00 +0000

The missing indexes feature uses dynamic management objects and Show plan to provide information about missing indexes that could enhance SQL Server query performance. The Missing Index feature will suggest you the candidates for index based on the execution plans of the queries being run against the database. This feature is based on the data generated from the execution plans stored in the cache.

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes. The missing indexes feature enables you to access information about these indexes so you can decide whether they should be implemented. You can use Sys.dm_db_missing_index_details DMV to view the details of missing indexes or you can examine the execution plan or XML show plans for a query to find the missing index details

Here on this article we will see the Limitations of the Missing Index feature.

The following are the Limitations of Missing Index Feature.

The missing index may recommend a non-clustered index that already exists. Over and over again.

Sometime the Missing Index feature recommends you to create a Non-Clustered Index that is already exist. This is actually a bug with the Missing Index feature (View details)

Setup the work table

CREATE TABLE MissingIndex

(

EmpID INT IDENTITY,

RefID INT,

LastRefID INT,

Remarks CHAR(4000)

)

-- Insert test data – Create a Covered Index

INSERT INTO MissingIndex (RefID,LastRefID,Remarks)

SELECT RAND()*10000,RAND()*10000,'NA'

GO 10000

-– Create a Covered Index

CREATE INDEX IDX_NCL_tMissingIndex_cRefID_cLastRefID

ON MissingIndex(RefID,LastRefID)

INCLUDE(EmpID)

SELECT EmpID

FROM MissingIndex

WHERE RefID BETWEEN 10 AND 1000

AND LastRefID > 6000 ;

The Execution plan will guide you to create those missing indexes.

Let’s create those missing indexes and examine the Execution plan again for the same query.

CREATE INDEX IDX_NCL_tMissingIndex_cRefID_cLastRefID

ON MissingIndex(RefID,LastRefID)

INCLUDE(EmpID)

Estimated Execution Plan after creating the Missing index Suggested by the optimizer.

Good to see an index seek operation, and the index I created was also useful. Now let’s see how this index works for a little complicated query.

Check the estimated execution plan for the following query

DECLARE @EmpID INT

DECLARE curMissingIndex CURSOR

FOR SELECT EmpID

FROM MissingIndex

WHERE RefID BETWEEN 10 AND 1000

AND LastRefID > 6000

OPEN curMissingIndex

FETCH NEXT FROM curMissingIndex INTO @ EmpID;

WHILE @@fetch_status = 0

BEGIN

FETCH NEXT FROM curMissingIndex INTO @ EmpID;

END

CLOSE curMissingIndex ;

DEALLOCATE curMissingIndex ;

This time also optimizer suggests me for a missing index. Let’s see what the missing index is.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[MissingIndex] ([RefID],[LastRefID])

INCLUDE ([EmpID])

Ah! Shocking? The missing index is actually exactly the same as the one I created little earlier. . It will continue to recommend you the same again and again even if you follow the missing index suggestion.Even if you examine the Sys.dm_db_missing_index_details DMV you can find an entry there.

It is not intended to fine tune an indexing configuration.

The missing indexes feature is a lightweight tool for finding missing indexes that might significantly improve query performance. It does not provide adequate information to fine tune your indexing configuration. It will not suggest you Partitioning or indexed Views etc. Use Database Engine Tuning Advisor for that purpose it is having a wide range of recommendations.

It cannot gather statistics for more than 500 missing index groups

Once maximum of 500 missing indexes groups are captured, no more missing index group data is gathered. This threshold is not a tunable parameter and cannot be changed.

It does not specify an order for columns to be used in an index.

The missing indexes feature does not consider the optimum sort order when suggesting an index. Consider an example

SELECT EmpID

FROM MissingIndex

WHERE RefID BETWEEN 10 AND 1000

AND LastRefID > 6000

ORDER BY LastRefID, RefID

For the above query the optimizer will suggest you a missing index with columns RefID and LastRefID and an INCLUDE part as EmpID, But the reality is that if you create the index with the column order RefID and LastRefID then you can take advantage of Sorting.
If I created the missing index as

CREATE INDEX IDX_NCL_tMissingIndex_cRefID_cLastRefID

ON MissingIndex(RefID,LastRefID)

INCLUDE(EmpID)

Then cost of the query was 1.34561

Instead of creating index “IDX_NCL_tMissingIndex_cRefID_cLastRefID”
If I created as the following

DROP INDEX IDX_NCL_tMissingIndex_cRefID_cLastRefID

ON MissingIndex

CREATE INDEX IDX_NCL_tMissingIndex_cRefID_cLastRefID

ON MissingIndex(LastRefID, RefID)

INCLUDE(EmpID)

Then cost of the query came down to 0.728691!

For queries involving only inequality predicates, it returns less accurate cost information

The missing index feature uses a simplistic model to generate cost information for queries involving only inequality predicates. Consequently the cost information returned for these queries may be less accurate than the information returned for queries that involve equalities.

It reports only include columns for some queries, so index key columns must be manually selected

Sometimes the feature only reports column usage information for include columns. For example, the feature might report equality columns = { }, inequality columns = { }, and include columns = {b, c, …}. In this case, one of the include columns must be selected for the index key column

It returns only raw information about columns on which indexes might be missing.

The missing indexes feature returns raw information about columns on which indexes might be missing. The information returned might require additional processing before we can use the information to create an index.

For example, if the column involved is a computed column, and the computation is nondeterministic or imprecise, the CREATE INDEX statement on that column will fail. In the case of an imprecise computation, the column should be persisted first, and then you can create the index.

Sometimes missing index groups appears multiple times on the Showplans

Let’s see an example,

Setup the sample tables

-- Create Customer Table

CREATE TABLE Customer

(CustomerID INT IDENTITY,

FirstName VARCHAR(10),

MiddleName VARCHAR(10),

LastName VARCHAR(10),

Remarks CHAR(2000)

)

-- Insert 1000 Customers

INSERT INTO Customer(FirstName, MiddleName, LastName, Remarks)

SELECT CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))

,CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))

,CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0)),'NA'

GO 1000

-- Contact information

CREATE TABLE CustomerContactInfo

(CustomerID INT,

Address1 CHAR(200),

Address2 CHAR(200),

CityID INT,

Phone VARCHAR(10),

)

INSERT INTO CustomerContactInfo

SELECT ROUND((999 * RAND() + 1), 0),

CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))

,CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))+CHAR(ROUND((26 * RAND() + 65), 0))

,ROUND(RAND()*100,0)

,CONVERT(VARCHAR,ROUND((9 * RAND() + 1), 0))+CONVERT(VARCHAR,ROUND((9 * RAND() + 1), 0))+CONVERT(VARCHAR,ROUND((9 * RAND() + 1), 0))

+CONVERT(VARCHAR,ROUND((9 * RAND() + 1), 0))+CONVERT(VARCHAR,ROUND((9 * RAND() + 1), 0))+CONVERT(VARCHAR,ROUND((9 * RAND() + 1), 0))

GO 3000

-- Sales Information

CREATE TABLE SalesSummary

(SalesID INT IDENTITY,

CustomerID INT,

UnitPrice MONEY,

Tax MONEY,

Discount MONEY,

TotalPrice AS UnitPrice+Tax+Discount,

Remark CHAR(2000))

INSERT INTO SalesSummary (CustomerID,UnitPrice, Tax, Discount, Remark)

SELECT ROUND((999 * RAND() + 1), 0),ROUND(RAND()*10000,0),ROUND(RAND()*100,0),ROUND(RAND()*1000,0), 'NA'

GO 10000

Execute the following query, since our tables don’t have any useful index to satisfy the query it The optimizer will place an entry on the Missing index details.

-- Execute the query

SELECT C.CustomerID,FirstName,MiddleName,LastName,Address1,Address2,CityID,Phone,SalesID,TotalPrice,S.Remark

FROM Customer C

INNER JOIN dbo.SalesSummary S

ON C.CustomerID = S.CustomerID

INNER JOIN CustomerContactInfo CC

ON CC.CustomerID = C.CustomerID

WHERE S.CustomerID = 223

Now, examine the missing index details by running the following query

-- Check the missing index details

SELECT OBJECT_NAME(Object_id),equality_columns,inequality_columns,included_columns

FROM sys.dm_db_missing_index_groups g

INNER JOIN sys.dm_db_missing_index_details d

ON g.index_handle = d.index_handle

ORDER BY object_id

You can see the optimizer came up with a suggestion to create two indexes on Sales table, but the Index Key Column is the same “CustomerID”, Only difference is the included column.This occurs when different parts of a single query benefit differently from the same missing index group.
Keep these limitations on mind while working on Query Performance Tuning!

SQL Server 2008 : Table-Valued Parameter

noreply@blogger.com (Unknown) — Mon, 20 Sep 2010 07:38:00 +0000

If you are new to TSQL you should have asked “How can I create arrays on SQL Server?” or ” How can I pass an array to SQL Server Stored Procedure?”. The answer is, Sorry! SQL Server does not support arrays because T-SQL is a Set-based language, deals with sets of data rather than Row by Row. But there are times that you may need to pass the parameters as arrays. Before SQL Server 2008, common ways to meet this need were based on dynamic SQL, a split function, XML, and other techniques. The approach using dynamic SQL involved the risk of SQL Injection and did not provide efficient reuse of execution plans. Using a split function was complicated, and using XML was complicated and non-relational.

But things changed with the release of SQL Server 2008. Table-Valued Parameters (TVP) are one of the key enhancement of SQL Server 2008 T-SQL. In this post I will explain what a Table-Valued Parameter is?When to use Table-Valued Parameter? How to create a Table-Valued Parameter? The benefits and restrictions of Table-Valued Parameter and an Example.

The Scenario.

I have a table SalesSummary to store the information about the Sales Summary Information about the Sales representative of a company.

/* Create the SalesSummary Sample table */

CREATE TABLE SalesSummary

(SalesID INT PRIMARY KEY IDENTITY,

SalesRepID INT,

SaleDate DATE,

TotalSales INT,

)

/* Insert some sample data */

INSERT INTO SalesSummary(SalesRepID, SaleDate, TotalSales)
VALUES (55,'02-01-2001',20),
(55,'03-11-2001',58),
(55,'05-06-2001',25),
(55,'06-12-2002',3),
(55,'06-21-2002',10),
(55,'12-10-2003',26),
(55,'04-05-2004',1),
(55,'12-01-2004',20),
(66,'02-01-2001',1),
(66,'03-11-2001',5),
(66,'05-06-2001',5),
(66,'06-12-2002',23),
(66,'06-21-2002',10),
(66,'12-10-2003',16),
(66,'04-05-2004',11),
(66,'12-01-2004',20),
(77,'02-01-2001',5),
(77,'03-11-2001',2),
(77,'05-06-2001',5),
(77,'06-12-2002',16),
(77,'06-21-2002',2),
(77,'12-10-2003',42),
(77,'04-05-2004',16),
(77,'12-01-2004',20)

-- Check the Inserted data
SELECT * FROM SalesSummary

Now the board members of the company wish to drill down the details of the Sales Rep who doesn’t met the target of 20 for each year.

Eg: Given below are the details of SalesRepthat the user wish to see

SalesRepID	Sales Year
55	2002
66	2001
66	2003
77	2001
77	2002

I.e., For the SalesRep 66, the user wish to get the details for the year 2001 and 2003 for 55, 2002 etc.

Lets see, how we done this before the release of SQL Server 2008 (I know you can use dynamic sql, Split function or using XML method to solve this, but I am using Split Method here.)

-- Create a stored procedure using split method separated

-- Sales Rep are separated by comma, SalesRep and year is separated by space and Multiple year for the SalesRep is separated by #

CREATE PROCEDURE dbo.usp_GetSalesRepSummary_SplitMethod

@inputString VARCHAR(100)

BEGIN

DECLARE

@SalesRepID VARCHAR(4),

@SalesYear VARCHAR(4)

DECLARE @temp TABLE

(SalesRepID INT,

SalesYear INT)

WHILE LEN(@InputString)<>0

BEGIN

-- Split the SalesRepID

SET @SalesRepID = LEFT(@inputString,CHARINDEX(' ',@inputString))

-- update the @inputString

SET @InputString = STUFF(@inputString,1,CHARINDEX(' ',@inputString),'')

-- Split the year

WHILE LEFT(@inputString,1) <> ',' AND LEN(@inputString) > 0

BEGIN

SET @SalesYear = LEFT(@inputString,4)

SET @InputString = STUFF(@inputString,1,4,'')

IF LEFT(@inputString,1) = '#'

BEGIN

SET @InputString = STUFF(@inputString,1,1,'')

END

INSERT INTO @temp

SELECT @SalesRepID, @SalesYear

END

-- update the @inputString

SET @InputString =TUFF(@inputString,1,CHARINDEX(',',@inputString),'')

END

SELECT SS.*

FROM @Temp t

INNER JOIN dbo.SalesSummary ss

ON t.SalesRepID = SS.SalesRepID

AND t.SalesYear = YEAR(ss.SaleDate)

END

-- Execute the procedure to see the result.EXEC dbo.usp_GetSalesRepSummary_SplitMethod '55 2002,66 2001#2002,77 2001#2002'

As you can observe that the logic inside split procedure is looping, and it really looks ugly because of complicated Parameter, split process and, of course not a pure set based approach!

If the user wants to see the details of 100 SalesRep with each one having 10 years, then the looping is 100*10

Let’s see what TVP can do here!

What is a Table-Valued Parameter?

Table Valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table Valued Parameters also have the benefit of being able to participate in set-based operations.

Table Valued Parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL Statement or a Routine, such as a Stored Procedure or a Function, without creating temporary table or many parameters.

How to create Table-Valued Parameter?

Table-Valued Parameter have two components, A SQL Server Type and a Parameter that reference that type.

Step1 : Creating a table Type and Table Structure.

Step2 : Declare a routine (Stored Procedure or Function) having Parameter of Table Type.

Step3: Declare a variable of the table type and reference the table type.

Step4 : Using Insert Statement fill the table variable created ion step3.

Step 5: After the table variable is created and filled, you can pass the variable to a routine.

Let's walk through the steps.

Step 1 : Creating a Table Type and Table Structure:

-- Create a table type and define the table structure

CREATE TYPE dbo.typSalesRepSummary AS TABLE

(SalesRepID INT,

SalesFrom DATE,

SalesTo DATE);

Step 2 : Declare a routine that has a parameter of the table type:

CREATE PROCEDURE usp_GetSalseRep_CustYearSummary

@CustDetails dbo.typSalesRepSummary READONLY

BEGIN

SET NOCOUNT ON

SELECT S.SalesRepID, CONVERT(VARCHAR,SalesFrom)+' - '+CONVERT(VARCHAR,SalesTo)Duration

, TotalSales

FROM @CustDetails C

INNER JOIN SalesSummary S

ON S.SalesRepID = C.SalesRepID

WHERE SaleDate BETWEEN SalesFrom AND SalesTo

END

Note: Currently, table-valued parameters are read only, and you must define them as such by using the READONLY keyword.

To execute the TVP Procedure.

Step 3: Declare a variable of the table type, and reference the table type:

Here in this step you need to create Table variable as type That reference the table type that we created on step 1. Later you will be using this table variable to insert the data and pass to the set to the TVP procedure

-- Declare a variable of the table type, and reference the table type

DECLARE @SalesRepSummary dbo. typSalesRepSummary

Step 4: Insert Statement fill the table variable created on Step 3:

INSERT INTO @SalesRepSummary

VALUES (55, '01-01-2002', '12-31-2002')

,(66, '01-01-2001', '12-31-2001')

,(66,'01-01-2003', '12-31-2003')

,(77,'01-01-2003','12-31-2001')

,(77,'01-01-2002','12-31-2002')

Step 5: Call the routine by passing the table variable that we created on step 4:

EXEC dbo.usp_GetSalseRep_CustYearSummary @SalesRepSummary

Scope

A table-valued parameter is scoped to the stored procedure, function or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement

Benefits

Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Table-valued parameters offer the following benefits:

· Do not acquire locks for the initial population of data from a client.

· Provide a simple programming model.

· Enable you to include complex business logic in a single routine.

· Reduce round trips to the server.

· Can have a table structure of different cardinality.

· Are strongly typed.

· Enable the client to specify sort order and unique keys.

Restrictions

·          SQL Server does not maintain statistics on columns of table-valued parameters.
·          Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
·          You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.