SQL Question and Answers

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

2013-05-15T03:19:00.001-07:00

March 5, 2007 by pinaldave

When create or alter SQL object like Stored Procedure, User Defined Function in Query Analyzer, it is created with following SQL commands prefixed and suffixed. What are these – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF?

SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGO--SQL PROCEDURE, SQL FUNCTIONS, SQL OBJECTGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO

ANSI NULL ON/OFF:
This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.

QUOTED IDENTIFIER ON/OFF:
This options specifies the setting for usage of double quotation. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.

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

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 4 of 31

2013-05-15T03:17:00.002-07:00

July 4, 2011 by pinaldave

SQL Server Interview Questions and Answers

Print Book Available (207 Pages) | Sample Chapters

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

What is the Difference between a Function and a Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

What is subquery? Explain the Properties of a Subquery?

Subqueries are often referred to as sub-selects as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A subquery is executed by enclosing it in a set of parentheses. Subqueries are generally used to return a single row as an atomic value although they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. This implies that a subquery SELECT statement can stand alone, and it does not depend on the statement in which it is nested. A subquery SELECT statement can return any number of values and can be found in the column list of a SELECT statement, and FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A subquery can also be used as a parameter to a function call. Basically, a subquery can be used anywhere an expression can be used. (Read more here)

What are Different Types of Join?

Cross Join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Inner Join

A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.

Outer Join

A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:

Left Outer Join: In Left Outer Join, all the rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.

Right Outer Join: In Right Outer Join, all the rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

Full Outer Join: In Full Outer Join, all the rows in all joined tables are included, whether they are matched or not.

Self Join

This is a particular case when one table joins to itself with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join. (Read more here)

What are Primary Keys and Foreign Keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental aspect of all keys and constraints. A table can have only one primary key.

Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.

What is User-defined Functions? What are the types of User-defined Functions that can be created?

User-defined Functions allow defining its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

Different Types of User-Defined Functions created are as follows:

Scalar User-defined Function

A scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages.

Inline Table-Value User-defined Function

An Inline table-value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-Statement Table-Value User-defined Function

A multi-statement table-value user-defined function returns a table, and it is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command, you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior encountered while using a stored procedure which can also return record sets. (Read here for example)

List of all the Interview Questions and Answers Series blogs

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

SQLAuthority News – Sharding or No Sharding of Database – Working on my Weekend Project

2013-05-15T03:16:00.001-07:00

May 7, 2013 by pinaldave

Recently I came across situation where database sharding was once again a suggested solution by architectures. Everytime I hear the word sharding I remember my earlier article about NuoDB on Shard No More – An Innovative Look at Distributed Peer-to-peer SQL Database. Sharding requires developers to think about things like rollbacks, constraints, and referential integrity across tables within their applications when these types of concerns are best handled by the database. It also makes other common operations such as joins, searches, and memory management very difficult.

Each NuoDB database consists of at least three or more processes that enable a single database to run across multiple hosts. These processes include a Broker, a Transaction Engine and a Storage Manager. Brokers are responsible for connecting client applications to Transaction Engines and maintain a global view of the network to keep track of the multiple Transaction Engines available at any time. Transaction Engines are in-memory processes that client applications connect to for processing SQL transactions. Storage Managers are responsible for persisting data to disk and serving up records to the Transaction Managers if they don’t exist in memory.

Here is my question to you all – do you ever face a situation of sharding of your database is required? When is sharding required not a good solution?

I have downloaded NuoDB today and going to build a small application which I can play with to build a scenario where sharding is no longer required. Would you be interesting in joining me with this exercise? I would like to work along with you to build such a scenario. Download NuoDB and let us start building something interesting.

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

Pessimistic Locking?

2013-04-25T00:03:00.002-07:00

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.
(Read more here http://bit.ly/sql-interview)

Which are the Important Points to Note when Multilanguage Data is Stored in a Table?

2013-04-11T01:15:00.001-07:00

Which are the Important Points to Note when Multilanguage Data is Stored in a Table?

There are two things to keep in mind while storing unicode data. First, the column must be of unicode data type (nchar, nvarchar, ntext). Second, the value must be prefixed with N while insertion. For example,

INSERT INTO table (Hindi_col) values (N’hindi data’)

(Read more here http://bit.ly/sql-interview)

What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?

2013-04-03T03:35:00.002-07:00

In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users. (Read more here http://bit.ly/sql-interview)

Explain Few of the New Features of SQL Server 2008 Management Studio

2013-04-03T03:32:00.004-07:00

SQL Server 2008 Microsoft has upgraded SSMS with many new features as well as added tons of new functionalities requested by DBAs for long time.

A few of the important new features are as follows:

IntelliSense for Query Editing
Multi Server Query
Query Editor Regions
Object Explorer Enhancements
Activity Monitors

(http://bit.ly/sql-interview)

Explain Few of the New Features of SQL Server 2008 Management Studio

Create a SQL Login User in SQL Server 2008

2013-03-22T01:44:00.001-07:00

This article will give a walkthrough on creating a SQL Login user.

Step 1
Login to SQL Server 2008 instance using windows authentication

Step 2

Right click on server instance name and select properties.

Step 3

Property window will be open. In that select the Security tab.

In server authentication tab you can see your SQL Server in configured for

Windows authentication mode
SQL Server and Windows Authentication mode

So if you want to enable SQL User login to SQL Server then select checked mixed mode here .

And then click ok. Now SQL Server is configured for mixed mode login.

Step 4

Now to create SQL LOGIN, right click on Security tab and select New and then Login .

Step 5

When you click on Login you will get the below window

Give the Login Name

Select SQL Server Authentication and provide your desired password

Disable Enforce password policy

Select default database

Leave default language and click OK to create a new SQL Server login

Now in object explorer you can see User1 login .

Now at time of connecting to SQL Server login User1 can be used.

SQL SERVER – Retrieve SQL Server Installation Date Time

2013-03-20T00:06:00.002-07:00

July 5, 2012 by pinaldave

I have been asked this question a number of times and my answer always has been “Search online and you will find the answer.” Every single time someone follows my answer, he finds the accurate answer in just a few clicks. However, this question is getting very popular nowadays, so I decided to answer this question through a blog post.

I usually prefer creating my own T-SQL script but in today’s case, I have taken the script from the Web. I have seen this script in so many places that I do not know who the original creator is, so I’m not sure who should get credit for the script.

Question: How do I retrieve SQL Server Installation date?

Answer: Run the following query and it will give you the date of SQL Server Installation.

SELECT create_date
FROM 
sys.server_principals
WHERE sid = 0x010100000000000512000000

Question: I have installed SQL Server Evaluation version. How do I know what is the expiry date for it?

Answer: SQL Server evaluation period lasts for 180 days. The expiration date is always 180 days from the initial installation. The following query will give the expiration date of evaluation version:

-- Evaluation Version Expire DateSELECT create_date 
AS InstallationDate,DATEADD(DD, 180, create_date) AS 'Expiry Date'FROM 
sys.server_principals
WHERE sid = 0x010100000000000512000000
GO

I believe there is a way to do this using registry, but I have not explored it personally. Now as what I’ve said earlier, there are many different blog posts on this subject. Let me list a few which I really enjoyed to read as they shared a few more insights about this subject:

Retrieving SQL Server 2012 Evaluation Period Expiry Date

How to find the Installation Date for an Evaluation Edition of SQL Server

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

What is Optimistic Locking ?

2013-03-20T00:03:00.003-07:00

Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn’t changed before you write the record back. If the record is dirty (i.e. different version to yours), then you abort the transaction and the user can re-start it.
(Read more here http://bit.ly/sql-interview)

How to backup and restore database on Microsoft SQL Server 2005

2013-02-26T03:43:00.004-08:00

Introduction

This post will give you a brief introduction of SQL Server backup. As we know, SQL Server backup software is springing up nowadays. Take EaseUS Todo Backup Advanced Server as an example, it’s an all-in-one SQL server backup software for MS SQL Server 2000/2005/2008, exchange server and server 2000/2003/2008, and provides proven database backup solution for Server administrators.

But in the example below, I will show a step-by-step database backup from SQL Server 2005 and restore the database to another SQL Server 2005 Express Edition using Microsoft SQL Server Management Studio.

Step-by-step

Backup a database.

Now I will backup AdventureWorks database on BKKSQL2005 which runs Microsoft SQL Server 2005 to a file.

Connect to source server. Open Microsoft SQL Server Management Studio and connect to BKKSQL2005.
Right-click on the AdventureWorks database. Select Tasks -> Backup…
On Back Up Database window, you can configure about backup information. If you’re not familiar these configurations, you can leave default values. Here are some short descriptions.
1. Database – a database that you want to backup.
2. Backup type – you can select 2 options: Full and Differential. If this is the first time you backup the database, you must select Full.
3. Name – Name of this backup, you can name anything as you want.
4. Destination – the file that will be backup to. You can leave as default. Default will backup to “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
5. Click OK to proceed backup.
Wait for a while and you’ll see a pop-up message when backup is finished.
Browse to the destination, you’ll see a backup file (.bak format) which you can copy to other server for restore in the next step. Default backup directory is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.

Restore the database.

Next, I will restore the AdventureWorks database from a file that I’ve created above to BK01BIZ001 which runs Microsoft SQL Server Express Edition.

Copy the backup file from source server to destination server. I’ve copied into the same directory as source server.
Connect to destination server. Open Microsoft SQL Server Management Studio Express and connect to BK01BIZ001.
Right-click on Databases. Select Restore Database…
Restore Database window appears. On Source for restore, select From device and click [...] buttton to browse file.
On Specify Backup, ensure that Backup media is “File” and click Add.
On Locate Backup File, select the backup file. This is the backup file that was created in Backup a database section and was copied to this server. Click OK. OK.
Back to Restore Database window.
1. On Destination for restore, select “AdventureWorks”.
  Note: If you haven’t added the backup file on Source before (step 4-6), you won’t see the database name on Destination.
2. On Source for restore, check the box in front of the backup name (in Restore column).
3. Click OK.
Wait until restore finish and there’ll be a pop-up message notify.
Now you’ll see the restored database on the destination SQL Server.

SQL Authority News – Download SQL Server Data Type Conversion Chart

2013-02-26T02:57:00.001-08:00

December 9, 2012 by pinaldave

Datatypes are very important concepts of SQL Server and there are quite often need to convert them from one datatypes to another datatype. I have seen that deveoper often get confused when they have to convert the datatype. There are two important concept when it is about datatype conversion.

Implicit Conversion: Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function.
Explicit Conversions: Explicit conversions are those conversions that require the CAST or CONVERT function to be specified.

What it means is that if you are trying to convert value from datetime2 to time or from tinyint to int, SQL Server will automatically convert (implicit conversation) for you. However, if you are attempting to convert timestamp to smalldatetime or datetime to int you will need to explicitely convert them using either CAST or CONVERT function as well appropriate parameters.

Let us see a quick example of Implict Conversion and Explict Conversion.

Implicit Conversion:

Explicit Conversion:

You can see from above example that how we need both of the types of conversion in different situation. There are so many different datatypes and it is humanly impossible to know which datatype require implicit and which require explicit conversion. Additionally there are cases when the conversion is not possible as well.

Microsoft have published a chart where the grid displays various conversion possibilities as well a quick guide.

Download SQL Server Data Type Conversion Chart

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

SQL SERVER – Delete Duplicate Records – Rows

2013-02-22T03:32:00.000-08:00

March 1, 2007 by pinaldave

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table inexample is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Watch the view to see the above concept in action:

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

SQL SERVER – 2008 – Introduction to Table-Valued Parameters with Example

2013-02-22T02:17:00.001-08:00

August 31, 2008 by pinaldave

Table-Valued Parameters is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow steps shown below:

Create a table type and define the table structure
Declare a stored procedure that has a parameter of table type.
Declare a table type variable and reference the table type.
Using the INSERT statement and occupy the variable.
We can now pass the variable to the procedure.

For Example,

Let’s create a Department Table and pass the table variable to insert data using procedure. In our example we will create Department table and afterward we will query it and see that all the content of table value parameter is inserted into it.

Department:

CREATE TABLE Department(DepartmentID INT PRIMARY KEY,DepartmentName VARCHAR(30)
)

GO

1. Create a TABLE TYPE and define the table structure:

CREATE TYPE DeptType AS TABLE(DeptId INT, DeptName VARCHAR(30)
);

GO

2. Declare a STORED PROCEDURE that has a parameter of table type:

CREATE PROCEDURE InsertDepartment@InsertDept_TVP DeptType READONLY

AS
INSERT INTO Department(DepartmentID,DepartmentName)SELECT * FROM @InsertDept_TVP;

GO

Important points to remember :

- Table-valued parameters must be passed as READONLY parameters to SQL routines. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

- You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

3. Declare a table type variable and reference the table type.

DECLARE @DepartmentTVP AS DeptType;

4. Using the INSERT statement and occupy the variable.

INSERT INTO @DepartmentTVP(DeptId,DeptName)VALUES (1,'Accounts'),
(2,'Purchase'),
(3,'Software'),
(4,'Stores'),
(5,'Maarketing');

5. We can now pass the variable to the procedure and Execute.

EXEC InsertDepartment @DepartmentTVP;GO

Let’s see if the Data are inserted in the Department Table

Conclusion:

Table-Valued Parameters is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table or passing so many parameters. It helps in using complex business logic in single routine. They reduce Round Trips to the server making the performance better.

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

What is CHECK Constraint?

2013-02-20T02:22:00.000-08:00

A CHECK constraint is used to limit the values that can be

placed in a column. The check constraints are used to

enforce domain integrity.

(Read more here http://bit.ly/sql-interview)

SQL SERVER – Stored Procedure Optimization Tips – Best Practices

2013-02-12T22:23:00.001-08:00

February 16, 2010 by pinaldave

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.

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.ProcNameAS
SET NOCOUNT ON;--Procedure code hereSELECT column1 FROM dbo.TblTable1-- Reset SET NOCOUNT to OFFSET 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 ofSELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:EXEC dbo.MyProc -- Preferred method
--Instead ofEXEC 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 sysobjectsWHERE 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 = 25SET @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 hereEND TRYBEGIN CATCH--Your error handling code goes hereEND CATCH

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

SQL SERVER – Stored Procedure and Transactions

2013-02-12T22:20:00.001-08:00

June 2, 2010 by pinaldave

I just overheard the following statement – “I do not use Transactions in SQL as I use Stored Procedure“.

I just realized that there are so many misconceptions about this subject. Transactions has nothing to do with Stored Procedures. Let me demonstrate that with a simple example.

USE tempdb
GO-- Create 3 Test TablesCREATE TABLE TABLE1 (ID INT);CREATE TABLE TABLE2 (ID INT);CREATE TABLE TABLE3 (ID INT);GO-- Create SPCREATE PROCEDURE TestSPAS
INSERT INTO TABLE1 (ID)VALUES (1)INSERT INTO TABLE2 (ID)VALUES ('a')INSERT INTO TABLE3 (ID)VALUES (3)GO-- Execute SP
-- SP will error outEXEC TestSP
GO-- Check the Values in TableSELECT *FROM TABLE1;SELECT *FROM TABLE2;SELECT *FROM TABLE3;GO

Now, the main point is: If Stored Procedure is transactional then, it should roll back complete transactions when it encounters any errors. Well, that does not happen in this case, which proves that Stored Procedure does not only provide just the transactional feature to a batch of T-SQL.

Let’s see the result very quickly.

It is very clear that there were entries in table1 which are not shown in the subsequent tables. If SP was transactional in terms of T-SQL Query Batches, there would be no entries in any of the tables. If you want to use Transactions with Stored Procedure, wrap the code around with BEGIN TRAN and COMMIT TRAN.

The example is as following.

CREATE PROCEDURE TestSPTranAS
BEGIN TRAN
INSERT INTO TABLE1 (ID)VALUES (11)INSERT INTO TABLE2 (ID)VALUES ('b')INSERT INTO TABLE3 (ID)VALUES (33)COMMITGO-- Execute SPEXEC TestSPTran
GO-- Check the Values in TablesSELECT *FROM TABLE1;SELECT *FROM TABLE2;SELECT *FROM TABLE3;GO-- Clean upDROP TABLE Table1DROP TABLE Table2DROP TABLE Table3
GO

In this case, there will be no entries in any part of the table. What is your opinion about this blog post? Please leave your comments about it here.

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

SQL SERVER – Effect of SET NOCOUNT on @@ROWCOUNT

2013-02-12T03:10:00.001-08:00

December 2, 2011 by pinaldave

Today I had very interesting experience when I was presenting on SQL Server. While I was presenting the session when I ran query SQL Server Management Studio returned message like (8 row(s) affected) and (2 row(s) affected) etc. After a while at one point, I started to prove usage of @@ROWCOUNT function.

As I was in hurry, I quickly wrote very similar to following example on my screen. (The real query is much complected, but for the purpose of this post I am simplifing it here.

DECLARE @table TABLE(col1 INT IDENTITY(1,1), col2 INT)INSERT INTO @table(col2) VALUES (2932),(142),(352),(342)SET NOCOUNT ON
SELECT * FROM @tableSELECT @@rowcountSET NOCOUNT OFF

Before I execute the script nearly 10 hands went up in audience. This was bit surprise to me as I do not know why they all got alerted. I assumed that there should be something wrong with either project, screen or my display. I quickly asked to one person what is wrong, he pointed out that I will not get right results in @@ROWCOUNT as I am using immediately before SET NOCOUNT ON. When I asked the reason they told me that because any statement when ran right before @@ROWCOUNT resets its value.

Let us stop here for a second – do YOU think same way as well?

If yes, run above code and check your result in following image.

If you believed the same I guess we learned something new. I have no problem confessing I learn something new every single time, I learn something new. However, I was bit surprised with this common myth of SET NOCOUNT ON and @@ROWCOUNT.

Are there any other common myths – if yes, do share here with your comment. If I think it is interesting, I will publish it this blog with due credit and you can win my new SQL Server Interview Questions and Answers book as well.

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

How to Install SQL Server 2008 - A Step by Step Guide

2013-01-24T04:31:00.000-08:00

A Step by Step guide to installing SQL Server 2008 simply and successfully with no prior knowledge
Developers and system administrators will find this installation guide useful, as will seasoned DBAs. It will teach you the basics required for a typical, problem-free installation of SQL Server 2008, allowing you to add other components later if you wish.
Remember to install the .Net Framework 3.5 Before you start the installation, you’ll need to install the .Net 3.5 Framework. This comes pre-installed on Windows 2008 Server, but for earlier versions of Windows, you’ll need to install it first. This is a straightforward pre-requisite and is usually included as part of the SQL Server 2008 installation. However, if you don’t know how to do this, or for some reason you need to download it, check out the guide Installing .Net Framework 3.5 for SQL Server 2008.
Once this Framework in installed you can commence the installation of SQL Server 2008.
STEP 1 : Copy the installation files First off I’d recommend you copy the entire directory structure from the SQL Server 2008 installation disc to the C: drive of the machine you are going to install it on.
Although this means you need to grab a cup of coffee whilst it’s copying, this has three advantages:

It makes the installation process much faster than running it from CD/DVD once it gets started.
It allows you to easily add or remove components later, without having to hunt around for the CD/DVD.
If your media is damaged and a file won’t copy, you get to find out now, rather than halfway through the installation.

Here’s what my system looks like after the copy:
STEP 2 : Setup.exe Double click on the setup.exe file.
After a few seconds a dialog box appears:
This will disappear from the screen and then the main installation page appears:
STEP 3 : SQL Server Installation Center
Click on the Installation hyperlink on the left hand side of the screen:
STEP 4 : SQL Server Installation Center Click on the "New Server stand-alone installation" link on the right side of the screen:
The following dialog appears on the screen whilst the install program prepares for installation:
After a minute or so (the timing will vary according to your system), the following screen appears:
STEP 5 (optional) :
If any checks have failed, click on the Show details button or "View detailed report link" to find out the cause, correct it, then click on the Re-run button to perform the checks again. STEP 6 : Product key
If all checks have passed, click on the OK button. After a few moments, the option to select the edition and to enter the license key (or “product key”) will appear. Note that the product key box may already be populated, depending on which edition you have. Don’t enter the product key we’ve shown here, it won’t work on your system!:
STEP 7 : License Terms Enter the product key into the box, or choose the free edition if you're evaluating SQL Server 2008, and click on the Next button:
Click in the "I accept the license terms" check box, then click on the Next button again. STEP 8 : Setup Support Files
The following screen appears; click on the Install button:
The following screen will appear whilst Windows Installer prepares itself for the installation. This will take a short while:
After 30 seconds or so the dialog appears again:
STEP 9 : Setup Support Rules
If all is well, the following screen appears:
Click on the Next button again. STEP 10 : Feature Selection
Select the features you want to install.
At a minimum, the following are useful (I'd argue essential), but what you need will depend on your needs:
Click on the Next button. STEP 11 : Instance Configuration
After a short while the following screen appears:
For most installations, keep the default settings.
Click on the Next button. STEP 12 : Disk Space Requirements
This screen just tells you if you have sufficient disk space on the drive you’re installing to, and what’s going to be installed where.
Click on Next.

STEP 13 : Server Configuration
This step allows you to set up the service accounts that will be used to run SQL Server. If you have created Windows NT or Active Directory accounts for use with services, use these. If not, then just to get the installation up and working, use the built-in Network Service account for all three services listed (this account does not require a password).
This allows SQL Server to start up after installation. However, it can be easily changed later to another account through the Services applet (Control Panel -> Administrator Tools -> Services):
In addition, remember to change the Startup Type to Automatic, for all three services. This automatically starts the SQL Server database engine, SQL Agent and SQL Browser services when the server is re-booted. The first service runs the SQL Server database engines executable process. The other two services allow scheduled jobs to run after installation (and after a re-boot), and allow the SQL Server to be found by clients on the network.
Do not worry about changing the collation tab, unless there is a specific requirement for anything other than the default collation sequence. Finally, click on Next. STEP 14 : Database Engine Configuration – Account Provision
This screen allows you to set up database engine security.
Change the Authentication Mode to Mixed Mode unless you are certain you only need Windows-only authentication.

Many third party applications rely on SQL Server logins to operate correctly, so if you are setting up a server for a third party application, rather than one developed in-house, enabling Mixed Mode authentication is a good idea.

If you pick Mixed Mode security, you must also enter a password for the sysadmin account (sa). Enter and confirm a secure password for the sa account and keep it somewhere safe. Do not give it to any one you do not want to have access to the SQL Server.
Note that you MUST also provide a Windows NT account on the local machine as a SQL Server administrator. If you do not want Windows system administrators to be able walk up to the box and login to SQL Server, create a new, local, dummy Windows user and add this account instead. Otherwise, add in the local administrator account, or your own Windows account on the domain in which the SQL Server will reside. STEP 15 : Database Engine Configuration – Data Directories
Click on the Data Directories tab.
Change the directories to specify which drives in your system will be used for the various types of database files. Generally it’s advisable to put the User database directory and User log directory on separate physical drives for performance, but it will depend on how Windows has been configured and how many disk drives you have available.
If you are installing on a single drive laptop or desktop, then simply specify:

Data root directory	C:\Program Files\Microsoft SQL Server
User database directory	C:\Data
User log directory	C:\Logs
Temp DB directory	C:\TempDB
Temp Log directory	C:\TempDB
Backup directory	C:\Backups

Do not click on the FILESTREAM tab unless you know you need to change these options, as it is not generally required for most installations, but can easily be changed by using sp_configure 'filestream_access_level', ''after SQL Server has been installed. Click on Next.
STEP 16 : Error Usage Reporting This screen simply asks if you want to send error information to Microsoft and can safely be skipped if you do not want to share any information.

Click boxes if you want to help Microsoft help you.
Click on Next again… STEP 16 : Installation Rules
This screen simply checks if there are any processes or other installations running which will stop the installation of SQL Server 2008.
Click on Next again – you’re almost ready to install:
STEP 17 : Ready to Install This screen summarises what you are about to install and gives you a last chance to cancel or change anything that’s wrongly configured:
Check that what’s being installed is what you want and then click on Install when you’re sure you want to start the installation process:
Installation Progress SQL Server 2008 will now install. How long it takes depends on the speed of your machine, what load it’s under, the installation media (CD is slower) and what you’ve chosen to install.
…More Installation Progress

... and Finally
Finally, the installation will complete:
...and the following dialog box will appear:
Click on OK, the machine will NOT reboot.
The following will appear:
…followed by:
Click on the Next button again... STEP 18 : Installation Complete
The following screen appears:
It may be worth clicking on the installation log at the top of the screen to check everything’s gone as expected. Not that this is MUCH smaller than the usual SQL Server installation log files of old. Finally, click on the Close button. The following dialog will appear:
Click on OK – your server will NOT re-boot at this point.
The dialog box will disappear and you will be returned to the Installation Center:
Click on the Close button (the “x”) in the top right of the screen.
Finally, manually reboot your machine to complete the SQL Server 2008 installation. Top Tips :
How to check that SQL Server 2008 has installed correctly Here are a short number of post-installation checks which are useful to perform after re-booting your new SQL Server. You don’t have to run these, and there are other ways to check, but they are very useful for non-DBAs to be sure that the installation is basically sound and a connection can be made to the new SQL Server before handing it over to someone else.
Check 1: Has the SQL Server Service Started? Check SQL Server 2008 has started.
Check 2: Does Management Studio Work?
Check Management Studio works by firing it up.
Click on NO when you see this dialog box:

Check 3: Can you run a basic query against the new SQL Server? Check SQL Server works by running a simple query from Management Studio:
Enter the query shown below and hit F5 to run it:
Check 4: Is SQL Server Agent Running?
Check SQL Server Agent is running for scheduled jobs. There should be a green arrow next to the SQL Server Agent database symbol (it’s small, you might have to look hard):
Check 5: Can SQL Server be seen from the Network? Check that the new SQL Server can be seen from another SQL Server on the same domain by running isql –L (or osql –L):
If you can’t see the new SQL Server in this list, check that the SQL Server Browser service is started on the machine where you have just installed SQL Server. Check 6: Has the TCP/IP network protocol library been enabled on the server?
If the browser service is started but you still cannot connect to the server, click on Start ->Programs -> SQL Server 2008 -> SQL Server Configuration Manager (on the server where SQL Server’s just been installed)
The SQL Server Configuration Manager window opens.
Click on the SQL Server Network Configuration node and expand it. In the example below, we have MSSQLSERVER (a base instance of SQL Server), and SQLEXPRESS showing as installed.
If in doubt, click on Protocols for MSSQLSERVER.
In the above screenshot, the TCP/IP network protocol library is disabled. We need to enable it in order that remote servers can talk to the newly installed SQL Server.

A word of explanation : In most installations, Named Pipes can be ignored, unless there is a requirement for it. In virtually all environments, VIA can also be ignored as this protocol requires a special network card. Shared memory is the “local” protocol that SQL Server uses when talking to a client application on the same server as itself, for example when SQL Server Management Studio connects to it. It is usually best to leave this enabled.

You will need the TCP/IP protocol enabled if you need to connect to your new SQL Server from a remote client or another server via TCP/IP, which is what most networks use.
If it shows as DISABLED (above), double click on the TCP/IP protocol line, and the following window will appear:
Ensure that Enabled is set to Yes, and click on OK.
The following warning will appear:
Click on OK, and you will be returned to the Configuration Manager window, where TCP/IP will now be shown as enabled:
Go back to the Services applet, and re-start the MSSQLSERVER service so that the TCP/IP protocol can be used to connect to your new SQL Server. Then try to connect to it again from a remote machine.
If you have experienced problems with the previous connectivity tests, you should now be able to repeat at least some of them successfully.

SQL SERVER – Fix: Error: 10920 Cannot drop user-defined function. It is being used as a resource governor classifier

2013-01-20T22:46:00.003-08:00

June 5, 2012 by pinaldave

If you have not read my SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor yesterday’s detailed primer on Resource Governor, I suggest you go ahead and read it before continuing this article. After reading the article the very first email I received was as follows:

“Pinal,

I configured resource governor on my development server and it worked fine with tests I ran. After doing some tests, I decided to remove the resource governor and as a first step I disabled it however, I was not able to drop the classification function during the process of the clean up. It was continuously giving me following error.

Msg 10920, Level 16, State 1, Line 1
Cannot drop user-defined function myudfname. It is being used as a resource governor classifier.

Would you please give me solution?”

The original email was really this short and there is no other information. I am glad he has done experiments on development server and not on the production server. Production server must not be the playground of the experiments. I think I have covered the answer of this error in an earlier blog post.

If the user disables the Resource Governor it is still not possible to drop the function because it can be enabled again and when enabled it can still use the same function. Here is the simple resolution of the how one can drop the classifier function (do this only if you are not going to use the function).

The reason the classifier function can’t be dropped because it is associated with resource governor. Create a new classified function for your resource governor or just assign NULL as described in the following T-SQL Script and you will be able to drop the function without error.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)GOALTER RESOURCE GOVERNOR DISABLE
GODROP FUNCTION dbo.UDFClassifier
GO

I am glad that user asked me question instead of doing something radically different, which can leave the server in the unusable state. I am aware of this only method to avoid this error. Is there any better way to achieve the same?

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

SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor

2013-01-20T22:43:00.002-08:00

June 4, 2012 by pinaldave

Let us jump right away with question and answer mode.

What is resource governor?

Resource Governor is a feature which can manage SQL Server Workload and System Resource Consumption. We can limit the amount of CPU and memory consumption by limiting /governing /throttling on the SQL Server.

Why is resource governor required?

If there are different workloads running on SQL Server and each of the workload needs different resources or when workloads are competing for resources with each other and affecting the performance of the whole server resource governor is a very important task.

What will be the real world example of need of resource governor?

Here are two simple scenarios where the resource governor can be very useful.

Scenario 1: A server which is running OLTP workload and various resource intensive reports on the same server. The ideal situation is where there are two servers which are data synced with each other and one server runs OLTP transactions and the second server runs all the resource intensive reports. However, not everybody has the luxury to set up this kind of environment. In case of the situation where reports and OLTP transactions are running on the same server, limiting the resources to the reporting workload it can be ensured that OTLP’s critical transaction is not throttled.

Scenario 2: There are two DBAs in one organization. One DBA A runs critical queries for business and another DBA B is doing maintenance of the database. At any point in time the DBA A’s work should not be affected but at the same time DBA B should be allowed to work as well. The ideal situation is that when DBA B starts working he get some resources but he can’t get more than defined resources.

Does SQL Server have any default resource governor component?

Yes, SQL Server have two by default created resource governor component.

1) Internal –This is used by database engine exclusives and user have no control.

2) Default – This is used by all the workloads which are not assigned to any other group.

What are the major components of the resource governor?

Resource Pools
Workload Groups
Classification

In simple words here is what the process of resource governor is.

Create resource pool
Create a workload group
Create classification function based on the criteria specified
Enable Resource Governor with classification function

Let me further explain you the same with graphical image.

Is it possible to configure resource governor with T-SQL?

Yes, here is the code for it with explanation in between.

Step 0: Here we are assuming that there are separate login accounts for Reporting server and OLTP server.

/*-----------------------------------------------
Step 0: (Optional and for Demo Purpose)
Create Two User Logins
1) ReportUser, 2) PrimaryUser
Use ReportUser login for Reports workload
Use PrimaryUser login for OLTP workload
-----------------------------------------------*/

Step 1: Creating Resource Pool

We are creating two resource pools. 1) Report Server and 2) Primary OLTP Server. We are giving only a few resources to the Report Server Pool as described in the scenario 1 the other server is mission critical and not the report server.

-----------------------------------------------
-- Step 1: Create Resource Pool
-----------------------------------------------
-- Creating Resource Pool for Report ServerCREATE RESOURCE POOL ReportServerPoolWITH( MIN_CPU_PERCENT=0,MAX_CPU_PERCENT=30,MIN_MEMORY_PERCENT=0,MAX_MEMORY_PERCENT=30)GO-- Creating Resource Pool for OLTP Primary ServerCREATE RESOURCE POOL PrimaryServerPoolWITH( MIN_CPU_PERCENT=50,MAX_CPU_PERCENT=100,MIN_MEMORY_PERCENT=50,MAX_MEMORY_PERCENT=100)GO

Step 2: Creating Workload Group

We are creating two workloads each mapping to each of the resource pool which we have just created.

-----------------------------------------------
-- Step 2: Create Workload Group
-----------------------------------------------
-- Creating Workload Group for Report ServerCREATE WORKLOAD GROUP ReportServerGroup
USING ReportServerPool ;GO-- Creating Workload Group for OLTP Primary ServerCREATE WORKLOAD GROUP PrimaryServerGroup
USING PrimaryServerPool ;GO

Step 3: Creating user defined function which routes the workload to the appropriate workload group.

In this example we are checking SUSER_NAME() and making the decision of Workgroup selection. We can use other functions such as HOST_NAME(), APP_NAME(), IS_MEMBER() etc.

-----------------------------------------------
-- Step 3: Create UDF to Route Workload Group
-----------------------------------------------CREATE FUNCTION dbo.UDFClassifier()RETURNS SYSNAME
WITH SCHEMABINDINGAS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'ReportUser')SET @WorkloadGroup = 'ReportServerGroup'ELSE IF (SUSER_NAME() = 'PrimaryUser')SET @WorkloadGroup = 'PrimaryServerGroup'ELSE
SET @WorkloadGroup = 'default'RETURN @WorkloadGroupENDGO

Step 4: In this final step we enable the resource governor with the classifier function created in earlier step 3.

-----------------------------------------------
-- Step 4: Enable Resource Governer
-- with UDFClassifier
-----------------------------------------------ALTER RESOURCE GOVERNORWITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);GOALTER RESOURCE GOVERNOR RECONFIGUREGO

Step 5: If you are following this demo and want to clean up your example, you should run following script. Running them will disable your resource governor as well delete all the objects created so far.

-----------------------------------------------
-- Step 5: Clean Up
-- Run only if you want to clean up everything
-----------------------------------------------ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)GOALTER RESOURCE GOVERNOR DISABLE
GODROP FUNCTION dbo.UDFClassifier
GODROP WORKLOAD GROUP ReportServerGroup
GODROP WORKLOAD GROUP PrimaryServerGroup
GODROP RESOURCE POOL ReportServerPool
GODROP RESOURCE POOL PrimaryServerPool
GOALTER RESOURCE GOVERNOR RECONFIGUREGO

I hope this introductory example give enough light on the subject of Resource Governor. In future posts we will take this same example and learn a few more details.

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

SQL SERVER – A List of Various SQL Server RTM and Service Pack Number

2013-01-20T22:35:00.001-08:00

January 18, 2013 by pinaldave

A common question I receive is that how do user know which version user is using and what is the latest version available for the product. Here is something at this beginning of the year, check with your production server. See if you are using the latest version of the product or if you need to upgrade. If you need to upgrade you might want to plan it with your entire team. As I get this question often here is something I have already built and kept it with me. As soon as I receive an email with the question about the version, I right away send it to them.

Now onwards as I have blogged about this subject, in future question, I will like to this blog post. Please feel free to add more details if I have missed any.

Here are two of the methods, you can use to identify your SQL Server version:

Open SSMS and in a New Query Window run following T-SQL code.

1) SELECT@@version

2) SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The above query will return the version details of installed SQL Server.

Here is the list of the all the available RTM and Service Pack Numbers.

SQL Server 2012 Versions

Release	Product Version
SQL Server 2012 Service Pack 1	11.00.3000.00
SQL Server 2012 RTM	11.00.2100.60

SQL Server 2008 R2 Versions

Release	Product Version
SQL Server 2008 R2 Service Pack 1	10.50.2500.0
SQL Server 2008 R2 RTM	10.50.1600.1

SQL Server 2008 Versions

Release	Product Version
SQL Server 2008 Service Pack 3	10.00.5500.00
SQL Server 2008 Service Pack 2	10.00.4000.00
SQL Server 2008 Service Pack 1	10.00.2531.00
SQL Server 2008 RTM	10.00.1600.22

SQL Server 2005 Versions

Release	Product version
SQL Server 2005 Service Pack 4	9.00.5000.00
SQL Server 2005 Service Pack 3	9.00.4035
SQL Server 2005 Service Pack 2	9.00.3042
SQL Server 2005 Service Pack 1	9.00.2047
SQL Server 2005 RTM	9.00.1399

SQL Server 2000 Versions

Release	Product version
SQL Server 2000 Service Pack 4	8.00.2039
SQL Server 2000 Service Pack 3	8.00.760
SQL Server 2000 Service Pack 3	8.00.760
SQL Server 2000 Service Pack 2	8.00.534
SQL Server 2000 Service Pack 1	8.00.384
SQL Server 2000 RTM	8.00.194

This method will help us find edition of the relational database. In future blog posts we will see the methods to identify the version of the Business Intelligence Services.

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

What is Collation?

2013-01-20T22:30:00.002-08:00

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence with options for specifying case sensitivity, accent marks, Kana character types, and character width.

(Read more here http://ow.ly/gSP8A )

What are the Different Types of Triggers?

2012-12-20T23:33:00.000-08:00

There are two types of Triggers.

1) DML Trigger

... There are two types of DML Triggers

1.Instead of Trigger
Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.

2. After Trigger
After triggers execute following the triggering action, such as an insert, update, or delete.

2) DDL Trigger

This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers.

(Read more here http://ow.ly/gbwHq )

What is a Trigger?

2012-12-19T22:30:00.003-08:00

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be considered to be similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself; so when the trigger is fired because of data modification, it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

(Read more here http://ow.ly/gbwDn )

SQL Question and Answers

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 4 of 31

What is the Difference between a Function and a Stored Procedure?

What is subquery? Explain the Properties of a Subquery?

What are Different Types of Join?

Cross Join

Inner Join

Outer Join

Self Join

What are Primary Keys and Foreign Keys?

What is User-defined Functions? What are the types of User-defined Functions that can be created?

SQLAuthority News – Sharding or No Sharding of Database – Working on my Weekend Project

Pessimistic Locking?

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. (Read more here http://bit.ly/sql-interview)

Which are the Important Points to Note when Multilanguage Data is Stored in a Table?

What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?

Explain Few of the New Features of SQL Server 2008 Management Studio

Create a SQL Login User in SQL Server 2008

SQL SERVER – Retrieve SQL Server Installation Date Time

What is Optimistic Locking ?

How to backup and restore database on Microsoft SQL Server 2005

Introduction

Step-by-step

Backup a database.

Restore the database.

SQL Authority News – Download SQL Server Data Type Conversion Chart

Implicit Conversion:

Explicit Conversion:

SQL SERVER – Delete Duplicate Records – Rows

SQL SERVER – 2008 – Introduction to Table-Valued Parameters with Example

August 31, 2008 by pinaldave

What is CHECK Constraint?

SQL SERVER – Stored Procedure Optimization Tips – Best Practices

SQL SERVER – Stored Procedure and Transactions

SQL SERVER – Effect of SET NOCOUNT on @@ROWCOUNT

How to Install SQL Server 2008 - A Step by Step Guide

SQL SERVER – Fix: Error: 10920 Cannot drop user-defined function. It is being used as a resource governor classifier

SQL SERVER – Simple Example to Configure Resource Governor – Introduction to Resource Governor

SQL SERVER – A List of Various SQL Server RTM and Service Pack Number

SQL Server 2012 Versions

SQL Server 2008 R2 Versions

SQL Server 2008 Versions

SQL Server 2005 Versions

SQL Server 2000 Versions

What is Collation?

What are the Different Types of Triggers?

What is a Trigger?

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.
(Read more here http://bit.ly/sql-interview)