System Stored Procedure sys.sp_tables in SQL Server

to know the list of the tables from the database:

SELECT *
FROM sys.tables
GO

The script above provides various information from create date to file stream, and many other important information. If you need all those information, that script is the one for you. However, if you do not need all those information, I suggest that you run the following script:

EXEC sys.sp_tables
GO

The script above will give all the tables in the table with schema name and qualifiers. Additionally, this will return all the system catalog views together with other views. This Stored Procedure returns all the tables first in the result set, followed by views.

Even though Stored Procedure returns more numbers of rows, it still performs better than the sys.table query.

Let us verify it with two different methods for database AdventureWorks:

1) SET STATISTICS IO ON

USE AdventureWorks
GO
SET STATISTICS IO ON
SELECT *
FROM sys.tables
GO
EXEC sys.sp_tables
GO


(81 row(s) affected) (This is for sys.tables)
Table ‘syspalvalues’. Scan count 0, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘syssingleobjrefs’. Scan count 0, logical reads 324, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysidxstats’. Scan count 81, logical reads 168, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘syspalnames’. Scan count 0, logical reads 162, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysschobjs’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(456 row(s) affected) (This is for sys.sp_tables)
Table ‘sysobjrdb’. Scan count 1, logical reads 29, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘sysschobjs’. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

2) Execution Plan

You can see that the execution plan for sys.table has much higher cost of query batch.

Well, if you only need to know the name of the tables, I suggest that you start using SP_TABLES; at least it takes less typing to do.

Source: sqlserverauthority

SQL Server 2008 SP2 is now available for download

Microsoft has announce the availability of Service Pack 2 for SQL Server 2008. Both the Service Pack as the Feature Pack are available for download from the Microsoft Download Center. Service Pack 2 for SQL Server 2008 includes new features for compatibility with SQL Server 2008 R2, product enhancements based on requests from the SQL Server community as well as solutions hotfix provided in SQL Server 2008 SP1 Cumulative 1 to 8.

Key enhancements in Microsoft SQL Server 2008 Service Pack 2 are:

• Reporting Services in SharePoint Integrated Mode. SQL Server 2008 SP2 provides updates for integration services between SSRS and SharePoint products. The SSRS of SQL Server 2008 SP2 can be integrated with SharePoint 2010 products. SQL Server 2008 SP2 also provides additional support for grant support in the integration of SQL Servers Report Server 2008 R2 SharePoint 2007 products. This now enables SharePoint 2007 to use with the Report Servers in SQL Server 2008 R2. For additional information on the new integration of SSRS and SharePoint click on the link below What's New (Reporting Services).

SQL Server 2008 R2 and Application Compatibility Multi-Server Management with SQL Server 2008.

• Instance Management SQL Server 2008 with SP2 applied, a database instance of SQL Server 2008 can be included in the utility control SQL Server 2008 R2 as an instance managed by SQL Server. SQL Server 2008 SP2 allows organizations to extend the utility value of the control of SQL Server instances without upgrading those servers to SQL Server 2008 R2. For additional information click on the link below Overview of SQL Server Utility.
• Data-tier Application (DAC) Support. Instances Database SQL Server 2008 supports all operations DAC distributed SQL Server 2008 R2 after SP2 has been installed. You can distribute, update, search, extract and delete DAC's SP2 does not update the client tools to provide support for DAC's. You must use the SQL Client Tools Server 2008 R2 and SQL Server Management Studio, to perform DAC's. A CAD application is an entity that contains all the objects in a database and instantiated objects used by any application. DAC provides a single unit to approve, distribute and manage objects DAC. For additional information click on the link below Designing and Implementing Data-tier Applications.

To download SQL Server 2008 SP2 selected from the following links:

• SQL Server 2008 SP2: http://go.microsoft.com/fwlink/?LinkId=196550

• SQL Server 2008 SP2 Express: http://go.microsoft.com/fwlink/?LinkId=196551

• SP2 SQL Server 2008 Feature Packs: http://go.microsoft.com/fwlink/?LinkId=202815

Downloads and Documents:

• SQL Server 2008 SP2: http://go.microsoft.com/fwlink/?LinkId=196550
• SQL Server 2008 SP2 Express: http://go.microsoft.com/fwlink/?LinkId=196551 
• SP2 SQL Server 2008 Feature Packs: http://go.microsoft.com/fwlink/?LinkId=202815
• SQL Server 2008 SP1 CU's Released: http://support.microsoft.com/kb/970365
• KB of Microsoft SQL Server 2008 SP2: http://support.microsoft.com/kb/2285068
• Overview of SQL Server Utility
• Designing and Implementing Data-tier Applications
• What's New (Reporting Services)

What is Microsoft SQL Azure Database

Microsoft SQL Azure Database is a cloud-based relational database service built on SQL Server® technologies. It provides a highly available, scalable, multi-tenant database service hosted by Microsoft in the cloud. SQL Azure Database helps to ease provisioning and deployment of multiple databases. Developers do not have to install, setup, patch or manage any software. High availability and fault tolerance is built-in and no physical administration is required. SQL Azure Database supports Transact-SQL (T-SQL). Customers can use existing knowledge in T-SQL development and a familiar relational data model for symmetry with existing on-premises databases. SQL Azure Database can help reduce costs by integrating with existing toolsets and providing symmetry with on-premises and cloud databases.

 

What is SQL Azure Database?

Microsoft Project Code-Named “Houston” is a lightweight and easy to use database management tool for SQL Azure databases. It is designed specifically for Web developers and other technology professionals seeking a straightforward solution to quickly develop, deploy, and manage their data-driven applications in the cloud. Project “Houston” provides a web-based database management tool for basic database management tasks like authoring and executing queries, designing and editing a database schema, and editing table data. It is now available on SQL Azure Labs.

Use SQL Azure Database to:

• Build scalable, custom web applications, which is especially a need for small to mid-size businesses, hobbyist, and startups
• Build packaged line-of-business applications, which is attractive to traditional, SaaS ISVs and custom developers
• Build corporate departmental applications
• Consolidate multiple data sources in the Cloud and enable secure access from multiple locations, desktop and/or devices

Benefits of using SQL Azure

• No need to install or patch software or other physical administration
• Automatic high availability and fault tolerance
• Simple provisioning and deployment of multiple databases
• Scale databases up or down based on business needs
• Multi-tenant
• Integration with SQL Server and tooling including Visual Studio
• Support for T-SQL based familiar relational database model

The release of SQL Server 2008 R2 adds client tools support for SQL Azure, including added support through SQL Server Management Studio (SSMS). SQL Server Management Studio can be used to manage SQL Azure and can be downloaded for free here along with SQL Server Express. Additionally, SQL Server 2008 R2 and SQL Server Express have full support for SQL Azure – in terms of seamless connectivity, viewing objects in the object explorer, SMO scripting, and more.

 Full Details on http://www.microsoft.com/windowsazure/sqlazure/

Function to Retrieve First Word of Sentence - SQL Server

DECLARE @StringVar VARCHAR(100)
SET @StringVar = ' anything '
SELECT CASE CHARINDEX(' ', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX(' ',LTRIM(@StringVar), 1) - 1)
END FirstWordofSentence


Above script can be converted to User Defined Function which you can later use in SELECT statement as well. Here is how you can convert it to UDF and use it afterwords.

CREATE FUNCTION GetFirstWord (@StringVar VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @rtStringVar VARCHAR(MAX)
SELECT @rtStringVar = CASE CHARINDEX(' ', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX(' ',LTRIM(@StringVar), 1) - 1)
END
RETURN @rtStringVar
END
GO
 

--Usage of Function
SELECT dbo.GetFirstWord ('') AS FirstWordofSentence;
SELECT dbo.GetFirstWord (' Space Before') AS FirstWordofSentence;
SELECT dbo.GetFirstWord ('Long Statement') AS FirstWordofSentence;
SELECT dbo.GetFirstWord ('SingleWord') AS FirstWordofSentence;

You can see in the resultset where it will find the very first word of the any sentence.

Source: Pinal Dave

SQL Server Monitoring Management Pack - Download

The SQL Server Management Pack provides the capabilities for Operations Manager 2007 SP1 and R2 to discover SQL Server 2005, 2008, and 2008 R2. It monitors SQL Server components such as database engine instances, databases, and SQL Server agents.

The monitoring provided by this management pack includes performance, availability, and configuration monitoring, performance data collection, and default thresholds. You can integrate the monitoring of SQL Server components into your service-oriented monitoring scenarios.

In addition to health monitoring capabilities, this management pack includes dashboard views, extensive knowledge with embedded inline tasks, and views that enable near real-time diagnosis and resolution of detected issues.

 

Download SQL Server Monitoring Management Pack

Source: microsoft.com

Datetime Function SWITCHOFFSET Example - SQL Server

It returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

What essentially it does is that changes the current offset of the time to any other offset which we defined. Let us see the example of the same.

SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-04:00') 'GetCurrentOffSet-4';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-02:00') 'GetCurrentOffSet-2';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00') 'GetCurrentOffSet+0';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:00') 'GetCurrentOffSet+2';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+04:00') 'GetCurrentOffSet+4';

Now let us check the resultset of the same.

The example above clearly shows that Switch offset does not only change the offset; it also alters the current time. If you look at GetcurrentOffset, it is +5.30; but, you will notice that GetCurrentOffset-2 does not only change the offset to -2. It also changes the time with the appropriate time at Timezone -2.

I suggest that you run the code in SSMS Query Window and observe the code behavior.

Source: Pinal Dave

Fast Track Data Warehouse for SQL Server 2008 in SQL SERVER

Fast Track Data Warehouse is a very unique effort by Microsoft, where few Reference Architectures which offer scalability and reliability to huge database are proposed. Fast Track Datasheet provides some excellent points about this reference architecture. I am listing few of the same here.

• Scale from 4 up to 48 Terabytes using compression capabilities in SQL Server 2008 Enterprise
• Choose from industry-standard hardware from Dell, HP, Bull, IBM, EMC and other leading vendors
• Pre-configured servers, storage and networking, specifically balanced and optimized for warehousing
• Implement an enterprise-class solution for less than one-third the price of a comparable Oracle system
• Fast Track Data Warehouse has a primary emphasis on eliminating potential performance bottlenecks.
• Optimized for sequential IO rather than random IO, each Fast Track Data Warehouse is designed to provide up to 200 MB/s per CPU core

The Fast Track approach derives its strength from the advanced data warehouse enhancements included in SQL Server 2008 Enterprise such as compression, parallel partitioning and star join query optimization

Well, I think it is great effort by Microsoft and I must express deep gratitude to Microsoft for giving me opportunity to learn this unique initiative. I clearly see that in near future, lots of Data Warehousing solutions will be switching to this solution. You can read more about this reference architecture over here. The image used in blog post is taken from MS official site of Fast Trac

Source: Pinal Dave

What the hell is Denali - SQL Server

Denali is code name of SQL Server 2011.

Here is the list of the code name of other versions of SQL Server.

In 1988, Microsoft released its first version of SQL Server. It was developed jointly by Microsoft and Sybase for the OS/2 platform.

* 1993 – SQL Server 4.21 for Windows NT
* 1995 – SQL Server 6.0, codenamed SQL95
* 1996 – SQL Server 6.5, codenamed Hydra
* 1999 – SQL Server 7.0, codenamed Sphinx
* 1999 – SQL Server 7.0 OLAP, codenamed Plato
* 2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
* 2003 – SQL Server 2000 64-bit, codenamed Liberty
* 2005 – SQL Server 2005, codenamed Yukon (version 9.0)
* 2008 – SQL Server 2008, codenamed Katmai (version 10.0)
* 2010 – SQL Server 2008 R2, Codenamed Kilimanjaro (aka KJ)
* Next – SQL Server 2011, Codenamed Denali

Sql Server - Merge Operations – Insert, Update, Delete in Single Execution

MERGE is a new feature that provides an efficient way to do multiple DML operations. In earlier versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions; however, at present, by using the MERGE statement, we can include the logic of such data changes in one statement that even checks when the data is matched and then just update it, and similarly, when the data is unmatched, it is inserted.

One of the most important advantages of MERGE statement is that the entire data are read and processed only once. In earlier versions, three different statements had to be written to process three different activities (INSERT, UPDATE or DELETE); however, by using MERGE statement, all the update activities can be done in one pass of database table.

I have written about these Merge Operations earlier in my blog post over here SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE. I was asked by one of the readers that how do we know that this operator was doing everything in single pass and was not calling this Merge Operator multiple times.

Let us run the same example which I have used earlier; I am listing the same here again for convenience.

--Let’s create Student Details and StudentTotalMarks and inserted some records.
USE tempdb
GO


CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO


INSERT INTO StudentDetails
VALUES(1,'SMITH')
INSERT INTO StudentDetails
VALUES(2,'ALLEN')
INSERT INTO StudentDetails
VALUES(3,'JONES')
INSERT INTO StudentDetails
VALUES(4,'MARTIN')
INSERT INTO StudentDetails
VALUES(5,'JAMES')
GO
 

CREATE TABLE StudentTotalMarks
(
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
 

INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
INSERT INTO StudentTotalMarks
VALUES(3,200)
GO
 

-- Select from Table
SELECT *
FROM StudentDetails
GO
SELECT *
FROM StudentTotalMarks
GO
 

-- Merge Statement
MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO
 

-- Select from Table
SELECT *
FROM StudentDetails
GO
 

SELECT *
FROM StudentTotalMarks
GO
 

-- Clean up
DROP TABLE StudentDetails
GO
DROP TABLE StudentTotalMarks
GO

The Merge Join performs very well and the following result is obtained.

Let us check the execution plan for the merge operator. You can click on following image to enlarge it.

Let us evaluate the execution plan for the Table Merge Operator only.

We can clearly see that the Number of Executions property suggests value 1. Which is quite clear that in a single PASS, the Merge Operation completes the operations of Insert, Update and Delete.

I strongly suggest you all to use this operation, if possible, in your development. I have seen this operation implemented in many data warehousing applications.

Source: Pinal Dave

List All the DMV and DMF on Server in SQL Server

Its a very simple query

SELECT name, type, type_desc
FROM sys.system_objects
WHERE name LIKE 'dm_%'
ORDER BY name

What is AdventureWorks in SQL Server

AdventureWorks is a Sample Database shipped with SQL Server and it can be downloaded from http://codeplex.com site.

AdventureWorks has replaced Northwind and Pubs from the sample database in SQL Server 2005.

The Microsoft team keeps updating the sample database as they release new versions.

Here are some quick links:
AdventureWorks SQL Server 2008 SR4

AdventureWorks 2008R2 November CTP

AdventureWorks for SQL Azure (December CTP)

AventureWorks for SQL Server 2005 SP2A

SQL SERVER – 2008 – Download and Install Samples Database AdventureWorks 2005 – Detail Tutorial

Create Primary Key with Specific Name when Creating Table in SQL Server

There are basically two methods to do so:

Method 1:
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](100) NULL)
GO
ALTER TABLE [dbo].[TestTable] ADD  CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
([ID] ASC)
GO
  


Method 2:

CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](100) NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
([ID] ASC)
)
GO

However it would look very normal to all experienced developers, it can be still confusing to many.

Take DataBase Offline and Online in SQL Server

Its a very tiny script for taking database offline and online.

-- Create Test DB
CREATE DATABASE [myDB]
GO
-- Take the Database Offline
ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Take the Database Online
ALTER DATABASE [myDB] SET ONLINE
GO
-- Clean up
DROP DATABASE [myDB]
GO

ref: Pinal Dave

Change the default value of “Edit Top 200 Rows” in SQL Server 2008

The SQL Server 2005 Management Studio allows us to “Edit” and “View” the whole set of records in a table. But in SQL Server 2008 Management Studio, this flexibility was restricted. SQL Server 2008 by default allows us to select top 1000 records and allows us to edit top 200 records. If our records in a table are more than the default numbers, SQL Server 2008 won’t display those records or allows us to edit those records. We can increase those default value.

Here are the steps…

1. Open SQL Server 2008 Management studio.

2. Right Click on any of the table. It will display the default value.

3. Go to “Tools” > “Option” > “SQL Server Object Explorer” > “Command”.

4. Change the default value to your desired value. Here i am entering “10000″.

5. Now go and check again. The value gets changed.

We successfully changed the default value to our desired value.
Ref : (sujeev.wordpress.com)

10 reasons why to use SQL Server 2008

SQL Server 2008 has many new features and functionality that can ease your query writing and yet it will be powerful. It has many small features that results great but here are some major ones listed out.

Why you should use SQL SERVER 2008........

10.  Plug-in model for SSMS.   SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind.  Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom.

9.  Inline variable assignment.  I often wondered why, as a language, SQL languishes behind the times.  I mean, it has barely any modern syntactic sugar.  Well, in this version, they are at least scratching the the tip of the iceberg. 
Instead of:

DECLARE @myVar int 
SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

8.  C like math syntax.  SET @i += 5.  Enough said.  They finally let a C# developer on the SQL team. 

7.  Auditing.  It's a 10 dollar word for storing changes to your data for later review, debugging or in response to regulatory laws.  It's a thankless and a mundane task and no one is ever excited by the prospect of writing triggers to handle it.  SQL Server 2008 introduces automatic auditing, so we can now check one thing off our to do list.

6.  Compression.  You may think that this feature is a waste of time, but it's not what it sounds like.  The release will offer row-level and page-level compression.  The compression mostly takes place on the metadata.  For instance, page compression will store common data for affected rows in a single place. 
The metadata storage for variable length fields is going to be completely crazy: they are pushing things into bits (instead of bytes).  For instance, length of the varchar will be stored in 3 bits. 
Anyway, I don't really care about space savings - storage is cheap.  What I do care about is that the feature promised (key word here "promises") to reduce I/O and RAM utilization, while increasing CPU utilization.  Every single performance problem I ever dealt with had to do with I/O overloading.  Will see how this plays out.  I am skeptical until I see some real world production benchmarks.

5.  Filtered Indexes.  This is another feature that sounds great - will have to see how it plays out.  Anyway, it allows you to create an index while specifying what rows are not to be in the index.  For example, index all rows where Status != null.  Theoretically, it'll get rid of all the dead weight in the index, allowing for faster queries. 

4.  Resource governor.  All I can say is FINALLY.  Sybase has had it since version 12 (that's last millennium, people).  Basically it allows the DBA to specify how much resources (e.g. CPU/RAM) each user is entitled to.  At the very least, it'll prevent people, with sparse SQL knowledge from shooting off a query with a Cartesian product and bringing down the box.
Actually Sybase is still ahead of MS on this feature.  Its ASE server allows you to prioritize one user over another - a feature that I found immensely useful.

3.  Plan freezing.  This is a solution to my personal pet peeve. Sometimes SQL Server decides to change its plan on you (in response to data changes, etc...).  If you've achieved your optimal query plan, now you can stick with it.  Yeah, I know, hints are evil, but there are situations when you want to take a hammer to SQL Server - well, this is the chill pill.

2.  Processing of delimited strings.   This is awesome and I could have used this feature...well, always.  Currently, we pass in delimited strings in the following manner:

exec sp_MySproc 'murphy,35;galen,31;samuels,27;colton,42'

Then the stored proc needs to parse the string into a usable form - a mindless task.
In 2008, Microsoft introduced Table Value Parameters (TVP). 

CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int) 
DECLARE @myPeeps PeepsType 
INSERT @myPeeps SELECT 'murphy', 35 
INSERT @myPeeps SELECT 'galen', 31 
INSERT @myPeeps SELECT 'samuels', 27 
INSERT @myPeeps SELECT 'colton', 42

exec sp_MySproc2 @myPeeps 

And the sproc would look like this:

CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY) ...

The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc.  Say goodbye to all those string parsing routines.

1. Intellisense in the SQL Server Management Studio (SSMS).  This has been previously possible in SQL Server 2000 and 2005 with use of 3rd party add-ins like SQL Prompt ($195).  But these tools are a horrible hack at best (e.g. they hook into the editor window and try to interpret what the application is doing).

Built-in intellisense is huge - it means new people can easily learn the database schema as they go.

How to avoid SQL Injection

What Is SQL Injection?

SQL injection is security vulnerability, a serious security threat that enables an attacker to execute unauthorized SQL commands by embedding them in the SQL statements by taking advantage of non-validated input in Web applications that attempt to build SQL queries dynamically.

When Does It Occur?

This typically happens in situations where your application accepts user input and builds SQL statements dynamically without a proper input validation mechanism. How? Let s assume there is a login form where the user needs to fill in the user name and the password and then click the Submit button to log in to an application. Suppose the user fills out the form as shown below:

Login: OR =

Password: OR =

The resultant query is:

SELECT userName FROM Users WHERE userName = '' OR ''='' AND Password = '' OR ''=''

This, of course, will always return true.

A smart intruder can inject SQL statements into a SQL query that is built dynamically in your application and can turn the query into the form, as shown below:

SELECT * FROM products WHERE productID = 1 or 1=1

This would always return true, irrespective of the value of the product id. Hence, your data is under threat!

How Do I Prevent It?

The following points highlight the effective measures that can be adopted to prevent SQL injection attacks:

• Prevent unauthorized access to the database and limit the permissions that are granted to the database user account that the application uses.
• Validate user input properly before using it, stripping off the potentially malicious characters.
• Always use parameterized SQL queries and stored procedures rather than building the SQL statements dynamically.
• Avoid displaying the actual database errors or messages to the end users.

Sql Server Stored Procedure Optimization Tips

Here are the tips to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips too, but these are easily applicable accroding to me.

• Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

• Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like

SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method

• Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

• Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')

• Use the sp_executesql stored procedure instead of the EXECUTE statement.
  The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

• Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
• Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
• Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

Sql Server Database Engine Tuning Advisor

The users who like to watch over their database and query performances Database Engine Tunig Advisor (dta) can be helpful. It can be used to compare your sql queries  too.

This msdn tutorial will teach you how to navigate the Database Engine Tuning Advisor GUI, and how to perform some basic tasks with both the GUI and the dta utility. It contains the following lessons:

Lesson 1: Basic Navigation in Database Engine Tuning Advisor

In this lesson, you will familiarize yourself with the new Database Engine Tuning Advisor GUI and learn how to set display options and layout.

Lesson 2: Using Database Engine Tuning Advisor

In this lesson, you will learn how to perform basic tuning tasks with the Database Engine Tuning Advisor GUI.

Lesson 3: Using the dta Command Prompt Utility

In this lesson, you learn how to start the dta command prompt utility and how to run some simple tuning commands.     

SQL Server - Find Tables With Foreign Key Constraint

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Sql Server Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')