Granting Execute permission on All Stored Procedure

How to grant Execute permission on All Stored Procedure for a new User in SQL Server?


Most of the application have users who are not creator (owner) of the Stored Procedures. Hence the users don't have EXECUTE permission on any of the Stored Procedures by default, considering that they are not part of any existing Role that has execute permission.
 
I have created a small script which returns a string that grants permission to the new User to all the Stored Procedures. Here is the script:
 
SELECT 'GRANT EXECUTE ON ' + name + ' [UserName]'
FROM sysobjects WHERE xtype IN ('P')
 
You need to copy the script generated by above query and execute. Alternatively, you can use below script - where you need not to copy the script generated by above query. Just execute the below script in required database.

DECLARE @UserName [varchar](128), @SQL [varchar](1000)
SET @UserName = '[UserName]'


DECLARE Cur CURSOR FOR
   SELECT 'GRANT EXEC ON ' +
   '[' + u.[Name] + ']' + '.' +
   '[' + o.[Name] + ']' +
   ' TO ' + @UserName  AS SQLCommand
FROM dbo.sysobjects o
JOIN dbo.sysusers u
  ON o.[uid] = u.[uid]
WHERE o.[Type] = 'P'

OPEN Cur
FETCH NEXT FROM Cur INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @SQL
   EXEC (@SQL)
   FETCH NEXT FROM Cur INTO @SQL
END
CLOSE Cur
DEALLOCATE Cur

Cheers !!!

What is NEWSEQUENTIALID

NEWSEQUENTIALID() creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

What is the use UPDATE_STATISTICS command?

We add statistics on the columns that don't have statistics in order to boost query performance. UPDATE_STATISTICS Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan but in some cases you can improve query performance by using UPDATE_STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

UPDATE_STATISTICS command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

Here is T-SQL Command:
1. Updating All Statistics with sp_updatestats
EXEC sp_updatestats

2. Update all statistics on a table
UPDATE STATISTICS TableName

3. Update the statistics for an index
UPDATE STATISTICS TableName, IndexName

For more information, click here

The SQL Server Query Optimizer has the ability to use statistics on columns that are not indexed in order to create more optimal execution plans. Doing so can increase performance without having the overhead of indexes. Column statistics are generated automatically by SQL Server when queries are being optimized by the Query Optimizer. For example, column statistics are automatically created when there are currently no statistics available for the column being used as a search argument in a WHERE clause or a JOIN clause. There are two cases when SQL Server will not automatically create column statistics, even when they fit the criteria described above. These include: when the costs needed to create the column statistics are larger than the costs of the query plan; and when SQL Server is too busy.

If you want to find out if an index has had its indexes updated or not, you can use the DBCC SHOW_STATISTICS command, like this:

DBCC SHOW_STATISTICS(table_name, index_name)

This command will not only tell you when statistics were last updated, but also provide you information on the kind of statistics that has been collected for the index you are examining


Reference: MSDN

Sleep Command in T-SQL?

Is there to way write a T-SQL command to just make it sleep for a period of time?
WAITFOR command is the answer.

-- wait for 1 minute
WAITFOR DELAY '00:01:00'

-- wait for 1 second
WAITFOR DELAY '00:00:01'

This command allows you a high degree of precision but is only accurate within 10ms - 16ms for example, the call WAITFOR DELAY '00:00:00:001' is likely to result in no wait at all.

What is Trace Flag 610

What is Trace Flag 610? When should we use it?

Trace Flag 610 (TF610) can be used to have minimal logging into a b tree, i.e. clustered table or an index on a heap, that already has data. TF610 can be used to get minimal logging in a non-empty B-Tree. When you insert a large amount of data, you don't want to create a lot of transaction log. So, initially the idea was to automatically do this in the engine, but because of bunch of issues this was put under a TraceFlag.


There are a few things one shoud be aware of:

1. When a Transaction COMMITs, it writes all pages that were minimally logged to disk. But there is possibilty that not all the pages can be written in time in case of a slow I/O subsystem. This can make the operation slower than full logging because for full logging, SQL only needs to write the commit log record and it's done. 
2. There is an issue in SQL 2008 where the transaction log grows very big due to log reservation - even though the log is never used. This is due to how rollback works for minimally logged pages. However, this bug was fixed in SQL 2008 R2.
3. If you'hv a large buffer-pool and the I/O subsystem cannot keep up, the commit could take very long time.
4. Don't make transactions too big. Even though the features is build to be used when inserting a large number of rows, it does not work very well if you make the transactions too big. Inserting data around 1TB in a single transaction may caused some issues.
5. The feature is NOT designed to make inserts go faster. It is mainly written to reduce the size of the transaction log.

I would recommendation to test this feature in a test environment - very similar I/O characteristics than the production system - and see if this really helps.

I would only use it if I have fast enough I/O to keep up with creation of the minimally logged pages.

What is uniqueidentifier in SQL Server?

The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use for a GUID is for assigning an identifier that must be unique in a network that has many computers at many sites.

A column or local variable of uniqueidentifier data type can be initialized to a value in the following ways:

1. By using the NEWID() function.
2. By converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

Note: The only operations that can be performed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators can be used. All column constraints and properties, except IDENTITY, can be used on the uniqueidentifier data type.

Reference: MSDN

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

By default (unless changed) the default TCP Port for SQL Server is 1433.

There are a few options when trying to find out this information… one way would be to go right to the registry and look at the TCP settings:
SQL 2005
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.\MSSQLServer\SuperSocketNetLib\TCP\

SQL 2008
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\MSSQLServer\SuperSocketNetLib\TCP\

How to change TCP/IP port:
Open SQL Server Configuration Manager and expand the SQL Server Network Configuration, then select Protocols for MSSQLSERVER (default instance) in the right hand pane, you’ll see your protocols (Shared Memory, Named Pipes, TCP/IP, VIA).


If you double click on TCP/IP, the TCP/IP Properties dialog box will appear and by selecting the IP Addresses tab you will now see your IP addresses and which TCP port they are running under.


If you need to change this port number, make sure you restart the SQL Server service as this change will not take affect until you do.

Fun With TRANSACTION

What will be the output of the SELECT statement in the following query?

DECLARE @Table TABLE ([ID] INT IDENTITY(1,1), [Name] VARCHAR(10))
INSERT @Table ([Name]) VALUES ('Hari')
BEGIN TRANSACTION Test
INSERT  @Table ([Name]) VALUES ('Jon')
INSERT @Table ([Name]) VALUES ('Peter')
ROLLBACK TRANSACTION Test
INSERT @Table ([Name]) VALUES ('Max')
SELECT [ID],[Name] FROM @Table

Option1:

Option2:

Option3:

Option4:

None of the above.

What will be the output of below query?

DECLARE @Date datetime
SET @Date = '2010-01-31'
SELECT DATEADD(MONTH,-3,DATEADD(MONTH, 3, '31-Jan-2010')) AS [Date]

1. 2010-01-31 00:00:00.000
2. 2010-01-30 00:00:00.000
3. 2010-02-01 00:00:00.000
4. This will throw an error.
5. None of the above.

Difference between a WHERE clause and HAVING clause?

• 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.

as

Concatenating Row Values using Transact-SQL

Sometime SQL programmers come across a requirement to concatene a result-set using T-SQL query.

Below is an example of this scenarion:

SELECT Dept_Code,Emp_Name

FROM Employee (NOLOCK)

ORDER BY Dept_Code,Emp_Name

And user wants to see the data in below format:

You can do these using Transact SQL. Below is the T-SQL query to achieve this solution:

Method1: Concatenation that uses the FOR XML clause with PATH mode:

SELECT E1.Dept_Code,

(

   SELECT Emp_Name + ',' FROM Employee E2

   WHERE E2.Dept_Code = E1.Dept_Code

   ORDER BY Emp_Name

   FOR XML PATH('')

) AS Emp_Name

FROM Employee E1

GROUP BY Dept_Code

Method2: There is a similar approach using the CROSS APPLY operator.

SELECT DISTINCT E1.Dept_Code, Temp.Emp_Name

FROM Employee E1 CROSS APPLY

(

   SELECT Emp_Name + ',' FROM Employee E2

   WHERE E2.Dept_Code = E1.Dept_Code

   ORDER BY Emp_Name

   FOR XML PATH('')

) AS Temp (Emp_Name)

Regular Expression Problem in T-SQL

I have one SQL Challenge for you:

A table has one column Code. Here is the sample data.

DECLARE @T TABLE(Code varchar(20))

INSERT @T VALUES

('STQ-309-A65'),('XYZ-999-A65'),

('AZZ-345-B66'),('CzA-123-C671'),

('GUP-999-C67'),('STQ-123-c67'),

('AtT-456-B66'),('ATT-000-B66'),

('AWT-101-A65'),('AUV-111-d68'),

('stq-007-c67'),('att-123-A97'),

('stq-777-c99'),('byz-789-d100'),

('stq-111-250'),('1at-p2a-149')

You need to filter the codes based on below conditions:

1. Code can be only 11 or 12 CHAR long.

2. First char must be a - s or A - S

3. Second char must be t - z to T - Z

4. Third char can be any char a - z or A - Z but not a digit.

5. Digit 4th and 8th must be "-"

6. Char 5th, 6th, and 7th must be a digit.

7. Char 5th should be non-zero digit.

8. Char 8th can be a - z but not a digit

9. Position 9th and 10th must be ASCCI value of 8th CHAR. If ASCII code is of three digit then 9th, 10th, and 11th position should be occupy by ASCII code.

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

How to connect to SQL Server Management Studio through RUNAS command from command prompt using credentials like Security Group and Password?

Here you go:

Step 1: Open a new command window by simply typing cmd in Run.

Step 2: Type RUNAS /user:UserID "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe" and press enter. Here UserID is your login credential.




Note: Check the path of Ssms.exe in your system. This path can be different, so make sure you are entering correct path.

Step 3: Now enter Password to connect to SQL Server.





Step 4: as soon as you enter the password and press enter, it will open SQL Server Management Studio for given credentials 

Function to Split Multi-valued String

1. Can you write a query to split a comma seperated value?
2. Can you create a function to spilt a delimitted string into multipled rows? Delimiter can be any char like comma (,), @, &, ; etc.
3. How to use a multi valued parameter in a Stored Procedure to filter report data? I am sure you can't use a multi valued parameter directly in T-SQL code without splitting multiple values.

To find the answer of above questions create a user defined function using below T-SQL code:

/**********************************************
CREATED BY HARI
PURPOSE : To split comma seperated values
--------------------------------------------
Use this function to split any multivalued string
seperated by any delimiter into multiple rows
***********************************************/
CREATE FUNCTION [dbo].[SplitMultivaluedString]
(
   @DelimittedString [varchar](max),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'') + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,CharIndex(@Delimiter,@sTemp)-1)
     
      SET @sTemp = RIGHT(@sTemp,LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END
GO

/* How to use this function:
SELECT * FROM [dbo].[SplitMultivaluedString] ('1,2,3,4', ',')
SELECT * FROM [dbo].[SplitMultivaluedString] ('1;2;3;4', ';')
*/

COUNT Number of Records for all the Tables in a Database

Easiest way to get an exact value of Number of Rows for all the tables in a SQL Server database

1. Use DBCC UPDATEUSAGE - this updates the values of rows for each partition in a table.
2. Use undocumented stored procedure sp_msForEachTable and store the result set in a table.

Below is the query to get required output:

USE [DatabaseName]
GO

DECLARE @DynSQL NVARCHAR(255)
SET @DynSQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC(@DynSQL)

IF OBJECT_ID('tempdb..#T','U') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T (TableName nvarchar(500),NumberOfRows int)
GO

INSERT INTO #T
EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1) as TableName,COUNT(*) as NumberOfRows FROM ?'
GO 
SELECT * FROM #T ORDER BY NumberOfRows DESC

How to get Month Number from Month Name

How to convert month name to month number?
Here are two solutions, however, first one is best solution.

--Approach 1 [Best way]--
---------------------------------
DECLARE @MonthName varchar(15)
SET @MonthName = 'January'
SELECT MONTH(CAST(@MonthName + '1 2010' AS datetime))  AS MonthNo
GO


--Approach 2 --
---------------------------------
DECLARE @MonthName varchar(15)
SET @MonthName = 'January'
SELECT CASE @MonthName
   WHEN 'January' THEN 1
   WHEN 'February' THEN 2
   WHEN 'March' THEN 3
   WHEN 'April' THEN 4
   WHEN 'May' THEN 5
   WHEN 'June' THEN 6
   WHEN 'July' THEN 7
   WHEN 'August' THEN 8
   WHEN 'September' THEN 9
   WHEN 'October' THEN 10
   WHEN 'November' THEN 11
   WHEN 'December' THEN 12
END MonthNo
GO

What is WITH TIES clause in SQL Server?

Have you ever used WITH TIES keyword?

Actually TOP keyword specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. Here is Transact-SQL Syntax:

[

      TOP (expression) [PERCENT] 

      [ WITH TIES ]

 ]

Arguments

expression: is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

PERCENT: indicates that the query returns only the first expression percent of rows from the result set.

WITH TIES: specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.

Example:

DECLARE @TEST TABLE(
ID int, Amount int)
INSERT INTO @TEST VALUES
(1,10),(2,30),(3,60),
(4,50),(5,50),(6,70),
(7,20),(8,70),(9,70),
(10,44),(11,80),(12,90)

Below is the result-set using TOP without and with clause WITH TIES.

What is Log Shipping in SQL Server 2008?

How to Perform SQL Server Log Shipping: Log shipping is the process of automating the backup ofdatabase and  transaction log files on a production SQL server, and then restoring them onto a standby server. Only Enterprise Editions support log shipping. In log shipping, the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

Log shipping

• Automated process to ship transaction log backups to another server
• Three jobs to perform the required actions: backup, copy and recover
• Involves three servers/instances: primary, secondary and (optional) monitoring server
• Requires full or bulk logged mode
• Can have multiple secondary copies
• Information about log shipping kept in MSDB

Log Shipping Options

• Interval – Default is 15 minutes. Consider the impact before using a smaller interval
• Delayed log restore – option that allows you to have an older copy
• If secondary is far behind. Consider backup/copy to media/restore, consider partitioning the data
• Careful – Backup typically goes to a file server share. Agent account needs access to shared files
• Careful – Weekend maintenance jobs can make you run out of disk space
• Monitoring – MSDB tables, agent history

Problems with Log Shipping

Log shipping is a compromise but it is not the ideal solution, it is a practical solution given real-world budget constraints. Some of the problems with log shipping include:

• Log shipping failover is not automatic. The DBA must be present when the failover occurs.
• The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over.
• Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable.
• The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally.
• When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user's applications to the new standby server. In some cases, neither of these options is practical.

What is a NOLOCK?

Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is used in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared locks, means multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).

How to use:
SELECT * FROM TableName (NOLOCK)

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

1. As far as syntax is concern, global temporary tables are created using ## as prefixed while Local temporary tables with #.
2. Global temporary table is available to any connection once created. They are cleared when the last connection is closed. Local temporary table is available to the current DB connection for current user and are cleared when connection is closed.
3. Multiple users can’t share a local temporary table however Global temporary table can be shared by multiple user sessions.
4. The Local temporay table available only in source connection, not for other connections.

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

There are three types of User-Defined functions in SQL Server:

1. Scalar Function
2. Inline Function
3. Multi-statement Table-valued Function

Scalar UDFs

Scalar UDFs return a single value. They are similar to built-in functions such as GETDATE(), or OBJECT_NAME(), which return a single string, date, or integer. The value returned by a scalar UDF can be based on the parameters passed.
Scalar UDFs can return any scalar system-supplied data type, except TIMESTAMP. You cannot return values with a user-defined data type from scalar UDFs. If you want to do so, you must specify the underlying system-supplied data type instead.

In-line UDF
In-line UDFs return a single row or multiple rows and can contain a single SELECT statement. Because in-line UDFs are limited to a single SELECT, they can't contain much logic. They can be effective, however, for lookups that return multiple values, such as the top five best-selling books with title, author, and publication date.

Multi-statement UDFs

The multi-statement UDFs can contain any number of statements that populate the table variable to be returned. Notice that although you can use INSERT, UPDATE, and DELETE statements against the table variable being returned, a function cannot modify data in permanent tables. Multi-statement UDFs come in handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.

Difference between Function and Stored Procedure?

• 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

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

User-Defined Functions allow to define its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type. A function can have a maximum of 1024 input parameters. When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in user-defined stored procedures in which omitting the parameter also implies the default value. User-defined functions do not support output parameters. When you can't find a built-in function that meets your needs, you can write your own.

User-defined functions are created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement. Each fully qualified user-defined function name (schema_name.function_name) must be unique.


Valid Statements in a Function
The types of statements that are valid in a function include:

• DECLARE statements can be used to define data variables and cursors that are local to the function.
• Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.
• Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.
• Control-of-flow statements except TRY...CATCH statements.
• SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
• UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.
• EXECUTE statements calling an extended stored procedure.

 The following nondeterministic built-in functions cannot be used in Transact-SQL user-defined functions.

1. NEWID
2. RAND
3. NEWSEQUENTIALID
4. TEXTPTR

What is Stored Procedure?

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.

Explain SQL Server JOINs with examples?

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

Adding a DEFAULT constraint to an existing column

• DEFAULT Constraint can be added when the table is created, as part of the table definition.
• DEFAULT Constraint can be added to an existing table

How to CREATE DEFAULT Constraint on existing table?

To explain this, First I will create a table and then inserts a record (value into the first column and NULL into second column).

CREATE TABLE dbo.TestDefault(

column1 [int],

column2 [int]
)
GO
INSERT INTO dbo.TestDefault (column1) VALUES (10)
GO

Now I will add a DEFAULT constraint to the second column using below T-SQL code:

ALTER TABLE dbo.TestDefault

ADD CONSTRAINT TestDefault_DC_Columns2

DEFAULT 99 FOR column2
GO

To verify whether the default is applied or not, another value is inserted into the first column and the table is queried:
INSERT INTO dbo.TestDefault (column1) VALUES (20)
GO
SELECT * FROM TestDefault
GO 

 

What is the Difference between TRUNCATE and DELETE commands?

DELETE & TRUNCATE both are intended to delete data from the database tables. Both the commands can only remove the data from the tables but can not remove the table from the database - in other words, can't drop the structure of the table. Since both commands are used only to delete the data from the table, there should be obvious differences between thsese commands in aspects like T-SQL syntax, behavious, performance etc. Below are the differences between DELETE & TRUNCATE commands:

DELETE

• T-SQL Command: 
DELETE FROM [TableName] WHERE [Condition]
• DELETE is DML command hence it can be rolled back.
• A WHERE clause can be used to provide the facility of conditional-based deletion
• Triggers can be fired
• When we use delete command on a table having IDENTITY column, it will not reset the counter of  identity column. Instead, it maintains the same counter for new rows.
• DELETE is slower than Truncate because it logs deleted data into transaction log files. DELETE command removes one row at a time and logged into transaction log files. 

TRUNCATE

• T-SQL Command:
TRUNCATE TABLE [TableName]
• TRUNCATE is DDL command hence it can't be rolled back.
• A WHERE clause can't be used for conditional-based deletion. It removes entire rows at once.
• Triggers will not be fired - because Truncate doesn't remove any row, it just deallocates the data pages
• When we use truncate command on a table having IDENTITY column, it will reset the counter used by identity column to the seed value defined for the column.
• TRUCATE is faster than DELETE because it removes data directly without copying into transaction log files. In fact, TRUNCATE is also a logged operation but it logs the deallocation of the data pages in which the data exists - means your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. Thats why it is faster than DELETE command.
• Can't use truncate command if table has foreign key constraint, even if the referring table has no records. You will get below error:
Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.
• Can't use truncate command if table has a column that participate in Indexed view.

How to ADD CHECK CONSTRAINTS on existing table?

Adding CHECK constraint to an existing column
The following T-SQL code adds a constraint to an existing column in the table. Option WITH CHECK is used to to apply a check against existing rows. WITH CHECK option is optional in the code because it is defalut check option.

ALTER TABLE TestPK
WITH CHECK --Optional
ADD CONSTRAINT TestPK_CHECK_DoB
CHECK (DoB < DateAdd(yy,-20,GetDate()))
 
If any existing value violates the constraint, we get following error:
Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the CHECK constraint "ConstraintName". The conflict occurred in database "DBName", table "dbo.TableName", column 'ColumnName'.
 
Adding CHECK constraint WITH NOCHECK
The following T-SQL code adds a constraint to an existing column in the table with nocheck option. I assume that the column has a value that violates the constraint. Therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow for the constraint to be added

ALTER TABLE TestPK WITH NOCHECK
ADD CONSTRAINT TestPK_CHECK_DoB
CHECK (DoB < DateAdd(yy,-20,GetDate()))

T-SQL Query to Calculate Month End Date

How to calculate Month End Date in one liner query?

Here is the easiest way to calculate Month End Date for any given date:

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)-1 AS MonthEndDate

Example:
If you replace GetDate() with any date, above query will return the Month End Date for that particular month.
If GetDate() value is '2010-01-25' then Output will be '2010-01-31'
If GetDate() value is '2010-02-20' then Output will be '2010-02-28'

T-SQL Query to Find Nth Largest number

How to find Nth Highest number using SQL query?

This is very simple to achieve by using Ranking Functions. Below is the answer of this query:

-- PREPARE TEST DATA
DECLARE @T TABLE (Amount int)
INSERT INTO @T VALUES
(101),(120),(14),(110),(930),(310),
(12),(104),(330),(423),(110),(10)


DECLARE @N int
SET @N = 5 -- SET Nth Number

-- ACTUAL QUERY
SELECT [Rank],Amount FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Amount DESC) [Rank]
    ,Amount FROM @T) AS Temp
WHERE [Rank] = @N

How to Create FOREIGN KEY Constraint by Using WITH NOCHECK

When a FOREIGN KEY constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure that all values, except NULL, exist in the columns of the referenced PRIMARY KEY or UNIQUE constraint.
Database Engine will throw below error if any value is missing in the referenced column:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "NameOfConstraint". The conflict occurred in database "DBName", table "dbo.TableName", column 'ColumnName'.

However, by specifying WITH NOCHECK, the Database Engine can be prevented from checking the data in the column against the new constraint and made to add the new constraint regardless of the data in the column. The WITH NOCHECK option is useful when the existing data already meets the new FOREIGN KEY constraint, or when a business rule requires the constraint to be enforced only from this point forward.

ALTER TABLE TestFK WITH NOCHECK
ADD CONSTRAINT TestFK_IDFK FOREIGN KEY(IDFK)
REFERENCES TestPK (ID)

Note: You should be very careful while adding a constraint WITH NOCHECK option because this bypasses the controls in the Database Engine that enforce the data integrity of the table.

How to CREATE Foreign Key on existing table?

• We can create a FOREIGN KEY constraint as part of the table definition when we create a table.
• If a table already exists, we can add a FOREIGN KEY constraint, provided that the FOREIGN KEY constraint is linked to an existing PRIMARY KEY constraints or UNIQUE constraint in any another or the same table.

Only 253 FOREIGN KEY constraint can be added to a table in SQL Server 2008. Click here to see the maximum sizes and numbers of various Database Objects defined in SQL Server 2008.

Creating FOREIGN Key on existing table
To explain this, I'll create a table TestFK without any Foreign Key. I will use Table TestPK to reference the Foreign Key. Below is T-SQL to create a TestFK table:

IF OBJECT_ID('TestFK','U') IS NOT NULL
DROP TABLE TestFK
GO
CREATE TABLE TestFK (
   [IDFK] [int] NULL,
   [Date] [datetime] DEFAULT (GETDATE())
)
GO
INSERT INTO TestFK (IDFK) VALUES
(1),(2),(1),(NULL),(2),(NULL),(3)
GO
SELECT [IDFK],[Date] FROM TestFK (NOLOCK)

Now ALTER the table to ADD Foreign Key
Execute below T-SQL code to add foreign key:

ALTER TABLE TestFK
ADD CONSTRAINT TestFK_IDFK FOREIGN KEY (IDFK)
REFERENCES TestPK (ID)

How to CREATE Primary Key on existing table?

Only single PRIMARY KEY constraint can be added to a table.

• Primary Key can be added when the table is created, as part of the table definition.
• Primary Key can be added to an existing table, provided that no other PRIMARY KEY constraint already exists.

How to CREATE Primary Key on existing table?

To explain this, I'll create a table without Primary Key constraint. Below is T-SQL to create a TestPK table:

IF OBJECT_ID('TestPK','U') IS NOT NULL
DROP TABLE TestPK
GO
CREATE TABLE TestPK (
    [ID] [int] NOT NULL,
    [Name] [varchar](50),
    [DoB] [date],
    [DeptNo] [tinyint]
)
GO
INSERT INTO TestPK (ID,Name,DoB,DeptNo) VALUES
(1,'Name1','1983-04-07',1),
(2,'Name2','1992-10-21',3),
(3,'Name3','1981-02-17',1),
(4,'Name4','1975-11-23',2)
GO
SELECT ID,Name,DoB,DeptNo FROM TestPK (NOLOCK)


Now ALTER the table to ADD Primary Key
Execute below T-SQL code to add primary key:

ALTER TABLE TestPK
ADD CONSTRAINT TestPK_PrimaryKey_ID PRIMARY KEY (ID)

Note: Before adding a PRIMARY KEY constraint to an existing column(s) in the table, ensure that the existing data follows the rules for primary keys:
1. No NULL values  2. No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.