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

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

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

Auditing in SQL Server 2008 white paper.

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.

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

Security TIPS for Harden SQL Server 2008

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

Microsoft Baseline Security Analyzer (MBSA)

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

Microsoft SQL Server 2005 Best Practices Analyzer – BPA

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

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

Clustered Index in SQL Server

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