Resource Governor in SQL Server 2008 – Beware of catches

Resource governor is one of the best additions in SQL Server 2008. It gives you a real control on your environment as a DBA and can be very useful for better utilization of your database server resources. So far so good, but don’t just start using it without knowing how it actually restricts resource utilization.  Following are some points to remember for better utilization of this feature:

1. Normally, most of us assume that if we restricts a memory usage for a user A upto 10% only then he will never be able to utilize more than 10% of server in any case. Right? Wrong. Actually, Resource governer only restricts user to utilize not more than 10% if it feels that remaining memory is not available but if memory is available and there are no pending workload then it will allow the user to use more than its allowed quota of 10%. This is there to optimize the utilization of memory and avoids wastage of resources. But it can have worse effects also because if User A fires it query before other users then server will start utilizing all the available memory and all other users which came afterwards will suffer the consequences.
2. Please note that Resource Governor is for database engine not for other services in SQL Server 2008. It means you can not control usage of Reporting Services, Analysis Services or Integration Services.
3. If you have multiple instances running on same machine then you can not use Resource Governor to manage load between these instances.

Keeping these points in your mind will help you to better understand how to use resource governor and what to expect. It is one of the best tools to manage your load and highly recommended but make sure you know the pros and cons of it.

Conditionally add a column in the table

Sometimes we need to add a column in an already existing table but we need to make sure that this column should not be present already. This conditional column creation requires a way to check column existence in the given table. SQL Server 2005 INFORMATION_SCHEMA.COLUMNS view is an standard way to do this. Following script gives you the ability to conditionally add a column in the table, if that column is not already present.

Use tempdb
Go

Create table testTable (id int, city varchar(50))
Go

—Check for column existence
If not exists
(
Select * from INFORMATION_SCHEMA.COLUMNS
Where
TABLE_CATALOG=‘tempdb’
AND TABLE_SCHEMA=‘dbo’
AND TABLE_NAME=‘testTable’
AND COLUMN_NAME=‘id’
)
Begin
–Add new column
ALTER Table dbo.testTable Add id int Null
Print ‘Column added successfuly’
End
Else
Begin
–Column already exists. leave the table as it is.
Print ‘Column already exists.’
End
Go

Drop table testTable
Go

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

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

SET ROWCOUNT will not be supported in future version of SQL Server

Things are changing very rapidly in SQL Server future versions. Some of the features which were once treated as standard solutions to some typical scenarios of TSQL are now scheduled to be deprecated in future versions of SQL Server. One of these feature was the SET ROWCOUNT statement. This statement is also scheduled for being deprecated in future versions of SQL Server. The only way to limit your results will be by using the TOP keyword.

Similarly some other SET options will no longer be supported like

SET ANSI_NULLS
SET ANSI_PADDING
SET CONCAT_NULL_YIELDS_NULL

I will suggest you to study the complete list of deprecated database features here
and make sure you are not relying too much on these features, if you want to make sure that your database is compatible with future versions of SQL Server.