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
Advertisements

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

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

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

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

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

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