SQL Server Guide

Learn T-SQL

2012-11-03T01:00:00.000-07:00

SQL Basics? SQL Server Interview Questions And Answers

Create Database

Table

View

Stored Procedure

Function

Trigger

SQL TOP

SQL LIKE

SQL Wildcards

SQL IN

SQL BETWEEN

SQL Alias

SQL JOINs

SQL UNION

SQL UNION ALL

SQL SELECT INTO

SQL CONSTRAINTS

Data Definition Language (DDL)

Data Manipulation Language (DML)

SQL Server Data Types

SQL NULLs

SQL ISNULL

SQL NULLIF

SQL Date and Time Functions

T-SQL Control of Flow

CURSOR

T-SQL Operators

RAISERROR

Error Handling

SET Statements

Security Statements

BACKUP

RESTORE

TRANSACTIONS

SQL Server Interview Questions And Answers

2012-10-20T05:00:00.000-07:00

How to Create Database using T-SQL command?

Explain Candidate Key, Alternate Key, and Composite Key

How to create Primary Key on existing table?

How to create Foreign Key on existing table?

How to create FOREIGN KEY constraint by using WITH NOCHECK

How to add a DEFAULT constraint to an existing column?

How to add CHECK CONSTRAINTS on existing table?

What's the difference between a primary key and a unique key constraints?

Explain SQL Server JOINs with examples?

What is the difference between TRUNCATE and DELETE?

Write T-SQL query to calculate Month End Date

How to get Month Number from Month Name

Write T-SQL Query to find Nth largest Number

Maximum Capacity Specifications for Database Objects in SQL Server 2008 R2

What is View in SQL Server?

What is Stored Procedure?

What is User Defined Functions (UDF)

What is User-Defined Function (UDF) in SQL Server?

Types of User Defined Functions (UDF) in SQL Server?

Difference between Function and Stored Procedure?

What is the difference between a local and a global temporary tables?

What is a NOLOCK?

What is lock escalation?

What is Log Shipping in SQL Server?

What is WITH TIES clause in SQL Server?

COUNT Number of Records for all the Tables in a Database?

Function to Split Multi-valued String

How to connect to SQL Server using RUNAS command from command prompt?

Regular Expression Problem in T-SQL

Concatenating Row Values using Transact-SQL

Which TCP/IP port does SQL Server run on? How can it be changed?

What is uniqueidentifier in SQL Server?

What is NEWSEQUENTIALID?

What is Trace Flag 610?

Sleep Command in T-SQL?

What is the use UPDATE_STATISTICS command?

How to Granting Execute permission on All the Stored Procedure?

How to get SQL Server Restore history using T-SQL?

Function to Convert Decimal Number into Binary, Ternary, and Octal

How to find all the IDENTITY columns in a database?

Difference between a WHERE clause and HAVING clause?

2012-09-10T01:00:00.000-07:00

A WHERE clause used to filter the physical rows in your table(s) whereas the HAVING clause operates on groups of a result-set (usually aggregate) and specifies which of the summary rows are returned in your resultset.
A WHERE clause is independent of GROUP BY clause but the HAVING clause does depend on this, although it can be used without this as well.
First SQL Server filters the physical rows using WHERE conditions and then performs GROUP BY on filtered data in memory and then filters the aggregated rows again with HAVING clause.

Example:

List of all the employee with basic greater than 3000.

Now I want to calculate the Dept codes for which total of basic is greater than 20,000 for those employees who are getting basic more than 3000.

T-SQL Clauses

2012-09-06T21:19:00.000-07:00

Most commonly used T-SQL command is SELECT statement. Below are the basic T-SQL clauses every SQL beginner must know:

SELECT <Column_List>
FROM <Table_Name>
WHERE <Conditional_Statement>
GROUP BY <Column_List>
HAVING <Conditional_Statement>
ORDER BY <Column_List>

Lets take an example of Employee table to understand these clauses:

EmployeeID	FirstName	MiddleName	LastName	DOB	Basic	Department
1	Hari	N	Sharma	10/10/1980	2500	Sales
2	John	D	Howell	11/11/1981	1900	Sales
3	Vijay	NULL	Sharma	10/20/1982	1500	Engineering
4	Paul	NULL	Catlin	7/25/1976	2600	Engineering
5	Romil	F	Driscoll	1/13/1979	2400	Sales
6	Karthik	P	Nagarajan	8/10/1969	2800	Engineering
7	Hany	H	Rodriguez	4/10/1977	2200	Management
8	William	NULL	Green	1/21/1981	1800	Management
9	Michelle	NULL	Jones	5/19/1974	2300	Management
10	Thomas	D	Lee	3/31/1984	1600	Sales

SELECT statement is used to pull the data from a table, view, or table valued function.
FROM clause is used to specify the name of the source table which we pull the data from.

For instance, we want to pull FirstName. LastName of all the employees along with Department name then we will use following query:

SELECT FirstName,LastName,Department
FROM dbo.Employee

Here is output:

WHERE clause is used to filter the data coming from a table. For instance, you want to pull FirstName, MiddleName, LastName, and Basic of all employees who belong to Sales department:

SELECT FirstName,MiddleName,LastName,Basic
FROM dbo.Employee
WHERE Department = 'Sales'

　
Here is output:

GROUP BY clause is used to aggregate the data based on certain group of columns. For instance, find out the TOTAL of basic amount for each department.

SELECT Department, SUM(Basic) BasicTotal
FROM dbo.Employee
GROUP BY Department

Here is output:

HAVING clause is used to filter out the data based on aggregated values. For instance, find out the list of Departments for which Total of basic is greater than 6500:

SELECT Department, SUM(Basic) BasicTotal
FROM    dbo.Employee
GROUP BY Department
HAVING   SUM(Basic) > 6500

I will explain the difference between WHERE and HAVING in my next post.

ORDER BY clause is used to arrange the result set in desired order, We can order the result in DESC or ASC order. For instance, get the list of all the employees in the order of First Name and then Last Name:

SELECT FirstName,MiddleName,LastName,Basic
FROM    dbo.Employee
ORDER BY FirstName,LastName

You can also write this query in following way (1 means first column in SELECT statement and 2 means second column the SELECT statement):

SELECT FirstName,MiddleName,LastName,Basic
FROM dbo.Employee
ORDER BY 1,2

SQL Server Tables

2012-09-05T16:56:00.000-07:00

How to Create a Table using T-SQL

SQL Server Table Name Limitations

What is View in SQL Server?

2012-09-04T00:00:00.000-07:00

A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. Only SELECT statement is stored in the database. The result set of the SELECT statement forms the virtual table returned by the view.

Views are called virtual tables because the result set of a view is us not usually saved in the database. The result set for a view is dynamically incorporated into the logic of the statement and the result set is built dynamically at run time.

Views are created by defining the SELECT statement that retrieves the data to be presented by the view. The data tables referenced by the SELECT statement are known as the base tables for the view.

Views can be created to hide data complexity as well data security. For instance you have three tables, Employee, Department, and BasicPay and you want to display only specific columns from these table.

Table / Entity	Fields / Attributes	Comments
Employee	(EmployeeID, EmployeeName, DOB, DepartmentID)	User can see all the attributes.
Department	(DepartmentID, DepartmentName, ManagerEmployeeID)	User can see all the attributes.
BasicPay	(EmployeeID, StartDate, EndDate, PayScale, BasicAmt, BonusAmt, SalaryCurreny)	User can see only PayScale and should not have any access to BasicAmt and BonusAmout.

Suppose user is interested in following reporting data:

SELECT e.EmployeeName

,d.DepartmentName

,mgr.FirstName AS DeptManagerName

,b.PayScale

FROM dbo.Employee e

LEFT JOIN Department d

ON d.DepartmentID = e.DepartmentID

LEFT JOIN dbo.Employee mgr

ON mgr.EmployeeID = d.ManagerEmployeeID

LEFT JOIN dbo.BasicPay b

ON b.EmployeeID = e.EmployeeID

Now you can create view a view to store the above query and grant access to the view instead of granting access to all three tables.

IF OBJECT_ID('dbo.vwEmployeeDetails') IS NOT NULL

BEGIN

PRINT 'Dropping view dbo.vwEmployeeDetails'

DROP VIEW dbo.vwEmployeeDetails

END

PRINT 'Creating view dbo.vwEmployeeDetails'

CREATE VIEW dbo.vwEmployeeDetails

/***********************************************

Created By : Author Name

Created Date : MM/DD/YYYY

History

-----------------------------------------------

MM/DD/YYYY Comments

***********************************************/

SELECT e.EmployeeName

,d.DepartmentName

,mgr.FirstName AS DeptManagerName

,b.PayScale

FROM dbo.Employee e

LEFT JOIN Department d

ON d.DepartmentID = e.DepartmentID

LEFT JOIN dbo.Employee mgr

ON mgr.EmployeeID = d.ManagerEmployeeID

LEFT JOIN dbo.BasicPay b

ON b.EmployeeID = e.EmployeeID

Now you can pull the data directly from the view instead of using complex query:

SELECT EmployeeName

,DepartmentName

,DeptManagerName

,PayScale

FROM dbo.vwEmployeeDetails

Advantages of View

There are several advantages of views as mentioned below:

· We can hide data complexity in a view. Instead of forcing users to learn the T-SQL syntax, you might wish to provide a view that runs a commonly requested SQL statement.

· View is an ideal object to protect your data. If you have a table containing sensitive data in certain columns, you might wish to hide those columns from certain groups of users. For instance allow employees who do not work in payroll to see the name, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information, so you can create a view that only displays required columns. You can grant permissions to that view without allowing users to query the underlying tables.

· Join columns from multiple tables so that they look like a single table.

· Enforcing some simple business rules. For example, if you wish to generate a list of customers that need to receive the fall catalog, you can create a view of customers that have previously bought your shirts during the fall.

· Aggregate information instead of supplying details. For example, present the sum of a column, or the maximum or minimum value from a column.

· Data exports with BCP. If you are using BCP to export your SQL Server data into text files, you can format the data through views since BCP's formatting ability is quite limited.

· Customizing data. If you wish to display some computed values or column names formatted differently than the base table columns, you can do so by creating views.

Disadvantages of View

· Even though views can be a great tool for securing and customizing data, they can be performance bottleneck. Indeed, they are not any faster than the query that defines them.

Note: From SQL Server 2000 onwards, indexed views (also referred to as "materialized" views) are supported to overcome this limitation.

· Views can especially degrade the performance if they are based on other views. Therefore, it is recommended NOT to create views based on other views.

Local temporary table VS Global temporary table

2012-09-01T14:00:00.000-07:00

In this post I will describe the differences between a Local temporary table and global temporary table.
Below are the differences in tabular form:

Local Temporary Table	Permanent Temporary Table
They have a single number sign (#) as the first character of their names.	They have a two number sign (##) as the first character of their names.
They are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server.	They are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.
If local temp table is created inside a stored procedure then it will get deleted once the execution is completed.	If global temp table is created inside a stored procedure then it will exists once the execution is completed. This will dropped automatically when the last user session that references the table disconnects.
Multiple users can't share these tables.	Multiple users can share these tables; in fact, all other database users can access it.
You can't grant or revoke permissions on these tables because it is available only to the owner.	You can grant or revoke permissions on these tables because these are always available to everyone.
The local table we created in our procedure by using sp_executesql won't be accessible to either the procedure or its child procedures. Within its execution scope, an sp_executesql system stored procedure creates and drops a local table. By definition, when the sp_executesql procedure ends, the life of the table also ends—hence the need to create a global temporary table.	These tables will always available till the last user session that references the table disconnects.

Implementation of Database Object Schemas

2012-08-24T15:15:00.000-07:00

A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects. It removed the tight coupling of database objects and owners to improve the security administration of database objects.

Schema can be considered as a container that can hold many database objects. Database objects are created under a specific schema. If you don’t define any schema while creating a table then object will be created under default schema “dbo” which has database owner privilege.

Example of schema: For a Sales database, we can create following schemas:

HR – all the human resource related tables can be created under this schema.
Product – Product related tables an be create under this schema
Sales – Sales transactions tables can be created under this schema.

Advantages of using schemas:

· Protecting database objects from being altered by users without the knowledge of the database owner.

· Preventing database base objects, independent software vendor (ISV) databases in particular, from ad hoc or incorrect user access leading to poor application performance

· Bringing related groups of objects (logical entities) together within one physical database to reduce physical database administrative overhead

Note: A schema cannot be dropped if it contains any objects. If a DROP SCHEMA statement is executed while it contains objects, the drop operation fails.

Creating schema using SQL Server Management Studio:

1. In Object Explorer, expand the Databases folder.

2. Expand the database in which to create the new database schema.

3. Right-click the Security folder, point to New, and select Schema.

4. In the Schema - New dialog box, on the General page, enter a name for the new schema in the Schema name box.

5. In the Schema owner box, enter the name of a database user or role to own the schema. Alternately, click Search to open the Search Roles and Users dialog box.

6. Click OK.

Creating schema using T-SQL

USE [DatabaseName]

CREATE SCHEMA [SchemaName] AUTHORIZATION [OwnerName]

USE [Test]

CREATE SCHEMA [HR] AUTHORIZATION [dbo]

System Databases in SQL Server

2012-08-21T09:00:00.000-07:00

When you install SQL Server, you will start with four system databases:

· master

· model

· msdb

· tempdb

All the above databases are essential to run your server properly.

The master database

Every SQL Server, regardless of the version, has master database and hold special set of tables called system tables. These system tables keeps track of the system as whole. For instance, when you create a new database then an entry goes to sys.databases or sysdatabases table.

SELECT * FROM sysdatabases

All the extended and system stored procedures are stored in this database regardless of which database they are intened to use with.

Note: everything that describes your server is stored in master database so this is very critical database nd hence can not be deleted.

The model database

This is a database on which a copy can be based. Model database forms a template for any new database that we create.So you can modify model database if you want to change what standards, newly created database look like. For instance, you can add some set of tables, functions anmd procedures that need to be part of every newly created database. Also you can include some users that would be cloned into every new database.

Note: this database serves as a template for any other database, it is a required database and can not be deleted.

The msdb database

MSDB database stores all the tasks and processes related to SQL Agent. For instance, if you schedule backups to run on a databases nightly then there will be an enrty in msdb database.

The tempdb database

One of the key workin area for the server is tempdb. SQl Server needs to create interm temporary tables to solve complex or large queries and these interim tables are created in tempdb.

tempdb is different from any other database. Not only are the objects within it temporary, the database itself is temporary. This is the only database in the system that is completely rebuilt from scratch everytime you start your SQL server.

Column Data-types in SQL Server

2012-08-20T09:00:00.000-07:00

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record. For example, a table that contains employee data for a company might contain a row for each employee and columns representing employee information such as employee number, name, address, job title, and home telephone number.

In this post I will describe SQL Server data-types along with their size (in bytes) and the domain values that can be taken by the data types.

Following data-types can be used for a column.

Data type	Size [bytes]	Description
Exact Numerics
bit	1	An integer data type that can take a value of 1, 0, or NULL. The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
tinyint	1	Integer data type that can store 0 to 2^8-1 (255)
smallint	2	Integer data type that can store -2^15 (-32,768) to 2^15-1 (32,767)
int	4	Integer data type that can store -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
bigint	8	Integer data type that can store -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
decimal	5-17	Numeric data types that have fixed precision and scale.
numeric	5-17	Numeric data types that have fixed precision and scale.
smallmoney	4	Currency that can store - 214,748.3648 to 214,748.3647
money	8	Currency that can store -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Approxmate Numerics
float	4-8	Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Float cans store - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
real	4	Real can store - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.
Date and Time
date	3	stores DATE in 'YYYY-MM-DD'. Range 0001-01-01 through 9999-12-31
smalldatetime	4	stores DATETIME like 'YYYY-MM-DD hh:mm:ss'. Date range 1900-01-01 00:00:00 through 2079-06-06 23:59:59
datetime	8	stores DATETIME like 'YYYY-MM-DD hh:mm:ss'. Date range 1753-01-01 00:00:00 through 9999-12-31 23:59:59
time	5	Stores only time like 'hh:mm:ss.nnnnnnn'. Range 00:00:00.0000000 through 23:59:59.9999999
Character Strings
char	n	CHAR(n). Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8000.
varchar	n	VARCHAR(n \| max). Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB)
text		Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). Storage size, in bytes is the string length entered.
Unicode Character Strings
nchar	2n	CHAR(n). Fixed-length, Unicode string data. n defines the string length and must be a value from 1 through 4000.
nvarchar	2n	VARCHAR(n \| max). Variable-length, Unicode string data. n defines the string length and can be a value from 1 through 4000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB)
ntext		Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823). Storage size, in bytes, is two times the string length entered
Unicode Character Strings
binary	n	Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8000
image		Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

How to CREATE a table using T-SQL command

2012-08-17T19:37:00.000-07:00

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record. For example, a table that contains employee data for a company might contain a row for each employee and columns representing employee information such as employee number, name, address, job title, and home telephone number.

Tables can be created through SQL Server Management Studio and T-SQL command.

CREATE TABLE command is used to create a table in the database. For instance, following command can be used to create MyTable:

CREATE TABLE dbo.MyTable

(

ID int

,Name varchar(30)

,DoB date

,gender char(1)

)

If you execute the above command twice, you will get following error:

Msg 2714, Level 16, State 6, Line 1

There is already an object named 'MyTable' in the database.

You need to check the object's existence before creating it to avoid above error as shown below:

IF OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable
DROP TABLE dbo.MyTable

CREATE TABLE dbo.MyTable

(

ID int

,Name varchar(30)

,DoB date

,gender char(1)

)

You will see following message after executing above query:

Command(s) completed successfully.

Note: This is always a good idea to add some logging information while creating and dropping any object or executing any T-SQL code for that matter. Logging always helps in troubleshooting.

IF OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable

BEGIN

PRINT 'Dropping table dbo.MyTable...'

DROP TABLE dbo.MyTable

END

PRINT 'Creating table dbo.MyTable...'

CREATE TABLE dbo.MyTable

(

ID int

,Name varchar(30)

,DoB date

,gender char(1)

)

Now you will see following messages after executing above code:

Dropping table MyTable...

Creating table MyTable...

What is SQL

2012-08-01T05:00:00.000-07:00

SQL stands for Structured Query Language. SQL is a standard language used to access data in Database Management Systems. Almost all the databases implement the standard version of SQL, making it portable across database management systems.

Basic SQL Syntax

SELECT   <Column_List>
FROM     <Table_Name>
WHERE    <Conditional_Statement>
GROUP BY <Column_List>
HAVING   <Conditional_Statement>
ORDER BY <Column_List>

Click here to learn about the above T-SQL clauses!

SQL Server Table Name Limitations

2012-07-02T20:44:00.000-07:00

Table Name in SQL Server can be a maximum of 128 characters (except local temporary table names that cannot exceed 116 characters) and must follow identifier rules undermentioned:

· The first character must be one of the following

o Character from a-z or A-Z

o Underscore (_), at sign (@), or number sign (#)

If a table name starts with number sign (#) then this table will be created as temporary table in TempDB and will be access only through current connection.

If a table name starts with double number sign (##) then this table will be created as global temporary table in TempDB and will be access only through current connection.

· Subsequent characters can include the following:

o Character a-z or A-Z

o Digit 0-9

o Underscore (_), at sign (@), number sign (#) or dollar sign ($)

· The identifier must not be a T-SQL reserved word.

· Special characters are not allowed.

CREATE a Database

2012-07-01T22:20:00.000-07:00

What is a database
A database in SQL Server is made up of a collection of tables that stores a specific set of structured data. A table contains a collection of rows, also referred to as records or tuples, and columns, also referred to as attributes. Each column in the table is designed to store a certain type of information, for example, dates, names, dollar amounts, and numbers.

How to create a Database in SQL Server?

We can create databas in SQL Server by following ways:

1. Through SQL Server Management Studio
2. Through T-SQL command.

Create a database SQL Server Management Studio (SSMS):

In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
Right-click Databases, and then click New Database.
In New Database, enter a database name.
To create the database by accepting all default values, click OK; otherwise, continue with the following optional steps.
To change the owner name, click (…) to select another owner.
To change the default values of the primary data and transaction log files, in the Database files grid, click the appropriate cell and enter the new value.
To change the collation of the database, select the Options page, and then select a collation from the list.
To change the recovery model, select the Options page and select a recovery model from the list.
To change database options, select the Options page, and then modify the database options.
To add a new filegroup, click the Filegroups page. Click Add and then enter the values for the filegroup.
To add an extended property to the database, select the Extended Properties page.
1. In the Name column, enter a name for the extended property.
2. In the Value column, enter the extended property text. For example, enter one or more statements that describe the database.
To create the database, click OK.

Create a database using T-SQL:

USE [master]

CREATE DATABASE [DatabaseName] ON PRIMARY

(

NAME = N'DatabaseName' --Logical data-file name

, FILENAME = N'D:\Microsoft SQL Server\MSSQL\DATA\DatabaseName.mdf' --Data file

, SIZE = 2048KB --You can specify the size as per your requirement

, MAXSIZE = UNLIMITED

, FILEGROWTH = 1024KB

)

LOG ON

(

NAME = N'DatabaseName_log' --Logical log-file name

, FILENAME = N'D:\Microsoft SQL Server\MSSQL\LOG\DatabaseName_log.ldf' --Log file

, SIZE = 1024KB

, MAXSIZE = 2048GB

, FILEGROWTH = 1024KB

)

Arguments:

DatabaseName is the name of new database.
NAME is the logical file name. Name is required when FILENAME is specified.
FILENAME is the physical (operating system) file name. This is the path and file name used by the operating system when you create the file.
SIZE is the initial size of the file. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB
MAXSIZE is the max size to which the file can grow.
FILEGROWTH is the automatic growth increment of the file. This is the amount of space added to the file every time new space is required

Once database is created, we can create other objects in the databases like Table, View, Stored Procedure, Functions, Triggers etc.

User Defined Functions (UDF)

2011-10-04T21:18:00.000-07:00

What are User Defined Functions (UDF) in SQL Server?

Types of User Defined Functions in SQL Server?

Difference between Function and Stored Procedure?

Difference between Function and Stored Procedure?

2011-07-09T17:46:00.000-07:00

User Defined Function (UDF) can be used in the SQL statements anywhere in the SELECT, WHERE, and HAVING section where as Stored procedures cannot be used.
Functions are designed to send their output to a query or T-SQL statement while Stored Procedures use EXECUTE or EXEC to run.
We can not use EXECUTE and PRINT commands inside a function but in SPROC
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
UDFs can't change the server environment or your operating system environment, while a SPROC can.
Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Stored Procedures are stored in compiled format in the database where as Functions are compiled and excuted runtime.
SPROC can be used with XML FOR Clause but Functions can not be.
SPROC can have transaction but not Functions.
Functions can be used in a SPROC but SPROC cann't be used in a Function. Only extended stored procedures can be called from a function.
Of course there will be Syntax differences and here is a sample of that

CREATE PROCEDURE dbo.ProcedureName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype OUTPUT
)
AS
BEGIN
   T-SQL statements
   RETURN
END
GO

CREATE FUNCTION dbo.FunctionName
(
   @parameter1 datatype = DefaultValue,
   @parameter2 datatype
)
RETURNS datatype
AS
BEGIN
   SQL Statement
   RETURN Value
END
GO

Explain SQL Server JOINs with examples?

2011-05-27T13:07:00.000-07:00

I will explain types of SQL JOINs in in this article. JOINs in SQL Server can be classified as follows:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN

Each type is explained below with suitable examples:
Let’s consider two tables as Table1 & Table2 for our example.

– CREATE TEST TABLES
CREATE TABLE Table1(ID [int], Code [varchar](10));
GO
CREATE TABLE Table2(ID [int], Amount [int]);
GO

– INSERT DUMMY DATA
INSERT INTO Table1 VALUES
(1,’AAA’),(2,’BBB’),(3,’CCC’)
GO
INSERT INTO Table2 VALUES
(1,50),(2,30),(4,40)
GO

INNER JOIN:
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Below is the query for inner join:

SELECT *
FROM Table1 A
INNER JOIN Table2 B
ON A.ID = B.ID
GO

LEFT OUTER JOIN
Left Outer joins return all rows from the left table referenced with a left outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Left Outer Join:

SELECT *
FROM Table1 A
LEFT OUTER JOIN Table2 B
ON A.ID = B.ID
GO

RIGHT OUTER JOIN
Right Outer joins return all rows from the right table referenced with a right outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Right Outer Join:

SELECT *
FROM Table1 A
RIGHT OUTER JOIN Table2 B
ON A.ID = B.ID
GO

FULL OUTER JOIN
Full Outer joins return all rows from both the tables. Unmatched records will be NULL. Below is the query for Full Outer Join:

SELECT * FROM Table1 A
FULL OUTER JOIN Table2 B
ON A.ID = B.ID
GO

CROSS JOIN
In cross joins, each row from first table joins with all the rows of another table. If there are m rows from Table1 and n rows from Table2 then result set of these tables will have m*n rows. Below is the query for

SELECT * FROM Table1 A
CROSS JOIN Table2 B
GO

Stored Procedure

2011-04-04T22:06:00.000-07:00

What is Stored Procedure?

Difference Between Function and Stored Procedure?

How to find all the IDENTITY columns in a database?

2011-03-20T23:25:00.000-07:00

Here is the easiest way to list all the IDENTITY Columns of any database:

SELECTOBJECT_NAME([object_id]) as TableName,

name as ColumnName

FROM [DatabaseName].sys.columns

WHERE is_identity = 1

What is Stored Procedure?

2011-03-05T01:10:00.000-08:00

A stored procedure is the saved collection of T-SQL statements in the SQL Server database that can take and return user-supplied parameters. Stored Procedures (also called sproc) can have input parameters and output parameters. Same stored procedure can be used over the network by several clients by passing different input data for input parameters. When a stored procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance.

Here is the syntax of SPROC:

CREATE PROC [ EDURE ] [owner.] ProcedureName
[ { @parameter data_type } [= DefaultValue] [OUTPUT] ] [ ,...n ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
AS
BEGIN
   T-SQL-statements
END

Here is an example:
CREATE PROC ProductList (
   @ProductType varchar(30) = 'Electronics')
WITH ENCRYPTION
AS
BEGIN
   SELECT
     ProductName, ProductType,
     Model, UnitPrice
   FROM Products (NOLOCK)
   WHERE ProductType = @ProductType
END

How to execute:
-- Return all the products belongs to Electronics
EXECUTE ProductList
GO

-- Return all the products belongs to Auto Mobiles
EXECUTE ProductList 'Auto Mobiles'
GO

SQL Statement Limitations in a Procedure
Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch.

Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:
•ALTER TABLE
•CREATE INDEX
•CREATE TABLE
•All DBCC statements
•DROP TABLE
•DROP INDEX
•TRUNCATE TABLE
•UPDATE STATISTICS

Permissions
CREATE PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permission to execute a stored procedure is given to the procedure owner, who can then set execution permission for other database users.

Function to Convert Decimal Number into Binary, Ternary, and Octal

2011-03-01T00:08:00.000-08:00

In this article I am sharing user defined function to convert a decimal number into Binary, Ternary, and Octalequivalent.

IF OBJECT_ID(N'dbo.udfGetNumbers', N'TF') IS NOT NULL
DROP FUNCTION dbo.udfGetNumbers
GO

CREATE FUNCTION dbo.udfGetNumbers
(@base [int], @lenght [int])
RETURNS @NumbersBaseN TABLE
(
decNum [int] PRIMARY KEY NOT NULL,
NumBaseN [varchar](50) NOT NULL
)
AS
BEGIN
WITH tblBase AS
(
    SELECT CAST(0 AS VARCHAR(50)) AS baseNum
    UNION ALL
    SELECT CAST((baseNum + 1) AS VARCHAR(50))
    FROM tblBase WHERE baseNum < @base-1
),
numbers AS
(
    SELECT CAST(baseNum AS VARCHAR(50)) AS num
    FROM tblBase
    UNION ALL
    SELECT CAST((t2.baseNum + num) AS VARCHAR(50))
    FROM numbers CROSS JOIN tblBase t2
    WHERE LEN(NUM) < @lenght
)

INSERT INTO @NumbersBaseN
SELECT ROW_NUMBER() OVER (ORDER BY NUM) -1 AS rowID, NUM
FROM numbers WHERE LEN(NUM) > @lenght - 1

OPTION (MAXRECURSION 0);
RETURN
END
GO

-- Unit Test --
-- Example with decimal, binary, ternary and octal

SELECT
   U1.decNum   AS Base10,
   U1.NumBaseN AS Base2,
   U2.NumBaseN AS Base3,
   U3.NumBaseN AS Base8
FROM dbo.udfGetNumbers(2, 10) U1
JOIN dbo.udfGetNumbers(3, 7) U2
ON u1.decNum = u2.decNum
JOIN dbo.udfGetNumbers(8, 4) U3
ON u2.decNum = u3.decNum

Here is the output:

Fun with TRANSACTION

2011-02-01T00:50:00.000-08:00

What will be output of below T-SQL code:

CREATE TABLE MyTable
(
   MyId [INT] IDENTITY (1,1),
   MyCity [NVARCHAR](50)
)

BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')
BEGIN TRANSACTION InnerTran
    INSERT INTO MyTable VALUES ('London')
ROLLBACK WORK
    IF (@@TRANCOUNT = 0)
    BEGIN
      PRINT 'All transactions were rolled back'
    END
    ELSE
    BEGIN
      PRINT 'Outer transaction is rolling back...'
      ROLLBACK WORK
    END
    DROP TABLE MyTable

Here are the options:
1.   All transactions were rolled back
2.   Outer transaction is rolling back...
3.   ERROR: Incorrect syntax near 'WORK'.

Correct answer: All transactions were rolled back
Explanation: Issuing a ROLLBACK WORK rolls back all the way to the outer BEGIN TRANSACTION

How to get SQL Server Restore history using T-SQL?

2011-01-15T23:24:00.000-08:00

How to find SQL Server databases Restore History?
How to find Last Restore Date od SQL Server database?

Few developers asked me how to find the last time a database was restored using T-SQL. I have formed the following query using MSDB..RestoreHistory table.

SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = 'DatabaseName'
ORDER BY restore_date DESC

What is lock escalation?

2011-01-07T22:29:00.000-08:00

Lock escalation is the process of converting a lot of low level locks (For example row locks, and page locks) into higher level locks (e.g. table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

In the hierarchy of the objects in a SQL Server instance, at the top level we have Database, followed by Schema, Tables, Table Partitions, Pages and then finally the individual Rows. If you acquire a lock at higher level, it can cover more resources there by you consume fewer lock resources (each lock structure takes approximately 100 bytes) and the locking overhead but this comes at a price of lower concurrency. So for example, if you want to select all the rows of a table, if you acquire a lock at table level, you will not need to lock individual rows or pages but then it will block any concurrent update transaction. Similarly, if you lock individual rows, you will get higher concurrency but then you will incur the overhead of acquiring/releasing locks on each row and lot more locking resources depending upon the isolation level of your transaction, as you may need to hold the locks on all the rows till the end of transaction.

Depending upon the estimates during query compilation, the SQL Server recommends the locking granularity (i.e. row, page or table) appropriately and during query execution, depending on the concurrent work load, the appropriate locking granularity is applied. User can override the locking granularity option explicitly by providing locking hints and/or by executing sp_indexoption stored procedure. While locking granularity is chosen at the start of query execution but during the execution, the SQL Server may choose to escalate the lock to lower level of granularity depending on the number of locks acquired and the availability of memory at run time.

T-SQL Challenge

2011-01-01T01:01:00.000-08:00

What will be the output of below T-SQL code:

CREATE TABLE #TestDate
(
   [ID] int IDENTITY(1,1)
   ,[FullDate] datetime DEFAULT (GETDATE()),
)
GO
INSERT INTO #TestDate VALUES
('01/07/2010'),('2010/07/01'),('07/01/2010')
GO
SELECT COUNT([FullDate]) FROM #TestDate
WHERE CAST([FullDate] as int) = 40358
GO
DROP TABLE #TestDate
GO

Answer this without cheating (without executing the Query). Here are the options:

A.   1
B.   2
C.   3
D.   Error
F.   None of the above

Correct Answer is B.
Reason: Dates 07/01/2010 and 2010/07/01 are inserted as 2010-07-01 00:00:00.000 in the database which is equal to 40358 of int type. So when the conversion into int and then taking ceiling of the values, these 2 records generate the same values. However, 01/07/2010 is saved as 2010-01-07 00:00:00.000 in the database which is equal to 40183 of type int.