Welcome onboard - Microsoft SQL Server

SQL Server New Version Denali - New Feature Sequence

2011-07-30T01:37:00.000-07:00

One of the new featured in SQL Server 2011 – Denali is Sequence. Whoever have worked in Oracle or DB2 can easily connect to this feature and it is in the SQL Server wishlist for long time. While migrating Oracle database to SQL Server this was one area quite bit of effort needed because in previoius versions of SQL Server Sequence was not supported. Though there were workaround to achieve the same feature, there was no One to One mapping for sequence object in SQL Server. Now in SQL Server 2011 Microsoft has comeup with an equivalent. Having said that, there are behavioural changes in SQL Server sequcne from Oracle sequence. The syntex is different, there are limitation and flxibility as well. Let us try to understand more about SQL Server Sequence.

Problem :
In earlier versions of SQL Server, to Generate sequence of numeric values for a Surrogate primary key or otherwise we used to use Identity property on a Numeric Column. The Identity column was tightly coupled with the Table and it had its own advantage and disadvantage. The main issue was if you want to have same Sequence to be shared accross many tables or columns , then we had to go for a workaround. In SuperType subtype kind of relations we used to face this problem of sharing same sequence across multiple tables.

Solution
In SQL Server 2011, Microsoft has added a new Object called Sequence which is more or less similar to Oracle Sequence object.

What is Sequence in SQL Server?
A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Generally it is used to create auto generated Primary key (surrogate key) value.

Create Sequence
As usual, you have TSQL command and GUI. TSQL Syntax is as follows

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ ] } | { NO CACHE } ]
[ ; ]
You can refer this Link for more detail on Syntax.

Eg. Creating a Sequence with all arguments

CREATE SEQUENCE [dbo].Seq_EmployeeID
START WITH 0
INCREMENT BY 2
MINVALUE 0
MAXVALUE 2000
CYCLE
CACHE 20

Eg. Creating Table with Sequence as Default value for PK. This is another way of usage.

CREATE TABLE Audit.ProcessEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EventCode nvarchar(5) NOT NULL,
Description nvarchar(300) NULL
) ;
GO

Other system Functions and Objects related to Sequence

NEXT VALUE FOR : Generates a sequence number from the specified sequence object. The NEXT VALUE FOR function can be used in stored procedures and triggers. This function also have few limitations you may want to read the details here

Sys.Sequence : Contains a row for each sequence object in a database. For more info refer this link

sp_sequence_get_range: Returns a range of sequence values from a sequence object. The sequence object generates and issues the number of values requested and provides the application with metadata related to the range.

Modify Sequence
ALTER SEQUNCE : Modifies the arguments of an existing sequence object. If the sequence was created with the CACHE option, altering the sequence will recreate the cache. For more info refer this link

Drop a sequence
DROP SEQUENCE : After generating a number, a sequence object has no continuing relationship to the number it generated, so the sequence object can be dropped, even though the number generated is still in use. For more info refer this link

Important Architectural Consideration and Limitations

(a) By default Sequence uses BIGINT Datatype and it take 8 Byte. If the requirement is to store small number, then change the datatype to TinyInt, Smallint or Int accordingly
(b) If the starting value is not defined while creating, then SQL Server set it to the lowest value what that datatype can support. So by default if the datatype is BIGINT the starting number will be (-9,223,372,036,854,775,808)
(c) The Increment cannot be 0. If the increment is a negative value, the sequence object is descending; otherwise, it is ascending. By default the increment is 1
(d) If the requirement is to migrate Oracle Sequence to SQL Server, then select the datatype as Numeric(28,0)
(e) Specify CYCLE/NOCYCLE to define the behavior if t the sequence number reaches the maximum value
(f) SQL Server as of CTP 3 does not support Current Value
(g) Cache option is different from Oracle. In oracle by default database caches 20 Sequence but in SQL Server database engine select the size. If the cache option is enabled without specifying a cache size, the Database Engine will select a size. However, users should not rely upon the selection being consistent. Microsoft might change the method of calculating the cache size without notice.
(h) Sequence objects support ownership chaining. If the sequence object has the same owner as the calling stored procedure, trigger, or table (having a sequence object as a default constraint), no permission check is required on the sequence object. If the sequence object is not owned by the same user as the calling stored procedure, trigger, or table, a permission check is required on the sequence object
(i) Altering a column and Adding Identity is not possible in SQL Server. In such scenario if adding a new column with Identity , dropping the old column and renaming the new column is not possible then may be adding SEQUENCE as a default can be a workaround

How does it differ from Identity Column?

Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.
Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. To prevent sequence values from being changed, use an update trigger on the table to roll back changes.

Performance Comparison of Sequence and Identity

Aaron Bertrand has covered this in detail. Check this Blog
Other than this I did test one more case, Table with Identity and Table with Sequence as Default for PK. In the table where sequence was used, I did not provide value for the PK column so that default gets fired. In this case Identity table insert perform almost 60% Better from a table with Sequence as default. Script to Reproduce the scenario I tested is as follows :-

-- Table with sequence as default

CREATE TABLE [dbo].[SequencePerf](
[EventID] [int] NOT NULL,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SequencePerf] ADD DEFAULT (NEXT VALUE FOR [Seq_EventId]) FOR [EventID]
GO

CREATE TABLE [dbo].[IdentityPerf](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]

GO

-- Run both the insert in single batch and see the cost it take. In my case it was 71:29

Insert [SequencePerf] (Description)
Select top 100000 o.name From sys.objects o,sys.columns

Insert [IdentityPerf] (Description)
Select top 100000 o.name From sys.objects o,sys.columns

Different Scenario of Sequence Usage in SQL Server

Create database [TestDenali]
GO

USE [TestDenali]
GO
CREATE SEQUENCE [dbo].[Seq_EventId]
AS [bigint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
CYCLE
CACHE 200
GO

CREATE TABLE [dbo].[SequenceTest](
[EventID] [int] NOT NULL ,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SequenceTest] ADD DEFAULT (NEXT VALUE FOR [Seq_EventId]) FOR [EventID]
GO

-- Not providing EventID value so that Default

INSERT INTO dbo.[SequenceTest](Description) select top 100 Name from sys.objects

-- Inserting bulk number of records , getting sequence in Select
INSERT INTO dbo.[SequenceTest] ([EventID],description)
SELECT top 100 NEXT VALUE FOR Seq_EventId AS SecondUse, Name from sys.objects

-- Insert a single row

INSERT [SequenceTest] ([EventID],description)
VALUES (NEXT VALUE FOR Seq_EventId, 'Test') ;

-- Usage with SELECT INTO
SELECT NEXT VALUE FOR Seq_EventId AS EventID, Description
INTO [SequenceTest_clone]
FROM [SequenceTest] ;
-- Temp table
SELECT NEXT VALUE FOR Seq_EventId AS EventID, Description
INTO [#SequenceTest_clone]
FROM [SequenceTest] ;

-- Creating Temp table with Sequence as Defualt

CREATE TABLE #SequenceTest (
[EventID] [int] NOT NULL DEFAULT (NEXT VALUE FOR Seq_EventId) ,
[Description] [nvarchar](300) NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
) ON [PRIMARY]

INSERT #SequenceTest ([EventID],description)
VALUES (NEXT VALUE FOR Seq_EventId, 'Test') ;
INSERT #SequenceTest ( description)
VALUES ( 'Test') ;
Select *from #SequenceTest

-- Creating Table Variable with Sequence as Default

Declare @SequenceTest TABLE (
[EventID] [int] NOT NULL DEFAULT (NEXT VALUE FOR Seq_EventId) ,
[Description] [nvarchar](300) NULL)

INSERT @SequenceTest ([EventID],description)
VALUES (NEXT VALUE FOR Seq_EventId, 'Test') ;
INSERT @SequenceTest ( description)
VALUES ( 'Test') ;

Select *From @SequenceTest
-- Table Valued Parameter
CREATE TYPE [dbo].[SequenceTestTableType] AS TABLE
(
[EventID] INT DEFAULT (NEXT VALUE FOR Seq_EventId), description VARCHAR(128)
)
/*
Msg 11719, Level 15, State 1, Line 3
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views,
user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.
*/

Create view vwTestSequence
as
SELECT top 10000 NEXT VALUE FOR Seq_EventId AS SecondUse,description FROM dbo.Sequencetest

/*
Msg 11719, Level 15, State 1, Procedure vwTestSequence, Line 4
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views,
user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.

*/

-- Capture the Inserted Sequencenumber using OUTPUT. In SuperType-Subtype scenario may be usefule
Declare @tab table (Eventid int)
Insert [SequencePerf] (Description) OUTPUT inserted.[EventID] Into @tab
Select top 100000 o.name From sys.objects o,sys.columns
SELECT *From @tab

Reference

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspx
http://blogs.msdn.com/b/ssma/archive/2011/07/12/converting-oracle-sequence-using-ssma-for-oracle-v5-1.aspx

SQL Server New Version - Denali - Step by Step Installation

2011-07-23T00:50:00.001-07:00

SQL Server 2011 – Denali CTP 3 is recently released and as usual there are many new features which will enhance SQL Server footprint in Enterprise Database arena. The main advantage with Microsoft what I see is, they have all three layer in their control ie. Operating system , Database and Programming languages. They can leverage this freedom and integrate all these three layer well and come up with good end products which of course have a limitation of Only on Windows platform. This is the reason why we have great tool like Management Studio which is powered by Visual Studio and makes database administrators /developers/users life easy.

The major new configuration change in installation process is Distributed Reply Component Configuration. This component enable Realtime stress Workload testing against your database server. If you don’t want to install this option while selecting services don’t opt “Distributor Replay Controller” and “Distributed Replay Controller Client”

Another new service you can see while installation is “Data Quality Service”. This is a new feature added in Denali for knowledge-driven data cleansing solution.
You may want to see the FAQ link for DQS…

For any installation you must be aware of the Hardware and software requirement. So for Denali. Here is the link for Pre-requisite

Once you ensure that you have a system which meets the mentioned hardware and software requirement , here is the next steps

SQL Server Code-Named “Denali” Evaluation Edition is supported on the Windows Vista SP2, Windows Server 2008 SP2, Windows 2008 R2 SP1, and Windows 7 SP1 operating systems.

Pre-Installation Steps
• Refer the above mentioned sites and ensured that your system meet all the requirements
• Ensure that you have planned your Instance name if it is a named Instance
• Ensure that you have planned all the Data Directory. Ie. Where to keep System Databases, TempDB, and User Databases.(if not you can go by Default but it is not generally recommended).
• Ensure that you have planned Startup Account for all the services. (if not you can go by Default but it is not generally recommended.)
• Ensure that you have a list of features that you wants to install. Like if you are not going to use Filestream no need to configure that during installation

My System Configuration
• Operating System : Windows Vista SP2
• Existing SQL Server Instance
o Default Instance : SQL Server 2008 SP 1
o Named Instance 1: SQL Server 2008 RTM
o Named Instance 2 : SQL Server 2008 R2 RTM
Named Instance 3 : SQL Server 2005 Express Edition SP3

Step 1 : Download the Binary from http http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

Step 2 : Extract the downloaded binary and start installation as shown below

Figure 1

Figure 2

Figure 3

Figure 4

Figure 5

Figure 6

Figure 7

Figure 8

Figure 9

Figure 10

Figure 11

Figure 12

Figure 13

Figure 14

Figure 15

Figure 16

Figure 17

Figure 18

Figure 19

Figure 20

Figure 21

Figure 22

Figure 23

Summary : I had SQL Server 2005 Express Edition, SQL Server 2008 Developer Edition, SQL Server 2008 R2 Evaluation Edition on my Machine and all of these instance in the first look working fine. Not found any abnormal behaviour so far. Hope all these versions can co-exists. My SQL Server 2011 CP3 is installed and good to go

FAQ : What is Partition Scheme in SQL Server?

2009-11-29T03:54:00.001-08:00

Once you have created the Partition Function then the next step is to create Partition Scheme. As already mentioned, One partition function may have one or more Partition Scheme. . The partition scheme defines how any resulting partitions will be stored on filegroups. You list the filegroups for a partition scheme in the same order that you want to have them map to the partitions defined by the partition function. Creating a partition scheme assumes that you have already created the filegroups. You can mention ALL TO keyword to create all the partition to a single filegroup.

Eg. From BOL
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);

Edit : Should Filegroup single file or multiple files considered as out of scope in this context. But selecting single filegroup VS multiple filegroup and Single files per filegroup VS multiple files also a significant decision to be made in terms of performance.

FAQ : What is Partition Function in SQL Server?

2009-11-28T20:19:00.000-08:00

Partition functions are special kind of objects (of course created by user) created in a database which can be used only by partition scheme. Partition function contains a data type , that must be the same data type as the partition column chosen in the table. Partition Function as such not directly related to any table.

NOTE: Even though Partition Function to Partition scheme is One to Many, in sliding window scenario moving data between in and out of partition is a common requirement, which require Partition function modification. So practically it makes each Partition table should have exclusive partition function and scheme.
Partition functions are not user defined functions.

Here are few of facts to be known about partition functions• Partition functions are not listed as database objects in the sys.all_objects or sys.objects system tables; instead, you can find them listed in sys.partition_functions.
• Partition functions are not contained by a database schema.
• Special commands must be used for creating, altering, and dropping a partition function:
o CREATE PARTITION FUNCTION
o ALTER PARTITION FUNCTION
o DROP PARTITION FUNCTION
• Partition functions can be invoked interactively in Transact-SQL by using the $PARTITION function.

Partition Functions System Objects

• sys.partition_functions : Contains a row for each partition function.
• sys.partition_range_values : Contains a row for each range boundary value of a partition function of type R.
• sys.partition_parameters : Contains a row for each parameter of a partition function.
• $PARTITION. :Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.
Eg. SELECT $PARTITION.RangePF1 (10) ;

FAQ : What is the advantages and disadvantages of Table Partitioning of 2005 over Partition View of earlier versions?

2009-11-27T19:59:00.001-08:00

Advantage of Table Partitioning
• No need to create separate physical tables for each ranges manually.
• There are few limitations when you insert data into Partition View like you cannot use BULK INSERT/BCP etc.
• Less complex to manage and administrate
• SQL Server automatically manages the placement of data in the proper partitions.

Disadvantage of Table Partitioning
• Major disadvantage is, we cannot have different index model for different range. Ie. In general, we may need to have more number of indexes when the data is READ ONLY and less or different index model for the data which is READWRITE. That is not possible with partition table.
• You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.

Partition is not recommended for a table in following condition

2009-11-27T19:18:00.000-08:00

1. If you want to have different indexing structure for READ ONLY and READ WRITE data then partition is not recommended.
2. If the table is not accessed much or do not required much index maintenance
3. If the query on the tables are filtered(WHERE condition) by some column which cannot be used for partitioned.
4. If you planning to use ONLINE INDEX REBUILD option you cannot use partition. You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.

FAQ: How to determine partition suits for a table ?

2009-11-27T19:13:00.000-08:00

There are few general guidelines for determining whether the partition feature is suited for a table or not. One point to be noted here is, if a table is not partitioned properly (ie. Not selected the most suitable partition column), though it will not adversely affect the performance but it will not give any performance benefit either. Remember that the query optimizer can limit the partitions that are scanned to resolve a query only if the query filters on the partitioning column. Precisely, just because the table is partitioned, we will not have performance benefit.

1. First of all, just because of a table is big, it should not be partitioned. Ie. Only size of the table cannot be "THE" criteria for partition.
2. If the index maintenance on a table is time consuming or make the table offline for the users, you may opt partition because re-indexing on only required partition is better than re-indexing the whole table. Partitioning a large table divides the table and its indexes into smaller partitions, so that maintenance operations can be applied on a partition-by-partition basis, rather than on the entire table.
3. You can go for partition if the data must be aged out of the table (archived) periodically and the current delete process is taking time or blocking other users.
4. If the table is loaded with new data periodically and the loading process takes time and the table data lends itself to a partition column based on ascending date or time we may go for partition.
5. Table backup of huge table can be managed smartly by using new backup features of sql server 2005 and partitioning.
6. To summarize, the best way to put this in single statement is, if you have any table which fits into SLIDING WINDOW scenario you must partition it.

FAQ: What all are the types of table partitions in SQL Server

2009-11-27T19:09:00.000-08:00

There are two types of partitions. Horizontal and vertical.

Horizontal partition
Horizontal partition divides the data on subset of rows. Ie. Divide the data in such a way that all the rows of single year /month /week(Range) may be in one partition kind. In earlier versions of SQL Server this method was achieved by creating physical tables for each subsets and UNION the tables using a View(Partition View) and application access the data using Views.

Vertical partition
In Vertical Partition , the columns of a very wide table are spread across multiple physical tables containing distinct subsets of the columns with the same number of rows. The relation would be ONE TO ONE among the tables. The result is multiple tables containing the same number of rows but different columns, usually with the same primary key column in each table. Often a view is defined across the multiple tables and queries directed against the view.

FAQ : What is Table Partitioning in SQL Server?

2009-11-27T19:08:00.001-08:00

Partitioning is the process of dividing data into small , more manageable chunks. We can divide the data horizontally (on rows ) or vertically (on columns) . Table partitioning can make very large tables and indexes easier to manage, and improve the performance of APPROPRIATELY FILTERED queries. Horizontal Partitioning is one of the new feature added in SQL Server 2005 (in earlier versions we used to create separate physical table and create a wrapper view which union all the tables which is also known as Partition View). When a table is created as a partitioned table, SQL Server automatically places the table's rows in the correct partition, and SQL Server maintains the partitions behind the scenes. You can then perform maintenance operations on individual partitions, and properly filtered queries will access only the correct partitions. But it is still one table as far as SQL Server is concerned

Error : CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.

2009-11-23T03:52:00.000-08:00

One of the common reason for this error is, when we try to attach a MDF file which is already attached (to the same instance or any other instance) we get this error. So the solution is this, if you get this error when you try to attach a MDF file, then ensure that the file is not accessed by any other process or attached to any SQL Server instance already.

FAQ : How to disable AUTO UPATE STATISTICS for a specific table or index in SQL Server

2009-11-19T09:20:00.000-08:00

Using sp_autostats system stored procedure we can switch off AUTO UPDATE STATISTICS on a specific table or index. Please note that, if AUTO UPDATE STAT is OFF in Database level, you can not switch ON object level. In otherwords, if the DB level Auto update stat is ON then you can override the DB level setting and switch off AUTO UPDATE STAT object level using this stored procedure.

Syntax
sp_autostats [ @tblname = ] 'table_name'
[ , [ @flagc = ] 'stats_flag' ]
[ , [ @indname = ] 'index_name' ]

Enabling automatic statistics for all indexes of a table
The following example enables the automatic statistics setting for all indexes of the Product table.

USE AdventureWorks;
GO
EXEC sp_autostats 'Production.Product', 'ON'

Community Technology Preview of Microsoft SQL 2008 R2 is available for download

2009-11-15T16:30:00.000-08:00

The latest Community Technology Preview (CTP) for SQL Server 2008 R2 available to the general public today. The new capabilities in the latest CTP include support for Windows Server 2008 R2 – including Hyper-V with Live Migration – as well as enhanced data compression with support for Unicode UCS-2. Also included are new visualization features and a Report Part Gallery to Report Builder 3.0.

SQL Server 2008 R2 includes enhancements designed to help administrators centrally monitor and manage multiple database applications, instances or servers. In addition, it has enabled high-scale complex event-stream processing through SQL Server StreamInsight, and expanded business intelligence capabilities with SQL Server PowerPivot for Excel.

At the PASS Summit last week in Seattle, Microsoft announced two new premium editions – SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse – these will be available once SQL Server 2008 R2 is released. SQL Server 2008 R2 is on track for general availability in the first half of 2010.

The Community Technology Preview of Microsoft SQL 2008 R2 is available for download Here

FAQ : Why we need to have different service account for each service in SQL Server?

2009-11-08T04:30:00.000-08:00

In SQL Server, as per the components we select, different services gets installed like SQL SERVER Database engine, Analysis Service, Integration Services, Reporting Services and SQL Server Agent Services. But each service have its own specific tasks to do which requires different privilege and permissions. If we are configuring all the services startup account with a single user, this user will have to have a sum of all the permission required by all the services, which is very against security policy recommended by Microsoft. For eg. DB Engine needs permission to the folder where the data and log files are kept why because it has to write the data into these files. Whereas SQL Server Agent does not need this permission. So, if we are using same user for both the services, if the SQL Server Agent service got hacked or compromised , even the database engine is at risk. This is the very basic reason why we need different windows user for each service.

SQL Server BI Webcast series by Amit Bansal

2009-11-05T00:03:00.000-08:00

There is a webcast series starting soon on SQL Server BI by Amit Bansal.

Webcast 1: BI for you -November 6, Friday, 3 pm to 4.30 pm

Webcast 2: Create your first cube -November 9, Monday, 3 pm to 4.30 pm

Webcast 3: Extract data from the cube -November 11, Wednesday, 3 pm to 4.30 pm

Webcast 4: Learn MDX scripting -November 13, Friday, 3 pm to 4.30 pm

To register & browse more details about each webcast, visit

Check this nice article on A-Z Guide to Being an Architect

2009-10-28T06:04:00.000-07:00

A-Z Guide to Being an Architect

FAQ : What is Domain of a column? How it defer from Datatype

2009-10-06T09:31:00.000-07:00

Domain of a columm is the set of the values that are applicable for a column. For example, Gender column can have Female or Male so these value make the domain for Gender column.

Though Domain and Datatype looks similar but it is not so. Datatype is the type of data that can be stored in the column . ie. Integer datatype column can only store numeric data.

FAQ : What is the difference between Framework and Standard?

2009-10-03T21:49:00.001-07:00

We have heard lot about ITIL and ISOS. Here ITIL is framework where as ISOS is Standard. What is the difference between standard and framework? Have you ever thought about this?

Standard : Standard is something very rigid which should be followed across. Standard will define proper method to do a process. ISOS is a standard.

Framework : Framework is something where you have the boundary defined. It is flexible and not always defined with step by step process. ITIL is a framework.

FAQ : What is SQL Server PRINT equivalent in Oracle

2009-10-02T05:33:00.000-07:00

Print In SQL Server
Returns a user-defined message to the client. Mainly Print is used while debugging procedures or triggers.
Eg
PRINT N'This is from first Loop.';

Oracle Equivalent :
What can come close in Oracle is DBMS_OUTPUT.PUT_LINE. Dbms_output is a PL/SQL Package which has a put_line procedure which write data to flat file or to direct your PL/SQL output to a screen.

DBMS_OUTPUT.PUT_LINE ('This is from first Loop);

FAQ : What is Oracle Predefined Exceptions equivalent in SQL Server

2009-10-02T04:47:00.000-07:00

What is Oracle Predefined Exceptions
An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers. PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself.
Here are few examples
NO_DATA_FOUND : A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.

TOO_MANY_ROWS : A SELECT INTO statement returns more than one row.

DUP_VAL_ON_INDEX : Your program attempts to store duplicate values in a database column that is constrained by a unique index.

Read more about Predefined exceptions here

What is SQL Server Equivalent?

Till SQL Server 2008 we do not have any equivalent. Ie there is no Predefined Exceptions in SQL Server. But this can be achieved by BEGIN TRY .. END TRY BEGIN CATCH … END CATCH. ERROR Number/Message combination.

FAQ : Oracle %ROWTYPE equivalent in SQL Server

2009-10-02T04:25:00.000-07:00

What is %ROWTYPE in Oracle?

The %ROWTYPE attribute provides a feature to create Table based or Cursor Based record. Ie. If you have a table with 5 column and you want to select all the five column value of a single record at a stretch instead of declaring 5 scalar variable you just declare a single variable of %ROWTYPE which will intern have all the five column. Consider we have a table called Department with three columns Department_ID, Description and Name. Either you can declare three scalar variable or you can declare single variable of %ROWTYPE attribute. If you want to store a collection (multiple records ) then you can use TABLE OF TableName%RowType;
See the below eg.
DECLARE
Dept_REC DEPARTMENT%ROWTYPE;
BEGIN
SELECT *
INTO Dept_REC
FROM DEPARTMENT
WHERE DEPARTMENT_ID = 1;
DBMS_OUTPUT.PUT_LINE (' DEPARTMENT ID: '|| Dept_REC.DEPARTMENT_ID);
DBMS_OUTPUT.PUT_LINE ('COURSE DESCRIPTION: '||
Dept_REC.DESCRIPTION);
DBMS_OUTPUT.PUT_LINE ('Name: '||
Dept_REC.Name);
END;

SQL Server Equivalent.

In SQL Server what can come close to TABLE OF TableName%RowType is Declare a table type variable (in case it is collection). Another way may be create a #temp table. But temp table method may involve IO which need to be aware of and also in there are limitation where you can create #temp table. For a single row, it is better to declare separate scalar variable for each columns

Table variable Method
Declare @Dept Table (Department_id Int,Name Varchar(100),Description Varchar(1000))
The below statement will insert all the rows from Department to @Dept variable.
Select *INTO @Dept
FROM
Department

Temp table method
Select *into #Temp_Dept
FROM Department
The above statement will create a temp table on the fly. This can be used in Procedure but not in Function.

FAQ : What is SQL Server Profiler equivalent in Oracle

2009-10-01T22:38:00.000-07:00

What is SQL Server Profiler?

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly. Basically this tool is a wrapper which internally calls SQL Trace system stored procedures. Generally, running profiler is not recommended in production if you need to trace events, then in production it is better to go for SQL Trace system Stored procedure. Refer this link for more info.

Oracle Equivalent . (please note that this is based on my little experience in Oracle and If anyone feels that If I am going wrong please feel free to correct me)

Automatic Workload Repository (AWR)

Oracle offers many ways to trace events which again later version may have new tools and features. In 10 G, Oracle offers Automatic Workload Repository (AWR) which is used to collect statistics including
• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

Read more about AWR here

SQL trace files and TKPROF

If you are still in Oracle 9, you can go for SQL trace files which gives you statement by statement log of SQL and PL/SQL executed by Database Client(s). The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can help you monitor and tune applications running against the Oracle Server. SQL Trace files entries can be classified in to mainly four categories.
• Database calls (parse, execute, and fetch)
• Wait events
• Bind variable values
• Miscellaneous events like timestamps, instance service name, session, module, action, and client
identifier

TKPROF

You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file. Optionally, TKPROF can also:
• Determine the execution plans of SQL statements
• Create a SQL script that stores the statistics in the database
Read more about SQL Trace files and TKPROF here

FAQ: What is optimizer in SQL Server?

2009-09-11T21:05:00.000-07:00

Unlike procedural language SQL Statements does not state the exact steps that database engine should follow to extract the data or manipulate the data. In procedural language the statements are ran as per the sequence they have written. But in SQL , database engine must analyse the statement to determine the most efficient way to extract/manipulate data. This process is called optimization and the component which does this process is called Optimizer.

The input to the optimizer contains , Query , schema (table/indexes definitions) and the statistics available.

The output from the optimizer is query execution plan.

The SQL Server query optimizer is a cost-based optimizer. Each possible execution plan has an associated cost in terms of the amount of computing resources used. The query optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Some complex SELECT statements have thousands of possible execution plans. In these cases, the query optimizer does not analyze all possible combinations. Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

FAQ : What is Optimizing SQL statement .

2009-09-06T21:04:00.000-07:00

FAQ : What is the equivalent of SELECT * INTO of SQL Server in Oracle

2009-09-02T21:16:00.000-07:00

SQL Server
Select * into TargettableName from sourceTableName

Oracle
Create table targetTableName as Select *From sourceTableName

FAQ : What is Packages in Oracle and its equivalent in SQL Server

2009-09-02T21:15:00.001-07:00

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.

There is no equivalent in SQL Server yet.

FAQ : How to declare a variable in SQL Server (T-SQL) and in Oracle PL/SQL

2009-09-02T21:11:00.001-07:00

SQL Server

Variables are declared in the body of a batch or procedure with the DECLARE Statement and are assigned to values with either a SET or SELECT Statement.After declaration all variables are initialized as NULL
Variables are often used in batch or procedure /functions as counters for WHILE, LOOP or for an IF..ELSE block. Variables can be used only in expression, not in the place of object names or key words (in that case you need to use Dynamic SQL. See Dynamic SQL for more info).

Types of Variable : Three types of variables are available
• @local variable
• @Cursor_Variable_Name
• @table_variable_name

Declare a variable :
DECLARE @VariableName datatype : Variable prefixed with @ symbol.
Eg. DECLARE @Count INT
DECLARE @Name varchar(50), @Address varchar(200)

Seting /initialising a variable :

SET @Name=’Madhu’
SET @Count=100
Initialise multiple variable using Select statement :

SET can be used for only single variable where as SELECT can be used for multiple variable in a single statement

SELECT @Name=’Madhu’ , @Count=0 , @Address=’XYZ’

WHILE(@i<@Count)
BEGIN
SET @i=@i+1
PRINT @i
END

Oracle (PL/SQL) : Declaring Variables
In oracle, Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or a PL/SQL-only datatype, such as BOOLEAN or PLS_INTEGER. For example, assume that you want to declare variables for part data, such as part_no to hold 6-digit numbers and in_stock to hold the Boolean value TRUE or FALSE. You declare these and related part variables as shown in Example 1. Note that there is a semi-colon (;) at the end of each line in the declaration section.

Example 1–1. Declaring Variables in PL/SQL
DECLARE
part_no NUMBER(6);
part_name VARCHAR2(20);

You can also declare nested tables, variable-size arrays (varrays for short), and records using the TABLE, VARRAY, and RECORD composite datatypes.

Assigning Values to a Variable
You can assign values to a variable in three ways. The first way uses the assignment operator (:=), a colon followed by an equal sign, as shown in Example 2. You place the variable to the left of the operator and an expression, including function calls, to the right. Note that you can assign a value to a variable when it is declared.

Example 1–2 Assigning Values to Variables With the Assignment Operator
DECLARE
wages NUMBER;
hours_worked NUMBER := 40;
emp_rec1 employees%ROWTYPE;
country := UPPER('Canada');

The second way to assign values to a variable is by selecting (or fetching) database values into it. In Example 1–3, 10% of an employee's salary is selected into the bonus variable. Now you can use the bonus variable in another computation or insert its value into a database table.

Example 1–3 Assigning Values to Variables by SELECTing INTO

DECLARE
bonus NUMBER(8,2);
emp_id NUMBER(6) := 100;
BEGIN
SELECT salary * 0.10 INTO bonus FROM employees
WHERE employee_id = emp_id;
END;

The third way to assign a value to a variable is by passing it as an OUT or IN OUT parameter to a subprogram, and then assigning the value inside the subprogram.

FAQ : How to declare a variable in SQL Server (T-SQL) and in Oracle PL/SQL

2009-09-02T21:11:00.000-07:00

FAQ : How to find Product version in SQL Server and in Oracle

2009-09-02T21:09:00.000-07:00

SQL Server

Select @@Version
Or
SET NOCOUNT ON;
SELECT
CONVERT( varchar, SERVERPROPERTY('Edition')) AS Edition
, CONVERT( varchar, SERVERPROPERTY('ProductVersion')) AS Version
, CONVERT( varchar, SERVERPROPERTY('ProductLevel')) AS Level

Oracle

select * From V$VERSION ;
Note : V$VERSION is the catalog view which provides all the information in Oracle

FAQ : What is String summary statistics in SQL Server?

2009-08-11T21:39:00.000-07:00

SQL Server 2005 introduces additional information collected by statistics created on char, varchar, varchar(max), nchar, nvarchar, nvarchar(max), text, and ntext columns. This information, called a string summary, helps the query optimizer estimate the selectivity of query predicates on string patterns . This helps the optimizer better estimate the selectivity of conditions that use the LIKE operator. String summary information is not maintained if the summary for a column sample is larger than the Database Engine can maintain. For example, a string summary will not be maintained on statistics created by using WITH FULLSCAN on a unique varchar(80) column with 80 characters in each string, almost no similarity between strings, on a table with 85,000 rows. To determine whether a string summary is stored for a particular statistics object, use DBCC SHOW_STATISTICS (Transact-SQL) .

FAQ : What is Equi-JOIN and Non-Equi JOIN in SQL

2009-08-03T10:46:00.000-07:00

What is Equi-Join?

When a JOIN is made on Exact match between two columns it is called equi-Join.
Eg.
SELECT A.*
FROM EMPLOYEES A, EMPLOYEE_SAL B
WHERE A.EMP_ID=B.EMP_ID
OR
SELECT A.*
FROM EMPLOYEES A
INNER JOIN EMPLOYEE_SAL B
ON A.EMP_ID=B.EMP_ID
Both the queries mentioned above can be considered as Equi-join.

What is NON-Equi Join.

If you use any other comparison operator other than = in JOIN condition, it is called NON-Equi JOIN
SELECT A.*
FROM EMPLOYEES A, EMPLOYEE_SAL B
WHERE A.EMP_ID <> B.EMP_ID

FAQ : What is WITH ROLLBACK ROLLBACK IMMEDIATE | AFTER integer [SECONDS] | NO_WAIT in ALTER DATABASE in SQL Server

2009-06-27T20:27:00.000-07:00

When you ALTER a database state what to do with the current transaction(s) (If any) on the database are defined by these statements.

WITH ROLLBACK IMMEDIATE

WITH ROLLBACK IMMEDIATE tell the DB engine to rollback all the transaction immediately and make the DB into the given state in the Alter Statement. Ie
In my database, there is a transaction running for last one hour and it is not yet committed and now I want to make this database into SINGLE_USER. The following line of Alter statement will do that for me
-- Set the database to single user
Alter Database MyDatabase set SINGLE_USER WITH ROLLBACK IMMEDIATE

The above statement will rollback the current transaction immediately and bring the DB to Single_USER

WITH ROLLBACK AFTER integer [SECONDS]

This command tells DB engine to wait for the specified seconds before rolling back all the transaction. If the transaction still not committed in the given seconds then rollback.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK AFTER 30

The above statement will bring the database to SINGLE_USER after 30 sec. If any transaction is there after 30 sec it will rollback.

WITH NO_WAIT
This is bit different from the above two statements. This tells the DB engine to stop or terminate ALTER Statement rather than terminating or rollback the transaction. Ie. If any active transaction is there on the database, the ALTER Statement will fail.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH NO_WAIT

FAQ : What is Database in SQL Server and Oracle?

2009-06-26T22:38:00.000-07:00

Database in SQL Server.
A Microsoft SQL Server database is a collection of entities that stores and manipulate data. There are two types of Databases in SQL Server, System Databases and User Databases. There are four system databases which is mandatory in any instance. An instance can have max 32767 user databases which is not practical in real time scenario. But instances with 100s of DBs may be common. A database in SQL Server is made up of a collection of tables. These tables contain data and other objects, such as views, indexes, stored procedures, user-defined functions, and triggers that are defined to support activities performed with the data. The data stored in a database is typically related to a particular subject or process, such as inventory information for a manufacturing warehouse.

Database in Oracle
In Oracle the database is physical structure that consists of files stored on disks . Oracle’s database structures include tablespaces, control files, redo log files, archived logs, block change tracking files, Flashback logs, and recovery backup (RMAN) files etc.

One point to note here is, in general , the database definition may looks like similar in both SQL Server and Oracle but the way it implemented is little different. Oracle generally, will have single database and different schema for each User (read department) . And Oracle provides a feature to backup /restore only single schema /user. But in SQL Server though there is schema, but this backup/restore feature is not available by default (yes there is a tweaking but not recommended). So in SQL Server we may create separate database for each Department/user. In that case keeping FK relation across database is not possible.

FAQ : What is an instance in SQL Server and Oracle

2009-06-26T22:02:00.000-07:00

SQL Server Instance :
In SQL Server you can have multiple instance running on same physical machine. It means, you will have multiple database engine (windows services) running on the same machine. Each instance of the SQL Server database engine has its own set of system and user databases that are not shared between instances. Each instance will have its own memory area and other resources. There are two types on instances in SQL Server, Default instance and Named Instance. You will have only one Default instance on a machine but may have more than one (depends upon the edition of SQL Server what you have, it can be anything upto 50) Named Instance on a machine.

Why we need multiple instance of SQL Server?
The reason can be technical or non-technical (political). Ie. If you want to have more resources allocated for a particular department(read database(s)) than other department(s) you may go for different instances per department (Exception : In SQL Server2008 onwards there is resource governor feature which can control resource allocation). Another technical reason would be if one of your applications is highly tempdb intensive then you may go for different instances because each instance will have its own tempdb.

Political reason is, generally SA privilege may not be sharable between departments and each department would like to have its own SA, then you may go for different instances.

Oracle Instance.
Instance in Oracle is not similar to Instance in SQL Server. Generally, people uses Instances and Databases in Oracle as synonyms which is incorrect. One need to understand the distinction to understand the Oracle Architecture. To explain Instance in Oracle you need to understand what is Database in oracle too. In short, Databases are PHYSICAL and Instances are LOGICAL. Instance consists of memory structures and process in the server. Oracle has an area of shared memory called System Global Area (SGA) and each process have its own private memory area called Program Global Area (PGA). Instance to Database is ONE TO ONE. Ie An Instance can be part of only one Database but database can have multiple Instances (I never see some thing like this in my short stint as Oracle developer).

Why you would go for multiple instance in Oracle?
I would like to have the answer for this question from viewer. I do have some idea, but not convinced as the way SQL Server reasoning for Multiple instance on same machine.

FAQ : SQL Server 2005 Error, OBJECT_SCHEMA_NAME is not a recognized built-in function name

2009-05-17T10:01:00.000-07:00

OBJECT_SCHEMA_NAME function was newly introduced in SQL Server 2005 SP2. If you have not applied SP2 you will have "OBJECT_SCHEMA_NAME is not a recognized built-in function name" error.

Download and Install SP2 to solve this problem.

FAQ : How to import MS Excel 2007 data to SQL Server

2009-05-04T07:30:00.000-07:00

Import data from Office 2007 Excel to SQL Server

SELECT * INTO YourTableName FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\YourExcelfilename.xlsx', [Sheet1$])

Import data from Office 2003 Excel to SQL Server

SELECT * INTO YourTableName
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0; Database=c:\bb.xls',
'SELECT * FROM [Sheet1$]') AS XL

Tech Ed India 2009

2009-04-22T23:02:00.000-07:00

Tech Ed India 2009 at Hyderabad brings to you the best of the IT Pro technologies in-depth coverage! Check here

Visual Representation of SQL Joins

2009-02-08T06:12:00.000-08:00

Excellent article on Joins

Service Pack 3 for Microsoft SQL Server 2005 released

2008-12-18T02:08:00.000-08:00

Service Pack 3 for Microsoft SQL Server 2005 is now available.

FAQ : How to Restore Suspect Database in SQL Server

2008-09-10T21:42:00.000-07:00

Check this post by Paul Randal

Wonderful resource in Concatenating row values in Transact-SQL

2008-09-10T06:43:00.000-07:00

Check out this article by Umachandar Jayachandran & Team

Restoring Master and MSDB database in SQL Server

2008-08-29T06:20:00.000-07:00

I don't need to mention the significance of Disaster Recovery (DR) plan for System and User Databases in SQL Server. I do test DR plan for User Database very often, but System Database generally I skip. In this post, I want to explain the DR plan and DR Plan testing for System databases. System database restoration is not as similar as User Database restoration. There are broadly three scenario where system database (Master and MSDB ) needs to be restored

(a) Master or MSDB to be restored from a earlier backup to undo the changes
(b) The Server machine crashed and sql server have been reinstalled with fresh copy.
(c) SQL Server is not starting because Master database corrupted
This scenario is well covered in this article

Proposed Backup Plan for System Databases

(a) Full Backup Daily
(b) If any major changes done like replication configuration or creation of Jobs take a backup of system databases after the configuration

You can either go for Hot Backup of Cold Backup of System databases. Hot backup is nothing but taking backup of system databases using BACKUP DATABASE T-SQL Command and you have to restore that backup using RESTORE DATABASE command. Hot backup is an online activity. Cold backup is stopping the SQL Server Engine , Copy MDF and LDF physical files to a backup location and Restart the service. Cold backup requires downtime and generally it is not the case in Production servers.

Scenario 1 : Restore Master & MSDB databases form the Backup file

I have full backup of Master and MSDB. My Master and MSDN databases got corrupted or i did some major changes and i want to rollback. Since i have backup i can restore the system database from the backup.

High level Steps

(a) Stop and Start the SQL Server Database Engine in Singe User Mode
(b) Restore the Master Database from SQLCMD prompt
(c) Start the SQL Server Database Engine
(d) Restore MSDB Database

Windows Start -- Run – Services.MSC --- Enter

You will get this window

Restore the Master Database from Command Prompt

Windows --- Start --- Run --- CMD --- Enter

-- If the Screen is not readable this is the command and the result

C:\>SQLCMD
1> RESTORE DATABASE MASTER FROM DISK='D:\MASTER_FULL.BAK' WITH REPLACE
2> GO
Processed 352 pages for database 'MASTER', file 'master' on file 1.
Processed 2 pages for database 'MASTER', file 'mastlog' on file 1.
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.

C:\>

The message says that you have successfully restored Master database.

Note : You have to restart the Database Engine Now

Restore MSDB Database from Management Studio

RESTORE DATABASE MSDB FROM DISK='D:\MSDB_FULL.BAK' WITH REPLACE

Scenario 2 : Server Crashes – Reinstalled SQL Server Instance

In this scenario, the important point is , you must build the new installation to the same build from where the backup was taken. You have to have same OS Version Edition, Service pack ,Patches and You also must have SQL Server Version , Edition , Service Pack and Patches as the Earlier server from where the backup was taken. Once you build your server to the same configuration the process is same as mentioned in Scenario 1

Configuring Linked Server from SQL Server to MySQL

2008-08-28T04:24:00.000-07:00

Recently I got a project in which there was a Migration Task to be done from MYSql to SQL Server. Prior to this I never worked in MySQL and I thought it would be just simple as creating LinkedServer to mySQL and pull the data to SQL Server from SQL Server itself. So I decided to create linked server from SQL Server to My SQL and pull the data using OPENQuery Function. I tried to configure linked server in SQL Server and then realized that it is not simple as that. I had to lot of steps like download driver, install it , configure the provider etc. As usual I searched in net and found a thread which neatly mentioned the steps ( SQLServerCentral.com). This thread really solved my problem in hours. Of course the thread was good enough for an expert (indirect way of saying that am an expert ), but for a newbie it may be bit confusing. So I thought to make a comprehensive document which has all the step by step screen shots so that the process will be easier. Having said that, I am not taking any credit for this document, the whole credit goes to Jim Dillon who posted his solution in the above mentioned site.

Tested Environment :
Operating System : Windows XP, Windows 2000, Windows 2003
SQL Server : SQL Server 2005, (Note : I have not tested SQL Server 2000 but I feel it should work)
MySQL : MySQL Server 5.0
MySQL ODBC : ODBC 3.51

High level Steps

• Download and Install ODBC Driver from MYSQL Site
• Create ODBC DSN
• Create Linked Server in SQL Server
• Configure Provider
• Create linked server
• Enable OpenQuery in SQL Server instance
• Create Openquery script

Step 1 : Download MySQL ODBC Connector
Download MySQL Connector/ODBC 3.51 from : http://dev.mysql.com/downloads/connector/odbc/3.51.html

Download the ODBC connector for your Operating System. I have windows xp , so I will download Windows MSI installer (x86)

Step 2 : Install ODBC connector

Double click the downloaded file

At last screen you will get a Finish button and click that. Now You have installed ODBC connector for MySQL.

Step 3 : Create System DSN for MYSql ODBC Driver

Windows Start --- >> Run --- >> odbcad32

Or

Windows Start --- >> Control Panel ->Performance and Maintenance (in XP) --- >> Administrative Tools -> Data Sources (ODBC)

Step 3 : Create Linked Server in SQL Server 2005.

Change the properties of the Provider
Select the properties as shown in the screens

--======================================================================================================

Script to Create Linked Server
It is always better to create linked server using Script than GUI. You have more control and script of course is more readable. I am not giving the screen shot for this steps. Copy paste the script to a query analyzer change DSN Name, Username, Password and run.
--======================================================================================================
/****** Object: LinkedServer [LinkedServerTOMySQL] Script Date: 08/28/2008 11:40:28 ******/
EXEC master.dbo.sp_addlinkedserver
@server = N'LinkedServerTOMySQL',
@srvproduct=N'MySQLDatabase',
@provider=N'MSDASQL',
@datasrc=N'MYSQLODBCConnection'

Parameter Explanation

@server = N'LinkedServerTOMySQL', -- Linked server name (it can be anything)
@srvproduct=N'MySQLDatabase', -- Keep as it is
@provider=N'MSDASQL', -- Keep as it is
@datasrc=N'MYSQLODBCConnection' --DSN Name Created in Step 3 (very important)

/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LinkedServerTOMySQL',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'root',
@rmtpassword='change thepasswordhere'

GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServerTOMySQL', @optname=N'use remote collation', @optvalue=N'true'

--======================================================================================================

Enable Distributed Queries (OpenQuery) in SQL Instance
By default in SQL Server 2005 Distributed Queries are disabled. You have to enable it using the following script. (Note : In SQL Server 2000 no need to run this script)

Copy this script to a query analyzer and run
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'Ad Hoc Distributed Queries', N'1'
RECONFIGURE WITH OVERRIDE

Finally, Test the Linked Server and Run a OpenQuer against the LinkedServer Configured

Check the following Screen

Restart Database Engine and SQL Server Agent

Once you have completed all the steps you may restart the services. I have not tested this whether you need to restart it or not

Summary :
If anyone feels some screen shots are missing please drop a comment.

Creating Files from the images stored in Binary format in the database table.

2008-08-09T23:23:00.000-07:00

Problem :-
Images are stored in a database table-Varbinary (SQL Server 2005) column. Form the stored images we need to create individual image files.

Solution :
BCP QUERYOUT with little format file tweaking can be a wonderful solution for this. You can include this BCP queryout in a XP_CMDShell dynamic sql from SQL Env and it will provide a simple solution to this complex requirement.

Demo environment details
Database : AdventureWorks
Table : ProductionPhoto
Image Column : LargePhoto
There are 101 rows in this table, each one having a LargePhoto column with some image. We need to create 101 JPG files from this table. Here we go…

Step 1 : Creation of Format File

C:\>bcp AdventureWorks.Production.ProductPhoto format nul -T -n -f c:\PP.fmt

Step 2 : Format file will looks something like this

Step 3 : Tweak Format file : We need to get the LargePhoto column binary data.

• Remove all column except LargePhoto
• Change Prefix of LargePhoto column from 8 to 0 (zero)
• Change Number of column from 6 to 1
• Change column sequence from 4 to 1
• After the ncessary modification the format file should look like next sceen

Step 4 : Use BCP QUERYOUT to create Image files

My BCP QUERYOUT query is this
--=====================================================================================
C:\>bcp "SELECT top 1 LargePhoto FROM AdventureWorks.Production.ProductPhoto wh
ere ProductPhotoID=69" queryout c:\ProductPhotoID_69.jpg -T -fC:\PP.fmt

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (1000.00 rows per sec.)

C:\>
=====================================================================================

The above BCP Queryout statement is created a file ProductPhotoID_69.jpg . Now let us go and open that form windows

Double click the file and the file opened

Step 5 : Loop through all the records in this table and create respective files of each row in C:\Photo folder

--==============================================================================
SET NOCOUNT ON

DECLARE @ProductPhotoID int, @Sqlstmt varchar(4000),@LargePhotoFileName varchar(200)

DECLARE Cursor_ProductPhoto CURSOR FOR
SELECT ProductPhotoID, LargePhotoFileName
from AdventureWorks.Production.ProductPhoto
ORDER BY ProductPhotoID

OPEN Cursor_ProductPhoto

FETCH NEXT FROM Cursor_ProductPhoto
INTO @ProductPhotoID, @LargePhotoFileName

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @LargePhotoFileName

-- Framing DynamicSQL for XP_CMDshell

Set @Sqlstmt='bcp "SELECT top 1 LargePhoto FROM AdventureWorks.Production.ProductPhoto where ProductPhotoID='+str(@ProductPhotoID)+'" queryout c:\Photo\'+ltrim(@LargePhotoFileName)+' -T -fC:\PP.fmt'
print @sqlstmt
exec xp_cmdshell @sqlstmt
FETCH NEXT FROM Cursor_ProductPhoto
INTO @ProductPhotoID, @LargePhotoFileName
END
CLOSE Cursor_ProductPhoto
DEALLOCATE Cursor_ProductPhoto

--==============================================================================

Check this Screen. The above script has created individual files in this folder

Summary :-

This solution provides very simple method to create files from the binary stored in Database by little tweaking in Format file. If you want to keep a backup of Images in file system format then this method can really help.

Microsoft SQL Server 2008 Released

2008-08-06T21:56:00.000-07:00

Finally Microsoft SQL Server 2008 RTM version is released today. It was suppose to release almost one year back but somehow did not. There are seven editions in SQL Server 2008.

In a press release, Microsoft cited several large enterprise customers who are testing SQL Server 2008, including Xerox, Siemens, Clear Channel Communications and Fidelity Investments.

Best Practices, Design and Development Guidelines for SQL Server

2008-07-24T01:05:00.000-07:00

There are many resources available in the net but here I have a list of Best Practices,Design Guidelines and General Guidelines in Database Development and Designing specifically for SQL Server.

Best Practices

1. Use Stored Procedure: Benefits are as follows :-
(a) Code reusability
(b) Security : You can control permission on sp
(c) Execution plan reusability : Though adhoc query also create and reuse plan, the plan is reused only when the query is textual match and the datatypes are matching with the previous call. Any datatype or you have an extra space in the query then new plan is created
Eg.
-------------------------------------------------------------------------------------
Select Sal from Employee where sal=$10 --Money
--And
Select Sal from Employee where sal=10 -- Int
-------------------------------------------------------------------------------------
Above statements will create different execution plan because the datatype of value is different.
(d) Procedure gives more Readability and Manageability.

2. Use Fully Qualified Name for objects : This is very significant. You must use fully qualified name when you refer any object in SQL Server. Ie. SchemaName.ObjectName. Because, when the execution plan is prepared by the query engine , in the binding process, Query engine has to resolve the Object existence. If you specify the fully qualified name the object resolution become easy for the engine and also it will be more readable.

3. Avoid using Scalar Function in SELECT statement: Recently I faced this issue and I emphasis this point. Never use Scalar function inside a query which returns a large number of rows. Scalar function behave like a cursor when you use Scalar function inside a query which returns large number of rows . Change the scalar function to Inline or Multiline table function or a view.

4. Avoid Mixing-up DML and DDL statement on a temp table inside sp : This is very important. When you Create a temptable (#table) and ALTER the same temptable in the same storedprocedure later, this DDL and DML mix-up causes the stored procedure to get recompiled. So, if a stored procedure is getting recompiled in each call check this point.

5. Select only the required columns: Select only the required column in select statement. Usage of SELECT * can be the cause of NOT using the indexes available on the table . Also if you are selecting more data then you are doing more IO. In short we should limit the IO.

6. Avoid Usage of HINTS : HINTS prevent Query engine automated optimization capability. You may find a hint gives you better performance on a particular scenario. But it may behave differently as the data grows or when scenario changes. Check this KB on HINTS http://msdn.microsoft.com/en-us/library/ms187713.aspx

7. Use Table variable and Temp table as far as possible: You must use Table variable (@TableName) or Temp table (#TableName) for intermediate storage of records in an procedure. Avoid using Table variable for large record set. There are pros and cons between Table variable and Temp table, but in general, if the record set is small you should go for Table variable.

8. Use SET NOCOUNT ON : Basically, you must reduce the data transferred on the network. Database Engine, return the number of rows effected by the statements to the client which is unnecessary and you can avoid that using this statement. It is a must in all Stored procedure.

9. Do not change SET OPTION In connection: Changing SET Option during connection or anywhere else will cause the stored procedure to recompile. Refer this KB for more info http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

10. EXISTS vs IN : IN Operator can easily be replaced by EXISTS which is more optimized for correlated queries. But you may find IN better for small tables.

11. Keep the transaction as short as possible: Deadlock is a outcome of ill-formed query. You must keep the transaction as short as possible to avoid dead lock. And also refer the object in the same order inside transaction.

12. Avoid user input inside a transaction: Do not accept a user input inside a transaction.

13. Avoid doing Front-End work in Databases : String processing , Ordering , Conversion etc can easily be done at the client side and you should avoid doing these kind of work in Database. The point here is, you should have a clear segregation of tasks between Data Layer, Data Access Layer (DAL) and Business Layer. For example, you can easily number your rows in client side where as if you do that in Database you have to use a Function or extra join which can be avoided.

14. Avoid Function in select statement: Any functions like CONVERT(),CAST,ISNULL usage in query may ignore the indexes available on the table.

15. Do not use EXEC (‘String’) , use sp_executeSql : As far as possible you try to avoid Dynamic SQL. If there is no other option use sp_ExecuteSQL DO NOT USE EXEC(‘string’). Because EXEC statement is prone to SQL Injection and it is not parametrized query which can re-use execution plan.

16. Use proper size for the input parameter: This is one of the step to avoid SQL Injection and also the reduce the memory usage.

17. Do not keep the name of sp with sp_ prefix: Naming convention is very important. Do not name the storedprocedures with SP_ as prefix (eg sp_ somespname ) because this naming convention is used for system stored procedure in MS SQL Server.

18. USE WHERE Condition as far as possible: Basically, you should limit the rows fetched by the query.

19. Avoid Negative operator : Avoid using <> , NOT IN, NOT EXISTS kind of operator because it causes a table scan. Query engine has to ensure there is not data till the last row is read.

20. Avoid Cursor /loops: In SET Based operation, in general looping can be avoided.

21. Avoid using Like ‘% %’ : If you use % in both side of the searching value, the query will go for table scan which should be avoided. If the application is more text searching kind go for Full Text Index.

22. Do not use WITH Recompile : Usage of WITH Recompile causes the procedure to recompile each time it call. You must avoid this command.

23. JOIN Consideration : When you JOIN two table consider these points
(a) Avoid using negative operator (<> ,NOT IN) in JOIN
(b) Avoid Like operator in Join

Design Guidelines

1. Create Covering indexes: Create covering indexes. Covering index will have all the data required by the query at the leaf level itself. Covering contains all the columns used in SELECT, WHERE, ORDERBY, JOIN etc.
Eg.
Select Col1,Col2 From YourTableName Where Col3=1 Order by Col4.
The coveing index for the above mentioned query will be
Col1+ col2+ col3+ col4. (Note : Most selective column should come first in the index creation statement)

2. Remove Unwanted indexes : In SQL Server 2005 it is very easy to find unused indexes. Too many or too less indexes on a table are equally bad. If you have unwanted/unused indexes on a table Insert/Update statement will have performance hit and also we all know indexes consume space.

3. Create the indexes most selective column as first column in Index : Index creation has to be done after proper analysis. You must create the index with Most Selective column at first and so on.

4. Formatting the stored procedure and queries : You must have a format / template for each object (sp/function/views) and everyone (the dev team) should stick to the format defined. And also the query has to be formatted well so that it is more readable.

5. Use Identity column if the table is INSERT oriented table as Clustered Index to avoid page split : This is a design and data modeling issue. If you have more insert kind of table (some kind of logging table) then you must go for Identity Column (ever increasing) as Clustered Index. This helps to resolve page split. There may be Hotspot issue (all transaction contending for same page of a table), but I have never faced.

6. Use proper fillfactor for Indexes: Very important to avoid Page Split. In general transactional table can be kept at 80-90 fillfactor.

7. Balanced Normalization / De-normalization: You must have a trade off between Normalization and de-normalization. At time De-normalization can give you better performance at the cost of Data redundancy.

8. Primary Key size and Composite primary key : You must limit the size of the PK because, in a relational database, you may be creating foreign key which refers this primary key. If you have multiple Column in PK (composite PK) or big size , you are knowingly or unknowingly increasing the space usage. If the composite PK contains more than 3 columns then you may go for surrogate key like Identity column as PK.

9. Do not alter system Objects: If your application requires some tweaking of system objects then you are in trouble. The structure of system object can be changed by Microsoft in any release or patches. So avoid such modeling.

Guidelines for Datatype Selection

As a Database architect I believe in the significance of proper datatype selection while designing the tables. if you do a proper analysis of the data and then select the datatype, then you can control the row, page, table size and hence increase the overall performance of the database. Following points you may consider when you design a table :-

1. If your database is to support web-based application better to go for UNICODE for the scalability of the application. (Unicode (nchar, nvarchar) takes 2 bytes per char where as ASCII (char,varchar) datatypes takes 1 bytes per char)

2. If your application is multi-lingual go for UNICODE.

3. If you are planning to include CLRDatatype (SQL Server 2005) in the database go for UNICODE Datatypes , because, if CLRDatatype is going to consume the data then it must be in UNICODE.

4. For numeric column, find the range that column is going to have and then choose the datatype. For eg. You have a table called Department and DepartmentID is the Primarykey Column. You know that the maximum rows in this table is 20-30. In such cases it is recommended to choose TinyINT datatype for this column. Generally keeping all numeric columns type of INT without analyzing the range that column going to support is not at all recommended from storage perspective.

5. Description /Comments /Remarks sort of columns may or may not have data for all the rows. So it is better to go for Variable datatypes like Varchar ,Nvarchar.

6. If you know the column is not nullable and it may contain more or less the same size of the data then for sure go for Fixed datatype like CHAR or NCHAR. Having said that it is important to know that, if you select fixed datatypes and if the column is nullable then, if you do not have any data (null) then also the column will consume the space.

7. If the size of the column is less than 20 char , use fixed width datatypes like NCHAR or CHAR.

8. I have seen in many applications use Decimal to store currency kind of data though the application needs the precision which can be supported by money. So, my point here is, use Money datatype if you need only 4 precision.

9. Use UniqueIdentitifier column as PK and ClusteredIndex or so only when it is unavoidable because UniqueIdentitifier takes 16 Bytes of the space.

General Guidelines

1. Write ANSI standard Code : You must write standard code which will scale your application. ie migration to next version will not be an issue. Do not use Deprecated features. For eg. There are DBCC Command to rebuild index but in SQL Server 2005 in order to standardize things, you have ALTER INDEX command which does the same thing.

2. Do not give Error message which expose your architecture to the frontend: I have seen folks giving very detailed error message which tells you “ blah blah table do not have this rows in blah blah database” kind which can be a invitation to the hacker

3. Use proper Isolation level required for the application: This is very significant. Before going for any isolation level, you must know the implication. All application cannot afford READUNCOMMITTED Isolation level since it can cause data inconsistency issues like Dirty Read, Phantom read, Lost Update etc. WITH NOLOCK Hint is nothing but READ UNCOMMITTED isolation level.

4. Keep the Database Object Scripts in Source Control: We all are aware of this point but generally ignore. This is important for fault tolerance and management when multiple developers are working on same project.

Visio - ERROR – Failed to Initialize the Modeling Engine.

2008-07-21T23:38:00.000-07:00

Recently I reinstalled Visio 2003 Enterprise Architecture edition and it started popping up the above mentioned error. I was not able to do any Database modeling kind of tasks since the options were not available. I goggled as usual and I found the same issue is being reported by many. The solution for this issue is uninstalling Visio Update patch KB947650. I did the same and it fixed my error

follow these steps :-

Control Panel –
Add or Remove Programs
Check “ Show Updates” check box in Add or Remove programs window
– MS Visio Enterprise Architect
Select KB947650 update and remove it.

Scalar Function and Performance Issue

2008-07-15T04:15:00.000-07:00

Performance tuning is one of the main tasks I do and there is always scope to learn new things in this area. Recently, I got a query which was using a Scalar function takes almost 4 min. When the scalar function is removed it came within one sec. I know scalar function inside a query is not optimized and may be because of that I very rarely use scalar function. But in this particular project, scalar function is very common and tuning query with scalar function is little tricky. Following are the suggestions I got from my MVP friends :-
(a) If possible create a view and join with the view: This was not possible for me since I had to pass the parameter.
(b) You may also create a Inline or multiline table function: I went with this route. I created a multiline function and I tell you the performance was improved manifold.

Sample code
The scalar function was something like returning appointments concatenated string for a given EmployeeID

Same logic in Multiline function which uses FOR XML PATH query

Create function fnConcatMultipleRowValuesToColumn ()
Returns @tab Table (EmpID int,con varchar(200))
As
Begin

DECLARE @Designation TABLE (EmpID INT, Code VARCHAR(50))

INSERT @Designation
SELECT 100, 'SR. Engr.' UNION ALL
SELECT 100, 'TL' UNION ALL
SELECT 100, 'PM' UNION ALL
SELECT 200, 'QA' UNION ALL
SELECT 200, 'SR QA' UNION ALL
SELECT 200, 'TL QA'UNION ALL
SELECT 300, 'DBA'UNION ALL
SELECT 300, 'SR. DBA'UNION ALL
SELECT 300, 'TL DBA'

-- Show the expected output
Insert @tab
SELECT DISTINCT s1.EmpID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Designation AS s2
WHERE s2.EmpID = s1.EmpID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Designation AS s1
ORDER BY s1.EmpID

Return
End

Function Call
Select *From fnConcatMultipleRowValuesToColumn ()

Summary :
Avoid Scalar functions as far as possible. This was introduced in SQL Server 2000 and there may be certain scenario where it may be useful like encapsulation. But when you use scalar function within a select statement which returns many rows may trouble you.

Few good resources to refer
http://sqlblog.com/blogs/adam_machanic/archive/2006/08/04/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post.aspx
http://www.sqlservercentral.com/Forums/Topic446308-360-1.aspx

Challenges of being a Database Architect

2008-07-13T09:27:00.000-07:00

I have been working as a database architect for quite sometimes. Working as a database architect in small organization (for that matter any organization) is very challenging because at times you may be find yourself into the projects which do not have any SLA (Service Level Agreement) or even SRS (Software Requirement Specification). There are many Non-Functional Requirement (NFR), you need to be concerned while conceiving the architecture. If you ignore those, then you are in trouble sooner than later. If you have not judged NFR which is not mentioned anywhere in any documents then it will certainly backfire when you implement the project. I have seen in many projects requires last minutes architectural patch-up because of ignorance or slackness in the Database Architecture’s side. To be in safer side, though there is no SRS or SLA, you should have a list of assumptions in which, whatever concerns you have are mentioned and the assumptions are either agreed or disagreed by the client. If disagree, then client has to mention what are they looking for. By doing this what you achieve is, you are preparing a SRS and SLA by yourself and client is aware of it and approved it knowingly or unknowingly. No need to say, everything has to be properly documented and tracked through mails. In general, following are few NFRs we as a database architect should be aware of :-

(a) Performance is of course the main issue in any project: - Generally, when you conceive the data architecture you should be well aware of the current application(s) performance and its issues. Of course the client must be expecting performance than the existing application (if any) since he/she is spending lot of money on the new one. So when you design the model you should be well aware till what level you must normalize and where you want to de-normalize the data to increase the performance.
(b) Maintainability &supportability: This point is very important. The client should be able to maintain the application and the database without any unreasonable increase in cost . For eg. If you are designing architecture where there is a need of proper fulltime DBA and the client is not aware of it or not ready for it.
(c) Data migration and synchronization issues: - I have seen in many application the client is not aware of the data migration issues and the requirement. The new data model of course has to different from the existing one . If the data structure is changed then the data has to be migrated with lot of transformations. So you as a database architect you should be aware of this task and the risk involved in it.
(d) Authentication method : - A database architect should be well aware of the authentication method you are going to use and the formalities involved in the process .
(e) Security :- This is another important point which may cause major change if you have not analyzed it properly. You should be well aware of the encryption method if the application demands. Also Database architect should be aware of what access method you want to use or whether you are want firewall exception kind.
(f) SQL Injection : Any application which developed on these days should be SQL Injection proof. As a database architect you should not wait for any SRS or SLA for these kind of standard.
(g) Isolation level the application demands: - Very important from the performance point of view. If you are aware of the isolation level requirement for the application, then you can use decide the isolation level accordingly.
(h) Disaster recovery or Fault tolerance required :- This may not be mentioned in any SLA or SRS. But it is understood (or client may think so) that any application should address these requirement by default.
(i) Capacity Planning :- You should be well aware of approximate size of your database. If you are committed to store the images in database for some reasons like security and management, you should do capacity plan accordingly.
(j) Excessive Usage of features: - I want to emphasis this point. If a product offers few advanced features, it does not mean that you must use that in your architecture for the sake of updated technology. You must be aware of the feature inside out and you should use only if it suits you. I have faced such excessive usage of features in couple of projects.

Summary :-
My list may not be the comprehensive one and please feel free to correct my list. My point here is, Database Architect or Architecture is crucial to any project. Basically, if you see, 95% of the applications in this world will be dealing with data in some way or other. So any erroneous decision in database architecture will kill the whole application. To become a successful database architect in you should be aware of the risk involved in this job.

DDL Trigger to prevent creation of SQL Login with Blank Password

2008-07-09T06:44:00.000-07:00

There was a query in MSDN forum regarding preventing creation of login with Blank password using T-SQL script. I was clueless about this. Then Rich was referred to Laurentiu Cristofor's blog. There was no direct answer but it give me a wonderful solution which I was not aware. I would like to thank both of them here. Please check the blog entry Here

So I thought to tweak pwdcompare() function and make a DDL Trigger. Here we go…

Create TRIGGER Svr_DDLtrg_Audit_BLANK_PASSWORD
ON All Server
FOR
Create_Login,Alter_Login
AS
SET NOCOUNT ON

BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

declare @loginname varchar(100)
select @loginname=@Eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)')
print @loginname

if exists (select 1 from sys.sql_logins where pwdcompare('', password_hash) = 1 and name=@loginname)
begin
Rollback
end
else Print 'good password'

End

Note : I rollback the statement if the password is blank. IF you want to audit or log then you may modify the script accordingly

Best Practices - Datatype Selection while designing tables

2008-07-01T02:02:00.000-07:00

• If your database is to support web-based application better to go for UNICODE (Unicode like nchar, nvarchar 2 bytes per char where as ASCII datatypes takes 1 bytes per char) datatypes because you may be going to support different types of clients.
• If your application is multi-lingual go for UNICODE.
• If you are planning to include CLRDatatype (SQL Server 2005) in the database go for UNICODE Datatypes instead of ASCII Datatypes, because, if CLRDatatype is going to consume the data then it must be in UNICODE.
• For numeric column, find the range that column is going to have and then choose the datatype. For eg. If you are sure that the column cannot have more than 255 like DepartmentID in a small organization may not go probably beyond 10 or 20. In such cases it is recommended to choose TinyINT datatype. Generally keeping all integer columns type INT without analyzing the range that going to support is not at all recommended from storage perspective.
• Description /Comments /Remarks sort of columns may or may not have data for all the rows. So it is better to go for Variable datatypes like Varchar ,Nvarchar.
• If you know the column is not nullable and it may contain more or less the same size of the data then for sure go for Fixed datatype like CHAR or NCHAR. Having said that it is important to know that, if you select fixed datatypes and if the column is nullable then if you donot have any data (null) then also the column will consume the space.
• If the size of the column is less than 10 char , use fixed width datatypes like NCHAR or CHAR.
• I have seen in many applications use Decimal to store currency kind of data though the application need less precision which can be supported by money. So, my point is, use Money datatype if you need only 4 precision.
• Use UniqueIdentitifier column as PK and CI or so only when it is unavoidable because UniqueIdentitifier takes 16 Bytes of the space.
Note : The point I want to make here is, if you do a proper analysis of the data and then select the datatype then you can control the row, page, table size and hence increase the performance.

FAQ : Explain a scenario which supports vertical partitioning of a table in SQL Server.

2008-06-30T01:28:00.000-07:00

The scenario may be different according to the SQL Server Version you have (SQL 7.0, SQL 2000, SQL 2005). I will be covering SQL Server 2000 and 2005

SQL Server 2000.
In SQL Server (in 2000 and 2005) the IN_ROW_DATA or the row size of a table can have only max 8060 bytes. If you have a table which contains 4 columns of varchar (3000) then though you can create the table but if the data being inserted is more than 8060 bytes then the insert will fail. So what we generally do is, we vertically partition the table to two or more table as per the requirement and keep ONE to ONE relation between all the tables. So this is a valid reason to partition your table vertically. When you do vertical partitioning, try to keep most commonly used small size column in single table.

SQL Server 2005
In SQL Server 2005, the above mentioned problem of SQL Server 2000 is not there because of the storage architecture change called ROW_OVERFLOW_DATA. Ie. In SQL Server 2005 you can have a row which exceed 8060 bytes provided the columns are variable types(Varchar,nvarchar). What Database engine internally does is, it keep all the variable datatypes (varchar, nvarchar )columns data in ROW_OVERFLOW_DATA page. Precisely, the Row size limitation is only applicable to fixed size columns like CHAR, NCHAR. So SQL Server 2000 scenario of partitioning table because of the row size exceeds 8060 bytes is not valid in SQL Server 2005.
But there is a valid reason to do vertical partition of the table in SQL Server 2005. If you are using ONLINE INDEX feature of SQL Server 2005 then you cannot use LOB data as a part of index at the leaf level. And you want to use the LOB column in Index because of the performance benefit it provides. In that case best method is to partition the table vertically in such a way that, keep the small ,mostly used columns in single table (like product detailed description may not be asked frequently by the user) and the columns those are referred in less frequency in another table. Since you do not have LOB data in the table you can use ONLINE INDEX feature in the table.

FAQ: How to find the authentication mode of a SQL Server Instance ?

2008-06-19T04:14:00.000-07:00

Using System Stored procedure xp_loginconfig

xp_loginconfig system strored procedure will give you the authentication information in SQL Server. You can call the sp without parameter to get detailed information or with “Loging Mode” parameter to get only the Authentication mode.
EG.
EXEC xp_loginconfig 'login mode'

Result
Name Config_Value
login mode Mixed

Read Registry to get the authentication Information.

Another method would be reading Registry. Authentication information can be read from the following registry entry.
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.##\MSSQLServer\LoginMode

Note : Change ## the number according to your environment.

Eg.

EXECUTE MASTER..XP_REGREAD
'HKEY_LOCAL_MACHINE', 'SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\MSSQL.4\MSSQLSERVER',
'LOGINMODE'
Result
Value Data
LoginMode 1

FAQ : How to find the Index Creation /Rebuild Date in SQL Server

2008-06-18T22:08:00.000-07:00

AFAIK, there is no system object gives you the information of Index creation Date in SQL Server. If the Clustered index is on PK then the creation date can get from sysobjects or sys.objects. But that is not the case always.

This query which uses STATS_DATE() function to get the STATISTICS updated date. This will not give you accurate result if you are updating STATISTICS explicitly. The logic behind the query is, if you rebuild indexes the STATISTICS are also being updated at the same time. So if you are not explicitly updating STATISTICS using UPDATE STATISTICS tableName command , then this query will give you the correct information

--In SQL Server 2000
Select Name as IndexName,
STATS_DATE ( id , indid ) as IndexCreatedDate
From sysindexes where id=object_id('HumanResources.Employee')

-- In SQL Server 2005
Select Name as IndexName,
STATS_DATE ( object_id , index_id ) as IndexCreatedDate
From sys.indexes where object_id=object_id('HumanResources.Employee')

Cumulative update package 7 for SQL Server 2005 Service Pack 2

2008-06-01T22:13:00.000-07:00

Check out Cumulative update package 7 for SQL Server 2005 Service Pack 2 here
http://support.microsoft.com/kb/949095/en-us.

Check the bug fix list and if your application need that bug fix then only apply cummulative hotfix in Production env.

How to assign XML output of select * FROM Yourtable FOR XML AUTO query to a variable

2008-06-01T22:12:00.000-07:00

declare @DataXML xml
set @DataXML =(SELECT * FROM YourTable FOR XML AUTO, ELEMENTS)
select @DataXML

FAQ : What is the difference between Lazywriter and Checkpoint in SQL Server

2008-06-01T22:11:00.000-07:00

Lazywriter
The lazywriter thread sleeps for a specific interval of time. When it is restarted, it examines the size of the free buffer list. If the free buffer list is below a certain point, dependent on the size of the cache, the lazywriter thread scans the buffer cache to reclaim unused pages. It then writes dirty pages that have a reference count of 0. On the Windows 2000, Windows Server 2003, and Windows XP operating systems, most of the work populating the free buffer list and writing dirty pages is performed by the individual threads. The lazywriter thread typically has little to do.

Checkpoint
The checkpoint process also scans the buffer cache periodically and writes any dirty data pages to disk. The difference is that the checkpoint process does not put the buffer page back on the free list. The purpose of the checkpoint process is to minimize the number of dirty pages in memory to reduce the length of a recovery if the server fails. Its purpose is not to populate the free buffer list. Checkpoints typically find few dirty pages to write to disk, because most dirty pages are written to disk by the worker threads or the lazywriter thread in the period between two checkpoints

Refer
http://msdn.microsoft.com/en-us/library/aa175260(SQL.80).aspx
BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/4b0f27cd-f2ac-4761-8135-adc584bd8200.htm

FAQ: How to see what is inside hidden SQL Server 2005 Resource Database (mssqlsystemresource) ?

2008-06-01T22:10:00.000-07:00

We all know the master database in SQL Server 2000 was split into two databases in SQL Server 2005. One kept as Master only and the other database is Resource database which is hidden. You can’t see that in Management studio or in any tool. You can see the physical files in Data ( default )folder. The idea behind this separation of objects is to allow very fast and safe upgrades.

If you want to see what is inside this database follow these steps

(a) Stop SQL Server
(b) Copy /paste mssqlsystemresource mdf and ldf files to a new location
(c) Create a new database by Attaching the files from the new location

Now you have the hidden database right in front….

Upgrading SQL Server from lower version to higher version

2008-06-01T22:09:00.000-07:00

Upgrade methodology

(a) First step, before going for any up gradation, you should take full backup of the existing databases including system databases. If anything goes wrong you should have a full backup to fall upon
(b) Run Upgrade advisor : You must run this tool before going for up-gradation. This tool will analyze the existing database in lower version and suggest the potential problems , which may be because of the feature is removed or deprecated in the newer version. Once the upgrade advisor projected any critical problem , you may address the problem before going for up-gradation.
(c) Once you found that there is no major issues reported by Upgrade Advisor, you can plan your up-gradation. You must document each and every step you follow. Also ensure that you have a rollback plan incase anything goes wrong. You must have a proper testing script as well.
(d) You have two choices in upgrade.
In Place upgrade : When you upgrade an earlier SQL Server release in-place through the install upgrade process, all existing application connections remain the same because the server and server instance do not change. This approach requires a more thorough fallback plan and testing. By performing an in-place upgrade, logins and users remain in-sync, database connections remain the same for applications, and SQL Agent jobs and other functionality is concurrently upgraded during the installation. Note that several features, such as log shipping, replication, and cluster environments, have special upgrade considerations.

Side By Side : This method is more or less manual process hence you have full control over the process. You can test the upgrade (migration) process by running parallel system , test it and once proven bring it online. But the disadvantage here is, you have to have additional resource (Hardware/licenses ) . Side by side will be always better since you have the existing instance intact. But at time it may not be feasible because of hardware constraint or Instance Name (like the application demands Default Instance. I would say this method is more clean and controllable.

(e) Once you have upgraded test the system with the application with full load before going online.

Migrating Database in Side by Side Upgrade.
(a) If you have chosen side by side method, after installation of new version of sql server you must migrate the user database from older version sql server instance. There are three options available here.
(a) Backup /Restore : Best and easy method. Benefit is source database can still be online , no downtime
(b) Detach / Attach : You have down time at the source but easy method.
(c) Copy Database Wizard : This tool internally does detach/attach only.

Migration of SQL Logins
This is one of the major disadvantage of Side by Side up-gradation. You must transfer the SQL Logins explicitly. You must transfer the login with the password. Microsoft has provided the script for that. How to Transfer SQL Logins

Migration of SQL Scheduled Jobs
Simple , just need to create the script from source and run that script in target server.

Migration of SSIS Packages
You can use save as option of SSIS package . You can open the package and save the package as file system and then migrate.

FAQ : What all are the recommended events for DTA / ITW workload

2008-05-28T22:14:00.000-07:00

If you have provided same workload without different events and columns DTA may give you different report. For eg. if you have not included Duration column, DTA will tune the workload in the order they appear in the workload. IF the workload contains duration column , DTA will tune the events in the descending order of the Duration. So to get better result from DTA included recommended events and columns.

Events Recommended
RPC:Completed
RPC:Starting
SP:StmtCompleted
SP:StmtStarting
SQL:BatchCompleted
SQL:BatchStarting
SQL:StmtCompleted
SQL:StmtStarting

FAQ : How to search for an object in all the databases

2008-05-27T22:56:00.000-07:00

SQL Server 2005

CREATE TABLE #TEMP (TABLENAME SYSNAME, OBJECTNAME SYSNAME,TYPE CHAR(10))

INSERT INTO #TEMP

EXEC SP_MSFOREACHDB "SELECT '?' DATABASENAME, NAME,TYPE FROM ?.SYS.ALL_OBJECTS WHERE NAME='YourSearchingObjectName'"

SELECT * FROM #TEMP

DROP TABLE #TEMP

SQL Server 2000

CREATE TABLE #TEMP (TABLENAME SYSNAME, OBJECTNAME SYSNAME,TYPE CHAR(10))

INSERT INTO #TEMP

EXEC SP_MSFOREACHDB "SELECT '?' DATABASENAME, NAME,XTYPE FROM ?..SYSOBJECTS WHERE NAME='YourSearchingObjectName'"

SELECT * FROM #TEMP

DROP TABLE #TEMP

Note : Replace "YourSearchingObjectName" with the object name which you are searching in the select query

How to Audit or Bypass TRIGGER Execution Using CONTEXT_INFO()

2008-05-06T23:10:00.000-07:00

When a TRIGGER executes, it may be useful to have the TRIGGER code know which Stored Procedure caused the action that is executing the TRIGGER code. It may be that there is a need to log the entry point, or perhaps, there is conditional logic in the TRIGGER that depends upon the entry point. In this paper, two different scenarios are addressed. First, How to Track Which Stored Procedure Fired the Trigger, and Second, How to Limit a TRIGGER to Fire ONLY from Certain Stored Prodedures. This approach has been tested with SQL Server 2005 and 2008. SQL Server 2000 requires a minor modification that is presented at the end of the paper.

Check my paper in MSDN Wiki page
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AuditOrBypassTriggerExecution

FAQ - How to use Stored Procedure in Select Query

2008-05-01T23:00:00.000-07:00

Very common question in T-SQL Forums

The requirement is, need to use Stored procedure output (Exec SP) in Select query or in a Join. There is a method which may not be a recommended one. You can create a LoopBack Linked server to your own server and use OPENQUERY TO extract the data by EXECUTE Stored procedure.

LoopBack Linked Server

Linked servers can be defined to point back (loop back) to the server on which they are defined. Loopback servers are most useful when testing an application that uses distributed queries on a single server network.

Eg.

SELECT *FROM OPENROWSET
('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')

OR

sp_addlinkedserver @server = N'MyLink',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'MyServer',
@catalog = N'AdventureWorks'

Note :
@server = N'MyLink' : It can not be your Server name. It is just a name and can be anything otherthan your actual servername. IF you give your server name in this parameter you will get an error as follows
Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server 'LHI-115' already exists.

@datasrc = N'MyServer' : This parameter value has to be your server name or IP.

@catalog =N'AdventureWorks' : This is the database in which the Stored procedure exists.

OPENQUERY to Extract data from Loopback linkeserver

Select *from openquery([YourLoopbackServerName],'exec AdventureWorks.dbo.sptest')

FAQ : How can we know the progress of a maintenance command using DMVs

2008-05-01T22:59:00.000-07:00

Its very common that, when we run DBCC maintenance command we would like to know how long will it take or how much percentage the process is completed. Here we go…

Select R.Session_id,R.Command,R.Percent_complete
From
sys.dm_exec_requests R
Inner Join
Sys.dm_exec_sessions S
on S.Session_id=R.Session_ID and S.IS_User_Process=1

This process is supported for DBCC CheckDB, DBCC CheckTable, DBCC CheckFilegroup,
DBCC IndexDefrag, DBCC Shrinkfile

FAQ : How to truncate and shrink Transaction Log file in SQL Server

2008-05-01T22:17:00.001-07:00

First of all truncation of transaction log is not a recommended practice. But it is unavoidable if you have not kept proper backup policy and recovery model for your database. Its always better to know the cause and prevention for the transaction log size issue. Refer the following KB for more info

Managing the Size of the Transaction Log File
ahttp://msdn.microsoft.com/en-us/library/ms365418(SQL.100).aspx
Transaction Log Physical Architecture
http://msdn.microsoft.com/en-us/library/ms179355(SQL.100).aspx
Factors That Can Delay Log Truncation
http://msdn.microsoft.com/en-us/library/ms345414(SQL.100).aspx

Now coming to the point. If you have no space left with the drive where the Log file is kept and the size of the Transaction Log file is not manageable then its better to shrink the log.

Broadly , you have two steps here.
(a) Mark the inactive part of Trasaction log to release.
(b) Release the marked release portion of Transaction log to OS.

SQL Server 2005

-- Step 1 – Mark the inactive part of the log for release

Use YourDatabaseName
Go
Backup Log YourDatabaseName With Truncate_Only
GO

-- Step 2 - Release the marked space to OS

Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

Note : If you have single log file the above mentioned script will work. IF you have multiple log file the change the script accordingly

SQl Server 2000

-- Step 1 – Mark the inactive part of the log for release

Use YourDatabaseName
Go
Backup Log YourDatabaseName With Truncate_Only
GO

-- Step 2 - Release the marked space to OS

Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sysfiles where filename like '%.ldf'
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

Note : If you have single log file and the extension of the log file is .LDF the above mentioned script will work. IF you have multiple log file the change the script accordingly

SQL Server 2008

In SQL Server this process have been changed. In 20008, just change the recovery model to simple and then use DBCC SHrinkfile command.

select name,recovery_model_desc from sys.databases
GO
Alter database YourDatabaseName Recovery simple
GO
Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

FAQ : How triggers in SQL Server 2005 impact Tempdb

2008-05-01T22:17:00.000-07:00

In SQL Server 2005 the triggers are implemented using Version store feature. In earlier versions (SQL Server 2000/ SQL 7), the trigger's Inserted and Deleted tables data were captured by reading Transaction log. But in SQL Server 2005, the Inserted and Deleted data is stored in Tempdb as row version. So, if you have more trigger in SQL Server 2005, it may impact Tempdb performance.

FAQ : How to run DBCC DBREINDEX against all the user table in SQL Server

2008-05-01T22:16:00.000-07:00

Simple, use undocumented stored procedure sp_msforeachtable. Since its undocumented there is no gurantee that it will be available in all the future versions. But in SQL Server 2000 and 2005 it works.

Use YourdatabaseName
EXEC sp_msforeachtable 'DBCC DBREINDEX( ''?'')'

FAQ : SQL Server DBA as a career option

2008-05-01T22:14:00.000-07:00

This is a very common question in SQL Forums hence i thought to blog my view.

If you are a college passed out or a newbie in IT field, then I would suggest you to go for SQL Server MCTS (70-431) and MCITP DBA (70-443 and 70-444) certifications. Do not clear the certification for the sake of it. Learn the techniques and best practices mentioned in the syllabus. You must also go through the Virtual Courses available in Microsoft site. One great thing about Microsoft is , there are fantastic resource available in internet which comes free of cost. You just need to have MSN Live ID and login with that ID and refer the resource. Also must not forget download all the SQL OnDemand webcasts available in Microsoft site. Mind you, Webcast comes right from very experience folks who have been working for decades in this technology and also from the product teams who developed that products. Basically, reading , listening to experience people (webcast) and learn new technology will certainly give you a good launch in SQL Server. Always try to start with new product versions available (like at this point of time we have 2005 and 2008 CTP) in the market to be a leader rather than a follower.

For those who are already into IT field and wants to switch to DBA career can also more or less follow the same steps I have mentioned earlier. Key point in DBA for that matter any field, is Documentation. Try to document day to day activities and be process oriented. DBA is very risky at the same time secure job. Contradictory statements right?. Risky because you are working on production and data is nothing less than god for a DBA or you can say Data is your job. If anything goes wrong, it will may cost your job also. Secure, because the technology in Database generally not changes drastically. Consider one is working in VB 6 and one fine day he has been told to work in .Net 2.0. He will surely have tough time. But for a DBA to jump from SQL Server 2000 to 2005 or 2008 may be a smooth drive compared to other technologies.

SQL Server 2008 Virtual Lab
http://technet.microsoft.com/en-us/cc164207.aspx
SQL Server 2005 Virtual Lab
http://technet.microsoft.com/en-us/bb499681.aspx
SQL Server 2000 Virtual Lab
http://technet.microsoft.com/en-us/bb499685.aspx

Microsoft ondemand Webcasts
http://www.microsoft.com/events/webcasts/ondemand.mspx

Information about all the certification available SQL Server 2005
http://madhuottapalam.blogspot.com/2007_10_01_archive.html

How to find the time taken (total elapsed time) by the query?

2008-05-01T22:12:00.000-07:00

Though SQL Server provides SET STATISTICS TIME option to find the total elapsed time taken by a query or a sp, I generally do not use this because, for large number of statement reading the output of Statistics time will be difficult. It is better to keep your own script to get the elapsed time. Here is the script

DECLARE
@total_elapsed_time VARCHAR(100),
@start_time Datetime,
@complete_time Datetime

SELECT @start_time = GETDATE()
Print @start_time
-- here Paste the query which you want to execute
select *From sysobjects ,sys.tables – Replace this query with your query /sp

Set @complete_time=getdate()

SELECT
@total_elapsed_time = 'Total Elapsed Time (minutes:seconds) ' +
CONVERT(CHAR(3),
DATEDIFF(SS,@start_time,@complete_time)/60) +
':' +
CONVERT(CHAR(3),
DATEDIFF(SS,@start_time,@complete_time)%60)
print @total_elapsed_time

Note : If you want to know how long compilation and optimization took then use SET STATISTICS TIME

FAQ: How to rename SA Account in SQL Server 2005

2008-04-20T02:54:00.000-07:00

I never never knew that renaming SA is possible in SQL Server 2005. Recently i found a forum post and tried here we go...

ALTER LOGIN sa DISABLE;
ALTER LOGIN sa WITH NAME = [NewNameforSA];

How to export the output of a stored procedure or query to a text file using SQLCMD command in SQL Server 2005 and later versions

2008-04-15T23:02:00.000-07:00

How to pump the output of a stored procedure or query to a text file using SQLCMD command in SQL Server 2005 and later versions

(a) Running SQLCMD command in Command prompt and create a file from the output

C:\> sqlcmd -Sservername -dmaster -Usa -Psa -q"se
lect *from sys.objects" -oC:\testoutput.txt

(b) Push the output of a query to a file using XP_CMDShell and SQLCMD command

Now what happens If we want to do this from Query Analyser. Simple , run the same command using XP_CMDShell.

(a) Open a Query Analyser in Management Studio
(b) Run the following query
Exec XP_CMDShell 'sqlcmd -Sservername -dmaster -Usa -Psa -q"select *from sys.objects" -oC:\testoutput.txt'

(c) Third and simple method
We all know the two methods mentioned above. But there is another method which is very simple and flexible. SQL Server Management Studio Query Editor has support for SQLCMD scripts authoring and execution. We can switch ON or Off this feature.

(a) Open Management Studio
(b) Tools Menu -->> Select Options  Query Execution  General  check the check box at the bottom of the page on the right side of the dialog “By default, open new queries in SQLCMD mode.”
(c) Press OK and save the settings
(d) Now , open a query analyzer and type the following
:out c:\queryresults.txt
:error c:\queryerror.txt
GO
Select *from sys.objects
go
Select *from sys.objectsaaa

Note : In the above mentioned command the output of the first query will be pushed to C:\queryresults.txt and the error due to the second select statement will be pushed to c:\queryerror.txt

Disclaimer :

All the SQLCMD commands are not supported from Query Analyser. Following are the SQLCMD commands not supported in Query Analyser

(a):serverlist Lists local and SQL Servers on the network.

(b) :reset Discards the statement cache.

(c) :perftrace Redirects timing output to a file, stderr, or stdout. This is not supported because Query Editor doesn't have this concept.

(d) :listvar Lists the set SQLCMD scripting variables.

(e) :ed Edits the current or last executed statement cache.

(f) :help Shows the list of supported commands.

if you use any command in Query Analyser, Query Editor issues a non-fatal warning into the Messages tab during execution and continues on.

Summary :

All the three methods are equally useful. Many folks may not have observed the third method that is why i thought to blog this..

FAQ : How to clear SQL Server cache / memory

2008-04-01T23:00:00.000-07:00

Warning : Not to be used in production env.

In Development or tsesting env its very common that during performance tuning we do clear cache to get correct picture. It may also required that only a particular db related cache should be clear. here wo go...

(a) Clear entire procedure and databuffer cache

Checkpoint -- Write dirty pages to disk
DBCC FreeProcCache -- Clear entire proc cache
DBCC DropCleanBuffers -- Clear entire data cache

(b) Clear only a particular db procedure cache using undocumented DBCC command

Declare @DBID int
Select @DBID =db_id(‘YourDBname’)
DBCC FLUSHPROCINDB(@DBID) – Undocumented dbcc command to clear only a db proc cache

FAQ : How to move a physical file (MDF or LDF) from one location to another in SQL Server 2005

2008-04-01T22:58:00.000-07:00

In SQL Server 2000, if you want to move a physical file of a database from one location to another , you have to detach and attach the database. In SQL Server 2005, this process has been made very simple, you need to take the database offline, alter the file path with the new one using Alter Database command and copy the database file to new location manually and finally take the database online. Simple

--Step 1 : Create Database
Create Database TestMoveFile and check the database file location
GO
Select *From Sys.master_files where database_id=db_id('TestmoveFile')
GO
--Step 2 : Alter Database and Set the db to offline
Alter Database TestMoveFile Set Offline
GO
-- Step 3 : Move the physical file to new location
--Move the file to new location using dos command or Windows GUI

--Step 4 : Alter the database file path using Alter Database command
Alter Database TestMoveFile Modify File(Name='TestMoveFile',FileName='c:\TestmoveFile.mdf')
Go

-- Step 5 : Set the database Online and check the file path
Alter database TestMoveFile Set Online
GO
Select *From Sys.master_files where database_id=db_id('TestmoveFile')

Go
Drop database TestMoveFile

FAQ : What is the difference between DELETE TABLE and TRUNCATE TABLE commands

2008-04-01T22:57:00.001-07:00

TRUNCATE

• Less Transaction Log entry because TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log and hence TRUNCATE is fast
• TRUNCATE apply Fewer table locks
• TRUNCATE is DDL Statement
• TRUNCATE Release the tables spaces to System
• TRUNCATE Can not have WHERE Conditions
• TRUNCATE does not fire trigger
• TRUNCATE reset the identity to 0 (if table have any)
• TRUNCATE Can not be used against the tables involved in TRUNCATE transactional replication or merge replication.
• TRUNCATE Can not be used against the table used in Indexed view
• TRUNCATE can not be used against the table that Are referenced by a FOREIGN KEY constraint.
• TRUNCATE commands are not tracked by DDL trigger

Note : TRUNCATE can be rollbacked. I have seen many place where its is mentioned that it can not

DELETE

• DELETE FROM TABLE command logs each records in transaction log , hence DELETE is slow.
• DELETE apply more locks to the table
• DELETE is a DML command
• DELETE remove the records but will not release the space to the system
• DELETE can have WHERE conditions
• DELETE Fires TRIGGER
• DELETE do not RESET IDENTITY
• DELETE Can be used against table used transactional replication or merge replication
• DELETE Can be used in tables reference by a Foregin Key and tables involved in Indexed view

FAQ : Index Scan Vs Seek in SQL Server

2008-04-01T22:57:00.000-07:00

There are five logical/physical operators in SQL Server related to Index scan ,Index Seek and Table scan.

(a) Table Scan
(b) Clustered Index Scan
(c) Clustered Index Seek
(d) Index Scan (Non- Clustered index Seek)
(e) Index Seek (Non- Clustered index Seek)

Table Scan
Table Scan retrieves all rows from the table (if you have no WHERE Conditions). Basically, before returning the rows, it traverse through all data pages related to the table. If you have where condition, though it travel through all the pages only those rows are returned which satisfy the conditions. When you do not have Clustered index on the table it does a table scan. In other words, both Clustered Index Scan (clustered index is nothing but the data itself) and Table Scan are same because in both method system traverse through all the data pages. Generally, you should avoid table scan.

Clustered Index Scan
Clustered Index Scan is nothing but horizontally traversing though the clustered index data pages. Clustered Index Scan return all the rows from the clustered index (Clustered index is nothing but Data). If you have where condition , only the satisfying rows are returned, but system traverse through all the data pages of the clustered index. Both Table scan and Clustered Index Scan are generally considered to be bad. But at times like if the table is small contains only few rows table or clustered index scan may be good also

Clustered Index Seek
Clustered Index Seek traverse vertically right down to the Data page where the requested data is stored. Basically any seek is vertically traversing though the B-Tree structure (as we all know the index is stored in B-tree structure in sql server). System does a Seek when it find a useful index and generally its done for highly selective query.

Index Scan or Non-Clustered Index Scan
As already said, Scan is horizontal traversing of B-Tree data pages. But in this case it horizontally traverse though the Non-Clustered index available. Its not same as Clustered index scan or Table Scan. In SQL Server , while reading execution plan you can find only Index Scan not Non-Clustered Index Scan. But you must read Index Scan as Non-Clustered Index Scan.

Index Seek or Non-Clustered Index Seek
As already mentioned Seek is Vertical traversing of B-Tree to the data page. But in Index seek it vertical traversing of Non-Clustered Index. Generally, its considered as the best option for high selective query.

FAQ : How to reset SA Password when there is no Buildin\Administrator role in SQL Server 2005

2008-04-01T07:24:00.000-07:00

Check Raul Garcia's blog Entry

FAQ : Query to get all the Dynamic Management Views/Functions

2008-02-26T06:24:00.000-08:00

Select * From Sys.All_Objects
Where Type IN ('V','TF','IF')
and Name Like '%dm_%' And
Schema_ID=4

Webcasts in Feb 2008

2008-02-07T23:04:00.000-08:00

Check out the webcasts schedule in the month of Feb.
http://www.microsoft.com/india/webcasts/

DOs and Don’t when Running Profiler

2008-02-02T23:05:00.000-08:00

We run profiler often for many reasons. We generally, do this on Test/UAT environment. Running profiler on production is not recommended but not completely avoidable always. You may need to run profiler/trace even on production server. But to get better result with less overload, we may need to follow some guideline. I think following are the general guideline which we can follow when we run profiler on any environment.

(a) Do not save the trace result into a table on the same database
(b) Run the trace with proper filter. Monitor only the required events otherwise the trace may overload the system.
(c) Run the trace from any other machines other than which you are monitoring. You can connect from Test server or any other workstation which acts as a monitor to the actual server.
(d) If you are running the trace for workload then run the Trace for the whole business day when activities are high to get exact picture.
(e) Create a template with the events you need and save it to re-use.
(f) You may save the result to a table so that you can run sql query against it.

Note : It is always recommended to run T-SQL Storedprocedure that define the trace rather than running Profiler GUI.

A dream come true... you can call me MVP

2008-01-01T23:09:00.000-08:00

I have no words... I have been honoured by Microsoft and I am a MVP now... From a Soldier to a MVP, probably first MVP from Indian Defence Services , its been a great new year for me

SQL Server 2008 Virtual Lab

2007-12-09T23:14:00.000-08:00

TechNet Virtual Lab: Administering Servers by Using Declarative Management Framework (DMF) Policies
TechNet Virtual Lab: Authoring Reports Using SQL Server 2008 Reporting Services
TechNet Virtual Lab: Implementing Change Data Capture (CDC) in SQL Server 2008
TechNet Virtual Lab: Learn How to Build Occasionally Connected Applications with SQL Server 2008
TechNet Virtual Lab: What's new in SQL Server 2008 for Database Administrators
TechNet Virtual Lab: Working with the new DATE data type in SQL Server 2008

How to Install SQL Server Client Tools from Command Line

2007-10-20T23:18:00.000-07:00

Command line installation of Client Tools
driveletter:\Servers\Setup>start /Wait setup.exe ADDLOCAL=Client_Components,Connectivity,SQL_Tools90,SQL_BooksOnline

FAQ: SQL Server Connectivity Troubleshooting

2007-10-20T23:17:00.000-07:00

Connectivity issues are very common in SQL Server 2005 since there are few settings to be done at server level and OS level to enable the server for remote connection. There are many documents available from Microsoft Protocol team gives step by step troubleshooting process. I always refer those Bolgs whenever I see such issues in forums. Then I thought to have a consolidated list where all the links are available

(a) Check whether the server is configured to accept remote connections. Use SAC to enable server for remote connection
.
(b) Ensure that the SQL Browser service is started (SQL Browser equivalent in 2000 is SQL Server Resolution Protocol (SSRP) )

(c) Determine whether clients are specifying the correct port (for using fixed ports with named instances) in the server alias or connection string.

(d) Check whether the client’s network protocols are enabled and configured to correctly handshake with those of the server.

(e) Be sure you have permission to connect on the server’s endpoints.

(f) When using encryption, be sure the server and client certificates match (that is, check their Common Name (CN) and any other relevant attributes) and are installed and configured correctly on both sides.

(g) Make certain that your firewalls are configured to permit the required network traffic.

(h) Check to see whether your users have permission to log in to the server and access the specified database.

(i) Make sure the provider, driver, DSN, server alias, or other connection mechanism is still valid and hasn’t been altered or removed from the system.

Refer following KBs and Blogs for more info :-
http://support.microsoft.com/kb/914277
http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

How to Detect Deadlocks from SQL Profiler

2007-10-20T22:43:00.000-07:00

Deadloack detection is a very common requirement in Development (even in Production) environment. There are couple of method to detect Dead Locks. In SQL Server 2000, trace flag 1204 can be set to capture the processes involved in the deadlock. The output is text based but provides valuable information about the types of locks and the statements that were executing at the time of the deadlock. In addition to this approach, SQL Server 2005 offers the ability to capture detailed deadlock information via the SQL Server Profiler. The process of Dead Lock detection in SQL Server 2005 is as follows

(a) Create a new trace, Select a Blank template; this leaves the selection of all the events, data columns, and filters to you.

(b) Add the Locks:Deadlock graph event to the trace from the Locks category. An additional tab appears on the Trace Properties window, named Event Extraction Settings.

(c) Click the Save Deadlock XML Events Separately check box. This causes the deadlock information to be written to a separate file. You could also export the results after the trace has been run by using the File, Export option.

SQL Server 2005 Pricing and Licensing

2007-10-19T22:51:00.000-07:00

Licensing is one area where I always struggle to give my suggestion because it’s a legal issue and the suggestion should politically correct. Check this document right from Microsoft...

http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc

Also Check this thread

Developer and Express edition Licensing Model always need little clarificaiton

Developer Edition Licensing

The Developer Edition of SQL Server 2005 is available for a fixed price of $49.95(subjected to change). The Developer Edition is licensed per developer and must be used for designing, developing, and testing purposes only.

Express Edition Licensing

The Express Edition of SQL Server 2005 is available via free download Microsoft site. Developers can redistribute it with their applications at no cost by simply registering for redistribution rights with Microsoft. The Express Edition does not require a CAL when it is used on a standalone basis. If it connects to a SQL Server instance running Enterprise Edition, Standard Edition, or Workgroup Edition, a separate user or device CAL is required unless the SQL Server instance it connects to is licensed under the per-processor model.

Deprecated/Removed Command Line Utilities of SQL Server 2005

2007-10-19T22:50:00.000-07:00

Tool Purpose Status

isql Used to execute SQL/SP/Script from Command Prompt Removed

rebuildm This utility was used was used to rebuild the master database. Removed

regrebld This utility was used to back up and restore the Removed
SQL Server Registry entries.

sqlmaint This utility is used to execute maintenance plans that were Deprecated
created in previous versions of SQL Server.

readpipe This utility is used to verify a client’s connectivity to SQL Server Deprecated
through named pipes

Osql Used to execute SQL/SP/Script from Command Prompt Deprecated

makepipe This utility is used to verify a client’s connectivity Deprecated
to SQL Server through named pipes

SQL Server Express 4GB Size Limitation and the Error

2007-10-02T23:15:00.000-07:00

I have seen lot of queries regarding the size limitation in SQL Server Express. We all know there is limitaiton and its 4GB. But is it exactly 4GB or when the engine stops you pumping more data into the database? And whats the error you gets? These are the few FAQ on this subject. Then I thought I should try myself and document it.

SQL Server Version

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

(a) When We try to restore a database of size more than 4 GB

RESTORE FILELISTONLY FROM DISK='D:\GP.bak'
RESTORE DATABASE GPtogpx
FROM DISK='D:\GP.bak'
WITH MOVE'Gp' TO 'd:\GP.mdf',
MOVE 'Gp_log' TO 'd:\GP_log.ldf'

Error
Msg 1827, Level 16, State 4, Line 1
CREATE DATABASE or ALTER DATABASE failed because the
resulting cumulative database size would exceed your licensed
limit of 4096 MB per database. Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

(b) When a database crosses 4 GB Limit

sp_spaceused Result

database_name database_size unallocated space
-------------------- ------------------------------------
ABC 4216.75 MB 77.16 MB

reserved data index_size unused
------------------ ------------------ ------------------
4115296 KB 4098792 KB 11824 KB 4680 KB

Error

Msg 1101, Level 17, State 12, Line 3
Could not allocate a new page for database 'GPtogpx' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

So, the bottom line of this study is, the limitation is just not in the paper and it really going to hit you sooner or later if the database going to grow beyond 4GB. So go for Licensed Edition

How to upgrade SQL server 2005

2007-09-30T22:53:00.000-07:00

use SKUUPGRADE=1 swtich from the command prompt with setup.

Eg :
start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine SKUUPGRADE=1 /qb

Refer
http://msdn2.microsoft.com/en-us/library/ms144259.aspx#skuupgrade

Evaluation to EE Upgrade
Refer :
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2111140&SiteID=1

Troubleshooting Performance Problems in SQL Server 2005

2007-09-22T22:58:00.000-07:00

Good resource on Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

Msg 3154, Level 16, State 4, Server YourDatabaseName, Line 1

2007-08-29T04:42:00.000-07:00

This error tells you that the database being restored is already existing in the server. Use WITH REPLACE or Change the database Name which you are restoring.

RESTORE DATABASE YOURDATABASENAME FROM DISK='D:\YOURDB.BAK' WITH REPLACE

FAQ : SQL Server Certifications

2007-08-22T23:15:00.000-07:00

The New Generation of Microsoft Certifications http://www.microsoft.com/learning/mcp/newgen/default.asp

Microsoft Next Generation of Certifications: Frequently Asked Questions http://www.microsoft.com/learning/mcp/newgen/faq/

Microsoft Certifications for IT Professionals

http://www.microsoft.com/learning/mcp/mcitp

MCITP Database Developer

SQL Server MCTS (70-431)
70-441 (Designing DB Solution)
70-442 (Designing and Optimizing Data Access

MCITP DB Administrator

70-431 (MCTS)
70-443 Designing a DB Server Infrastructure
70-444 Optimizing and Maintaining a Database Administration Solution

Or If you are upgrading from MCDBA

70-431(MCTS )
70447 ( Database Administration by Using Microsoft SQL Server 2005 )

MCITP: Business Intelligence Developer

MCTS: SQL Server 2005 (70-431) +
70-445: PRO: Designing Business Intelligence Solutions by Using Microsoft SQL Server 2005 Analysis Services +
70-446: PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005

SQL Server 2005 Service Pack and hot fixes

2007-08-10T23:11:00.000-07:00

single window for all Service packs and other hot fixes of SQL Server 2005.

http://support.microsoft.com/kb/913089/

Next version of BPA forSQL Server 2005

2007-07-02T23:16:00.000-07:00

The new version of the Best Practices Analyzer from MSDN Downloads.

Bookmark Lookup Logical Operation removed from SQL Server 2005

2007-07-02T23:14:00.000-07:00

The Bookmark Lookup logical operation was removed in SQL Server 2005 and replaced with clustered index seek and RID lookup. Previous versions of SQL Server displayed the Bookmark Lookup icon but actually performed the clustered index seek and RID lookup.

patterns & practices Performance Testing Guidance project Community site.

2007-07-02T23:12:00.000-07:00

Perfect resource for Performance Testing. Worth to read and implement. Atleast gothrough once ,stuffed with lot of information.

http://www.codeplex.com/PerfTesting

Few SQL OS DMVs Query- Courtesy to Slava

2007-07-02T23:11:00.000-07:00

Taken from Slava Oks's Blog.
http://blogs.msdn.com/slavao/archive/2006/09/28/776437.aspx

sys.dm_os_schedulers
Q. Do I need to by more CPUs?
In order to answer this question you have to find out if your load is really CPU bounded. Your load is really CPU bounded if a number of runnable tasks per each scheduler always greater than 1 and all of your queries have correct plan. The latter statement is very important, your load can be CPU bounded due to the fact that somehow optimizer generated bad plan – it can happen if your statistics out of date or you tried to perform handcrafted optimization. In this case you don’t want to run to Circuit City to buy more CPUs right a way – you want to fix the plan. Here is the query to find out average length of a runable queue on the system:

select

AVG (runnable_tasks_count)

from

sys.dm_os_schedulers

where

status = 'VISIBLE ONLINE'

Buying more CPUs has also to do with capacity planning. You have to be very careful when performing capacity planning on hardware with HT enabled – remember you don’t have extra physical CPUs. Keep in mind that if your load runs at 60% CPU utilization - it doesn’t mean that you have 40% of extra CPU capacity. You will be very surprise how fast CPU load will jump from 60% to 80% and then even faster to 100% once you apply more and more load.

Q. What is affinity of my schedulers to CPUs?

select

scheduler_id,

CAST (cpu_id as varbinary) AS scheduler_affinity_mask

from

sys.dm_os_schedulers

Does my machine have either hard or soft NUMA configuration enabled?

select

CASE count( DISTINCT parent_node_id)

WHEN 1 THEN 'NUMA disabled'

ELSE 'NUMA enabled'

END

from

sys.dm_os_schedulers

where parent_node_id <> 32

Q. Should I configure SQL Server to use more threads – sp_configure ‘max server threads’?
You can answer this question by looking at the work queue length for each scheduler. If on average such value is above 1 then you might benefit from adding more threads to the system but only if

A. Your load currently is not CPU bounded (See info above on how to find out if your load is CPU bound)

B. Your load currently doesn’t experience any other heavy waits (If you add more threads in this case they will just end up waiting as everyone else)

select

AVG (work_queue_count)

from

sys.dm_os_schedulers

where

status = 'VISIBLE ONLINE'

Q: Is my system I/O bound?
You can answer this question by monitoring length of I/O queues.

select

pending_disk_io_count

from

sys.dm_os_schedulers

If over time they keep on growing or you are seeing periodic jumps or numbers stay relatively high most likely your system is I/O bound. In order to identify the cause you will have to dive further.

Connecting to SQL Server 2005 on Vista and Longhorn

2007-07-02T22:54:00.000-07:00

I have seen lots of post in forums connecting to sql server 2005 on Vista. Check out this post by Protocol Team. http://blogs.msdn.com/sql_protocols/archive/2007/06/18/connecting-to-sql-server-2005-on-vista-and-longhorn.aspx#comments

Default Trace in SQL Server 2005

2007-07-02T22:52:00.000-07:00

When i saw *SQL Trace ID 1 was started by login "sa" * in my Error Log I was little surprised. Since i have never started any trace I was wondering what is this all about. Then i came to know that this is the default trace started which is a new feature introduced in SQL Server 2005. Then i was curious about how can i see the contents. here we go...

To See All the Trace and its location :
select * from ::fn_trace_getinfo(default)

To get the File Location
select * from ::fn_trace_getinfo(default) where Property=2

And copy paste the Value column text.

To Get the Trace Data

SELECT * FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default) where starttime>'2007-10-15'

Webcast on SQL Server 2005 by Vinod

2007-06-20T23:44:00.000-07:00

Webcast on SQL Server 2005 by Vinod

Capacity Planning Considerations – SQL Server 2005 - June 20th (2-3 PM IST)

Understanding IO and Storage system with SQL Server 2005 - June 21st (2-3 PM IST)

Performance Tuning and Troubleshooting – SQL Server 2005 - June 22th (2-3 PM IST)

SQL Server 2008 (Katmai) CTP Released

2007-06-17T23:45:00.000-07:00

Great news... Microsoft has released CTP version of SQL Server 2008.

https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

SQL Server 2005 Performance Dashboard Reports

2007-05-26T23:47:00.000-07:00

This is a wonderful resource to have with SSMS. With SP2 giving you an window to build custom reports use these for your help to diagonize problems. Download from here
http://www.microsoft.com/downloads/details.aspx?FamilyId=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en