User defined stored procedures should not be prefixed with 'SP_'. IS IT SO?

Microsoft best practice says, User defined stored procedures should not be prefixed with 'SP_'. IS IT SO?

I have seen many blogs saying it is best practice not to use SP_ prefix to the user defined stored procedure. ???

The question of the hour is what it makes difference from system procedure if we create user defined stored procedure with SP_ prefix, and then we decide whether to stop writing stored procedure with SP_ prefix.

We will go into the depth to find out how it works when we declare stored procedure with SP_ prefix by taking enough of examples.

Example 1

I am creating a stored procedure in tempDatabase, then I am creating a user defined stored procedure with the name sp_tables.

create database tempDatabase

Command(s) completed successfully.

Database is ready.

use tempDatabase

go

CREATE PROCEDURE sp_tables

AS

BEGIN

      select * from EmployeeList

END

GO

Now we have created procedure which returns record set of all employee from EmployeeList table

Now its time to execute the stored procedure.

Expected output

Records from Employee List tables. Correct

Auctual Output

use tempDatabase

exec sp_tables

tempDatabase	dbo	EmployeeList		TABLE	NULL
tempDatabase	INFORMATION_SCHEMA	CHECK_CONSTRAINTS		VIEW	NULL
tempDatabase	INFORMATION_SCHEMA	COLUMN_DOMAIN_USAGE		VIEW	NULL

The output is something different from what we have expected, so where we go wrong???

The finding is, if user defined stored procedure name is same as system defined stored procedure, then the DB engine search the SP from Master database not from the user defined database.

So, the procedure will be executed from "MASTER" database... Not from user database. In our case the stored procedure is System stored procedure name, So, its searched and executed from MASTER database itself.

Example 2

Lets create a stored procedure in TempDatabase with the name sp_Tables1, which is not a system defined stored procedure name.

use tempDatabase

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      select * from EmployeeList

END

GO

Now its time to execute the stored procedure.

Expected output

Records from Employee List tables. Correct

Auctual Output

use tempDatabase

exec sp_tables1

id          empName

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

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

7           Siddhiksha

8           Sakshi

(8 row(s) affected)

It results as the same, what we expected... How and why?, The stored procedure name was prefixed as 'Sp_' , Then it should be searched in "MASTER" database. correct?

NO The reason is, This is not a "SYSTEM STORED PROCEDURE", So, It will be searched in Local database first and then "MASTER" database, In our case, The procedure will be there in user database (tempDatabase) itself, So, Its searched and executed from User database itself...

Example 3

I am creating one stored procedure in "MASTER" database with name Sp_Tables1. Before that we first need to drop the procedure sp_table1 from user database (tempDatabase) which we had created in previous example.

use tempDatabase

drop procedure sp_tables1

use master

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      select * from EmployeeList

END

GO

Now its time to execute the stored procedure. We are executing this procedure from user database (tempDatabase)

use tempDatabase

exec sp_tables1

Expected output

Don’t Know, because no procedure with sp_tables1 is present in user database(tempDatabase).

Auctual Output

use tempDatabase

exec sp_tables1

id          empName

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

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

7           Siddhiksha

8           Sakshi

(8 row(s) affected)

The reason is, The procedure is NOT A SYSTEM STORED PROCEDURE..., So, The it searches the Stored procedure in Local database first. If it is not there in local then only it searches in MASTER database.

In our case, the procedure will be there in MASTER database. So, It is searched in use database first, But the procedure will not be there in user database, So, Its searched and executed from MASTER database.

Example 4

Now lets create one stored procedure in both MASTER and User database (tempDatabase) named Sp_Tables1.

IN MASTER DATABASE

use master

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      print 'from master'

END

GO

Command(s) completed successfully.

IN USER DATABASE

use tempDatabase

go

CREATE PROCEDURE sp_tables1

AS

BEGIN

      select * from EmployeeList

END

GO

Command(s) completed successfully.

Now its time to execute the stored procedure. We will executing this procedure from user database (tempDatabase)

use tempDatabase

exec sp_tables1

id          empName

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

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

7           Siddhiksha

8           Sakshi

(8 row(s) affected)

The reason is, the procedure is NOT A SYSTEM STORED PROCEDURE..., So, the search operation is performed in user database (TempDatabase) first and then MASTER database.

If Procedure is there in user database then, the procedure will be executed from user database itself, and it will not be search in MASTER database.

Conclusion:

The 'SP_' prefixed stored procedure will not be searched the stored procedure in MASTER database always. It will be searched based on the various scenario described above.

When Object name is SYSTEM DEFINED NAME then only it searches the procedure in MASTER database first and then user current database.

Now it’s on u whether to use SP_ prefix to user defined stored procedure or not…

But DB practice says, not to use SP_ prefix to user procedure.

Coding gives immense pleasure J

Post Reference: Vikram Aristocratic Elfin Share

IDENT_CURRENT function vs @@IDENTITY in SQL Server

IDENT_CURRENT function returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

Let’s take an example for learning purpose

create table EmpTemp

(id int identity(1,1),

empName varchar(10))

Command(s) completed successfully.

Here ID is an identity column which will gets increment by one with every insert since the seed for the increment is set to 1.

Let’s insert few rows to EmpTemp table. Values for ID column is not required since it is an identity column, so automatically on insertion of any row to the table set the value of ID in corresponding row.

insert into EmpTemp(empName) values ('Aakhya')

insert into EmpTemp(empName) values ('Kruthika')

insert into EmpTemp(empName) values ('Aanandita')

insert into EmpTemp(empName) values ('Chandanika')

insert into EmpTemp(empName) values ('Ceitra')

insert into EmpTemp(empName) values ('Arasi')

Accede to see the output of Select query

select * from EmpTemp

id          empName

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

1           Aakhya

2           Kruthika

3           Aanandita

4           Chandanika

5           Ceitra

6           Arasi

(6 row(s) affected)

Now run the below query to see the result of IDENT_CURRENT. The IDENT_CURRENT function takes only Table or View name as parameter.

select IDENT_CURRENT('EmpTemp')

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

6

(1 row(s) affected

Now insert one more row and then check this:

insert into EmpTemp(empName) values ('Siddhiksha')

(1 row(s) affected)

select IDENT_CURRENT('EmpTemp')

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

7

(1 row(s) affected)

What does the function do ?

CONCLUSION: This always gives you the current id value from the table.

PART 2: Interesting to see!!! THE @IDENTITY

Run the query below.

insert into EmpTemp(empName) values ('Sakshi')

(1 row(s) affected)

select IDENT_CURRENT('EmpTemp')

select @@IDENTITY

OUTPUT

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

8

Both IDENT_CURRENT and @@IDENTITY that means @@IDENTITY does the same thing!!!!

Hang around and observe:

Open a new Query window and run the below query again:

select IDENT_CURRENT('EmpTemp')

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

8

(1 row(s) affected)

select @@IDENTITY

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

NULL

(1 row(s) affected)

Weird and wonderful! correct ???

·       IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

Post Reference: Vikram Aristocratic Elfin Share

Identity Columns in Table Object with DBCC Checkident

Identity Columns : When a row is inserted into a table the column will take the value of the current seed incremented by the step.

An identity column is not guaranteed to be unique nor consecutive. You should always place a unique index on an identity column if your system requires uniqueness.

CREATE TABLE table_name

(col1 INT IDENTITY(1,1),

 col2 INT)

How to reset Identity column.

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.




DBCC CHECKIDENT (yourtable, reseed, 34)

If table has to start with an identity of 1 with the next insert then table should be reseeded with the identity to 0




DBCC CHECKIDENT (yourtable, reseed, 0)

insert into table_name (col2) values(20)

insert into table_name (col2) values(30)

select * from table_name

col1        col2

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

1           20

2           30

(2 row(s) affected)

How to find current identity seed? (using DBCC checkident)

DBCC checkident (table_name)

Checking identity information: current identity value '2', current column value '2'.

Remember the next value will be the current seed plus the step

Post Reference: Vikram Aristocratic Elfin Share

Logical Query Processing Phase

Lets delve into the Logical Query Processing Phases, below given is the general form of query, along with the steps according to the order in which different clause are logically processed.

(8) SELECT (9) DISTINCT (11) TOP

(1) FROM <left_table>

(3) <join_type> JOIN <right_table>

(2) ON <join_condition>

(4) WHERE <where_condition>

(5) GROUP BY <group_by_list>

(6) WITH {CUBE | ROLLUP}

(7) HAVING <having_codition>

(10) ORDER BY <order_by_list>

In most of the programming language the code is executed in the order in which it is writer. In SQL, the first clause that is processed is the FROM clause, while the SELECT clause which appears first processed almost last.

Each step in query processing generates VIRTUAL TABLE that is used as the input for the next step to process. These virtual tables are not available to the caller. Only the table generated by the final step is returned to the caller.

Brief on various phases of Logical Query Processing

(1)   FROM: A Cartesian product(cross join) is performed between the tables in the FROM clause, and as a result a virtual table gets generated (VT1).

(2)   ON: The ON filter is applied to VT1. Only rows for which the join condition matches or / for which the join condition is true are inserted to form VT2.

(3)   OUTER (join): If outer join is specified, matching rows from the VT1 along with all the rows of VT2 are picked up and inserted to form VT3.

(4)   WHERE: The where filter is applied to VT3. Only rows for which the <where_condition> is evaluate to TRUE are inserted to form VT4.

(5)   GROUP BY: The rows from the VT4 are arranged by groups based on the column list specified in the GROUP BY Clause. VT5 is generated.

(6)   CUBE | ROLLUP: Subgroups are generated and added to the rows of VT5 to form VT6.

(7)   HAVING: The having filters are applied to VT6. Only groups for which the <having_condition> is true are inserted to VT7.

(8)   SELECT: The select is performed on VT7 to generate VT8.

(9)   DISTINCT: Duplicate rows are removed from VT8 to for VT9.

(10)ORDER BY: The rows from the VT9 are sorted according to the column list specified in the  ORDER BY clause to form VT10

(11)TOP: The specified number or percentage of rows are selected from top to form VT11.

And this VT11 is return to the caller.

In next post I will explain phases of query processing taking an example. By then Have gr8 coding. J  

Post Reference: Vikram Aristocratic Elfin Share

Backup and Restore with Split and Mirror

Backup and Restore your SQL Database

You can take the backup of your database by few line of t-sql like the one below

BACKUP DATABASE PracticeDatabase TO

DISK = N'D:\PracticeDatabase.bak'

, DISK = N'D:\try\PracticeDatabase.bak'

WITH FORMAT

NAME = N'PracticeDatabase-Full Database Backup'

GO

WITH FORMAT : means if there exist any backup with the name u defined in the query then it will delete/format that backup.

How to Split your database backup

There are situation where you want to split your database backup fine and want one file to stored in say d drive and another in e drive. You can do this very easily. Look at the code below

BACKUP DATABASE PracticeDatabase TO

DISK = N'D:\PracticeDatabase.bak'

, DISK = N'D:\try\PracticeDatabase.bak'

WITH FORMAT

NAME = N'PracticeDatabase-Full Database Backup'

GO

Here the backup is not going to stored in two location instead the whole backup get divided into two parts. One part get stored in D:/ and another will in D:/try

What is mirror backup and how can you take this?

Mirror backup is nothing but taking the same backup in two different locations.

BACKUP DATABASE [PracticeDatabase] TO
DISK = N'D:\PracticeDatabase.bak'
MIRROR TO DISK = N'D:\try\PracticeDatabase.bak'
WITH FORMAT
NAME = N'PracticeDatabase-Full Database Backup'

How to restore database

Restoring a database is very simple you just need to write few line of sql statement specifying the database name where you want to restore your database and the path where the backup file is lying.

RESTORE DATABASE TempPractice
FROM DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'

Here we are restoring our database backup file at TempPractice database. And the backup file is lying at C:\Backup\SingleFile\.

How to restore Split database

Now let us see an example where we restore a database from a split file. This method is very similar to restoring a database from a single file; just add an additional DISK option.

RESTORE DATABASE [TempPractice]
FROM DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

Here the database AdventureWork split into three part AdventureWork 1, AdventureWork 2, AdventureWork 3.  So to restore such split database you need to specify each and every split backup file in Disk option.    

Post Reference: Vikram Aristocratic Elfin Share