Digicorp

The database compatibility level determines how certain database behaviors work. For instance, in 90 compatibility, you need to use the OUTER JOIN syntax to do an outer join, whereas in earlier compatibility levels, you can use ‘*=’ and ‘=*’. Contrary to popular myth, all of the behavioral differences ARE documented – in the Books Online section for sp_dbcmptlevel – the SP used to set the compatibility level.

Version of SQL Server that can be reverted to can be one of the following:

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

The compatibly level setting is used by SQL Server to determine how certain new features should be handled

Compatibility level tells SQL Server how to interpret T-SQL commands. When you perform an upgrade from SQL Server 2000 to SQL Server 2005, the upgrade won’t automatically change the compatibility level from 80 to 90. This is because if you need to change the compatibility level then all the SP’s, DTS, functions etc should be migrated to 90 compatibility level since some of the T-SQL statements are removed in 90. You need to manually change the compatibility level after up gradation. As said above query plan changes between compatibility levels

When upgrading databases from an older version of SQL Server using either the backup and restore method or detach and attach method the compatibility level does not automatically change and therefore your databases still act as though they are running using an earlier version of SQL Server.  From an overall standpoint this is not a major problem, but there are certain features that you will not be able to take advantage of unless your database compatibly level is changed.

Identifying Compatibly Level

To check the compatibility level of your databases you can use one of these methods:

You can see the compatibility level of all databases by doing:

sp_helpdb

Or

For particular database

sp_helpdb ‘NHS_TESTING’

Changing Compatibility Level

sp_dbcmptlevel [ [ @dbname = ] name ]
[ , [ @new_cmptlevel = ] version ]

–to change to level 80
dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=80

–to change to level 90
dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=90

–or
sp_dbcmptlevel ‘test’, ’80′

sp_dbcmptlevel ‘test’, ’90′

Testing all procs before upgrading to Compatibility Level 90

If you use SQL Server 2005 but your database is still in Compatibility Level 80 (SQL Server 2000), you can use this script to test if any procs or functions won’t work when you upgrade the database.

The script is quite simple. All that it does is to try to recreate a proc with a different name (Temp_TestProc_DeleteMeTemp_TestProc_DeleteMe). If something goes wrong, the script will record the proc name, the code used to create the proc, and the error message. After trying this with all procs in the database, the procs that couldn’t be created will be listed. Those will be the ones that must be fixed before upgrading the database to compatibility level 90.

A word of caution:

1. If the problem is inside of a dynamic SQL code, it won’t be detected.
2. This script is intended to find incompatibility only in procs and functions. You still have to check codes used to create triggers and views.

Script

DECLARE @sql VARCHAR(max),

@Text VARCHAR(max),

@ProcName VARCHAR(200),

@ProcName1 VARCHAR(200)

DECLARE @T TABLE (ProcName VARCHAR(200), sql VARCHAR(max), ErrorMessage VARCHAR(4000))

DECLARE c Cursor FOR

SELECT O.Name, C.Text

FROM sysobjects O

JOIN syscomments C ON o.ID=C.ID

WHERE O.XType IN (‘P’,'TF’,'FN’)

and C.text IS NOT NULL

ORDER BY O.Name, C.colid

Open C

FETCH NEXT FROM c INTO @ProcName, @Text

SET @sql=@Text

SET @ProcName1=@ProcName

WHILE @@FETCH_STATUS = 0 BEGIN

FETCH NEXT FROM c INTO @ProcName, @Text

IF @@FETCH_STATUS = 0 AND @ProcName1=@ProcName BEGIN

SET @sql=@sql+@Text

END ELSE BEGIN

SET @sql = REPLACE(@sql, @ProcName1, ‘Temp_TestProc_DeleteMe’) — change proc name

IF EXISTS (SELECT * FROM sysobjects WHERE Name=’Temp_TestProc_DeleteMe’ AND XType=’P') EXEC(‘DROP PROC Temp_TestProc_DeleteMe’)

IF EXISTS (SELECT * FROM sysobjects WHERE Name=’Temp_TestProc_DeleteMe’ AND XType IN (‘TF’,'FN’)) EXEC(‘DROP FUNCTION Temp_TestProc_DeleteMe’)

BEGIN TRY

EXEC(@sql) — try to create the proc

END TRY

BEGIN CATCH

INSERT @T values (@ProcName1, @sql, ERROR_MESSAGE()) — record procs that couldn’t be created

END CATCH

print @ProcName1

SET @sql=@Text

SET @ProcName1=@ProcName

END

END

CLOSE c

DEALLOCATE c

IF EXISTS (SELECT * FROM sysobjects WHERE Name=’Temp_TestProc_DeleteMe’ AND XType=’P') EXEC(‘DROP PROC Temp_TestProc_DeleteMe’)

IF EXISTS (SELECT * FROM sysobjects WHERE Name=’Temp_TestProc_DeleteMe’ AND XType IN (‘TF’,'FN’)) EXEC(‘DROP FUNCTION Temp_TestProc_DeleteMe’)

SELECT * FROM @T

Try to avoid using SQL Server cursors, whenever possible.

SQL Server cursors can results in some performance degradation in comparison with select statements. Try to use correlated sub query or derived tables, if you need to perform row-by-row operations.

Do not forget to close SQL Server cursor when its result set is not needed.

To close SQL Server cursor, you can use CLOSE {cursor_name} command. This command releases the cursor result set and frees any cursor locks held on the rows on which the cursor is positioned.

Do not forget to de allocate SQL Server cursor when the data structures comprising the cursor are not needed.

To de allocate SQL Server cursor, you can use DEALLOCATE {cursor_name} command. This command removes a cursor reference and releases the data structures comprising the cursor.

Try to reduce the number of records to process in the cursor.

To reduce the cursor result set, use the WHERE clause in the cursor’s select statement. It can increase cursor performance and reduce SQL Server overhead.

Try to reduce the number of columns to process in the cursor.

Include in the cursor’s select statement only necessary columns. It will reduce the cursor result set. So, the cursor will use fewer resources. It can increase cursor performance and reduce SQL Server overhead.

Use READ ONLY cursors, whenever possible, instead of updatable cursors.

Because using cursors can reduce concurrency and lead to unnecessary locking, try to use READ ONLY cursors, if you do not need to update cursor result set.

Try avoid using insensitive, static and key set cursors, whenever possible.

These types of cursor produce the largest amount of overhead on SQL Server, because they cause a temporary table to be created in TEMPDB, which results in some performance degradation.

Use FAST_FORWARD cursors, whenever possible.

The FAST_FORWARD cursors produce the least amount of overhead on SQL Server, because there are read-only cursors and can only be scrolled from the first to the last row. Use FAST_FORWARD cursor if you do not need to update cursor result set and the FETCH NEXT will be the only used fetch option.

Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option.

If you need read-only cursor and the FETCH NEXT will be the only used fetch option, try to use FAST_FORWARD cursor instead of FORWARD_ONLY cursor. By the way, if one of the FAST_FORWARD or FORWARD_ONLY is specified the other cannot be specified.

Problem

Making the decision to upgrade to SQL Server 2005 should be one that is made with sufficient information, not haphazardly.  Understanding the changes needed to your SQL Server 2000 environment prior to upgrading should be one of the first sets of information gathered to determine the level of effort needed to upgrade.  Scanning your code for issues, reviewing your configurations and DTS Packages can be a time consuming process.  As such, how can I gather the needed information about my SQL Server 2000 environment to determine how much work I have to complete before upgrading to SQL Server 2005?

Solution

As SQL Server 2005 was released, Microsoft also released the SQL Server 2005 Upgrade Advisor which can be installed on your desktop and then connect to instances of SQL Server.  This application reviews all of your code (T-SQL, Analysis Services, Data Transformation Services, Notification Services, Reporting Services, etc.) in SQL Server as well as externally in Trace and batch files, then outlines all of the issues that need to be resolved before or after the upgrade process. 

You can find the steps needed to execute the same from the link given below :

http://www.mssqltips.com/tip.asp?tip=1220 

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I’ll discuss the differences between using temporary tables in SQL Server versus table variables. Each of the four table options has its own purpose and use, and each has its benefits and issues:

• Normal tables are exactly that, physical tables defined in your database.
• Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.
• Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.
• Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It’s a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Creating indexes on SQL Server tables

Because both local and global temporary tables are physical tables within the tempdb database, indexes can be created on these tables to increase performance as needed. As with any index creation, this process can take time on larger tables. Because temp tables are physical tables, you can also create a primary key on them via the CREATE TABLE command or via the ALTER TABLE command. You can use the ALTER TABLE command to add any defaults, new columns, or constraints that you need to within your code.

Unlike local and global temporary tables, table variables cannot have indexes created on them. The exception is that table variables can a primary key defined upon creation using the DECLARE @variable TABLE command. This will then create a clustered or non-clustered index on the table variable. The CREATE INDEX command does not recognize table variables. Therefore, the only index available to you is the index that accompanies the primary key and is created upon table variable declaration.

How do the internal workings of SQL Server perform differently between table variables and temporary tables?

The differences between accessing tables and variables cause the internal processes within SQL Server to treat the objects quite differently. Temporary tables are actually physical tables, so the SQL Optimizer and locking engine handle the tables just as they would any other database tables. Because reads to a temporary table are made (including local temporary tables), a read lock is placed on the table.

This locking process takes time and CPU resources. When reading from a table variable – because the table variable is stored partially within memory and cannot be accessed by any other user or process on the system – SQL Server knows locking is not required. In a very busy database, this lack of locking can improve system performance because locks do not have to be taken, escalated and checked for each data access operation.

Limits of temporary tables and table variables

Temporary tables and table variables both have their strengths, but they both have weaknesses too. On a heavy load system that has lots of usage of temporary tables, the disk array servicing the tempdb database will experience a higher than expected load. This happens because all reads and writes to the temporary tables are done within the tempdb database. Table variables will perform poorly with large record sets, especially when doing joins because there can be no indexes other than a primary key. Beware, though, when many users start using table variables — large amounts of RAM are used because all temporary tables are stored and processed directly in memory. Table variables should hold no more than 2 Megs to 3 Megs of data each (depending on user load and system memory).

Both temporary tables and table variables can be extremely useful tools in developers’ and administrators’ arsenals; however, care must be taken as to when to use each solution. There is no end-all solution, and you must choose the correct solution for the correct situation.

Local Temporary tables:

They are created using same syntax as CREATE TABLE except table name is preceded by ‘#’ sign. When table is preceded by single ‘#’ sign, it is defined as local temporary table and its scope is limited to session in which it is created.

Open one session in Query Analyzer or SSMS (Management Studio) and create a temporary table as shown below.

CREATE TABLE #TEMP
(
COL1 INT,
COL2 VARCHAR(30),
COL3 DATETIME DEFAULT GETDATE()
)

GO

Upon successful execution of above command, MS SQL Server creates table in tempdb database. One cannot create another temporary table with the same name in the same session. It will give an error but table with the same name can be created from another session. To do this, open another session from SSMS or query analyzer and issue same command again. It will successfully create new temporary table for that session.

In order to identify which table is created by which user (in case of same temporary table name), SQL Server suffixes it with the number. This is very common scenario when temporary table is defined in the stored procedure and procedure is getting executed by different users simultaneously. Since we have created temporary table with the same name from two different sessions, we should see two entries in tempdb database. From another session or any of the current session, issue following command. Output is displayed after select statement.

USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like ‘%TEMP%’
GO

Table_Catalog Table_Name
————- ———-
tempdb #TEMP________0000000001F7
tempdb #TEMP________0000000001F9

Now create some data from the session in which temporary table (#temp) is created.

INSERT INTO #TEMP(COL1, COL2) VALUES(1,’Decipher’);
INSERT INTO #TEMP(COL1, COL2) VALUES(2,’Information’);
INSERT INTO #TEMP(COL1, COL2) VALUES(3,’systems’);

Selecting data from temporary table will give following results.

COL1 COL2 COL3
———– —————————— ———————–
1 Decipher 2007-03-27 19:39:56.727
2 Information 2007-03-27 19:39:56.727
3 systems 2007-03-27 19:39:56.727

This data is not visible from another session since we are using local temporary table. We can verify it by connecting to another session and querying the #temp table. Local temporary tables are dropped when session which created the table is ended, if one has not dropped it explicitly.

Also, please do note that if you are creating temp tables in a stored procedure, the scope for the existence of those temporary tables is only the procedure execution. The temp tables automatically get dropped once the procedure execution is over (they can be explicitly dropped as well). Once the procedure execution is over, those temp tables will not be accessible from within that session. Example:

create proc test
as
begin
set nocount on
create table #temp (col1 int)
insert into #temp values (1)
end
go

exec test
select * from #temp

Msg 208, Level 16, State 0, Line 2
Invalid object name ‘#temp’.

Global Temporary tables:

Syntax difference between global and local temporary table is of an extra ‘#’ sign. Global temporary tables are preceded with two ‘#’ (##) sign. Following is the definition. In contrast of local temporary tables, global temporary tables are visible across entire instance.

CREATE TABLE ##TEMP_GLOBAL
(
COL1 INT,
COL2 VARCHAR(30),
COL3 DATETIME DEFAULT GETDATE()
)
GO

Execute above statement to create global temporary table. You can verify it by checking the tempdb database. As global temporary tables are available across the instance, SQL Server doesn’t suffix it with the number. Following is the output of query ran against tempdb.

USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like ‘##TEMP%’
GO

Table_Catalog Table_Name
————- ———-
tempdb ##TEMP_GLOBAL

There will be only single instance of global temporary table. Attempt of creating global temporary table with the same name from any other session will result into an error.

Create some data in one of the session where temporary table (##temp_global) is created.

INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(1,’Decipher’);
INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(2,’Information’);
INSERT INTO ##TEMP_GLOBAL(COL1, COL2) VALUES(3,’systems’);

Connect to other existing session or open new session. Execute following statement and you will notice that global temporary table is available along with the data from other session as well.

COL1 COL2 COL3
———– —————————— ———————–
1 Decipher 2007-03-28 09:52:34.310
2 Information 2007-03-28 09:52:34.310
3 systems 2007-03-28 09:52:34.310

Global temporary tables are dropped when last session accessing the tables is closed. It is always good practice to drop the temporary tables in the same scope, once we are done with it. This will help us in avoiding creation error when same connection from the connection pool is used by different processes which access temporary tables.

Global temporary tables can be used in data warehousing application where one session performs the ETL and populate the global temporary tables and other sessions read from the table, specific data and process it.

Features of Temp Tables

We’ll list out features that differentiate a Temp Table between either a Permanent Table or a Table variable. These pointers will be helpful to keep in mind when you consider Table Variables in our next post.

Scope: Within a connection, a temporary table object is visible to the creating level and inner levels (nested). For example, if you create a stored procedure and declare a temporary table object within it, you can call another stored procedure from that stored procedure (a nested stored procedure) and perform operations like inserting, updating and deleting that temporary table object. Once the main creating level terminates, the temp table is automatically destroyed. But don’t be too complacent – you’ll have to wait for the system to perform a clean up and therefore, it is highly recommended that you manually drop your temporary table.

Locking: The prospect of table locking is reduced when it comes to local temporary tables since this table is being used by only one user. One aspect where you might want to keep this in mind is that if you cancel a transaction which contains the creation of a temp table object and then cancel that query, an exclusive and update lock can appear on the tempdb. This lock will persist till the complete transaction has closed with a COMMIT or a ROLLBACK

Logging: There is less logging involved with temporary tables compared to permanent tables.

Transaction: When using a temporary table, a temporary table is an integral part of an outer transaction and therefore, ROLLBACKs must be supported by Logging

Indexing: We can create indexes on temporary tables explicitly on them. Hence, there is scope for performance enhancement when you talk about temp tables.

Constraints: All constraints are available for exploiting on a temp table EXCEPT when it comes to referring a Foreign Key Constraints

Statistics: SQL Server can create Statistics for temp tables just like we do for permanent tables and therefore, the query optimizer has the option of choosing different plans. Hence, with this in mind, be aware of the scope of Stored Procedure Recompiles.

Recompiles: There is scope for a large number of Stored Procedure Recompiles especially when you have DDL statements mixed anywhere within your Stored Procedure.

Temp Table Size: Can hold any volume of data. This will be a strong part of the deciding factor when you want to choose between a temporary table and a table variable.

Features of Table Variables

Now that you’ve got a hang around working with a Table Variable, let me mention the main pointers that we need to keep in mind while working with. This will set the stage for differentiating between a Temp Table and Table Variable which I’ll illustrate in my next post.

Transactions: Table Variables are not bound to any transaction as they are just like any other variable

Minimum Constraints: A Table Variable permits us to use only the PRIMARY KEY, UNIQUE KEY and NULL constraint only. What this implies behind the scenes is that we can have unique indexes. The only possibility of creating a non-unique index is if we add attributes and make that blend unique and have a PRIMARY KEY or a UNIQUE KEY on the combination we just made.

No SELECT INTO: We cannot use a SELECT INTO with Table Variables in SQL Server 2000 though the feature is available with Table Variables in SQL Server 2005. Likewise, we can also have INSERT INTO working with Table Variables against a SELECT but not against an EXEC Stored Procedure.

No ALTER TABLE Variable: We cannot ALTER a Table once it has been declared. This may look a little rigid but remember that recompilations can come out like wild fire when there are DDL (Data Definition Language .i.e Schema) changes and therefore, this helps to avoid recompilations.

Scope: Just like any other variable, a Table Variable’s scope exists only within the context of the current level. Therefore, unlike Temp Tables, it is not accessible to sub levels (of Stored Procedures)

Table Variables And The TempDb: Okay, now I’ll touch upon one of the most common myths that exist among developers: that Table Variables have nothing to do with TempDb and therefore, they have no physical representation in the TempDb and therefore, they reside in ONLY memory and therefore they’re the best option for efficient processing.

Not entirely true. Table Variables do indeed have a physical representation within the TempDb and this can proved with a simple query in your database against the TempDb:

No Statistics: When it comes to Table Variables, the SQL Server optimizer does not create distribution statistics. Therefore, you run the risk of referring not-so-good query plans when the SQL Server optimizer selects after checking up with histograms. And if you consider this aspect with Tables Variables that contain huge amounts of data, we fall into serious I/O thrashing. Hence, as stated in the closing section of the last point, we have to have a thorough understanding of our scenario to choose a temporary object for the context.

A possible replacement for temp tables is a table variable.

In summary, following are the key points when temporary tables are involved.

• Temporary tables can be defined as local or global temporary tables.
• Local temporary tables are available to session in which they are created. If another  session creates the table with the same name, it will be different copy of the table in tempdb database.
• Global temporary tables are available across the instance. Any user from any session can access it.
• It is best practice to drop the temporary table when related work is finished rather than relying on connection to end for the cleanup.
• Table variables can be used instead of temporary tables for performance reasons and when dealing with smaller sub-sets.
• When used in the procedure,function or trigger, its scope ends once execution is completed.

Limitations of Temporary Tables

Temporary tables are created in the tempdb database and create additional overhead for SQL Server, reducing overall performances. SQL Server has numerous problems with operations against temporary tables.

Using Temporary Tables Effectively

If you do not have any option other than to use temporary tables, use them affectively. There are few steps to be taken.

• Only include the necessary columns and rows rather than using all the columns and all the data which will not make sense of using temporary tables. Always filter your data into the temporary tables.
• When creating temporary tables, do not use SELECT INTO statements, Instead of SELECT INTO statements, create the table using DDL statement and use INSERT INTO to populate the temporary table.
• Use indexes on temporary tables. Earlier days, I always forget to use a index on temporary. Specially, for large temporary tables consider using clustered and non-clustered indexes on temporary tables.
• After you finish the using your temporary table, delete them. This will free the tempdb resources. Yes, I agree that temporary tables are deleted when connection is ended. but do not wait until such time.
• When creating a temporary table do not create them with a transaction. If you create it with a transaction, it will lock some system tables (syscolumns, sysindexes, syscomments). This will prevent others from executing the same query.

Conclusion

Generally, temporary tables should be avoided as much as possible. If you need to use them follow the steps above so that you have the minimum impact on server performance

If you have to use a temp table, do not create it from within a transaction. If you do, then it will lock some system tables (syscolumns, sysindexes, and syscomments) and prevent others from executing the same query, greatly hurting concurrency and performance. In effect, this turns your application into a single-user application.

To avoid this problem, create the temporary table before the transaction. This way, the system tables are not locked and multiple users will have the ability to run this same query at the same time, helping concurrency and performance.

What is SQL Injection?

SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. This vulnerability is present when user input is manipulated for string literal escape characters embedded in SQL statements or user input is not sufficiently filtered and thereby unexpectedly executed.

How SQL Injection looks like?

Basic SQL Injections

If anybody want to pull up the records of particular user name from the user information table and if “username” variable is set as

X‘ OR ‘Y‘ = ‘Y

By inputting the above code as user name , Let see how’s it work at back end?

SELECT * FROM UserInformation WHERE UserName = ‘X’ OR ‘Y’ = ‘Y’

If we use this type of code were in an authentication procedure then this example could be used to force the selection of a valid “username” because the evaluation of ‘Y’='Y’ is always true and you will be logged in as the user on top of the SQL table.

Same way if the “username” variable is set as

X‘ OR 1 = 1 –-

If we use double dashes (–) than at the back end these dashes at the end tell the SQL server to ignore the rest of the query.

SELECT * FROM UserInformation WHERE UserName = ‘X’ OR 1 = 1 –-‘

Same way more SQL Injection syntaxes are:

‘) OR (‘1’ = ‘1

‘ OR ‘1’ = ‘1

‘ OR 1 = 1

“ OR “1”= “1

“ OR 1 = 1 –-

OR 1 = 1 –-

How can we get free from SQL Injection?

• Validate all input before using it.We can validate the input by this way.Reject the input that contains the following characters:

1. Single Quote(‘)

2.  Dash ( – )

3.  /* and */

4. Semicolon ( ; )

• User parameterized input with stored procedures: Stored procedures may be susceptible to SQL injection if they use unfiltered input. So all the input provided to the stored Procedures is provided in the form of parameters

• Filtering input: Replace a Single Quote (‘) with two Single Quotes (‘’) to filter the input.

• Limit the database permission: Use a limited access account to connect to the database

• Don’t store secrets in plain text: Encrypt or hash passwords and other sensitive data; you should also encrypt connection strings

• Exceptions should divulge minimal information: Don’t expose too much information in error messages; display minimal information in the event of error handling.

Reference :

http://en.wikipedia.org/wiki/SQL_injection

http://www.secureworks.com/research/articles/sql-injection-attacks

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)