SireeS SQL Server Impresssions

When to Reorganize/Rebuild Index

2009-10-01T20:47:00.003-04:00

I had posted about using SQL Server 2005 DMV in the past. I had been thinking about adding some more details for a long time. Finally here it is..

When the data in the underlying tables change, indexes become fragmented (ie. logical ordering of index does not match with the physical ordering of the underlying table). When indexes get fragmented, the performance of the queries will suffer.

So most of the DBAs add to rebuild/reorganize the indexes in their maintenance jobs. Here are the differences between reorganizing and rebuilding the indexes.

Reorganize

Rebuild

Rebuild online in SQL Server 2005

It causes the data in the outermost data pages to reorder and will compact the index

Drops the desired index and creates a new index.

The database engine creates a new index and it is needs additional disk space to accommodate two concurrent indexes. During the process the new index is a write-only index and modifications to original index and applied to the new one. Once the process it done, it drops the original index.

Takes less system resouces and it is an online operation

Since the index is removed and recreated, it is an offline operation

The database engine will still have the original index available to the users while it building a new index. It uses Row Versioning to maintain the transactional consistency and integrity.

I personally look at the page count along with the avg_fragmentation_in_percent because high fragmentation on big indexes will have higher performance impact.

Even if you rebuild all the indexes, you may notice fragmentation for some tables will remain high. This is because if there are less than 1000 pages affected then the fragmentation will not be affected. For smaller tables, SQL server will prefer to use a table scan instead of an index.

Here is the script that I use

--Script to Reorganize/Rebuild indexes

DECLARE @cmd VARCHAR(1000)

DECLARE @Table VARCHAR(255)

DECLARE @SchemaName VARCHAR(255)

DECLARE @IndexName VARCHAR(255)

DECLARE @AvgFragmentationInPercent DECIMAL

DECLARE @fillfactor INT

DECLARE @ThresholdLowerLimit VARCHAR(10)

DECLARE @ThresholdUpperLimit VARCHAR(10)

DECLARE @PageCount INT

/* You can specify your value for reorganize and rebuild indexes

If avg_fragmentation_in_percent >= 5 and <30> 1000 Then Reorganize the Index

If avg_fragmentation_in_percent >= 30 AND Page_Count > 1000 Then Rebuild the Index

SET @fillfactor = 90 --you can specify value for fill factor

SET @ThresholdLowerLimit= '5.0' -- this value is percentage

SET @ThresholdUpperLimit = '30.0' -- this value is percentage

SET @PageCount = 1000 --if pages are less then fragmentation would potentially remain same; This is not a magical cut off point but a start

-- ensure the temporary table does not exist

IF (SELECT OBJECT_ID('tempdb..#IndexList')) IS NOT NULL

DROP TABLE #IndexList;

--Select all the indexes that have >= 5% percent fragmentation and PageCount > 1000

SELECT OBJECT_NAME(S.OBJECT_ID) AS [TableName],schema_name(T.schema_id) AS [SchemaName],

OBJECT_NAME(S.object_id) [IndexName],S.page_count, S.avg_fragmentation_in_percent, 0 AS IsDefraged INTO #IndexList

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) S

JOIN sys.tables T WITH (nolock) ON S.OBJECT_ID = T.OBJECT_ID

WHERE T.is_ms_shipped = 0 --We only need user tables

AND S.avg_fragmentation_in_percent >= CONVERT(DECIMAL, @ThresholdLowerLimit)

AND S.page_count > @PageCount

ORDER BY S.page_count DESC

WHILE EXISTS ( SELECT 1 FROM #IndexList WHERE IsDefraged = 0 )

BEGIN

SELECT TOP 1 @Table = TableName, @AvgFragmentationInPercent = avg_fragmentation_in_percent,

@SchemaName = SchemaName, @IndexName = IndexName

FROM #IndexList

WHERE IsDefraged = 0

--Reorganizing the index if avg_fragmentation_in_percent > 5 and < style="">

IF((@AvgFragmentationInPercent >= @ThresholdLowerLimit) AND (@AvgFragmentationInPercent < @ThresholdUpperLimit))

BEGIN

SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REORGANIZE'

EXEC (@cmd)

PRINT N'Reorganize Index completed successfully for [' + @Table + ']' + CONVERT(VARCHAR, GETDATE())

END

--Rebuilding the index if avg_fragmentation_in_percent > 30

ELSE IF (@AvgFragmentationInPercent >= @ThresholdUpperLimit )

BEGIN

SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ', STATISTICS_NORECOMPUTE = OFF)'

EXEC (@cmd)

PRINT N'Rebuild Index completed successfully for [' + @Table + ']' + CONVERT(VARCHAR, GETDATE())

END

--Update the temp table

UPDATE #IndexList

SET IsDefraged = 1

WHERE TableName = @Table

AND IndexName = @IndexName

END

DROP TABLE #IndexList

Get the id of the newly inserted row

2009-09-30T22:42:00.001-04:00

There are times when we need to retrieve the last inserted identity of a row. Here is a scenario

You need to insert a record into a table
You need the Id of the row you inserted, so you can use it to insert rows into tables referencing the first row

SQL Server has the options:

SCOPE_IDENTITY()– Gives the last identity value that was generated in the current scope. In other words, it will give the last identity value that you have explicitly created.

@@Identity –Gives the last identity value that was generated in the current session but in any scope.

IDENT_CURRENT(’tablename’) – Gives the last identity value produced in the table regardless if the connection it the scope.

In our scenario, SCOPE_IDENTITY() works perfect to return the identity of the recently added row.

SQL Server 2008 Resource Governor

2009-06-16T22:07:00.001-04:00

Resource Govenor enables to manage SQL Server workload and resource utilization.

Terminology:

Resource Pool – It is a collection of physical resources. SQL Server 2008 has two built in pools, default and internal. It allows to create maximum of 18 user defined pools.

Workload Group – It is a container for the requests that are similar to the classification criteria applied to each request. Like Resource pools there are two built in workload groups, default and internal.

Classifier Function – It is a user written criteria that is being applied to the requests.Each user session would be assigned to a particular workgroup load using the classifier function.

Limitations:

Limited to only CPU bandwidth and memory management
We can only control resource utlization for database engine. It cannot be used with SSIS, Analysis or Reporting services
Only one instance of SQL Server can be managed

Here is just an example of creating resource pool and workload group

--Enable Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE

USE [master]

-- Create resource pool to be used

CREATE RESOURCE POOL poolAdhoc

WITH (

-- allocate at least % of the CPU bandwidth for admin queries

MIN_CPU_PERCENT = 10

-- do not let them exceed % either

, MAX_CPU_PERCENT = 20

-- admin queries should be small and we will allocate about %

-- of the server memory for them

, MIN_MEMORY_PERCENT = 5

, MAX_MEMORY_PERCENT = 5 )

-- Create adhoc workload group

CREATE WORKLOAD GROUP groupAdhoc

WITH (

-- use all defaults

IMPORTANCE = MEDIUM

, REQUEST_MAX_MEMORY_GRANT_PERCENT =25

, REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0

, REQUEST_MAX_CPU_TIME_SEC = 50

, MAX_DOP = 0

, GROUP_MAX_REQUESTS = 0

)

USING

poolAdhoc

-- Create admin workload group

CREATE WORKLOAD GROUP groupAdmin

WITH (

-- use all defaults

IMPORTANCE = MEDIUM

, REQUEST_MAX_MEMORY_GRANT_PERCENT =25

, REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0

, REQUEST_MAX_CPU_TIME_SEC = 30

, MAX_DOP = 0

, GROUP_MAX_REQUESTS = 0

)

USING

poolAdhoc

CREATE FUNCTION [dbo].[rgClassifier_Adhoc]()

RETURNS sysname

WITH SCHEMABINDING

BEGIN

-- Define the return sysname variable for the function

DECLARE @grp_name AS sysname;

SET @grp_name = 'default';

-- Specify the T-SQL statements for mapping session information

-- with Workload Groups defined for the Resource Governor.

IF (IS_SRVROLEMEMBER ('sysadmin') = 1)

SET @grp_name = 'groupAdmin'

IF (APP_NAME() LIKE '%Management Studio%')

SET @grp_name = 'groupAdhoc'

RETURN @grp_name;

END

-- Set the classifier function for Resource Governor

ALTER RESOURCE GOVERNOR

WITH (

CLASSIFIER_FUNCTION = dbo.[rgClassifier_Adhoc]

)

-- Make changes effective

ALTER RESOURCE GOVERNOR RECONFIGURE

SQL Server 2008 – Where is Activity Monitor?

2009-06-05T20:14:00.002-04:00

For a minute I thought they deprecated the feature. I use it a lot to check the activity going on the databases.

They moved it to the launch point and the new one is really cool. Here is a blog with screenshots.

http://blogs.msdn.com/dtjones/archive/2008/07/10/where-the-heck-is-activity-monitor.aspx

SQL Server 2005 Pivots with Dynamic columns

2009-05-30T20:07:00.001-04:00

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Report Development - Report Requirements Document

2009-05-30T19:28:00.001-04:00

The purpose of report requirements document is to define the requirements for each report or group of reports.

Personally I don’t like to read 80 pages of technical documentation for a report. Nor would I want to scratch my head wondering what the report is doing Vs what it is supposed to do.

Here is a quick checklist of items to include in the report requirements documentation

Business need for the report
Requirements from the point of view of the end users
The selection and the filter criteria
Data elements in the report
Calculations if any
Parameters in the report
Sort, grouping and totaling requirements
Appearance and style requirements (i.e. layout of the report, column names to be shown etc)
Performance requirements (speed and latency)
Security requirements (who needs access to the report)

Microsoft Chart Controls

2009-05-23T06:04:00.002-04:00

Great blog about ASP.net charting control

http://weblogs.asp.net/scottgu/archive/2008/11/24/new-asp-net-charting-control-lt-asp-chart-runat-quot-server-quot-gt.aspx

Bad Data Models

2009-05-13T22:03:00.001-04:00

We all know what a good data model is and spend significant time designing a good data model. So if there are good data models then there are bad data models too.

There is a difference between incorrect data models and bad data models. An incorrect data model would not satisfy business requirements and hence they don't make to the implementation. But bad data models would get implemented. They do more harm to the system. Every part of the application has to compensate for the bad data model.

Bad data models evolve due to various reasons – time and resource constraints, inexperience, over engineered modeling and sometimes sloppiness.

SQL Server 2008 – Maximum Capacity Specs

2009-05-07T19:17:00.000-04:00

I found this article which gives the maximum size specifications for SQL Server 2008

http://msdn.microsoft.com/en-us/library/ms143432.aspx

SQL Server 2008 Min and Max Date Ranges

2009-05-07T19:15:00.000-04:00

Here are the Minimum and Maximum date ranges

SQL Server 2005
Min Date: 1753-01-01 00:00:00.000
Max Date: 9999-12-31 23:59:59.999

SQL Server 2008
Min Date: 0001-01-01 00:00:00.000
Max Date: 9999-12-31 23:59:59.999

Reporting services 2008 does not need IIS

2009-03-27T18:06:00.001-04:00

Here is how it handles without IIS

http://blogs.msdn.com/bwelcker/archive/2007/12/04/everybody-why-leave-iis.aspx

SSIS Configurations

2009-02-12T21:58:00.001-05:00

There is a significant change in how SSIS applies configurations in SQL Server 2008. The configurations are applied twice – once before applying the command line options and once after applying the command line options. So it overwrites the value specified on the command line.

This is a nice improvement since you can set up a package with SQL Server Configurations and change the connection string for SQL Server configuration.

Check this link for more information

http://msdn.microsoft.com/en-us/library/cc671625.aspx

Truncate Mirrored Database Log File

2009-02-01T06:31:00.000-05:00

If you are running asynchronous database mirroring, then there could be a backlog of transaction log records that have not been sent from the principal to the mirror (called the database mirroring SEND queue). The transaction log records cannot be freed until they have been successfully sent. With a high rate of transaction log record generation and limited bandwidth on the network (or other hardware issues), the backlog can grow quite large and cause the transaction log to grow.

On the mirrored database, you cannot backup the log file with TRUNCATE_ONLY. Here the steps to shrink the log file for a database participating in mirroring

Backup the log file to a location

BACKUP Log YourDatabaseName TO DISK = 'D:\BACKUP\DBNAME_20090201.TRN'

Check if there is enough free space on perform the shrink operation

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

FROM sys.database_files;

DBCC SQLPERF(LOGSPACE);

If there is no sufficient free space then the shrink operation cannot reduce file size.

Check if all the transactions are written into the disk

DBCC LOGINFO('YourDatabaseName')

The status of the last transaction should be 0. If not, then backup the transaction log once again.

Shrink the log file

DBCC SHRINKFILE(logfilename , target_size)

If the transaction lof file does not shrink after performing the above steps then backup the log file again to make more of the virtual log files inactive.

Also check the column LOG_REUSE_WAIT_DESC in the sys.databases catalog view to check if the reuse of the transaction log space is waiting on anything.

Check this link to find the factors that can delay log truncation

http://msdn.microsoft.com/en-us/library/ms345414(SQL.90).aspx

Connecting to Remote SSIS Server

2009-01-27T21:50:00.003-05:00

If you get the “Access is denied” error while connecting to remote SSIS server, you may have to perform the steps outlined in the following link

http://msdn.microsoft.com/en-us/library/aa337083.aspx

Additional steps to perform which are not in the MSDN page

Add the user to the Distributed COM Users group on the server
Restart the SSIS service after making changes
At the minimum, add the user to the MSDB db_dtsltduser role to edit their own packages. Check this link for more permissions on the msdb database

http://msdn.microsoft.com/en-us/library/ms141053.aspx

SSIS 2008 New Features

2009-01-27T21:48:00.000-05:00

This is a neat article which outlines the new SSIS features in SQL Server 2008

http://www.simple-talk.com/content/print.aspx?article=541

SQL Server 2008 Top 10 T-SQL features

2009-01-13T20:21:00.003-05:00

Here is the list of new T-SQL features that I really like in SQL Server2K8.

1.Intellisense in SQL Server Management Studio – It is easy to learn the database schema and the objects on the fly with this feature

2.Inline Variable Assignment – Inline variable initialization is possible now

DECLARE @i INT = 2

SELECT @i AS VALUE

3.Compound Assigment of variables

DECLARE @i INT = 3

SELECT @i += 1

SELECT @i

4.Filtered Indexes - Filtered index is used to index a portion of rows in a table. It is an optimized nonclustered index which can improve query performance and reduce index cost.

5.Table Valued Parameters – These are new parameter types in SQL Server 2008 which allows to send mulitple rows of data to a T-SQL statement or routine.

6.Row Constructors – These allow to insert multiple rows with a single INSERT statement

7.Merge statement – It is a new DML statement which allows to perform Insert/Update/Delete operations on one table using single scan.

8.New datatypes – SQL 2K8 has various new datatypes like spatial datatypes, datetime datatypes (DATE, TIME, DATETIME2) and Hierarchy datatypes. Of all FileStream Data type interests me the most (we don’t have to be creative inventing ways to store unstructured data anymore).

9.Grouping sets – This clause allows us to easily specify combinations of groupings to see different levels of aggregated data.

10.Sparse Columns – Columns created with this clause takes less space than the regular columns when it contains zero or null. These also allow us to create tables with more than 1024 columns.

SQL Server 2008 – What happened to Surface Area Configuration tool?

2009-01-11T06:24:00.002-05:00

Have you ever wondered what happened to the Surface Area Configuration (SAC) tool in SQL Server 2008? It has been removed in SQL Server 2008. SQL Configuration Manager or SSMS can be used to configure settings, options and component features.

Here is an article from BOL

http://msdn.microsoft.com/en-us/library/cc281850.aspx

SQL Server 2008 Merge Statement Error

2008-12-27T15:52:00.000-05:00

If multiple rows match in the target for the criteria then MERGE statement will throw the following error

Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times.

MERGE statement is deterministic whereas update with join will update any row without error. You can use one of the following options to fix the error

1.Make sure that the target row matches at most one source row
2.Use a GROUP BY to group the source rows.

In-line variable Initialization and Compound Assignment

2008-12-06T07:22:00.000-05:00

SQL Server 2008 supports in-line variable initialization and compound assignment, a feature which developers have been enjoying for years. This feature saves lot of code and it makes it more readable.

In line Variable Assignment

--SQL Server 2005

DECLARE @i INT

SET @i = 1

SELECT @i AS VALUE

--SQL Server 2008

DECLARE @i INT = 2

SELECT @i AS VALUE

Compound Assignment:

--Simple variable

DECLARE @i INT = 3

SELECT @i += 1

SELECT @i

--DML Statements

--Create table

CREATE TABLE Color

(ID int, ColorName varchar(10))

--Populate Staging table

INSERT INTO Color VALUES (1, 'Red') , (2, 'Blue') , (3, 'Green')

SELECT ColorName FROM Color WHERE ID = 1 --returns Red

UPDATE Color SET ColorName += '-B'

WHERE ID = 1

SELECT ColorName FROM Color WHERE ID = 1 --returns Red-B

SQL Server 2008 Data Compression

2008-12-06T06:06:00.001-05:00

SQL Server 2008 offers two different forms of data compression – Row and Page level compression.

Row Compression

Row level compression does not apply any algorithm to compress the data. It changes the physical storage format which is efficient in storing the numeric and fixed length character data types. It minimizes the metadata (column information, length, offset etc) associated with a row. No application changes are required when tables are enabled for compression.

Here is an example

--Create a table

CREATE TABLE TestData

(ID int,DataValue varchar(50))

--Insert data into the table

DECLARE @i INT

SET @i = 1

WHILE (@i <= 100000)

BEGIN

INSERT INTO TestData VALUES (@i, 'value:' + cast(@i AS VARCHAR(10)))

SET @i = @i + 1

END

--Check for the savings if row level compression is enabled

sp_estimate_data_compression_savings 'dbo','TestData',NULL,NULL,'Row'

--Alter the table to apply row level compression

ALTER TABLE TestData REBUILD

WITH (DATA_COMPRESSION = ROW)

Page Compression

This shares the common data between rows in a given page. It uses the both Row Compression and Prefix Compression.

Prefix Compression

For every column the duplicate prefixes are saved in the compressed information headers (CI) which reside after the page header. A reference number is assigned to the prefix and it is replaced on where the prefixes are used.

Dictionary Compression

All the duplicate values in the entire page are stored on the CI. Prefix compression is applicable to columns where as dictionary compression is applicable to the entire page.

--Check the savings is Page level compression is enabled

sp_estimate_data_compression_savings 'dbo','TestData',NULL,NULL,'Page'

--Alter the table to apply page level compression

ALTER TABLE TestData REBUILD

WITH (DATA_COMPRESSION = Page)

Compressing a table is a CPU intensive operation. There is an optional parameter called maxdop which can be used to specify the max number of processors we want the rebuild to use.

--Alter the table to apply page level compression

ALTER TABLE TestData REBUILD

WITH (DATA_COMPRESSION = Page, MAXDOP = 4)

For more information here

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/11/12/types-of-data-compression-in-sql-server-2008.aspx

This article explains the compression ratios with real world databases

http://sqlblog.com/blogs/linchi_shea/archive/2008/05/11/sql-server-2008-page-compression-compression-ratios-from-real-world-databases.aspx

SQL Server 2008 – Backup Compression feature

2008-12-02T20:23:00.001-05:00

To compress a backup specify COMPRESSION in the WITH clause of the backup command.

Here is an example:

BACKUP DATABASE YourDatabaseName

TO DISK = 'C:\YourDatabaseName.bak'

WITH INIT, COMPRESSION

Compressing a backup typically needs less I/O, because it is smaller so it increases the speed of the backup. But compression process consumes CPU usage.

By default backup compression is off. To enable it we can use the ‘backup compression default’ configuration.

Use master

Exec sp_configure 'backup compression default', '1'

RECONFIGURE

You don’t have to specify anything to restore the compressed backup

SQL Server 2008 - Row Constructors

2008-12-01T19:19:00.001-05:00

There are times when we need to insert multiple rows into a table. SQL Server 2008 has row constructors which simplifies the data insertion process.

Here are the various ways we can insert multiple rows with a single INSERT statement

--Create a table

CREATE TABLE RealTable

(ID int, name varchar(10))

-- Insert with VALUE clause

INSERT INTO RealTable Values (1,'Mary')

INSERT INTO RealTable Values (2,'Jim')

--Insert with Select clause

INSERT INTO RealTable SELECT 3, 'Hillary'

INSERT INTO RealTable SELECT 4, 'Lisa'

--Insert with UNION ALL

INSERT INTO RealTable

SELECT 5, 'Rick'

UNION ALL

SELECT 6, 'John'

UNION ALL

SELECT 7, 'Tom'

--Insert with Row Constructors (new in SQL Server 2008)

INSERT INTO RealTable (ID, Color)

VALUES

(8, 'Toni'),

(9, 'Joe'),

(10, 'dave')

SQL Server 2008 MERGE

2008-11-27T10:18:00.000-05:00

MERGE statement performs insert,update or delete operations on target table based on the results of a join with source table.

Here is a simple example

--Staging table

CREATE TABLE Staging

(ID int, Color varchar(10))

--Real table

CREATE TABLE RealTable

(ID int, Color varchar(10))

--Populate Staging table

INSERT INTO Staging VALUES (1, 'Red') , (2, 'Blue') , (3, 'Green')

--Merge statement

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

SELECT * FROM RealTable

--Test the update

UPDATE Staging SET Color = 'Pink' WHERE ID = 3

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

SELECT * FROM RealTable

--Test the delete

DELETE FROM Staging WHERE ID = 2

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

SELECT * FROM RealTable

DROP TABLE RealTable

DROP TABLE Staging

UPSERT functionality

2008-11-27T09:44:00.000-05:00

It UPDATEs rows if they exist in the selected row set or INSERTs rows if they don’t exist in one Sql command.

SQL Server 2005 does not have this feature. You will need 2 queries to get the functionality.

Here is a simple example

--Staging table

CREATE TABLE Staging

(ID int, Color varchar(10))

--Real table

CREATE TABLE RealTable

(ID int, Color varchar(10))

--Populate Staging table

INSERT INTO Staging VALUES (1, 'Red') , (2, 'Blue') , (3, 'Green')

--UPSERT Functionality

--Perform UPDATE first

UPDATE RealTable

SET Color = S.Color

FROM RealTable AS RT (NOLOCK)

INNER JOIN Staging S (NOLOCK) ON S.ID = RT.ID

--Perform INSERT

INSERT INTO RealTable(ID,Color)

SELECT ID, Color FROM Staging AS S

WHERE NOT EXISTS (SELECT * FROM RealTable AS RT WHERE RT.ID = S.ID)

SELECT * FROM RealTable

--Test the update

UPDATE Staging SET Color = 'Pink' WHERE ID = 3

UPDATE RealTable

SET Color = S.Color

FROM RealTable AS RT (NOLOCK)

INNER JOIN Staging S (NOLOCK) ON S.ID = RT.ID

--Perform INSERT

INSERT INTO RealTable(ID,Color)

SELECT ID, Color FROM Staging AS S

WHERE NOT EXISTS (SELECT * FROM RealTable AS RT WHERE RT.ID = S.ID)

SELECT * FROM RealTable

DROP TABLE RealTable

DROP TABLE Staging

SQL Server 2008 - Policy Based Management

2008-11-19T00:49:00.000-05:00

After lots of confusion between DMF (Dynamic Management Functions) and DMF (Declarative Management Framework), a new name is given to be clear – Microsoft SQL Server 2008 Policy Based Management.

Policy Based Management allows to define and enforce the polices for configuring and managing SQL Servers across the enterprise. Here is just a brief description of the terminology.

Target – Object to be managed

Facet – Property of the object(target)

Condition – list of criteria for target’s facets

Policy - Set of conditions on the facets of a target

Evaluation Modes

On Demand – policy is evaluated only when manually run by admin

On change: Prevent – DDL triggers prevent policy violations

On Change: log only – Event notifications are used to check the policy when changes are made

On Schedule – SQL Agent job checks the policies for periodical violations

SSIS Data types and expressions

2008-10-17T20:52:00.000-04:00

Here is the link to integration services data types

http://msdn.microsoft.com/en-us/library/ms141036.aspx

Expressions in SSIS:

To convert today’s date (getdate()) to string (Format: 2008-05-07)

SUBSTRING((DT_STR, 30, 1252)GETDATE(), 1, 10 )

To convert today’s date (getdate()) to string (format: 20080507)

( (DT_STR, 4, 1252) Year(GETDATE() )+RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) +RIGHT("0" + (DT_STR, 2, 1252) Day(GETDATE() ), 2) )

To convert today’s date (getdate()) to int (Format: 20080507)

(DT_I4) ( (DT_STR, 4, 1252) Year(GETDATE() )+RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) +RIGHT("0" + (DT_STR, 2, 1252) Day(GETDATE() ), 2) )

Grant permissions to view stored procedure text

2008-10-16T23:31:00.000-04:00

Sometimes in a production environment you may want to give developers read access to the text of the stored procedures but not be able to modify or execute them. In SQL Server 2005, you can grant VIEW DEFINITION permissions. Here are different ways to it

To grant view permissions to the entire schema

GRANT VIEW DEFINITION ON SCHEMA::dbo TO [UserName]

To grant permissions to specific stored procedure

GRANT VIEW DEFINITION ON YourStoredProcedureName TO [UserName]

To grant view definition on all the stored procedures in the database

--temporary table

DECLARE @tmpTable TABLE (

PK_ID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,

[name] SYSNAME

)

--declare variables

DECLARE @name SYSNAME,

@RowCount INT,

@RecCount INT,

@strSQL VARCHAR(1000)

INSERT INTO @tmpTable ([name])

SELECT ROUTINE_SCHEMA+'.'+ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME NOT LIKE 'dt_%'

-- counters for while

SET @RecCount = (SELECT count(*) FROM @tmpTable)

SET @RowCount = 1

WHILE (@RowCount < @RecCount + 1)

BEGIN

SELECT @name = [name]

FROM @tmpTable

WHERE PK_ID = @RowCount

SET @strSQL = N'Grant VIEW Definition on ' + rtrim(cast(@name AS VARCHAR(128))) + ' to [UserName]'

--Execute the Sql

EXEC(@strSQL)

--Decrement the counter

SET @RowCount = @RowCount + 1

--reset vars, just in case...

SET @name = null

END

SELECT * FROM @tmpTable

Database Snapshots

2008-10-16T23:06:00.000-04:00

Database snapshot is a read-only static database which provides the view of data at a point in time. Snapshots use the process of copy on write operation. If a page in the source database is modified for the first time, the original page is copied from the database to the snapshot. Updates after that don’t affect the contents of the snapshot.

Snapshots are very useful to off load historical reports from the OLTP systems. If mirroring is used for high availability, snapshots can be created on the mirror database.

SQL server management studio does not provide an option to create snapshots. The following T-SQL creates a snapshot named ProductSnap_0100 (0100 is the time stamp of when the database is created i.e. 1am) on the Products database with its sparse file named Products_data.ss.

--Drop if the snapshot exists

IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'ProductSnap_0100')

DROP DATABASE ProductSnap_0100

--Create a snapshot on CORIS database

CREATE DATABASE ProductSnap_0100

ON (NAME = N'Products_Data', FILENAME = N'E:\Snapshot\Products_data_0100.ss')

AS SNAPSHOT OF Products

Here is an excellent blog about how to switch the reports to the new snapshot.

http://blogs.msdn.com/sqlcat/archive/2008/08/05/microsoft-sql-server-database-snapshots-and-synonyms.aspx

NULLIF - ISNULL - COALESCE

2008-04-04T21:02:00.000-04:00

One thing which I always need more information is the NULL, because it is very tricky. Here is just a simple notes….

NULLIF (expr1, expr2)

Returns null value when both expression are equal; if not it returns the first expression

NULLIF is equivalent to the case statement.

Example from BOL:

USE AdventureWorks;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'
FROM Production.Product
WHERE ProductID < 10;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ISNULL (expr_check, expr_replace)

Replaces null value with the replacement value.

USE AdventureWorks;
GO
SELECT ProductID,(ISNULL(Weight, 10))
FROM Production.Product;
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COALESCE (expr1...exprn)

Returns first non-null expression among its arguments

USE AdventureWorks;
GO
SELECT ProductID,(COALESCE(Weight, 10))
FROM Production.Product;
GO

Difference between ISNULL and COALESCE

ISNULL takes only 2 parameters. COALESCE can take any number of parameters.

ISNULL returns the datatype of first parameter whereas the return type of COALESCE is determined
by the data precedence rules; explicit casting should be done to get the desired data type.

For example the following two statements give different results

DECLARE @var varchar(2) -- Please use meaningful variable names in real code
SET @var = null
SELECT ISNULL( @var, 'Test' )
SELECT COALESCE( @var, 'Test' )

Checking fragmentation in SQL Server 2005

2008-04-04T15:22:00.002-04:00

SQL Server 2005 provides sys.dm_db_index_physical_stats DMV (dynamic management view) which can be used to check index fragmentation.

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('YourDatabaseName'), NULL, NULL, NULL , NULL);

If the avg_fragmentation_in_percent > 30 then Rebuild the index

If the avg_fragmentation_in_percent >5 and <30 then Reorganize the index

In a nutshell, rebuilding an index takes more server resources than reorganizing.

Mirroring – Role of Witness Server and Quorum

2007-11-02T23:45:00.000-04:00

When a witness server is set, a mirroring session (high safety mode with automatic failover mode) needs quorum to keep the database service. A quorum is the minimal relationship among all connected servers required for synchronous database mirroring session.

Now the next question that comes in mind is about the single point of failure for witness. It is not a single point of failure because if witness fails, principal and mirror will still continue to form a quorum.

Various types of quorum are possible.

Say for example

A = Principal
B = Mirror
C = Witness

Full Quorum – Both partners and witness are included - A∩B∩C

Quorum of partners – Only the two partners are included - A∩B

Quorum with witness and partner – Witness and one of the partners are included C∩(AUB)

Quorum loses sessions

If all the servers are disconnected then the session loses quorum

Now that we know all possible types of quorum, let’s see how each one affects the database and application.

If witness is disconnected when either partner goes down, the database is unavailable since quorum cannot be formed. If the session loses quorum, then the database will not be available until the quorum is re-established.

SQL Server 2005 Database Mirroring

2007-04-01T15:43:00.000-04:00

I have been working with database mirroring feature in SQL Server 2K5 and these are some of interesting points I have noted during setting up the process.

1. In SQL Server 2005 SP1 and later versions, database mirroring is fully supported and is made available for general production use. To use database mirroring in a production environment, upgrade to SQL Server 2005 Service Pack 1 (SP1).

Source: http://support.microsoft.com/kb/907741

2. An error occurred while starting mirroringDatabase Mirroring Transport is disabled in the endpoint configuration.(Microsoft SQL Server, Error: 1486)

Solution: Run DBCC TRACEON (1400)

3. The mirror database, "XXX", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

Solution: After the full backup on the principal, you need to backup the Transaction log on the principal server. Restore the full backup on mirror in ‘NORECOVERY MODE’ and then restore the Transaction log in ‘NORECOVERY MODE’

4. Can we use database mirroring for the purpose of transferring data from production to test or development server?

In mirroring, if the first server should become unavailable, the system will failover to the mirrored database and SQL Server, helping to ensure high availability. I don't think you want to failover to test or development box.However, you can use Database Mirroring and create a Database Snapshot against the mirror. This Database Snapshot can be periodically dropped and recreated in order to give access to more up to date information. You can use this snapshot for reports or testing.

5. Can we use Database mirroring with Log Shipping?

This MS article explains it clearly

http://msdn2.microsoft.com/en-us/library/ms187016.aspx

T-SQL Convert Hex value to Integer

2007-03-24T22:42:00.000-04:00

--Convert Hex value to int
SELECT CONVERT(INT, 0x000FFEFF)

--Convert int value to hex
SELECT CONVERT(VARBINARY(8), 1048319)

SQL Server 2005 Access to System tables

2007-03-23T22:53:00.000-04:00

In SQL2K5 system data is hidden in resource tables and cannot be accessed. Instead, there are number of views which can provide the information.

Catalog Views – Shows metadata that describes the objects in an instance
Information Schema Views - They have the same functionality as in 2000 Provides internal view of SQL Server meta data
Compatibility Views – These are for backward compatibility. Beware that columns that previously were undocumented typically now only have NULL or 0
Dynamic Management Views – They provide inside details of SQL Server state data, which can be used to check the health of SQL Server instance. Consists of both views and table valued functions

Also from BOL, the catalog views and the compatibility views have restricted visibility, so by default they only return information about objects to which the user has some rights.

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
http://msdn2.microsoft.com/en-us/library/ms187997.aspx

SQL Server Management Studio – Editor Color Coding

2007-03-23T17:40:00.000-04:00

In Management studio, when you open query editor, there is a thin yellow vertical line in the left margin for each line you type. Sometimes the line disappears and sometimes it is green in color. Did you ever notice that? If not, continue reading……

These colors represent the modifications done to the file.

Yellow –modification, not saved
Green – modification, saved
No Color – no modification

Now, can you change these colors? Ofcourse, goto Tools->Options->Environment ->Fonts and Colors. You can change the colors for various display items.

SQL Server - Find primary keys in a table

2007-02-07T21:38:00.000-05:00

To find primary key in a given table :

Option 1: sp_pkeys 'yourtablename'

Option 2: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name = 'yourtablename' and constraint_type = 'primary key'

Option 3: sp_help 'yourtablename'

To find all primary keys in database:

Use yourdatabasename
select c.COLUMN_NAME ,c.CONSTRAINT_NAME,C.table_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

These would work for both SQL Server 2K and SQL Server 2K.

Business Intelligence Studio - Installation error

2007-02-07T21:20:00.000-05:00

After installing SQL Server developer edition, Business Intelligence Studio starts looking for file devenv.exe.

To resolve this issue:
1. You should make sure that Visual Studio is still installed.
2. If you didn't previously have VS installed, the BI Dev Studio installation will install a VS shell called Visual Studio Premier Partner Edition.
3. Look in Add or Remove Programs for “Microsoft Visual Studio Premier Partner Edition”. If you don't find any entry for Visual Studio go to the location for SQL Server setup and run \Setup\vs_setup.exe. (Usually in CD #2 under setup)
4. After VSShell is installed repair the BI Studio installation by running the following from the command line from the .\Tools directory: start /wait setup.exe /qb REINSTALL=SQL_WarehouseDevWorkbench REINSTALLMODE=OMUS

Import/Export between SQL 2005 and 2000

2007-01-31T22:37:00.000-05:00

Due to architectual differences, you can not restore a database backup from higher version to Lower version [SQL 2005 to SQL 2000]. Once it is attached or restored to a 2005 instance it can not go back intact.

One question which comes into mind immediately is, what if DB is set to SQL 2000 compatibility level? Compatibility level is for client connection behavior. On-disk it is still SQL 2005.
One solution is to create the database manually, create objects and use SSIS/DTS/BCP to transfer the data.

Enable xp_cmdshell

2007-01-31T20:35:00.000-05:00

With SQL Server 2005, xp_cmdshell is disabled by default

To enable this feature:

Option 1: Using T-SQL

--Enable the feature
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE

Option 2: SQL Server 2005 Surface Area Configuration tool

From Surface Area Configuration Tools -> Choose “Surface Area Configuration for Features” > Check “Enable xp_cmdshell”