Multiple Inserts in one statement – Row Constructor in SQL Server 2008

Don’t you feel boring sometimes when you have to write an script to insert multiple rows in a table and you do not have any other choice except writing multiple insert statements? In one of my previous posts I showed a way to insert multiple rows by using UNION ALL but wait! there is another way available in SQL Server 2008 called Row Constructor which is an ANSI terminology for pseudo table of rows.
It is actually a way to provide a set of row values in one statement. Following example will help you understand better. Please note that this example can work only on SQL Server 2008 or above:

–Switch to tempdb
Use tempdb
Go

–Create a test table in temdb
Create table tblCountries (id int, country varchar(50)
Go

–Inserting multiple values
Insert into tblCountries (id,country)
Values
(1,‘USA’), –Row 1
(2,‘UK’), –Row 2
(3,‘France’) –Row 3

–Now select, you will get 3 rows
Select * from tblCountries

Capture every data operation in SQL Server 2008

Logging all the data related activities to maintain an Audit Trail is always a prime requirement of sensitive applications. People either use Triggers to manage Audit Trails or use middle tiers to maintain the log by themselves.
SQL Server 2008 has introduced a completely new framework targeted for Audit Trail maintenance. This framework consists of in-built change tracking, change log tables and associated table value functions to access the data of change log. Following is a quick summary of how change tracking works in SQL Server 2008 as provided by BOL:

1. A member of sysadmin user enables the data capture for database by sys.sp_cdc_enable_db_change_data_capture stored procedure.
2. After enabling, a new schema called cdc will be created which will contain the change log tables.
3. Similarly a new user called cdc will be created.
4. Make sure SQL Server Agent is running.
5. Run sys.sp_cdc_enable_table_change_data_capture and supply the name of the table for which you want to capture the changes.
6. A new table will be created in cdc schema which will be the copy of source table along with additional column to describe the type of changes.
7. Change tracking has been setup. To query the changes, following two functions can be used

• cdc.fn_cdc_get_all_changes_[instance name] (All changes happened to the source table in the given interval. [instance name] will be replaced by the instance name specified during enabling of data capture.)
• cdc.fn_cdc_get_net_changes_[instance name] (Only final change happened to the source table in the given interval. [instance name] will be replaced by the instance name specified during enabling of data capture.)

This information is gathered from Microsoft Books Online and I will highly recommend every reader to read the topic Configuring Change Data Capture in Books Online of SQL Server 2008.

100 Nano seconds precision in SQL Server 2008 DATE/TIME data types

Today’s scientist use highly precise instruments and take measurements of time at nano seconds level. This level of precision needs a storage system which can provide storage of time and date data at nano seconds level and can easily scale up with future needs.
SQL Server 2008 has introduced a completely updated set of Date and Time data types which allows you to store Date and Time data separately or combined with variable precision settings. It allows you to record scientific readings at nano second level. Following is the list of Date and Time data types available in SQL Server 2008

Data Type	Range	Accuracy	Remarks
Time	00:00:00.0000000 through 23:59:59.9999999	100 nano seconds	ANSI-compliant date data type
Date	00001-01-01 through 9999-12-31	1 day	ANSI-compliant time data type with variable precision
Smalldatetime	1900-01-01 through 2079-06-06	1 minute
Datetime	1753-01-01 through 9999-12-31	0.333 seconds
Datetime2	0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999	100 nano seconds	like DATETIME, but with variable precision and large date range
Datetimeoffset	00001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)	100 nano seconds	timezone aware/preserved datetime

Represent Trees and Graphs in TSQL by using new data type: HierarchyID

Everyone is talking about SQL Server 2008 these days because it is coming up with lots of enhancements in nearly every area. One of these enhancements is the introduction of a new data type called HierarchyID.
This data type is introduced to address one of the typical problem faced by nearly every medium to large size enterprises which is the representation of Trees and Graphs in RDBMS. Trees and Graph concepts are applicable to various real life problems like Bill Of Materials (BOM) , Organizational Hierarchy, Road Networks etc.

SQL Server 2008 provides you full solution by giving you HierarchyID data type and its relevant functions to play with it. Following is the list of function available to manipulate Hierarchical data with HierarchyID data type:

1) GetAncestor
2) GetDescendant
3) GetLevel
4) GetRoot
5) IsDescendant
6) Parse
7) Read
Reparent
9) ToString
10) Write

For detailed discussion of these functions please visit here

MERGE Statement of SQL Seerver 2008

SQL Server 2008 has introduced a new statement called MERGE which will combine the logic of INSERT-IF NOT EXITS and UPDATE-IF EXISTS.
MERGE statement will give you the ability to write one single statement which will update the row if it already exists otherwise it will insert a new row.
Additionally, MERGE statement also enable you to combine the logic of delete if you want.
Following example from Microsfot SQL Server 2008 Books Online
will give you the better idea:

Use AdventureWorks
Go

MERGE Departments AS d
USING Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager dd.Manager
OR d.DeptName dd.DeptName
THEN UPDATE SET d.Manager = dd.Manager,
d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
INSERT (DeptID, DeptName, Manager)
VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN SOURCE NOT MATCHED THEN
DELETE;

Return Last n Orders by using APPLY operator

With many other new enhancements, SQL Server 2005
has introduced another very useful operator called
APPLY, which makes life very easy for some complex problems.
APPLY operator works as follows:
1. It applies a table valued function to each row of the table
by using the column values as parameters.
2. Resulting rows are then returned as table
which can be used as a normal table.

Following TSQL script will return Last 3 orders for every
SalesPerson in AdventureWorks database.
Just replace 3 with stored procedure parameter to get
n number of last orders from each sales person.

Use AdventureWorks
Go

–Creating function to
–return top 3 orders
Create Function
dbo.udf_GetLatestOrders(@p_SalesPersonId int)
Returns table
AS
return
Select Top 3 *
from Sales.SalesOrderHeader
Where SalesPersonId=@p_SalesPersonId
Order by ShipDate desc
GO

–Run the query to use above
–created function
Select SP.* from Sales.SalesOrderHeader S
Cross Apply
dbo.udf_GetLatestOrders(S.SalesPersonId) SP
Where
–Join function and Master table
S.SalesPersonId=SP.SalesPersonId

Number padding in TSQL

Sometime we need to pad leading zeros to numeric
values so that they can appear having same width
like 00001,0022,0934 etc.
Following code demonstrates the technique
to pad leading zeros in front of digits.

–Variable to hold max length
Declare @m_maxLength int

–Create a test table
Declare @m_testTable table (sampleValue int)

–Insert some sample values
Insert into @m_testTable values (1)
Insert into @m_testTable values (2)
Insert into @m_testTable values (19)
Insert into @m_testTable values (201)
Insert into @m_testTable values (20231)

–Calcualte the length of maximum number in the table
Select
@m_maxLength=Max(Len(convert(varchar,sampleValue)))
from @m_testTable

—Now select the values
Select
sampleValue,
Replicate(‘0′,@m_maxLength-Len(convert(varchar,sampleValue)))
+convert(varchar,sampleValue) as paddedValue
from @m_testTable

Microsoft Performance Point Server and Sharepoint Portal Server

Microsoft has announced another server software called “Microsoft Performance Point Server“. According to the information available up till now, this server will act as a bridge between Microsoft Office and SQL Server Business Intelligence architecture. So far so good, but don’t you think it looks similar as what Microsoft promised for SharePoint Server? I felt similar too.
I am trying to find out more information regarding the actual reason why Microsoft has brought this new server when SharePoint Portal Server is still there. In the mean while, when I was checking the system requirements of PPS, it says that it requires Windows SharePoint Services and SQL Server 2005. Things are very confusing right now and I will try to find more information about PPS. In the mean while you can take a look at PPS here.

Caching and Recompilation in SQL Server 2005

I was browsing through the newsgroups and found a very interesting white paper link about SQL Server 2005 Plan caching and batch recompilations. This white paper is a must read for anyone who really wants to master the query tuning and optimization strategies.
You can read this white paper here

ENABLE/DISABLE all triggers of all tables in one statement

Sometimes we need to disable triggers to perform some tasks. Disabling the trigger by going to each table is very tedious. Following is avery quick way of disabling all triggers on all tables of the given database in a single statement.

sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”

To enable all triggers, you can use following statement

sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”