Month: May 2014

Insert Multiple Rows Of Static Text At One Time

by
Facebooktwitterredditpinterestlinkedinmail

Have you ever wanted to insert more than one row of static text in to a SQL table using the VALUES argument?  Well, this is actually pretty easy to do.  To do this you just need to pass a comma delimited value list like in the example below.

Syntax

INSERT
INTO   Employees
       (LastName,  FirstName)
VALUES ('Gump',    'Forrest'),
       ('Taylor',  'Dan'),
       ('Blue',    'Bubba')


Show Query Execution Time

by
Facebooktwitterredditpinterestlinkedinmail

One of the limitations of SQL Server Management Studio is that it shows query execution time in seconds.  In most daily activities, this is accurate enough.  But when it comes to query permanence tuning, it is helpful to have a more accurate measurement.

Here are a 3 different ways to show you an accurate query execution time:

 
Statistics On
To have your exact query time show in the separate messages tab, all you need to do is run the following queries.

SET STATISTICS TIME ON

GO

-- Insert query here

GO

SET STATISTICS TIME OFF

GO

Then you can view the time by clicking on the Messages tab.

Show Query Execution Time

 
Include Client Statistics
To get a lot of in-depth information about your query, you can turn on Client Statistics before running your query.  The details of all the results will be shown in the Client Statistics tab after execution was completed.

Turn on client statistics by choosing the Include Client Statistics option from query menu.

show query execution time client stats

You can then view the query time by looking under the Client Statistics tab by your query results

Show Query Execution Time Client Stats

 
Inline Query Time Results
If you would like to just show the execution time in the standard results window, this query allows you to do this.  It will also allow you to show exact time of execution of a specific code segment in a query.

To use this, just place the code that you would like to get the execution time for in the center of the below script.  The exact time will be shown in the results.

DECLARE @Time1 DATETIME

DECLARE @Time2 DATETIME

SET     @Time1 = GETDATE()

-- Insert query here

SET     @Time2 = GETDATE()

SELECT  DATEDIFF(MILLISECOND,@Time1,@Time2) AS Elapsed_MS

Reference:  http://technet.microsoft.com/en-us/library/ms190287.aspx