How to run a SQL batch multiple times

Today I came across one surprising use of GO statement in SQL Server which is solely used as batch terminator in SQL Server utilities such as SQL Server Management Studio etc. Though “GO” is not a Transact –SQL statement it can be used in multiple ways .One such example is to run a batch of statements as illustrated below

CREATE TABLE TestData ( Name VARCHAR(100), Age INT)
GO
Insert into TestData
SELECT 'John' ,28
GO 100

In the above example I have inserted 100 rows of test data by just specifying 100 next to the GO statement. All these days I have used the traditional while loop statement for doing this, now just specify the number next to GO statment the job is done. This feature is no where documented and supported only in SQL Server 2005 and greater versions

Filter Index in SQL Server 2008

SQL Server 2008 introduces a new type of index called “Filtered Index” which is basically a covered indexed designed to retrieve a smaller set of qualified data from a table. This can be a very hand feature particularly in working with larger data tables.
A Filter index allows us to apply filter criteria on the index definition so that a particular sub set of rows in a table alone can be indexed. Filter indexes can be only created as non clustered index

Syntax
CREATE INDEX Index Name ON Table Name (Columns..) Filter Criteria

Example
CREATE INDEX IX_RegistrationDate ON Employee (RegistrationDate) WHERE RegistrationDate IS NOT NULL

Advantages of Filtered Index

• Improved Performance: The performance of the query is improved especially with larger tables as it has to scan through as lesser number of records
  
• Lesser Maintenance Cost: Since the size of the index is smaller compared to full table index the index maitntenance cost will be much lesser. Also index maintenance jobs like update statics could be faster.


• Lesser Storage: The amount of space required for index storage will also be very less since the size of the index is smaller compared to the full table index

Analysis

I have created a table patient with 1 lakh records of different Organisations and populated 70% data with OwnerOrganisation value 10 and selected the record with OwnerOrganisation value ="6"

Normal Index

CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID)

Filterer Index to exclude records of Organisation =10

CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID) WHERE OwnerOrganisationUID <>10




Conclusion

Index creation is always case to case basis as the need to create a filtered index should be carefully analysed based on the WHERE clause and the data distribution in the table. It is recommended to create filtered indexes if the data retrieved to be a smaller subset. Scenarios like columns with NULL data as major set and NOT NULL values of defined subsets could be a suitable candidate

How to find the Database Restore Details in SQL Server 2008

It is often useful to know the details from where the database is restored.The restore history of the database is stored in the msdb table "restorehistory".I recently came across this query which gives the complete information of restore history including the Restored Login , From & To Location of the Files

SELECT
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
CASE WHEN rsh.restore_type = 'D' THEN 'Database'
WHEN rsh.restore_type = 'F' THEN 'File'
WHEN rsh.restore_type = 'G' THEN 'Filegroup'
WHEN rsh.restore_type = 'I' THEN 'Differential'
WHEN rsh.restore_type = 'L' THEN 'Log'
WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
WHEN rsh.restore_type = 'R' THEN 'Revert'
ELSE rsh.restore_type
END AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id


You can apply filter criteria such as the restore date , Database by addding a where clause to the existing query as shown below

WHERE
rsh.restore_date >= DATEADD(dd, "No of Past Days" , GETDATE())
AND destination_database_name = ISNULL( "DB Name", destination_database_name)
ORDER BY rsh.restore_history_id DESC

SQL Server 2008 - Performance white paper

Microsoft has release its latest white paper on SQL Server 2008 performance trouble shooting.This article provides steps to diagonse the common performance problems faced by Microsoft CSS team.The following areas are covered in depth

• CPU Bottlenecks
• Memory Bottlenecks
• IO Bottlenecks
• Temp DB
• Slow Running Queries
• Extended Events
• Data Collector & MDV

The new features of SQL Server 2008 such as Extended Events & Data Collector are covered in detail .It is worth to have a look at this long running ( 102 pages) document. It is avilable for download at
http://msdn.microsoft.com/en-us/library/dd672789.aspx

SQL Server 2008 SP1- Released

Microsoft has released the latest service pack for SQL Server 2008 with the following key improvements

• SlipStream - The SQL Server 2008 and Service Pack 1 installation can be integrated and installed in a single step.
• Service Pack Uninstall – We can uninstall the service pack alone ( no need to un install the entire service)
• Report Builder 2.0 Click Once capability

It is available for download at

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19

SQL Server 2008 DMV's Relationship mapping

Microsoft has introduced the concept of DMV’s (Dynamic Management Views) in SQL Server 2005 and concept is extended to SQL Server 2008 also with additional DMV's for mirroring , memory management etc.

Microsoft has recently released the latest “System Views Map” for SQL Server 2008 which shows the key system views and the relationships between them. You can download the latest System Views Map at

http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en

The updated “System views Map” for SQ Server 2005 also can be downloaded at

http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en

Reasons for slower Delete in SQL Server

Delete statement plays a major role in many of the database maintenance activities. Extreme care should be taken before executing the DELETE statements
Before executing the DELETE statement it is better to check the “Estimated execution plan”, so that we can create proper indexes to speed up the execution .I have given some of the possible reasons for slower delete.

1. Locking / Blocking - If it is a production database process is having Lock / Block on the table

2. Fragmentation - The Index pages are fragmented due to excessive delete on the table (Defragment the Indexes and try again)

3. The table you are trying to delete is referred by many tables as foreign key and those columns are not indexed.

4. There might be hanging transactions on the table - Try to truncate the Log and do

5. You can also change the Recovery Mode to simple and try - but not advised in case of production DB

Also it is recommended to execute the DELETE statements in smaller batches so that the Log space & Roll back of the records can be easier as shown below

// assume you wanted to delete 100000 records, we can split them into 10 batches as
DECLARE @V_Count INT =0
WHILE @V_Count < 100000
BEGIN
DELETE TOP(10000)
FROM Table
WHERE COLUMN = Condition
SET @V_Count =@V_Count+10000
END

How to combine Multiple Rows into Single Column in SQL Server

I have come across a link where you can combine multiple rows into single column using the COALESCE function.It is very simple as shown below

CREATE TABLE WeekDays
([Name] varchar(40))
INSERT INTO WeekDays VALUES('Mon');
INSERT INTO WeekDays VALUES('Tue');
INSERT INTO WeekDays VALUES('Wed');

DECLARE @str VARCHAR(2000)
select @str = COALESCE(@str + ',', '') + [Name]
from WeekDays
SELECT @str

Output :Mon,Tue,Wed

You can see the original article on
http://www.sqlservercurry.com/2008/06/combine-multiple-rows-into-one-row.html

How to import Database Schema to XML

I often get questions on how to import database schema into XML file for doing activities like Data Comaprison etc. I have written a small script to generate the database schema including the following

• Column Details ( Name, data type ,length etc)
• Primary Key
• Foreign Key
• Indexes

Sample Script

SELECT a.name TableName,
( SELECT
c.name ColumnName,type_name(c.xusertype) DataType,
CASE WHEN type_name(c.xusertype)='NUMERIC' THEN CAST(c.prec AS SMALLINT)
WHEN type_name(c.xusertype)='UNIQUEIDENTIFIER' THEN NULL
WHEN type_name(c.xusertype)='BIGINT' THEN
CASE WHEN colstat =1 THEN CAST(IDENT_SEED(a.name) AS SMALLINT)
END
ELSE CAST(c.prec AS SMALLINT)
END DataLength,
CASE WHEN type_name(c.xusertype)='NUMERIC' THEN c.scale
WHEN type_name(c.xusertype)='BIGINT' THEN
CASE WHEN colstat =1 THEN CAST( IDENT_INCR(a.name) AS INT)
END
ELSE NULL
END Scale,
CAST(c.isnullable AS BIT) As IsNullable,NULL AS DataDefault,NULL AS DefConstraintName,
CASE WHEN colstat=1 THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS IsIdentColumn
FROM SYSColumns c
WHERE c.id = OBJECT_ID(a.name)
and a.id = c.id
AND C.CDEFAULT =0
FOR XML AUTO, TYPE
) columns,
(select 'UID' ColumnName,f.name PrimaryKeyName, f.type_desc PrimaryKeyType
from sys.indexes f
where f.object_id = a.id
AND f.NAME IS NOT NULL
AND f.Is_Primary_Key =1
AND OBJECT_ID > 97
FOR XML AUTO, TYPE
)PrimaryKey,
(Select
object_name(rkeyid) Parent_Table,object_name(fkeyid) Child_Table, object_name(constid) FKey_Name, c1.name FKey_Col,c2.name Ref_KeyCol
From
sys.sysforeignkeys s
Inner join sys.syscolumns c1
on ( s.fkeyid = c1.id And s.fkey = c1.colid )
Inner join syscolumns c2
on ( s.rkeyid = c2.id And s.rkey = c2.colid )
where s.fkeyid = a.id
FOR XML RAW,TYPE
) ForeignKey,
(select f.name IndexName ,DBO.fGetIndexCols (object_NAME(f.object_id), f.index_id ) IndexColumn,
f.type_desc IndexType
from sys.indexes f
where f.object_id = a.id
AND f.NAME IS NOT NULL
AND f.Is_Primary_Key =0
AND OBJECT_ID > 97
FOR XML AUTO, TYPE
) Indexes
from sysobjects a
where a.xtype ='u'
FOR XML PATH('Table'), ROOT('TableDetails')

SQL Server 2008 – Inline variable initialization

Microsoft has extended the support of inline variable initialization feature from the programming languages in to T-SQL in SQL Server 2008.

When we want to declare and initialize a value to variable in T- SQL, we need to do it is two steps (declaration & initialization) as shown in the example

DECLARE @V_Value DATETIME
SET @V_Value =GETDATE()

In SQL Server 2008 this can be simplified by combining both the lines into a single steps as we do in programming languages

DECLARE @V_Value DATETIME = GETDATE()

Even though it is a very small feature, it helps the developers who have the technical background for programmig languages like JAVA, C#.NET , VB.NET etc

Turn Off - Prevent saving changes in SQL Server 2008

I was working with SQL Server 2008 for quite some time and noticed a strange behavior (which is not in previous versions of SQL Server) when I try to save a table in Management Studio that requires table to be dropped and recreated (e.g. Try to ADD a new NOT NULL column to the table which is having data) .I get the following warning message

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

I though it is bug with SQL Server, but later cam to know it is the expected behavior and can be turned of by unchecking "Prevent saving changes that require table re-creation" in the Designer properties. Please refer to the below screen shot

Paging using LINQ

SQL Server supports many ways of implementing paging in the applications. With the introduction of LINQ (Language Integrated Query) support in VS 2008 paging is further made simple. Please follow the below steps to implement paging for Employee List screen

Step 1 :
Write a method to retrieve the employee list from the table “Employee” using LINQ as shown in the method “GetEmployeeList”

public List GetEmployeeList(int PageNo ,out int TotalPages)
{
Int NoofRecordsPerPage =10;
List oResultOut = new List();
var query = (from p in dc.Employees
select p).ToList();
if (PageNo == 0)
TotalPages = query.Count / NoofRecordsPerPage;

oResultOut = query.Skip(PageNo * NoofRecordsPerPage)
.Take(NoofRecordsPerPage)
.ToList();

}
The parameter “PageNo” is used to determine the current page of Employee List view. It is often required to know the total number of pages, the List view will span during the first execution of the query. The above method will fill the total number of pages using the “query.Count” attribute.
The query.Skip() will filter the records of the previous pages and the query.Take() method will show only the records qualified for the current page. The number of records to be displayed on each page can be controlled using the variable “NoofRecordsPerPage” .

Step 2 :
Calling the “GetEmplyeeList” from the UI based on current page selected by the user in the Employee List view

// assume the user sees the first page
List oResult = new List();
int TotalPages =0;
oResult = GetEmployeeList( 0, out Totalages);

// assume the user sees the fifth page
List oResult = new List();
int TotalPages =0;
oResult = GetEmployeeList( 5, out Totalages);

We will see the benefits of using LINQ and how to implement paging for retrievals using the stored procedures using DLINQ in my next post

SQL Server 2008 CTP is released

Microsoft has released its much-awaited SQL Server 2008 (known popularly as “Katmai”) CTP version that is designed to meet the Data Platform vision of the Microsoft. The SQL Server 2008 capabilities deliver on the four key areas of the data platform vision.

• Mission-Critical Platform –SQL Server 2008 Declarative management Framework (DMF) will allow you to manage your SQL Server configuration across many databases and servers by defining policy rules that are automatically applied, monitored and enforced. SQL Server 2008 also protects valuable information in existing applications and disconnected devices. In addition, SQL Server 2008 delivers predictable query performance with an optimized platform.
• Dynamic Development – SQL Server 2008 along with the .NET Framework enables developers to build the next generation of applications. Developers are more productive because they work with business entities instead of tables and columns. They can build applications that enable users to take their data with them and synchronize their data with back-end servers.
• Beyond Relational Data – SQL Server 2008 supports developers to consume any type of data, from XML to documents and build applications that incorporate location awareness that can solve existing globalization problems.
• Pervasive Business Insight – SQL Server 2008 provides a scalable infrastructure that can manage reports and analysis of any size or complexity while at the same time empowering users because of its close integration with the Microsoft Office System. This enables IT to drive business intelligence throughout the organization. SQL Server 2008 makes great strides in data warehousing, enabling users to consolidate data marts in an enterprise data warehouse.

To know more about Microsoft Data Platform Vision and how SQL Server 2008 meets the needs of the next generation of data-driven applications please find the below white paper from Microsoft

http://www.microsoft.com/sql/techinfo/whitepapers/sql2008Overview.mspx

Microsoft also allows the free download of the CTP version for those registered with the SQL Server 2008 CTP program.