Schema in sql server: Creating adding altering default schema finding in a database

In this article we will walk through Create or adding schema, Default schema, Alter or change schema, show the schema, Drop schema, change schema owner,find schema etc in sql server 2005,2012, 2014.

What is schema in sql server?

Schema is container of database objects like table, view, stored procedures etc.

 

In simple word a database can have more than schema and a schema can have more than one tables, view, procedures etc.

Create or adding schema in a database sql server:

We can create schema by using SSMS or by T-Sql

Using T-Sql

CREATE SCHEMA Exact_Help 

Using Sql server management studio (SSMS)

1. Go to Databases -> Your Database -> Security -> Schemas

2. Right click on schemas and choose "New Schemas ..."

3. Write any schema name and click on OK button.

Default schema sql server:

Default schema means if schema name has not specified in the object name then in which schema sql server will search first.

Default schema depends upon sql server user. Sql server will search the objects in schema dbo if it doesn't find it in default schema of user. 

Good example:

Creating a user named manish with default schema Exact_Help

--Creating login

CREATE LOGIN Manish

    WITH PASSWORD = 'manish';

--Creating user with default schema "Exact_Help"

CREATE USER Manish

FOR LOGIN Manish

    WITH DEFAULT_SCHEMA = Exact_Help

Creating two tables in the schema dbo and Exact_Help respectively and inserting some records into it.

--Creating the table in schema dbo   

CREATE TABLE dbo.tblEmployee(

    ntID BIGINT PRIMARY KEY IDENTITY,

    vcName  VARCHAR(100)

)

--Inserting record into it

INSERT INTO dbo.tblEmployee VALUES('Scott')

Sql server add table to schema:

--Creating the table in schema exact_Help

CREATE TABLE Exact_Help.tblEmployee(

    ntID BIGINT PRIMARY KEY IDENTITY,

    vcName  VARCHAR(100)

)

--Inserting record into it

INSERT INTO Exact_Help.tblEmployee VALUES('Greg')    

Now I have logged in  the sql server with user name Manish and password manish. Now if we will execute following sql query:

SELECT * FROM tblEmployee

In which schema it will search the table tblEmployee?

First it will search in the default schema of the user Manish which is Exact_Help. And we will get output:

ntID	vcName
1	Greg

This is equivalent to write:

SELECT * FROM Exact_Help.tblEmployee

What will happen if I will drop the table tblEmployee from schema Exact_Help that is:

DROP TABLE Exact_Help.tblEmployee

Now if we will execute this sql query:

SELECT * FROM tblEmployee

Will we get error message?

In this case sql server will first search in the default schema of user Manish which Exact_Help. Since, it is not present here. So it will search in the schema dbo and we will get output:

ntID	vcName
1	Scott

This is equivalent to write:

SELECT * FROM dbo.tblEmployee

Performance Tips:

Always write full qualified object name. That is object name with schema name. Since sql server will search the object only in that schema. For example:

SELECT * FROM Exact_Help.tblEmployee

Or

SELECT * FROM dbo.tblEmployee

How to move or transfer table from one schema to other ? 

Sql server 2012 change table schema without dropping table in server explorer 

Syntax:

ALTER SCHEMA <Destination_Schema> TRANSFER <Source_Schema>.<Object_Name>

For example I want to move tblStudent from schema Exact_Help to dbo:

ALTER SCHEMA dbo TRANSFER Exact_Help.tblStudent

Where to find schema in sql 2012  

By following script you can all the schema in a database:

SELECT name 

FROM sys.schemas 

By using ssms we can get it from:

Go to Databases -> Your Database -> Security -> Schemas 

How to compile any query without executing it in sql server
How to perform case sensitive comparison in sql server
Sql server string comparison is case sensitive or not
Insert default value when data or parameter is null in sql server
SQL SERVER PPT

Difference between actual execution plan and estimated execution plan in sql server

Difference between actual execution plan and estimated execution plan in SQL server

In SQL server management studio we can generate two types of the execution plan of any queries:

1. Estimated execution plan

2. Actual execution plan

There are many different ways to view estimated and actual execution plan:

A. By using key shortcut

Estimated execution plan: Ctrl + L

Estimated execution plan: Ctrl + M

B. By using SSMS (Using Icon)

C. By using SSMS (Using Query menu)

D.  By using SSMS:

We can also get these options if we will right click on any query page

Note: To get the actual plan, first enable the actual execution plan by using any above methods then execute the query. You will get a new tab "Execution Plan" in the query result window.

Why there are two types of the execution plan?

We can get the execution plan of a query without executing the query and also with executing the query. There are many scenarios in which we cannot execute the query while we need to view the execution plan to analyze the query performance. Following are may be the reason in which we cannot execute the query:

1. Any INSERT, UPDATE, DELETES or MERGES statement in a live server which can modify the tables.

2. A SELECT statement which takes too much time to execute the query.

3. A complex stored procedures in which we are not sure that execution will affect the application or not etc. 

Due to this SQL server has introduced two types of the execution plan:

1. Not executing the query: It is called estimated execution plan. As the name suggests SQL server query is guessing or estimating that query may follow this execution plan when SQL server will execute the query.

2. Executing query: It is called actual execution plan. As the name suggests it is actual execution plan, which SQL server has followed to execute this query.

Some important differences: Estimated Execution Plan Vs Actual Execution Plan

1. Estimated execution plan can generate the plan for language elements while not by the actual execution plan. Examples of SQL server language elements are:

a. Assignments

b. Type conversion

c. Declaration

d. If statement

e. While loop

f. A Select statement without from a table etc.

For example:

--Declaration

DECLARE @Var AS INTEGER

--Assignments

SET @Var = 10

--If statement

IF @Var > 5 BEGIN

    --Select statement without from a table

    SELECT @Var

   

END ELSE BEGIN

    --Select statement without from a table

    SELECT 0

   

END

--While loop

WHILE @Var > 10 BEGIN

    --Assignments

    SET @Var = @Var - 1

   

END

If we will generate the estimated execution plan:

 

It is generated the estimated execution plan for each language elements while SQL server will not generate any actual execution plan of the language elements.

2. In case of estimated execution plan it doesn't evaluate the language element while actual execution plan evaluates the language elements and on the basis of this, it generates the execution plan. For example:

DECLARE @Var AS INTEGER = 10

IF @Var > 5 BEGIN

    SELECT * FROM tblEmployee

WHERE ntEmployeeID = @Var

END ELSE BEGIN

    SELECT TOP(1) * FROM tblEmployee

WHERE ntEmployeeID < @Var

END

Its estimated execution plan:

Instead of validating the IF condition estimated execution plan has generated for both IF block as well as ELSE block.

Its actual execution plan:

Actual execution plan has first evaluated the IF condition and generated the execution plan of only that block which satisfies the IF condition.

Another example:

DECLARE @Var AS INTEGER = 3

WHILE @Var > 0 BEGIN

    SELECT * FROM tblEmployee WHERE ntEmployeeID = @Var

    SET @Var = @Var - 1

END

Its estimated execution plan:

 

In the above query WHILE loop will iterate three times but estimated execution plan doesn't evaluate it.

Its actual execution plan:

 

3. Estimated execution plan doesn't execute the query. If our queries creates any table and then we referenced that table in another query it will not able to generate the estimated execution plan. For example:

CREATE TABLE tblStudent(

    ntStudentID BIGINT PRIMARY KEY,

    vcName VARCHAR(100)

)

SELECT * FROM tblStudent

If we will try to generate the estimated execution plan we will get an error message: Invalid object name 'tblStudent'.

4. Sometimes both estimated and actual execution plan may generate the wrong execution plan due to not updated statistics. For example, I am creating a table and inserting some records into it:

--Creating table

CREATE TABLE tblEmployee(

    ntEmployeeID BIGINT PRIMARY KEY IDENTITY,

    vcName VARCHAR(100),

    ntAge INT

)

--Inserting some records into it

INSERT tblEmployee

SELECT

    LEFT([Text],100),

    severity 

FROM sys.messages

--Creating non-clustered index

CREATE NONCLUSTERED INDEX NCI_Age ON tblEmployee(ntAge)

   

Now I am deleting all records where ntAge is 21

DELETE FROM tblEmployee WHERE ntAge = 21

It will delete 671 records from the table.

Now I am generating the execution plan for this query:

SELECT vcName FROM tblEmployee WHERE ntAge = 21

We will get the same estimated and actual execution plan:

 

SQL Server query optimizer has generated the above execution plan assuming that table has 671 records where ntAge is 21 while we have already deleted those records. So now we are going to update all the statistics in the table tblEmployee:

UPDATE STATISTICS tblEmployee

Now if we will check the estimated or actual execution plan we will find query optimizer has changed its execution plan:

Sometimes we may get different estimated and actual execution plan due to not updated statistics.

Indexes best practices in SQL server: Where to start creating indexes?
SQL server query optimization tips: Tuning best practices with examples
Fastest or most efficient way to insert data in SQL server
Index in SQL server with examples
What is clustered index in SQL server

How to compile any query without executing it in sql server

In sql server we can compile any sql server quires without executing it by setting the property NOEXEC.

Syntax:

SET NOEXEC {ON|OFF}

When It is set to on then if we execute any sql quries it will only compile it while if it is set to off then it will compile the sql quries as will execute it. Default is off. For example:

SET NOEXEC OFF

SELECT 'Exact Help'

Output: Exact Help

When we will execute the above query first it will compile then execute it .

SET NOEXEC ON

SELECT 'Exact Help'

Output: No output.

When we will execute the above query it will only compile.

Use of NOEXEC ON

1. To check the synax of sql statements.

2. To check the existance of objects.

3. To debug the batch of quries.

Scope of NOEXEC property:

Scope of NOEXEC property is within a session. In other session we have to set the NOEXEC property once again.

Note: When we login or open a new query page or tab we creates a new session in sql server.

How to recover deleted data from table without using database backup in sql server Sql server backup stored procedures and auto schedule
Set up auto only schema or script backup in sql server
Sql server export stored procedures

Variables in sql server 2008 R2

Variables in sql server

In sql server there are three types of variables

Local variables in sql server:

Syntax for declaration and initialization of local variable in sql server:

DECLARE {@local_variable [AS] data_type  [ = value ]} [,...n]

Where:

@local_variable: It is name of the variable.

Local variable or identifier naming rule in sql server:

1. Variable name must be started with @ character.

2. Variable names can have alphabets (including some character from other languages), digits, #, @ and $.

3. Variables name are case sensitive.

4. It cannot be system variable like @@ROWCOUN, @@ERROR etc.

Valid name of identifiers:

1. @a

2. @123

3. @#

4. @@@

5. @#@$1b_

6. @VARCHAR

Invalid name:

1. abc

2. @total value

3. @@IDENTITY

data_type: It can be any data type of sql server including CLR data type except text, ntext, or image.

Value: It can be any constant or expression which can explicitly or implicitly convertible into its data type.

Note: It is bad practice to start variables name by @@ since it is used in the naming of system variable name.

Examples:

Declaration of a local variable of type integer  

DECLARE @empId INTEGER

Or

DECLARE @empId AS INTEGER

Declaration of more than one variable at the same time:

DECLARE

    @empId AS INTEGER,

    @empName AS VARCHAR(30),

    @isActive BIT

   

Declaration and initialization of variable at the same time:

Default value of all local variables is NULL. We can initialize the local variable at the time of declaration. For example:

1. DECLARE @empId INTEGER = 100

2.

DECLARE

    @empId AS INTEGER = 10,

    @empName AS VARCHAR(30)= 'David',

    @empAge INTEGER,

    @isActive AS BIT = 0

Initialization of local variable in sql server:

We can also initiate the local variables after the declaration.

For more details go through the link: Initialization of variables in sql server

Scope of local variables:

Scope of local variables is batch. We can think batch as a set of sql queries which are executed at the same time. Stored procedure, functions, triggers are examples of named batch. If we declare a local variable in a particular batch, it will no longer available in the other batch. For example:

Suppose we execute a sql query:

DECLARE @CompanyName AS VARCHAR(50)

Now we assign a value to it and execute it

SET @CompanyName = 'Exact help'

Then we will get an error message: Must declare the table variable "@CompanyName”

Since scope of the @CompanyName variable was only up to first batch. So we have to assign a value to in the same batch that we have to execute both statements in same batch, for example:

DECLARE @CompanyName AS VARCHAR(50)

SET @CompanyName = 'Exact help'

Note: Go keyword in sql server always created a new batch. So if we will execute following set of sql queries:

DECLARE @CompanyName AS VARCHAR(50)

Go

SET @CompanyName = 'Exact help'

We will get same error that is: Must declare the table variable "@CompanyName"

Use of local variables in sql server.

1. We can use local variable to store values returned from function, sql query etc and perform any operation on it. For example:

DECLARE @Id AS INTEGER

SET @Id = SCOPE_IDENTITY()

DECLARE @Marks INT

DECLARE @Name AS VARCHAR(50)

SELECT

@Name = Name,

@Marks = Marks

FROM Student WHERE StuId = 2

2. Local variables can be used as parameters of stored procedure and function. For example:

CREATE PROCEDURE PrintMsg(

    @Msg AS VARCHAR(100)

)

AS

BEGIN

    SELECT @Msg AS [Message]

END

Execution of above stored procedure:

EXECUTE PrintMsg 'Exact Help'

3. It can also be used as rerun type of function.

Table variables in sql server:

Syntax for declaration of table variables

DECLARE @table_variable [AS]

{TABLE (

      { <column_definition>} [ ,... ]

)}

| <user_defined_table_type>

Where

@table_variable: Name of the table variable. Naming rule will same as the naming of local variable in sql server.

<column_definition>: Definitions of all columns same as normal table which we generally create using CREATE TABLE statement. All the column definitions are separated by comma operator.

Note: We can also include column as well as table label constraints like PRIMARY KEY, NOT NULL etc.

<user_defined_table_type>: We can also defined table using the user defined table type. For more detail go through the: How to create user defined table type.

Example:

Declaration of simple table variable in sql server:

DECLARE @Employee AS TABLE(

    EmpId INTEGER,

    EmpName VARCHAR(100),

    DOJ DATETIME

)

Declaration of table variable including constraints in sql server:

DECLARE @Employee AS TABLE(

    EmpId INTEGER IDENTITY PRIMARY KEY,

    EmpName VARCHAR(100) NOT NULL,

    DOJ DATETIME DEFAULT(GETDATE())

)

Declaration of table variable using user defined table type in sql server:

CREATE TYPE Employee AS TABLE(

    EmpId INTEGER IDENTITY PRIMARY KEY,

    EmpName VARCHAR(100) NOT NULL,

    DOJ DATETIME DEFAULT(GETDATE())

)

DECLARE @Employee AS Employee

Sql query on table variable:

Insert statement:

INSERT INTO @Employee(EmpName,DOJ)

    VALUES('Ritesh','10/22/2011')

Select statement:

SELECT * FROM @Employee

Update statement:

UPDATE @Employee SET

    EmpName = 'manish'

WHERE EmpId = 1

Delete statement:

DELETE FROM @Employee

In general we can say we can perform the entire sql query on table variables which we can perform on normal tables.

Scope of table variables:

Scope of table variables is batch. We can think batch as a set of sql queries which are executed at the same time. Stored procedure, functions, triggers are examples of named batch. If we declare a table variable in a particular batch will no longer available in the other batch. For example:

Suppose we execute a sql query:

DECLARE @Student AS TABLE(

    RollNo INT,

    Name VARCHAR(30)

)

Now we execute a select query on this table variable

SELECT * FROM @Student

Then we will get an error message: Must declare the table variable "@Student"

Since scope of the @Student table was only up to first batch. So to perform any sql query on the @Student table we have to execute the declaration and perform operation at same batch that is

DECLARE @Student AS TABLE(

    RollNo INT,

    Name VARCHAR(30)

)

SELECT * FROM @Student

Note: Go keyword in sql server always created a new batch. So if we will execute following set of sql queries:

DECLARE @Student AS TABLE(

    RollNo INT,

    Name VARCHAR(30)

)

Go

SELECT * FROM @Student

We will get same error that is: Must declare the table variable "@Student"

Use of the table variables in sql server:

1. We can use table variables to store the values in a batch to perform some operation on it. For example suppose we a have student table like this:

StuId	Name	Subject	Marks
1	Robert	Phy	60
2	Pacino	Phy	54
3	Pacino	Che	45
4	Robert	Che	56
5	Pacino	Bio	67
6	Nicholson	Che	32
7	Nicholson	Phy	41
8	Nicholson	Bio	44
9	Robert	Bio	72

DECLARE @Student AS TABLE(

    RollNo INT,

    Name VARCHAR(30)

)

INSERT INTO @Student

SELECT TOP(5) StuId,Name FROM Student

2. To pass as parameters of stored procedures or functions but it must be read only.  For example:

CREATE TYPE StudentType AS TABLE(

    RollNo INT,

    Name VARCHAR(30)

)

GO

CREATE PROCEDURE GetStudentInfo(

    @StudentData StudentType READONLY,

    @InsertedBy VARCHAR(50),

    @InsertedData DATETIME = NULL

)

AS

BEGIN

   

    SELECT @InsertedBy AS InsertedBy , *

FROM @StudentData

   

END

GO

DEClARE @Student AS StudentType

INSERT INTO @Student

SELECT TOP(5) StuId,Name FROM Student

EXECUTE GetStudentInfo @Student,'Davis'

3. Table variable can also use as return type of function. For example:

CREATE FUNCTION GetStudentInfo()

RETURNS @StudentData TABLE(

    RollNo INT,

    Name VARCHAR(30)

)

AS

BEGIN

    INSERT INTO @StudentData

    SELECT TOP(5) StuId + 100,Name FROM Student

   

    RETURN

   

END

SELECT * FROM [dbo].[GetStudentInfo] ()

Output:

Roll No	Name
101	Robert
102	Pacino
103	Pacino
104	Robert
105	Pacino

Note: A function which returns table types is known as tabled value function otherwise it is called scalar functions.

Cursor variable in sql server

Syntax for declaration of cursor variables

DECLARE {@cursor_variable_name CURSOR}[,...n]

Where:

@cursor_variable: It is the name of the cursor variable   

Example:

DECLARE @Student_Id_Cur CURSOR

Use of cursor variable:

We can use cursor variable as output parameters of stored procedures. Cursor type parameters of stored procedure must be varying type and output parameter. For example:

CREATE PROCEDURE GetStudentInfo(

    @Student_Id_Cur CURSOR VARYING OUTPUT

)

AS

BEGIN

    SET @Student_Id_Cur = CURSOR FOR

SELECT TOP(3) StuId FROM Student

    OPEN @Student_Id_Cur

END

Execution of stored procedure:

DECLARE @Student_Id_Cur CURSOR

DECLARE @RollNo INTEGER

EXECUTE GetStudentInfo @Student_Id_Cur OUTPUT

FETCH @Student_Id_Cur INTO @RollNo

WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT * FROM Student WHERE StuId = @RollNo

    FETCH NEXT FROM @Student_Id_Cur INTO @RollNo

END 

Indexes best practices in sql server: Where to start creating indexes?
Sql server query optimization tips : Tuning best practices with examples
Fastest or most efficient way to insert data in sql server
Index in sql server with examples
What is clustered index in sql server
What is non clustered index in sql server