Digicorp

Most of the time we grab the date and time using sql server GetDate() Function.

Sometimes if data is filtered between two dates it may give erroneous result. Why? Because while retrieving data using date as filter criteria, it will fetch records which are nearest 3 milliseconds.

It will be more clear by following example.

Create Table datetesting
(
PK_id INT IDENTITY,
Datefield DateTime
)

Insert into datetesting(DateField)
Values (’1/2/09 00:00′)
Insert into datetesting(DateField)
Values (’1/2/09 23:59′)

Insert into datetesting(DateField)
Values (’1/2/09 11:59:59.995 pm’)

Insert into datetesting(DateField)
Values (’1/3/09′)

Now Execute the following query

Select * from datetesting where Datefield Between ’1/2/9′ AND ’1/2/9 11:59:59.998 PM’

Result of the above query

PK_id        DateField
—————————————
1        2009-01-02 00:00:00.000
2        2009-01-02 23:59:00.000
3        2009-01-02 23:59:59.997

Now, Try the following query to retrieve data. I have just added 1 millisecond and it will retrieve date 2009-01-03 00:00:00.000 which does not match the criteria.

Select * from datetesting where Datefield Between ’1/2/9′ AND ’1/2/9 11:59:59.999 PM’

PK_id        DateField
—————————————
1    2009-01-02 00:00:00.000
2    2009-01-02 23:59:00.000
3    2009-01-02 23:59:59.997
4    2009-01-03 00:00:00.000

So next time when you face wrong data by filter, don’t get perplexed, This may be the reason!

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

SELECT – Retrieves data from a table
INSERT -  Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

CREATE – Creates objects in the database
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to initial value.

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

GRANT – Gives user’s access privileges to database
REVOKE – Withdraws user’s access privileges to database given with the GRANT command

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT – Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in transactions

Reference : Pinal Dave (http://blog.SQLAuthority.com), Original Source

Of late, I penned down an article – SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN – which received a very intriguing comment from one of my regular blog readers Craig. According to him this phenomenon happens due to Logical Query Processing. His comment instigated a question in my mind. I have put forth this question to all my readers at the end of the article. Let me first give you an introduction to Logical Query Processing Phase.

What actually sets SQL Server apart from other programming languages is the way SQL Server processes its code. Generally, most programming languages process statement from top to bottom. By contrast, SQL Server processes them in a unique order which is known as Logical Query Processing Phase. These phases generate a series of virtual tables with each virtual table feeding into the next phase (virtual tables not viewable). These phases and their orders are given as follows:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

As OUTER join is applied subsequent to ON clause, all rows eliminated by the ON clause will still be included by the OUTER join as described in the article SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN.

However, I am perplexed about the last two, ORDER BY and TOP. According to some people TOP comes first in logical query processing phase while others suggest that ORDER BY comes first. Now, here I’ve laid down my questions for you all to think about:

1) What is the correct answer for order query processing phase – ORDER BY or TOP?
2) How can we create an example to verify query processing phase for ORDER BY and TOP?

I will soon publish the answers I receive to the above questions on this blog, with due credit given to my readers.

Reference : Pinal Dave (http://blog.SQLAuthority.com),

Of late, I penned down an article – SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN – which received a very intriguing comment from one of my regular blog readers Craig. According to him this phenomenon happens due to Logical Query Processing. His comment instigated a question in my mind. I have put forth this question to all my readers at the end of the article. Let me first give you an introduction to Logical Query Processing Phase.

What actually sets SQL Server apart from other programming languages is the way SQL Server processes its code. Generally, most programming languages process statement from top to bottom. By contrast, SQL Server processes them in a unique order which is known as Logical Query Processing Phase. These phases generate a series of virtual tables with each virtual table feeding into the next phase (virtual tables not viewable). These phases and their orders are given as follows:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

As OUTER join is applied subsequent to ON clause, all rows eliminated by the ON clause will still be included by the OUTER join as described in the article SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN.

However, I am perplexed about the last two, ORDER BY and TOP. According to some people TOP comes first in logical query processing phase while others suggest that ORDER BY comes first. Now, here I’ve laid down my questions for you all to think about:

1) What is the correct answer for order query processing phase – ORDER BY or TOP?
2) How can we create an example to verify query processing phase for ORDER BY and TOP?

I will soon publish the answers I receive to the above questions on this blog, with due credit given to my readers.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL Server 2008 Service Pack 1 (SP1) is now available. You can use these packages to upgrade any SQL Server 2008 edition.

Download SQL Server 2008 Service Pack 1

Build of SP1 is SP1 is build 10.00.2531.00.

Reference : Pinal Dave (http://blog.sqlauthority.com)

Gandhinagar SQL Server User Group launch event was held on March 27, 2009. This successful, well-attended event received very positive and warm community response. This launch event, unexpectedly, saw over 50 database enthusiasts participating. It was really a moment of pleasant surprise when we ran out of chairs. The otherwise spacious room started getting smaller as more and more people joined in, and unquestionably, we felt ecstatic about it! Visit Gandhinagar SQL Server User Group Portal and register yourself now!

We commenced Gandhinagar SQL Server User Group launch event sharp at 6:30 and completed it precisely at 7:30. During these 60 minutes we conducted intense database discussion, and we had our share of some light moments as well. As this was the very first launch event, I had the privilege of being the sole speaker for all the sessions.

The agenda of meeting was as follows:

6:30 PM – 7:00 PM – Introduction to Joins and Real Life Scenario
7:00 PM – 7:10 PM – Tips to Improve SQL Performance
7:10 PM – 7:15 PM – Simple SQL Quiz (3 Question)
7:15 PM – 7:20 PM – Feedback
7:20 PM – 7:30 PM – Award for “Best Participant” and Questions and Answer

I had a great time discussing Join and I promised users that I will definitely be sharing some of my slides and scripts from the meeting with everybody. Click here to download slides and script.

One of the requests I received was to write down a simple and precise definition of Joins. I was quite taken aback to see that Joins are still a subject of interest for developers. I am seriously considering this request. To all those developers wanting to get insight into Joins I promise that I will soon come up with a write-up on Introduction to Joins.

I shared some tips on SQL Server performance improvement and even conducted an interesting and informative quiz round. UG members who gave correct answer to the questions received nice gifts. The best part of the event was giveaway of gifts to the participants near the end when everybody was really involved. The gifts included a vibrant red shirt and few other interesting gifts for the best participants. These Gifts were co-sponsored by Digicorp and me.

Here are a few photographs of UG meeting.

My heartfelt thanks go to the sponsor of this event – Digicorp - for their significant contribution to make this event a success. They were extremely prompt in making the last minute arrangements, especially arranging for more chairs for the room which was brimming with participants. Digicorp is a prominent IT service provider and an outsourcing company based in Ahmedabad, India. Its services range from Customized Application Development to Facebook Application Development.

To sum up, I would like to say that there are many advantages of User Group meetings. The prominent advantages are ‘Gaining Knowledge’ and ‘Sharing Knowledge’, which will surely add value to you as a professional or as a knowledge seeker.

Next time, I will be expecting a similar overwhelming response and even more participants to show up in UG meeting.

Reference : Pinal Dave (http://blog.sqlauthority.com)

More than a year ago I had written article SQL SERVER – Union vs. Union All – Which is better for performance? I have got many request to update this article. It is not fair to update already written article so I am rewriting it again with additional information.

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

Run following script in SQL Server Management Studio to see the result between UNION ALL and UNION. Download complete script from here.

/* Declare First Table */
DECLARE @Table1 TABLE (ColDetail VARCHAR(10))
INSERT INTO @Table1
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth'
/* Declare Second Table */
DECLARE @Table2 TABLE (ColDetail VARCHAR(10))
INSERT INTO @Table2
SELECT 'First'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fifth'
/* Check the data using SELECT */
SELECT *
FROM @Table1
SELECT *
FROM @Table2
/* UNION ALL */
SELECT *
FROM @Table1
UNION ALL
SELECT *
FROM @Table2
/* UNION */
SELECT *
FROM @Table1
UNION
SELECT *
FROM @Table2
GO

In our example we have two tables: @Table1 and @Table2.

Now let us run UNION ALL and UNION together and see the resultset as well as Execution Plan compared to complete set of query. You can always turn on actual execution plan using CTRL+M.

We can see from the resultset of UNION ALL that it returns everything from both the table but from UNION it is very clear that only DISTINCT rows from both the table is only retrieved.

Additionally, when comparing the execution plan of UNION ALL and UNION it is also quite clear that UNION ALL is way less expensive than UNION as it does not have DISTINCT SORT operation.

Let me know what do you think about this article. If you have any suggestion for improvement please let me know and I will update articles according to that.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

I will start my blogging with Digicorp with very simple but useful trick. When working with multiple project, it is quite common to forget the name of the SQL Server instances. We can retrieve information of all the SQL Server instances running in local network with one single command.

First go to Start >> Run >> CMD (Open command prompt). Once in command prompt run following command based on SQL Server version installed on local machine.

For SQL Server 2000:
C:\> isql -L

For SQL Server 2005 / SQL Server 2008:
C:\> osql -L
OR
C:\> sqlcmd -L

It is also possible that list of SQL Server instances is very long and it needs to be pushed to an output file. In that case add additional command to send output to a file.

For SQL Server 2000:
C:\> isql -L > c:\outputfile.txt

For SQL Server 2005 / SQL Server 2008:
C:\> osql -L c:\outputfile.txt
OR
C:\> sqlcmd -L c:\outputfile.txt

This feature is also important for network security. There are chances that system administrator does not know about some of the SQL Server Instance being active. In large organization this can be huge security threat. If any SQL Server instance is not used it should be turned off because it uses resources of server and adversely affect performance.

Just try this simple command on your local machine and I am sure you will in for surprise. There will be lot more SQL Server Instance running then you might have thought of. Your feedback is very valuable.

Regards,
Pinal Dave