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

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’
GO
SET ROWCOUNT 2;
SELECT * FROM emps
–don’t forget to set 0 again
–otherwise all query will return 2 rows
SET ROWCOUNT 0;
GO
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Introduction of System Databases in SQL Server 2008

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.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

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.

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

A beauty of Set Based theory in SQL Server

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)
)
GO
–insert some records for testing puropse
insert into SetBaseTest
select ‘ritesh’,‘shah’ union all
select ‘rajan’,‘shah’
GO
–look at the record set
SELECT * FROM SetBaseTest
GO
–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
GO
SELECT * FROM SetBaseTest
GO
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Very common interview question about COUNT aggregate function.

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)
)
GO
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
GO
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
GO
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.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Audit in SQL Server 2008

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.

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
GO
–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’
)
GO
–Alter server audit object for
–making it enable
ALTER SERVER AUDIT [FirstAudit]
WITH (STATE=ON)
GO
–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
GO
–select newly created database
USE AuditTest
GO
–create two schema AUD1 and AUD2
CREATE SCHEMA Aud1
GO
CREATE SCHEMA Aud2
GO
–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)
GO
if OBJECT_ID(‘Aud2.Table1’) is not null drop table Aud2.Table1
CREATE TABLE Aud2.Table1 (id int)
GO
–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)
GO
–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
go
INSERT INTO Aud2.Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
go
UPDATE Aud1.Table1 set id=6 where ID=5
GO
UPDATE Aud2.Table1 set id=6 where ID=5
GO
–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)
go
You can find detailed text on this topic from MSDN. Do look at all reference link given below the article in MSDN.

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

CHECKPOINT and Transaction Log in SQL Server

CHECKPOINT is nothing but just a process which writes all dirty pages to the database. After reading this statement, question might pops up in your mind that what are dirty pages? So, Dirty page is nothing but just data pages that have been modified but not written to the disk.

So making a long story short, CHECKPOINT writes all dirty data pages to disk so you don’t lose any data. CHECKPOINT is a command which you can execute stand alone or/else it get executed automatically in following situations.

–> When you start talking the backup of database, database engine first make CHECKPOINT so that each and every data will be in backup file.
–> At the time of altering database, especially when you add/drop any database files.
There are few more situations other then above described situations.
Since I am talking about CHECKPOINT, how come I forgot to mention one of the very hot topics in SQL Server which is truncate transaction log?
I have observed many times that when log file became bigger in SQL Server, developer or DBA used to truncate transaction log file. I really against this habit as long as possible, don’t truncate your transaction log, it will break your log chain. Rather do regular full backup and transaction log backup which will CHECKPOINT itself and will keep your log file in control.
For more details on CHECKPOINT, please do refer MSDN.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Policy Management Enforcing naming convention in Database(s) in SQL Server 2008

Yesterday I was reviewing one of my latest projects and I found that one of the developers had created two stored procedure with “SP_” prefix.  Generally “SP_” prefix is used for System stored procedure name; look at one of my past article how to create your own system SP,  if You will create your own stored procedure with “SP_”, It will not give you any error, it will get executed successfully but it is really a overhead on processing as whenever compiler will get prefix “SP_” it will going to look into system SPs list, once it won’t get it from there, will look at user SP list. Apart from this, there should be some naming convention rules for user defined object that is what I believe so I thought to put some policy enforcement so that, even by mistake, nobody can do it.

Since this is not a tutorial article for Policy Management, I will not going into deep dive in Facets, Condition and policy, rather I will use it to show you one practical example.
1.)    Create your condition by right clicking on “Condition” tab under Instance Name-> Management-> Policy Management-> Conditions-> New Conditions

2.)    In a conditions dialog box, give Name as a “SPNamingConventions”, select facet “Multipart Name” and in expression give @Name NOT LIKE ‘SP_%’

3.)    After clicking on “OK” button in above dialog box, right click on “Plolicies”, select “New Policy” and fill out details as mentioned in below screen capture.

Finally you are giving Policy Name “Stored Procedure Naming Convention Policy”, making it enable, which is MUST, by check box given. Select the condition you created in step # 2 and also selecting the target from given list. Once you done with that, don’t forget to select “On Change: Prevent” Evaluation Mode and also don’t forget to click on “OK” button

Once you are done with that, try to create stored procedure with prefix “SP_”. I am doing it in my server in AdventureWorks database; however you can try it in your own database.
Use AdventureWorks
GO
Create PROC SP_TestProc
AS
BEGIN
      SELECT ‘Ritesh’ as Name,‘SQLHub.com’ as Website
END
–You will be greeted with error something like below
–Policy ‘Stored Procedure Naming Convention Policy’ has been violated by
–‘SQLSERVER:\SQL\RITESH-SRV\SQL2K8\Databases\AdventureWorks\StoredProcedures\dbo.SP_TestProc’.
–This transaction will be rolled back.
–Policy condition: ‘@Name NOT LIKE ‘sp_%”
–Policy description: ”
–Additional help: ” : ”
–Statement: ‘
–Create PROC SP_TestProc
–AS
–BEGIN
—    SELECT ‘Ritesh’ as Name,’SQLHub.com’ as Website
–END
–‘.
–Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
–The transaction ended in the trigger. The batch has been aborted.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

ERROR FIX performance counter registry hive consistency in SQL Server 2008 installation

Error “performance counter registry hive consistency” while installing SQL Server 2008 is really frustrating error.  I have installed Microsoft SQL Server 2008 Evaluation Edition quite a few times but this is first time I got error “performance counter registry hive consistency”.  This error itself shows that there is a problem in registry of performance  counter so the prime question is, how to resolve this issue? There may be few different ways but I use “LodCTR.exe” file.

LodCTR is a Microsoft Windows Operating system process file and I used it with \R attribute which will rebuild the performance counter registry strings and information from scratch based on the current registry settings and backup INI files.

So making long story short, open your dos prompt, may be with following steps:

Start menu->Run->type CMD->press enter key

It will open command prompt and you can run

C:\>lodctr /R

Give it a time and then try installing again, hopefully you will be able to install Microsoft SQL Server 2008.

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find actual Row Location by fn_PhysLocFormatter in your database in SQL Server 2008

There are many undocumented and unsupported functions available in Microsoft SQL Server. You won’t find it even in BOL since it is undocumented but many of them are really very useful. I was digging about filegroups, pages and memory allocation in Microsoft SQL Server 2008 and suddenly found one function “sys.fn_PhysLocFormatter”. This function is used to use for finding the real location of the row in SQL Server. It can give you File id, page no and slot no.  Let us see one small example.

create table RowLocater
(
      id bigint,
      FirstName char(2000),
      LastName char(2000),
      Country char(2000)
)
insert into RowLocater
SELECT 1,‘Ritesh’,‘Shah’,‘India’ Union ALL
SELECT 2,‘Dharmesh’,‘Kalaria’,‘USA’
CREATE TABLE RowLocater1
(
      id bigint,
      FirstName char(200),
      LastName char(200),
      Country char(200),
)
insert into RowLocater1
SELECT 1,‘Ritesh’,‘Shah’,‘India’ Union ALL
SELECT 2,‘Dharmesh’,‘Kalaria’,‘USA’
GO
select sys.fn_PhysLocFormatter(%%physloc%%)AS[Physical_Location],
FirstName 
from RowLocater
GO
–result of select is here in my server (BIG TABLE)
–Physical_Location  ID   FirstName                                            
————————————-
–(1:2776:0)          1   Ritesh
–(1:2780:0)          2   Dharmesh
select sys.fn_PhysLocFormatter(%%physloc%%)AS[Physical_Location], 
FirstName  
from RowLocater1
GO
–result of above query in my server (SMALL TABLE)
–Physical_Location  id       FirstName
———————————————
–(1:2777:0)          1       Ritesh                                               –(1:2777:1)          2       Dharmesh                                                                                                                                                                                                 
One more interesting thing to observe, you might know that one page contain 8KB of data in SQL Server, our first table is big one so no pages can contain more than one row, however small table has small datatype and you can see both records falls under only one page (2777). This page # may be different in your own server but the concept will remain same.

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah