Difference between Stored Procedures and Functions

Difference between Stored Procedures and Functions

Stored Procedures	Functions
Can be used to read and modify data.	Can only read data, cannot modify the database.
To run an SP Execute or Exec is used, cannot be used with SELECT statement.	Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
Cannot JOIN a SP in a SELECT statement.	Can JOIN a UDF in a SELECT statement.
Can use Table Variables as well as Temporary Tables inside an SP.	Cannot use a Temporary Table, only Table Variables can be used.
Can create and use Dynamic SQL.	Cannot use a Dynamic SQL inside a UDF.
Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.	Cannot use transactions inside a UDF.
Can use used with XML FOR clause.	Cannot be used with XML FOR clause.
Can use a UDF inside a SP in SELECT statement.	Cannot execute an SP inside a UDF.
Cannot be used to create constraints while creating a table.	Can be used to create Constraints while creating a table.
Can execute all kinds of functions, be it deterministic or non-deterministic.	Cannot execute some non-deterministic built-in functions, like GETDATE().

Difference between Stored Procedures and Views

Difference between Stored Procedures and Views

Stored Procedures	Views
Does not accepts parameters	Accept parameters
Can be used as a building block in large query.	Can not be used as a building block in large query.
Can contain only one single Select query.	Can contain several statement like if, else, loop etc.
Can not perform modification to any table.	Can perform modification to one or several tables.
Can be used (sometimes) as the target for Insert, update, delete queries.	Can not be used as the target for Insert, update, delete queries.

How to Swap the values of two columns in SQL Server

How to Swap the values of two columns in SQL Server

Suppose you have a Customer table in the database with the following data and you want to interchange the values of columns Name and Address.

SELECT * FROM CUSTOMER

Just write a simple update query for Address table like as :

UPDATE Customer SET Name=Address , Address=Name

Now After query execution you will find the the values of columns Name and Address have been interchanged.

Note : If column is identity ,then swapping is not possible.

SELECT * FROM CUSTOMER

String Functions – Count Words

String Functions – Count Words

In SQL Server, there's no built-in function that will return the number of words a given string contains.

DECLARE @String VARCHAR(4000)

SET @String = 'Health Insurance is an insurance against expenses incurred through illness of the insured.'

SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1

This query will return a value of 13, which is the number of words in the given string.

How to restore the sample database(.bak file) in SQL Server?

How to restore the sample database(.bak file) in SQL Server?

Download the sample database zip file and unzip the .bak file inside. Place the .bak file in your SQL Server backup folder. If you have not changed anything during installation it should be in C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012EXPRESS\MSSQL\Backup. Now go to your SQL Server 2012 instance, open up the Object Explorer [F8] (if it is not opened already) and right click on the Database folder. In the context menu go to Restore Database.

You should now be in the Restore database window.

Click on "Device" and then click on the button behind it. You should now see the Select backup devices window. Click "Add" and browse to the .bak file you just unzipped (if you don't see it immediately). Select the .bak file and click "OK". Make sure you have no other files selected in the Select backup devices window. Click "OK" and you should be back in the Restore database window. You can review your options and click "OK" again. The AdventureWorks database should now be restored and ready for use.

Maximum Capacity Specifications for SQL Server(Table Column Users etc)

Maximum Capacity Specifications for SQL Server

Database Engine Objects

The following table specifies the maximum sizes and numbers of various objects defined in SQL Server databases or referenced in Transact-SQL statements.

SQL ServerDatabase Engine object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)
Clustered indexes per table	1	1
Columns per index key7	16	16
Columns per foreign key	16	16
Columns per primary key	16	16
Columns per nonwide table	1024	1024
Columns per wide table	30,000	30,000
Columns per SELECT statement	4,096	4,096
Columns per INSERT statement	4096	4096
Database size	524,272 terabytes	524,272 terabytes
Databases per instance of SQL Server	32,767	32,767
Filegroups per database	32,767	32,767
Files per database	32,767	32,767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes
Foreign key table references per table4	253	253
Parameters per stored procedure	2,100	2,100
Parameters per user-defined function	2,100	2,100
Rows per table	Limited by available storage	Limited by available storage
Tables per database3	Limited by number of objects in a database	Limited by number of objects in a database
Tables per SELECT statement	Limited only by available resources	Limited only by available resources
Triggers per table3	Limited by number of objects in a database	Limited by number of objects in a database
Columns per UPDATE statement (Wide Tables)	4096	4096
User connections	32,767	32,767
XML indexes	249	249

For more information , Please go through the below link as

https://msdn.microsoft.com/en-us/ms143432.aspx

When we should use inner join and sub query?

When we should use inner join and sub query?

• Use joins when we need to get data from both the tables in SELECT statement.
• Use sub query when we need to get data from only one table and other table is used only to check existence. 

How to restrict the stored procedure naming convention in SQL server.

How to restrict the stored procedure naming convention in SQL server.

We can restrict or forced the users to follow stored procedure naming convention by using policy based management in SQL server. I am explaining it by using sql server management studio (SSMS). In this example user must have to keep prefix usp_ in the name of stored procedure in the specific database.

Step 1: Right click on Management -> Policy Management -> Policies and then click on New Policy:

Step 2: In create New policy window:

Name: We can write any name of policy.

In this example we are writing Stored Procedure Naming Rule.

Now we are going to click on new condition in the check condition drop down to add the conditions.

Step 3: In the create new condition window:

Name: We can write any name of the condition. In this example we are keeping Procedure Name Condition.

Facets: Choose stored procedure in the drop down.

Expression:

Field: On which property we want to put condition. In this example we are choosing @Name which implies name of the stored procedure.

Operator: In this example we are choosing LIKE operator.

Value: 'usp_%'

Now click on the OK button.

Step 4: In this previous window that is create new policy window:

Enabled: We are checking it to enable it after its creation.

Evaluation Mode: In this example we are choosing On Change:Prevent.

It means this event will occur if user will try to create or alter stored procedure name.

Step 5: This naming conditions is applicable in the all the database. If we want we can put this restriction in the specific database. For this click on the Every drop down for database and a new conditions. It will open new condition window. Write followings:

Field: @Name

Operator: IN

Value: Name of databases. In this example: Array(my_Database','Employee')

Insert Multiple Records Using One Insert Statement

Insert Multiple Records Using One Insert Statement

Creating table:

CREATE TABLE [dbo].[authors](

      [au_id] [int] NOT NULL,

      [Au_fname] [varchar](50) NULL)

Old Method 1:

INSERT INTO authors  (Au_fname,au_id) VALUES ('First',11);

INSERT INTO authors  (Au_fname,au_id) VALUES ('Second',12);

INSERT INTO authors  (Au_fname,au_id) VALUES ('Third',13);

INSERT INTO authors  (Au_fname,au_id) VALUES ('Fourth',14);

INSERT INTO authors  (Au_fname,au_id) VALUES ('Fifth',15);

Next Method 2:

INSERT INTO authors  (Au_fname,au_id)

    SELECT  'First' ,11

    UNION ALL

SELECT  'Second' ,12

    UNION ALL

SELECT  'Third' ,13

    UNION ALL

SELECT  'Fourth' ,14

    UNION ALL

SELECT  'Fifth' ,15

GO

New Method 3:

INSERT INTO authors  (Au_fname,au_id)

    VALUES ('First',11),

           ('Second',12),

           ('Third',13),

           ('Fourth',14),

            ('Fifth',15)

Insert/Select multiple records using one Insert/Select Statement

Insert/Select multiple records using one Insert/Select Statement

Execute same code multiple times without Copy and Paste multiple times using 'GO' command.

SELECT GETDATE() AS CurrentTime

GO 5

Using CASE with ORDER BY in SQL Server

Using CASE with ORDER BY in SQL Server

 Use CASE with ORDER BY clause.  Its a nice functionality which helps you to avoid UNION when you have to display the records in some specific order and still you need to sort them.

As shown below I have created an Employee table with ID (Primary key),Name, Address and Phone columns.

I have also inserted some temporary data as shown below,

1: INSERT INTO Employee VALUES (‘Chirag Darji’,‘Ahmedabad’,‘123456789′)

2: INSERT INTO Employee VALUES (‘Dipak Patel’,‘USA’,‘123456789′)

3: INSERT INTO Employee VALUES (‘Shailesh Patel’,‘USA’,‘123456789′)

4: INSERT INTO Employee VALUES (‘Piyush Vadher’,‘Gujarat’,‘123456789′)

5: INSERT INTO Employee VALUES (‘Mihir Panchal’,‘Gujarat’,‘123456789′)

6: INSERT INTO Employee VALUES (‘Vishal Patel’,‘Ahmedabad’,‘123456789′)

Now consider that have to display the records of employee table order by Address however you want first it should display all the records with USA in address field then Ahmedabad then Gujarat and after that all. Here is the output that we want,

Below is the query that displays the result as shown in above fig,

SELECT * FROM Employee ORDER BY

CASE WHEN Address = ‘USA’ THEN 1

WHEN Address = ‘Ahmedabad’ THEN 2

WHEN Address = ‘Gujarat’ THEN 3

ELSE 4

END