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

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

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)

)

GO

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'

GO

select Col1 from ViewTest order by col1

GO

--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

GO

--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

GO

SELECT * FROM vViewTest

GO

--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

GO

SELECT * FROM vViewTest

GO

--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

GO

SELECT * FROM vViewTest order by col1

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

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.

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

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

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

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.

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 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.

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

I used to use BPA (Microsoft SQL Server 2005 Best Practices Analyzer) tool for all my SQL Server 2005 servers. It is really very helpful.

The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

You can download this really very useful tool from here.

BTW, you can use this tool with Microsoft SQL Server 2005 only. BPA for SQL Server 2008 is not available yet.

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

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

When I was novice with SQL Server and mainly working with .NET applications, Index seemed to me as alien of the planet of MARS. I was always confused about Index concept and always making mistake in choosing clustered and non-clustered index for particular field. When SQL Server and database technologies attract me, I had started to understanding Index concept in details few year back and this article is coming from my own experience and learning.

Though Clustered Index is not a concept of Relational Database, it is very useful for performance improvement in highly volume transaction processing system.

Basically Clustered Index is a kind of data structure that provide fast data access. There are two types of index available in SQL Server 1.)  Clustered Index 2.) Non-Clustered Index. Today we are going to talk about 1^st Option which is Clustered Index.

You can have maximum one clustered index per page because clustered Index stores sorted physical data in leaf level and this is obvious that you can sort data in one direction only so that you can have maximum one Clustered Index in database table. However, you can define clustered index on one or more than one field but try to keep as less columns as possible in clustered index.

When you create Primary Key, unique index is automatically getting created on column(s) which is Clustered Index by default; however, you can make it non-clustered while generating Primary Key.

While choosing the field for Clustered Index, you should consider few things in your mind. Column shouldn’t be low-cardinality. Low-cardinality means, it is the column which contain very less distinct value. For example, if you consider Grade filed (I guess there should be four grade A, B, C, D), you maximum having 4 kind of value in column.

Column should be highly selective in your WHERE clause, GROUP BY clause, JOIN and ORDER BY clause so that you can get maximum benefit out of it.

If you would like to read some more articles about Index in this blog, go through below given link

can't create Index on view

Calculate the size of clustered and non-clustered index

DBCC SHOWCONTIG and DBCC INDEXDEFRAG

sys.dm_db_index_usage_stats and sys.dm_db_index_operational_sats

For getting the details about structure of Clustered Index, please Click Here

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

Basically Primary Key is combination of field(s) which can help to uniquely identify the row in one table. This concept is not limited to only Microsoft SQL Server but it is used in any RDBMS.

In short, you should have combination of fields or at least one field in your table which can uniquely identify the row in table, suppose in university database student identification number is unique. Primary Key ensures that there is no duplicate data available in database.  Primary Key is not only uniquely identifying the rows; it is also used to establish relations between tables which is a real heart of any RDBMS including SQL Server.

Primary Key will not accept NULL value (NULL is unknown value or missing value which is really different than blank space or 0)

Before making a field as a Primary Key, think whether the field is really unique for all rows, if answer is no, then you should make combination of fields as a primary key which is known as Composite Primary Key.

If I want to give you example then I would give example of POOR Primary Key which is first name or last name etc. because these are the fields which can be shared by many person and GOOD example of Primary Key is SSN (Social Security Number) which is unique for every individual.

If you don’t find any combination which makes row very unique than others, in that case there is a facility in SQL Server called IDENTITY field, You can use it. IDENTITY field was popular at auto increment id in past.

BTW, I have observed that even a seasoned developer also making IDENTITY filed in each and every table without even asking question to their self that why they are creating it? Is there any need? It is not a good idea to have IDENTITY column in every table even though you have other fields which can be used to identify unique row.

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

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