SQL-Server Blog of Ritesh Shah --Fight the fear of SQL with SQLHub.com

Create windows login and user in all databases with dataReader and dataWriter role in SQL Server 2008

2010-03-16T16:15:00.000+05:30

One of my recent projects was dealing in many databases in one SQL Server instance. I required one procedure which can create one Windows authenticated login and associated user in all databases. After creating users in databases, I needed to assign datareader and datawriter roles to those users. There is not inbuilt functionality in any of the SQL Server version so thought to create one stored procedure for my need. I have used it in my project and now want to share with all of you.

CREATE PROC CreateWindowsLoginAndUser(@FullLoginName sysname, @ActionName sysname)

begin

declare @DataBaseName sysname

declare @SQL varchar(255)

declare @Host sysname

declare @Login sysname

set @Host = LEFT(@FullLoginName, charindex('\',@FullLoginName)-1)

set @Login = Right(@FullLoginName, LEN(@FullLoginName)-charindex('\',@FullLoginName))

set @SQL = ''

if (@ActionName = 'CREATE')

set @SQL = 'USE [master] CREATE LOGIN ['+@FullLoginName+'] from windows'

else

set @SQL = 'USE [master] DROP LOGIN ['+@FullLoginName+']'

exec (@SQL)

declare dbname cursor for

select name from sysdatabases

where name not in ('master','model','msdb','tempdb') and

DATABASEPROPERTYEX(name,'IsInStandBy') = 0 and

DATABASEPROPERTYEX(name,'Status') not in ('OFFLINE','RESTORING','RECOVERING','SUSPECT','EMERGENCY') and

DATABASEPROPERTYEX(name,'Updateability') not in ('READ_ONLY') and

DATABASEPROPERTYEX(name,'UserAccess') = 'MULTI_USER'

order by name

open dbname

fetch next from dbname into @DataBaseName

while (@@fetch_status <> -1)

begin

if (@@fetch_status <> -2)

begin

set @SQL = ''

if (@ActionName = 'CREATE')

set @SQL = 'USE ['+@DataBaseName+'] CREATE USER '+@Login+' FOR LOGIN ['+@FullLoginName+']'

else

set @SQL = 'USE ['+@DataBaseName+'] DROP USER '+@Login+''

exec (@SQL)

set @SQL = ''

if (@ActionName = 'CREATE')

set @SQL = 'USE ['+@DataBaseName+'] EXEC sp_addrolemember ''db_datareader'','''+@Login+''''

exec (@SQL)

set @SQL = ''

if (@ActionName = 'CREATE')

set @SQL = 'USE ['+@DataBaseName+'] EXEC sp_addrolemember ''db_datawriter'','''+@Login+''''

exec (@SQL)

end

fetch next from dbname into @DataBaseName

end

close dbname

deallocate dbname

end

Once you are done with creating script, you can execute it by following commands.

--to create user, use following TSQL

exec CreateWindowsLoginAndUser 'DomainName\WindowsLoginName','CREATE'

--to drop user, use following TSQL

exec CreateWindowsLoginAndUser ' DomainName\WindowsLoginName','DROP'

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Restart SQL Server and SQL Server Agent from Command Prompt.

2010-02-22T16:38:00.001+05:30

Many times I need to restart SQL Server service and SQL Server Agent service in my testing server.. Generally people used to go at Control Panel->Administrative Tools->Services, find SQL Server service or/and SQL Server agent service and start, restart or stop it. This is general practice but I would like to do this from command prompt itself.

Syntax is:

Net Stop mssql$YourSQLServerInstanceName

Net Start mssql$YourSQLServerInstanceName

Above command is used to start and stop SQL Server Service. Now, here is the command for SQL Server agent.

Net Stop sqlagent$YourSQLServerInstanceName

Net Start sqlagent $YourSQLServerInstanceName

My instance name of SQL Server 2008 is “SQL2k8” so my command would be

Net Stop mssql$SQL2K8

Net Start mssql$SQL2K8

Net Stop sqlagent$SQL2K8

Net Start sqlagent $SQL2K8

Indeed I have one batch file which do this stuff for me. Just go to command prompt and run .BAT file is really easy than going to Control Panel->Administrative Tools->Services, find services and restart it.

Look at the screen below for more details.

Find out column name, data type and maximum number of character in that column in SQL Server 2008

2010-02-20T07:44:00.002+05:30

Yesterday I got one question about how to find column name, data_type and maximum number of character used in that column if it is varchar and if it is numeric than maximum number in that column. Basically you can find column_name and Data_type from the Information_Schema.Columns but it won’t return length of real data inserted into table so I tried to achieve it logically. Look at the script.

--using AdventureWorks database for demo

--if you don't have AdventureWorks db

--you can use your own

use adventureworks

declare @sql varchar(max), @table sysname

--going to show you demo on Person.Contact table

--in adventureworks database,

--you can change your own table name

select @table='Contact', @sql=''

select @sql=

case when character_maximum_length is null then

@sql+'select

'''+column_name+''' as column_name,

'''+data_type+''' as data_type,

max('+column_name+') as column_length

from '+Table_Schema + '.'+ Table_Name+' union all '

else

@sql+'select

'''+column_name+''' as column_name,

'''+data_type+''' as data_type,

max(len('+column_name+')) as column_length

from '+Table_Schema + '.'+ Table_Name+' union all '

end

from information_schema.columns

where table_name=''+@table+'' and data_type not in ('xml','bit','datetime','uniqueidentifier')

set @sql=left(@sql,len(@sql)-9)

--print @sql

exec(@sql)

Row count in big tables with DMV DM_DB_Partition_Stats and SP_SpaceUsed

2010-02-19T12:39:00.001+05:30

I have seen people counting row by using COUNT or may be COUNT_BIG, if it is very big table, aggregate function. When you go for COUNT function, you will scan full table and it may take very big amount of time, especially when you are having VERY BIG table with few millions of rows. So .NET developer also uses same COUNT aggregate function in .NET front end application which is really very time consuming.

I just see one of my .NET developers was doing this for the table, having 150 million rows so I corrected him with following two ways and thought to share it with my blog readers.

Rather than scanning full table I always prefer to use either DMV or SP_SpaceUsed stored procedure. In my early days few years back, I was using SP_MSTABLESPACE but in SQL Server 2005+, SP_MSTABLESPACE is just for backward compatibility so I do not recommend using SP_MSTABLESPACE and promoting dm_db_partition_stats and SP_Spaceused.

Let us see TSQL for each.

--Method1

SELECT

SUM(row_count) as TotRows

FROM

sys.dm_db_partition_stats

WHERE

object_name(object_id) = 'YourTableName'

AND (Index_Id = 1)

--Method2

SP_Spaceused 'YourTableName'

--Method3

SP_MSTABLESPACE 'YourTableName'

Read default SQL Server port and change it in SQL Server 2008

2010-02-18T11:35:00.000+05:30

Listening port plays really very vital role in any version of SQL Server. There are some port number like 1433, 1533, 1434 etc. are really widely known and famous so whenever hackers try to scan port of your SQL Server, they first try to scan these ports as those are very famous.

It is really good practice to change default SQL Server port to something else from 1433. After reading this short message, if you are not experience DBA and SQL Server developer, you might think, how can read the port of SQL Server? It is really very easy with few different ways.

Read/Change Default SQL Server Port:

Method 1:

1.)
Go to Start->All Programs->Microsoft SQL Server 2008->Configuration Tools->SQL Server Configuration Manager

2.) From the configuration manager, expand “SQL Server Network Configuration” from left hand side tree.

3.) Now click on “Protocols for YourSQL ServerInstance”

4.) From the right hand side, you can find “TCP/IP”, right click on it and click on property

5.) From the dialog box, click on the “IP Addresses” tab and look at port no in “TCP Port” property under “IP ALL”.

6.) If you want to change your port, change it in “TCP Port” and Restart your SQL Server instance

Method 2:

Read the port from your registry with below code snippet.

DECLARE @tcp_port nvarchar(10)

EXEC xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

--SQL2K8 is my instanace name of SQL Server

--may be different in your case

@key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQLServer\SUPERSOCKETNETLIB\TCP\IPAll',

@value_name = 'TcpPort',

@value = @tcp_port OUTPUT

select @tcp_port

If you wish to change port # from registry, open “regedit” from command prompt, go to the path given in above SP. Path is already provided in @key argument. Double click on “TcpPort” key and change the value right from there.

I have used XP_RegRead stored procedure to read registry. Sometime back I have used other system stored procedure to read registry and find default installation path of your sql server. The name of that stored procedure is XP_Instance_RegRead.

Read registry from SQL Server with xp_instance_regread and find the path of your SQL Server installation

2010-02-15T10:59:00.001+05:30

Some machines are really mess up with couple of successful and unsuccessful installation/un-installation of SQL Server and the result is you can find program files/Microsoft SQL Server folder in couple of drive. If you want to find current running installation of SQL Server than there are few different ways. Out of which I am going to show you couple of different approaches.

Method 1:

You can query sysaltfiles system catalog resides in MASTER database. You can see the path of system databases especially MASTER database. Most probably master database would be in the same folder of SQL Server.

Here is the TSQL:

select * from master..sysaltfiles

Method 2:

There is a system stored procedure in Master database, named “xp_instance_regread” which can help you to read registry. There is a specific location in system registry which will give you path of the installation directory. This method is more appropriate than previous one. Here the code snippet of the same.

declare @regreader int, @directory nvarchar(4000)

exec @regreader = master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\Setup',

N'SQLPath',

@directory output, 'no_output'

select @directory AS InstallationDirectory

If the user you are using while running above snippet doesn’t have permission to read registry, you might greeted with permission error.

Encryption - Decryption in SQL Server 2008 and backup database with encrypted data and restore it somewhere else

2010-02-10T12:40:00.003+05:30

Long way back, we had to take help from third party tools in order to encrypt and decrypt data. Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, we have improved and in-built facilities for Encryption and Decryption within SQL Server.

To encrypt data and decrypt it, it is really a very crucial task as one mistake and your data go out of your reach. It can become more difficult when you will encrypt some data in one database in one server and try to restore that database into different server.

In this Article, I am going to show you the script which can encrypt data in one database on one server, take its backup, and restore that encrypted database anywhere else and you will get your data with 100% security and no data risk.

We will now create one database which is going to be used in throughout this example.

Read full article here.

INDEX and SELECT * query in SQL Server 2008

2010-02-04T15:44:00.000+05:30

“SELECT *” in T-SQL select query affect performance of the query adversely, there are many reasons about why we shouldn’t use “SELECT *” query but today I am going to share one of my observation about how index works with “SELECT *” query. In short, if you really don’t want ALL FIELDS in result sets, don’t use “*” as it increase IO load, network traffic. One more reason is, it is not readable that which columns you are going to use so better to use Field Name rather “*”.

There are two types of main index 1.) Clustered Index 2.) non-Clustered Index. What are these indexes and how internally it works, is something out of the scope of this article but I would like to clear one point that there are two possibilities about index when you run SELECT query. Either it will “scan” index or it will “seek” index. Generally for big tables “SEEK” is good than “SCAN” as “SCAN” touches every row of the table. For more details on SEEK and SCAN, please read my friend and guru Pinal Dave’s article.

Actually I and my friend Bihag were discussing about Indexes, SEEK and SCAN. While our discussion, I felt to write this article.

Let us look small example to see in which situation we will get SCAN and in which situation we will get SEEK. Remember, we should try to achieve SEEK rather SCAN.

--create table for demo

create table ClusteredIndexTest

(

ID INT identity(1,1),

FirstName varchar(10),

LastName varchar(10)

)

--create clustered index on our demo table

CREATE CLUSTERED INDEX idx1 ON ClusteredIndexTest(ID)

--create non clustered index on demo table

CREATE NONCLUSTERED INDEX idx2 ON ClusteredIndexTest(LastName)

--insert few records.

INSERT INTO ClusteredIndexTest

SELECT 'Ritesh','shah' union all

SELECT 'Rajan','Jain'

--now select below given query, press CTRL+M to include actual Execution Plan with query results

--and hit F5 to run query

Select * From ClusteredIndexTest --(Always Clustered Index Scan)

Select * From ClusteredIndexTest Where ID=1 --(Clustered Index Seek)

Select * From ClusteredIndexTest Where LastName='shah' --(ALWAYS CLUSTERED INDEX SCAN DUE TO SELECT *)

Select LastName from ClusteredIndexTest Where LastName='shah' --(Always Index Seek on LastName Key)

SQL DMO and SQL SMO with .NET application

2010-02-03T11:42:00.002+05:30

Recently I was assigned one project for optimization which has been developed in windows .net 2.0 environments with C# language. Along with optimization I have to move database from SQL Server 2005 to SQL Server 2008.

When I configured that application in my server, I used to get following error:

System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {10020100-E260-11CF-AE68-00AA004A34D5} failed due to the following error: 80040154

That application was working fine on my client’s server and showing me error in my server so I looked at the code in debug mode and found when the application is trying to open connection form SQL DMO, connect don’t get open and showing the above error.

When I observed that I brush up my memory and remember that SQL DMO is older version, used to use with SQL Server 2000 and it was there in SQL Server 2005 too for backward compatibility. The server I was using to test that application had just SQL Server 2008 and this issue start raising.

Now I have two choices.

1.) Change complete code to SQL SMO from SQL DMO as SQL DMO is older version and deprecated in newer SQL Server

2.) Use backward compatibility tool which used to ship with SQL Server 2005

First choice is fair but since I am testing I can’t go for that as of now so I choose second way and downloaded component from following link and error got disappeared.

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

There are so many different downloaded on above link, you have to go for following section:

Microsoft SQL Server 2005 Backward Compatibility Components

CHARINDEX and PATINDEX with STUFF function for string manipulation in SQL Server 2008

2010-01-29T11:45:00.000+05:30

Whenever it comes to the string manipulation, people stops thinking about SQL Server and start thinking about how we can do it from front-end programming. This is not always true. I agree that SQL Server is not for string manipulation but in many scenarios, it becomes easy to manipulate string in SQL Server itself rather than in front-end programming.

Let us see one example about this.

Suppose we are storing pointer to image in our database and suddenly folder structure get changed and we have to update path in SQL Server. In this situation two very popular string manipulation functions CHARINDEX and PATINDEX comes to our help. There are several ways in SQL Server with which we can manipulate the string. I am going to show you two different ways today.

--create table and insert some dummy data

create table StringManipulate

(

ImagePath varchar(100)

)

INSERT INTO StringManipulate

SELECT '~/ROOT/IMAGES/11/ONE.jpg' UNION ALL

SELECT '~/ROOT/IMAGES/1/TWO.jpg' UNION ALL

SELECT '~/ROOT/IMAGES/111/THREE.jpg' UNION ALL

SELECT '~/ROOT/IMAGES/111/FOUR.jpg' UNION ALL

SELECT '~/ROOT/IMAGES/111/FIVE.jpg'

Now, let us think that we are going to remove subfolders of IMAGES and going to put all the images directly into the IMAGES folders so we have to update path accordingly in SQL Server also. We have sub folder from 1 to 999 in our IMAGES folder.

Before we make actual update, let us first SELECT it and will see how it will look.

--First and efficient way with PATINDEX to find a pattern

--and STUFF to manipulate string

SELECT

CASE

WHEN PATINDEX('%/[0-9]/%', ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX('%[0-9]/%', ImagePath), 2, '')

WHEN PATINDEX('%/[0-9][0-9]/%', ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX('%[0-9][0-9]/%', ImagePath), 3, '')

WHEN PATINDEX('%/[0-9][0-9][0-9]/%', ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX('%[0-9][0-9][0-9]/%', ImagePath), 4, '')

END

FROM StringManipulate

WHERE PATINDEX('%/[0-9]/%', ImagePath) > 0

OR PATINDEX('%/[0-9][0-9]/%', ImagePath) > 0

OR PATINDEX('%/[0-9][0-9][0-9]/%', ImagePath) > 0

--second way with CHARINDEX and very popular string manipulation functions

--LEFT and RIGHT

select left(ImagePath,14)+right(ImagePath,len(ImagePath)-charindex('/',ImagePath,15))

from StringManipulate

Once we are confident about our manipulation in SELECT statement, we will move to update our records in table. We will use same above SELECT query’s logic in UPDATE statement to actually manipulate string.

--update with first method

update StringManipulate set ImagePath=CASE

WHEN PATINDEX('%/[0-9]/%', ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX('%[0-9]/%', ImagePath), 2, '')

WHEN PATINDEX('%/[0-9][0-9]/%', ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX('%[0-9][0-9]/%', ImagePath), 3, '')

WHEN PATINDEX('%/[0-9][0-9][0-9]/%', ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX('%[0-9][0-9][0-9]/%', ImagePath), 4, '')

END

WHERE PATINDEX('%/[0-9]/%', ImagePath) > 0

OR PATINDEX('%/[0-9][0-9]/%', ImagePath) > 0

OR PATINDEX('%/[0-9][0-9][0-9]/%', ImagePath) > 0

--let us check whether our UPDATE has properly been made or not.

SELECT * FROM StringManipulate

--update with second method.

--if you have already run first update given above, you have to do this UPDATE in another table with same data

--if you will run this UPDATE statement with modfied data, it won't give you expected results.

UPDATE StringManipulate SET ImagePath=left(ImagePath,14)+right(ImagePath,len(ImagePath)-charindex('/',ImagePath,15))

SELECT * FROM StringManipulate

Isn’t it easy to do in SQL Server rather than C#?

BTW, I had written some more articles about string handling and CHARINDEX. If you wish, have a look.

Find and correct patent in string

SPLIT: user defined function

User defined function for Proper Case or Title Case

Get depended object from SP_Depends and Information_Schema.routines in SQL Server 2008

2010-01-28T11:35:00.000+05:30

SP_Depends is very useful stored procedure which can give you list of the dependent object of your table or views. If you pass table name in SP_Depends, it will give you depended views, stored procedures, functions etc. let us look at this by small demo.

--create one small database for testing

create database DependCheck

use DependCheck;

--create one table and insert some data

create table TestDepend

(

ID Int Identity(1,1),

Name Varchar(20)

)

Insert into TestDepend

SELECT 'RITESH' UNION ALL

SELECT 'RAJAN' UNION ALL

SELECT 'ALKA'

--create one PROC which is depend on TestDepend Table

CREATE PROC uspTestDependSelectAll

SELECT * FROM TestDepend

--try executing PROC

EXEC uspTestDependSelectAll

--check SP_Depends system procedure to get all dependent objects

sp_depends 'TestDepend'

--here is one alternate way to get dependent list

SELECT * FROM information_schema.routines ISR WHERE charindex('TestDepend', ISR.ROUTINE_DEFINITION)>0

--USE master;

--GO

--DROP DATABASE DEPENDCHECK

--GO

Well we have seen two different ways to get list of dependent objects of tables but in my live database when I run both way, it gives me different results however in our case it will give same results. So the question I would like to ask is, which way is correct and reliable?

ORDER BY clause and function with alias column name in SQL Server

2010-01-27T11:58:00.000+05:30

ORDER BY clause is really tricky sometime. It doesn’t accept alias column name in some situation nor it is being used in Subquery. However we do have some alternate way with the trick to do so.

There was small discussion going about this on Vijaya’s Blog Where I have participated. .NET MVP Vijaya’s raised the question about this topic and I found it interesting that is why I am sharing it here.

Basically he asked readers that why Order By is working with column alias

For eg:

SELECT

LoginID,

HireDate,

GETDATE() AS Current_Dt

FROM HumanResources.Employee

ORDER BY Current_Dt

And why the same alias doesn’t work with the function in ORDER BY clause.

For eg:

SELECT

LoginID,

HireDate,

GETDATE() AS Current_Dt

FROM HumanResources.Employee

ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate));

I found one trick with CTE which can work for this:

For Eg:

with cte as

(

SELECT

LoginID,

HireDate,

GETDATE() AS Current_Dt

FROM HumanResources.Employee

)select loginid,hiredate,current_dt from cte

ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate))

I would like my blog reader to participate there. Here is the link for that article. DotNetVJ

Set update trigger to keep watch on certain column’s update in SQL Server 2008

2010-01-26T14:35:00.000+05:30

I have seen many times that people keep asking the question how to track the updates in certain column.Well there are several ways to do so but the oldest way is to set UPDATE trigger on the table and keep an eye on the important column.

Please be informed that excesses use of trigger may affect performance so use this method carefully not for keeping track of so many columns in so many tables.

Anyway, let us get back to our core topic and see one small script.

--create one table for demonstration

CREATE TABLE TriggerTest

(

UserName varchar(20),

[Password] varchar(20)

)

--create one table which will LOG information

CREATE TABLE TriggerTestLog

(

ID INT identity(1,1),

UserName varchar(20),

OldPassword varchar(20),

NewPassword varchar(20),

DateOfUpdate datetime

)

--insert some records in first table

INSERT INTO TriggerTest

SELECT 'ritesh','shah' union all

SELECT 'rajan','shah' union all

SELECT 'bihag','goodluck' union all

SELECT 'bhaumik','shruti'

--set after update trigger for first table

--when password will be updated, it will log

--information in second table.

Create TRIGGER TrgTriggerTest ON TriggerTest

AFTER Update

BEGIN

SET NOCOUNT ON;

IF UPDATE([Password])

BEGIN

insert into TriggerTestLog(username,OldPassword,NewPassword,dateOfUpdate)

select i.username,d.[password],i.[password],getdate()

from inserted i join deleted d on i.username=d.username

END

--select records from second table

--which is blank right now ofcourse

select * from TriggerTestLog

--update first table

UPDATE TriggerTest SET [Password]='furious' WHERE UserName='ritesh'

--you will get information automatically inserted in second table.

select * from TriggerTestLog

SET ROWCOUNT in SQL Server 2008 with T-SQL and SSMS

2010-01-25T12:24:00.003+05:30

Today morning one of my junior .NET developer was trying to execute query and he was getting only few number of rows from result set since we have millions of records in that tables. He was frustrated and came to me for this reason. Actually this is not a big deal but due to lack of understanding and knowledge it becomes hard to find out why this was happening.

When I checked his SSMS->Tools->Options->Query Execution->General->Set RowCount, I found that there was a value 50 set so it was returning only 50 rows. Actually It suppose to be 0 to return all rows. This option is being set when we want only few rows out of the query and wanted to stop query execution once we achieve those rows.

This command is somehow similar to TOP clause but there is a difference which I will cover in later article.

Anyway, we saw above that how we can set ROWCOUNT from GUI now I will show you one simple script which will do the same.

Create Table emps

(

name varchar(20),

dept varchar(20)

)

Insert into emps (Name,Dept)

Select 'Ritesh','MIS' union all

Select 'Rajan','Acct' union all

Select 'Bihag','DBA'

SET ROWCOUNT 2;

SELECT * FROM emps

--don't forget to set 0 again

--otherwise all query will return 2 rows

SET ROWCOUNT 0;

Introduction of System Databases in SQL Server 2008

2010-01-22T08:48:00.000+05:30

Today I saw one question in Experts-Exchange.com which inspires me to write this introduction of system databases. Question asker has used Master database to create his tables for his application, as time passed, application became big and was using so many tables which he has generated in Master database. Somebody has updated him not to use Master database for user tables than he came to know what mistake he did and raised the question in forum that how can he decided what table belongs to system and what table belongs to his application and how can he move those tables.

This kind of situation may occur if you are not SQL Server expert and you don’t know what the system database is and how it is useful.

Anyway, let me come back to the point of system database in SQL Server 2008. Basically there are four most important system databases which you shouldn’t touch for creating your own user tables, views etc. and also afraid to change any table and/or information in such databases.

Here is the list of four most important system databases which ships with Microsoft SQL Server 2008 by default.

1.) Master

2.) Model

3.) MSDB

4.) TempDB

Let us look a brief description of each database.

Master Database: Master is one of the very crucial databases. No Master database No SQL Server J it is composed of system tables that keep track of your whole SQL Server and its installation along with all databases which will be going to created in the server. It also keeps records of your disk space, system wide configuration, file allocation, logins and existence of other databases to name a few. So as long as possible, don’t make any change in MASTER database and keep a latest backup copy always with you.

Model Database: Model database has its own importance. You can say it a template of all other databases. It has a standard set of objects within it so whenever you create any new database in your server, all objects from Model database will be inherited so if you want your new database to get generated with some default objects or permission, put it in Model Database and you are done.

MSDB Database: MSDB database is being used by SQL Server agent which used to handle all schedule jobs for you.

TempDB Database: TempDB database has really interesting behavior. It used to regenerate every time you restart your SQL Server service. It is basically a workspace and temporary table (global and local both) created by user, used to get stored in tempDB by default. Some intermediate operation like query processing and sorting also use TempDB database so it would be beneficial to keep TempDB on separate and fast drive in order to get performance.

Upgrade from SQL Server 2000 to SQL Server 2005

2010-01-21T10:53:00.000+05:30

There were major changes in architecture & engine of SQL Server 2005 as compare with SQL Server 2000. So as per my observation and experience in live environment, many different kind of error used to come while upgrading, I don’t mean that upgrade doesn’t work at all but personally I have came across so many different kind of error so rather than upgrading from SQL Server 2000 to SQL Server 2005, I would like to follow different approach which has been given below.

1.) Detach all use databases (don’t need to have system databases with us)

2.) Back up all the job if you have any in your SQL Server 2000

3.) Create login transfer script so that all logins of your SQL Server 2000 works fine in SQL Server 2005 with same passwords. There are few different approaches for this but I would like to point out one of the Microsoft’s link for this task. http://support.microsoft.com/kb/246133

4.) Now, this is the time to uninstall SQL Server 2000

5.) Install SQL Server 2005 and upgrade it with latest hot fixes and patches

6.) Attach all your databases which were detached from SQL Server 2000

7.) Restore all jobs which were backed up from SQL Server 2000

8.) Run the transfer login script which we have generated in step 3 above.

Isn’t it simple to go for? I find this easiest way to go for rather than upgrading the server. This is my personal point of view which I used to follow. The main reason of writing this article is, yesterday one of my friend was upgrading his SQL Server 2000 to 2005 and came across one weird error which he was not able to solve. I suggested him to follow these steps in future and also suggested few steps which might resolve the error he was facing. I used to get update from him today, If the way I suggested had helped him to resolve the error, I will provide full case study in my blog so everybody can take advantage of the same.

Reference: Ritesh Shah

7th Position as an Expert of the year in Experts-Exchange.com

2010-01-09T08:49:00.000+05:30

I am actively running my blog SQLHub.com from March-2009 and have started participated actively in Experts-Exchnage.com from the April-2009. From the month of May 2009 I was in the list of top 5 experts in few zones like SQL Server, SQL Server 2005 and SQL Server 2008 etc. Finally today experts of the year list came out from Experts-Exchange official and I am really very glad to see my name in the TOP-10 Expert of year.

Since last two months I am really very busy with heavy work load but this name in TOP-10 list encouraged me enough to participate more with community and reach to more and more people. I have learned lot of things from community so I consider it my duty to helping more and more people.

Thanks to everybody who always encouraged me in my mission especially Mr. Pinal Dave who never let myself stop helping people. He is real source of encouragement and inspiration.

Article on Audit facility in SQL Server 2008 in Experts-Exchange

2009-12-04T13:32:00.000+05:30

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server.

In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace which is not the most intuitive tools. However in the latest editions, we are now given quite a lot of power for auditing with some purpose built tools. The SQL 2008 Audit is meant to be replacing SQL Trace, it is faster, more granular and easier to interact with from SQL Server Management Studio, and with code. In this article, I am using a T-SQL code approach.

Since this is one of the big topics in SQL Server 2008 (and bound to become more popular), it is not possible to cover it completely here. However, I will try to give you one detailed but simple example as an introduction to SQL Audit.

Read Complete Article in Experts-Exchange.com.

A beauty of Set Based theory in SQL Server

2009-11-26T11:51:00.000+05:30

I always prefer Set Based theory versus step-by-step procedural programming. Today I am going to show you how efficiently it works. Suppose you have two variable containing one value each. You want to swap it so there are few different ways to do so but the most common way is to do it with the help of third variable, pseudo code would be something like:

VAR v1,v2,v3;
v1=1;
v2=2;
v3=v1;
v1=v2;
v2=v3
print v1,v2,v3

So, this is something procedural approach, now let me show the way in SQL Server which won’t use third container and will swap value without any external logic.

--create table for demonstration

create table SetBaseTest

(

name1 varchar(10),

name2 varchar(10)

)

--insert some records for testing puropse

insert into SetBaseTest

select 'ritesh','shah' union all

select 'rajan','shah'

--look at the record set

SELECT * FROM SetBaseTest

--execute update and swap value

--you might think that name2 will be assign to name1

--so now name1 should be name2 and then

--reassign name1 (which itself contain value of name2 right now)

--to name2, so both column should be same.

--but no, you are WRONG, look at the result after UPDATE statement

UPDATE SetBaseTest SET name1=name2,name2=name1

SELECT * FROM SetBaseTest

Very common interview question about COUNT aggregate function.

2009-11-24T12:31:00.002+05:30

I have observed that many interviewers asks question to SQL Server Developer about COUNT aggregate function. The question is something like

There is one table which has three fields.

1.) ID which is integer primary key so it won’t accept null value

2.) FirstName which varchar and can accept null value

3.) LastName which varchar and can accept null value

If I execute COUNT(*) in SELECT query and in other SELECT query, I execute COUNT(ID) and in third SELECT statement I execute COUNT(FirstName), what would be the results? Whether it is same or not?

Most of the SQL Server developer says, it would be same with very high confidence which is not true in all case. Let us see one small example.

create table countTest

(

id int primary key,

firstname varchar(10),

lastname varchar(10)

)

insert into countTest

select 1,'ritesh','shah' union all

select 2,null,'jain' union all

select 3,'rajan',null union all

select 4,'alka',null

select count(*) as CountStar from countTest --out put would be 4

select count(id) as countID from countTest --out put would be 4

select count(firstname) as countFirstName from countTest --out put would be 3

select count(lastname) as countLastName from countTest --out put would be 2

So after seeing last two SELECT statements, your confidence level will become zero because COUNT aggregate function will not consider NULL value. Not only COUNT but other aggregate functions like SUM, AVG etc. will follow the same rule.

Interesting observation about VIEW and ORDER BY clause in SQL Server 2008

2009-11-23T15:06:00.000+05:30

View is been always interesting topic for me and for many of the SQL professionals. Use of ORDER BY clause inside the view is really a question of debate since long. I am going to present my observation about view in this article which I have tested in SQL Server 2008 (10.0.1600.22 ((SQL_PreRelease).080709-1414 ))

It is not guaranteed that it will remain same in all versions of SQL Server.

Basically, you can’t create VIEW with ORDER BY clause. However, if you will use TOP clause inside your SELECT clause, you can specify ORDER BY clause.

NOTE: why Order by is not working with VIEW and why it is working with TOP clause is also an interesting topic which I will discuss sometime later.

Anyway, let us go through some small practical code snippet which is really basic but useful to make fundamental clear.

CREATE TABLE ViewTest

(

col1 CHAR(1)

)

INSERT INTO ViewTest

SELECT 'A' UNION ALL

SELECT 'C' UNION ALL

SELECT 'D' UNION ALL

SELECT 'B' UNION ALL

SELECT 'Z' UNION ALL

SELECT 'R'

select Col1 from ViewTest order by col1

--Result of above query

--Col1

------

--A

--B

--C

--D

--R

--Z

--(6 row(s) affected)

CREATE VIEW vViewTest AS

select Col1 from ViewTest order by col1

--creation of this view won't work rather,

--you will be greeted with following error

--Msg 1033, Level 15, State 1, Procedure vViewTest, Line 2

--The ORDER BY clause is invalid in views, inline functions, derived tables,

--subqueries, and common table expressions, unless TOP or FOR XML is also specified.

--you can't use ORDER BY clause in View that's why you faced an error.

--even if you will try use TOP clause, you can specify ORDER BY

CREATE VIEW vViewTest AS

select TOP 100 PERCENT Col1 from ViewTest order by col1

SELECT * FROM vViewTest

--result of above query

--Col1

------

--A

--C

--D

--B

--Z

--R

--(6 row(s) affected)

Result is not sorted, however it was working in SQL Server 2000 but right now it is not working in SQL Server 2005+ versions.

Let us try one more thing here.

ALTER VIEW vViewTest AS

select TOP 99.999 PERCENT Col1 from ViewTest order by col1

SELECT * FROM vViewTest

--result of above query is.

--Col1

------

--A

--B

--C

--D

--R

--Z

--(6 row(s) affected)

Bingo!!! it is working now but this is I guess error or something so I recommend not to trust this 99.999 stuff and simply use following solution for ordering your view. If you right now use “99.999” stuff and in next patches or in next fixes, it might stop working but if you will ordering your result set while executing select, it is guaranteed that ordering will work 100% sure. For eg:

ALTER VIEW vViewTest AS

select Col1 from ViewTest

SELECT * FROM vViewTest order by col1

Auditing in SQL Server 2008 white paper.

2009-11-21T12:55:00.000+05:30

A key part of any data security strategy is the ability to track who has accessed, or attempted to access, your data. This provides the ability to detect unauthorized access attempts or, if necessary, to piece together the actions of malicious insiders who misused their legitimate access. Furthermore, a rich and robust tracking capability can provide oversight of sensitive configuration changes made by administrators.

Such considerations are ever more relevant in today’s information economy. Data is collected, stored, used, and misused at an ever increasing rate. Governments and private sector organizations around the world are responding to this by establishing various compliance regimes to improve the stewardship of data by those who hold it. A few of the most widely known examples include:

European Union Data Protection Directive, a strict data protection policy with implications across the EU and the global economy.
HIPAA, or Health Insurance Portability and Accountability Act, part of United States law
Sarbanes-Oxley, part of United States law governing corporations.
Payment Card Industry Data Security Standard, mandated by major credit card companies, with worldwide implications.

These formal regulations affect organizations of all sizes, in all industries, around the world. They place significant pressure on organizations to ensure their IT platforms and practices are compliant. And ultimately, these requirements land at the feet of the DBAs, developers, and IT professionals who manage the data.

It is important that a data management platform provide the means to meet these requirements, and do so efficiently. To address these needs, SQL Server 2008 introduces a rich and deeply integrated auditing capability that offers major improvements over previous versions of the Microsoft® SQL Server® database software.

This paper will review the new audit features of SQL Server 2008, compare them to past versions, and walk through some implementation examples.

You can read full while paper by clicking here.

For studying one detailed example of Auditing in SQL Hub, click here.

Audit in SQL Server 2008

2009-11-20T14:09:00.000+05:30

Audit is really one of the interesting, useful yet difficult to maintain topic in history of SQL Server. I have already written few articles on how to maintain Audit trail in SQL Server. You can refer those articles from the below given links, once you will go through those articles and then read ahead this one, you will come to know how much it is easy in SQL Server 2008.

Audit Trail with Trigger

Keep Log of update in same table

Audit with OUTPUT clause

Earlier people had very few options for auditing in SQL Server but in latest technology, you are having quite a good power on auditing stuff. Since this is one of the big topics in SQL Server, it is not possible to cover it completely here, though I will try to give you one detailed example herewith below.

First of all when you want to use power of Audit in SQL Server 2008, you have to create AUDIT object. AUDIT object is nothing more than just a container of Audit specification on Server level as well as on database level. You can store log in Application Event Log, Security Event Log and Filesystem.

I am going to create one Audit object which will act as a container of one of the database level audit specification which will keep an eye on one Schema for any DML statement executed on that schema.

I will store log in D:\Audit in my server, you can change the path in given script if you wish, do create the folder and give reference in script below.

--Select MASTER Database

USE master

--create SERVER Audit

CREATE SERVER AUDIT [FirstAudit]

TO FILE

(

FILEPATH=N'D:\AUDIT\'

,MAXSIZE=10 MB

,MAX_ROLLOVER_FILES=100

,RESERVE_DISK_SPACE=ON

)

WITH

(

QUEUE_DELAY=1000

,ON_FAILURE=SHUTDOWN

,AUDIT_GUID='2EB5EF64-1B15-4AFF-B248-6F39D423E2E7'

)

--Alter server audit object for

--making it enable

ALTER SERVER AUDIT [FirstAudit]

WITH (STATE=ON)

--creating one test database,

--if it is exists, droping it first

if exists(SELECT 1 FROM SYS.DATABASES WHERE name='AuditTest')

BEGIN

USE master

drop database AuditTest

END

CREATE DATABASE AuditTest

--select newly created database

USE AuditTest

--create two schema AUD1 and AUD2

CREATE SCHEMA Aud1

CREATE SCHEMA Aud2

--create two table

--one with each schema

if OBJECT_ID('Aud1.Table1') is not null drop table Aud1.Table1

CREATE TABLE Aud1.Table1 (id int)

if OBJECT_ID('Aud2.Table1') is not null drop table Aud2.Table1

CREATE TABLE Aud2.Table1 (id int)

--create database level AUDIT SPECIFICATION

--for our server audit created above

--which will keep watch on schema AUD1

--for DML statements but won't watch for AUD2 schema

CREATE DATABASE AUDIT SPECIFICATION [AuditTestSpec]

FOR SERVER AUDIT [FirstAudit]

ADD (SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[Aud1] by [PUBLIC])

WITH (STATE=ON)

--making some DML actions in both table

--of both schema

INSERT INTO Aud1.Table1

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5

INSERT INTO Aud2.Table1

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5

UPDATE Aud1.Table1 set id=6 where ID=5

UPDATE Aud2.Table1 set id=6 where ID=5

--looking at what we have received in our audit file.

--you will know that you got data for AUD1 schema only

SELECT * FROM sys.fn_get_audit_file('D:\Audit\*',DEFAULT,DEFAULT)

You can find detailed text on this topic from MSDN. Do look at all reference link given below the article in MSDN.

Security TIPS for Harden SQL Server 2008

2009-11-19T12:27:00.004+05:30

Hardening SQL Server is really one of the challenging jobs in the field of SQL Server. It is not at all possible to cover it in one article rather one dedicated book should be there. Even, I am trying to give some important tricks as a first steps.

Authentication
There are two types of authentication available in SQL Server.

1.) Windows Authentication

2.) SQL Server Authentication (Mix Mode)

As long as possible, try to keep Windows authentication so that you can take advantage of your Active Directory and all users and account govern by the Active Directory itself. You can even use multiple password policy which is available in Windows Server 2008. You can have additional level of protection with Kerberos

If, due to any reason, you have to use SQL Server authentication, do keep offer password and lockout policy. Generally SQL Server authentication is needed to support legacy application/client.

Securing your SA account
SA account by default comes up with full privileges. You shouldn’t use SA for regular Admin task. Don’t forget to enforce a strong password with combination of uppercase and lowercase with numbers and non alphanumeric characters.

Security Patches
You should always keep your server updated with latest service packs and security patches. Do apply it first on staging server before applying it to live server and do keep a backup of live server before applying these patches.

Apart from these tips, you can use two free utility provided by Microsoft to check your server whether it is on risk or not. You can look those two utility from my past articles MBSA and BPA.

Microsoft Baseline Security Analyzer (MBSA)

2009-11-15T09:52:00.000+05:30

Microsoft Baseline Security Analyzer (MBSA) is an easy-to-use tool that helps small and medium businesses determine their security state in accordance with Microsoft security recommendations and offers specific remediation guidance. Improve your security management process by using MBSA to detect common security misconfigurations and missing security updates on your computer systems. Built on the Windows Update Agent and Microsoft Update infrastructure, MBSA ensures consistency with other Microsoft management products including Microsoft Update (MU), Windows Server Update Services (WSUS), Systems Management Server (SMS), System Center Configuration Manager (SCCM) 2007, and Small Business Server (SBS).

You can check following issues with your SQL Server 2005.

tWindows Administrative Vulnerabilities

tWeak passwords

tIIS administrative Vulnerabilities

tCheck for SQL administrative vulnerabilities

tCheck for security updates

You can download this useful utility from here. MBSA is available in English, German, Japanese and French languages for both 32 and 64 bit OS.