SQL Server tutorial and query examples

This SQL Server tutorial, we will discuss what is SQL server and also we will various SQL server query examples.

  • What is sql server?
  • SQL Server installation
  • Change authentication mode after installation of SQL Server
  • Get version in sql server
  • Transaction in SQL Server
  • Constraints in SQL Server
  • Add Primary Key Constraint to an existing table in sql server
  • Drop Primary Key Constraint in sql server
  • Create a database by command in sql server
  • Get database server name and version in sql server
  • How to take backup in SQL Server using management studio?
  • Restore database in sql server using management studio
  • sys.objects type in SQL Server
  • Get Column names of a table in SQL Server
  • Increase column size in sql server
  • Rename database in sql server
  • The database could not be exclusively locked to perform the operation rename database in sql server
  • How to assign column value to variable sql server
  • Add default date to column in SQL Server
  • Add new column to existing table in sql server
  • Rename table name or column name in SQL server
  • How to trim spaces in column values in sql server
  • How to get distinct data from table in sql server
  • Query to get records between two dates from tables in sql server
  • Get weekday name in sql server
  • How to find the difference in retrieving data with and without using index in sql server
  • Modify table structure in GUI in Sql Server
  • While loop in SQL Server
  • How to use If else in sql server
  • Case statement in SQL Server
  • SET XACT_ABORT ON in sql server
  • The service did not start due to logon failure in sql server
  • Like operator examples in SQL Server
  • View in sql server
  • Optimization Tips in sql server
  • Event ID: 3760 Database could not be access issue
  • Sql Server 2014 Wait on the Database Engine recovery handled failed
  • Reporting services catalog database file existence failed
  • Saving changes is not permitted sql server

SharePoint 2016 Tutorial Contents

What is sql server?

SQL Server one of the competitor to Oracle is a relational database management system (RDBMS) developed by Microsoft. SQL Server is built on top of SQL, a standard programming language for interacting with the relational databases.

One of the main components of the SQL server is the Database Engine. The database engine has components like a relational engine that processes queries and a storage engine that manages database files, pages, pages, indexes, etc. Other components like stored procedures, views, and triggers are also created and executed by the Database Engine in the SQL server.

SQL stands for “Structured Query Language”. It is a query language used for accessing and modifying information in the database. It is an ANSI/ISO standard.

SQL is a non-procedural, English like language that processes data in groups of records rather than one record at a time.

Functions Of SQL:

  • SQL can execute queries against a database
  • SQL can insert, update, delete and retrieve data from a database
  • SQL can create new databases, can create new tables in a database
  • It also, can create stored procedures in a database, can create views in a database
  • It can set permissions on tables, procedures, and views.

You may like following SQL Server tutorials:

SQL Server installation

You can follow the below articles for SQL Server installation steps for various versions of SQL Server.

Change authentication mode after installation of SQL Server

Now, we will see how to change authentication mode after installation of SQL Server. This steps I have followed for SQL Server 2008.

During installation, the SQL Server database engine is set to either Windows Authentication mode or SQL Server and Windows Authentication mode.

If Windows Authentication mode is selected during installation, the sa login remains disable.

If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled.

To enable the sa login, use the Alter Login statement.

The sa login can only connect to the server by using SQL Server Authentication.

To Change security authentication mode, in SQL Server Management Studio Object Explorer, right-click on the server, and then click Properties.

On the Security page, under the Server authentication, select the new server authentication mode, in the SQL Server Management Studio dialog box, click ok to acknowledge the requirement to restart the SQL Server.

To restart SQL Server from SQL Server Management studio:

In object explorer, right-click on your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To Enable the sa login by using Transact-SQL:

Execute the below sql statements to enable the sa password and assign a password.

Alter LOGIN sa Enable;

Alter Login sa WITH PASSWORD = '<enterstrongpassword>';

To Enable the sa loginby using Management Studio:

In Object Explorer, expand Security,expand Logins,right click sa, and then click Properties. on the General page , you might have to create and confirm a password for the sa Login. On the Status page, in the Login section, click Enabled, and then click OK.

This is how we can change authentication mode after installation of SQL Server.

Get version in sql server

Here, we will see how to check whether our sql server is 32 bit or 64 bit, version of sql server. It will also give information like whether it is sql server 2008 or 2005 or 2012 etc.

The sql query to get the version is:

SELECT @@VERSION

The output will come like below:

Output:
Microsoft SQL Server 2008 (SP3) – 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Transaction in SQL Server

Now, we will see what are Transactions in SQL Server.

A transaction is a single operation or set of operations that succeed or fail together as a whole. Means suppose in a transaction there are 3 statements, then either it will execute all statements or it will not execute any statement.

Transactions allow you to ensure consistency in your data.

  • BEGIN TRAN/TRANSACTION: This will start the transaction.
  • COMMIT TRAN/TRANSACTION: This will save the changes.
  • ROLLBACK TRAN/TRANSACTION: ROLLBACK is the opposite of COMMIT. Instead of saving, it undoes all changes made in the transaction.

Difference between COMMIT and ROLLBACK is that if a severe error occurs during the execution of a transaction, SQL Server rolls back the transaction.

Example:1

BEGIN TRAN
UPDATE Employees Set firstname='Bijay' where ID=4
UPDATE Salary set Salary=5000 where ID=4
COMMIT TRAN

Example:2

BEGIN TRAN
UPDATE Employees Set firstname='Bijay' where ID=5
UPDATE Salary set Salary=5000 where ID=5
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
COMMIT TRAN

Here in the 2nd example if some error occurred then it will ROLLBACK the transaction, else it will COMMIT the transaction.

Constraints in SQL Server

Now, we will discuss different Constraints in SQL server. The Constraints are like Not Null, Unique, Primary key, Check, Default, Foreign Key.

Constraints are used to enforce the integrity of the data in the columns, SQL Server 2005 provides the following mechanism to enforce the data in the column.

Below are the types of constraints in sql server.

  • Not Null
  • Unique
  • Primary Key
  • Check
  • Default
  • Foreign Key

Not Null Constraints

If it is applied to a column that column will not allow null values into it. This can be imposed on any no of columns.

Syntax:

CREATE TABLE <table_name>(column_name1 <dtype> [width] [Not Null],column_name1 <dtype> [width] [Not Null],
                        ……………………………………
                        ……………………………………
                        column_namen <dtype> [width] [Not Null])

For Example:

CREATE TABLE Bank(Custid int Not Null,Cname varchar(50),Bal decimal(7,2) Not Null)

After creating if you try to insert a null value into the Custid or Bal Columns it will restrict us.

Drawback:

The drawback with Not Null is that even if it restricts null values it will not restrict duplicate values.

Unique Constraints

To avoid the drawbacks of Not Null, we are using Unique constraints.

If it is imposed on a column or columns they will not allow duplicates or duplicate values into it.

Remember one thing: Unique, Primary key, Check, Foreign key Constraints can be imposed in two ways

  • Column Level Defination
  • Table Level Defination

Column Level Defination:

In this case the constraints defination is immediately followed after the Column defination

Syntax:

Create table <table_name >(column)_name1 <dtype> [width] [[Constraint<Name>] <Type>],
                          column)_name1 <dtype> [width] [[Constraint<Name>] <Type>],
                          …………………………………………………
                          column)_name1 <dtype> [width] [[Constraint<Name>] <Type>],

Example:

create table Bank(Custid int Unique,Cname varchar(50),Bal decimal(7,2)Not Null)

Now if you will try to insert a duplicate value into the Custid column it will restrict us.

Table Level Defination:

In this case the constraint defination is immediately followed after the column defination.

CREATE TABLE <table_name>(column_name1 <dtype> [width],column_name1 <dtype> [width],
                        ……………………………………
                        ……………………………………
                        column_namen <dtype> [width],
                        [[Constraint<Name>] <type> (<collist>)],
                        …………………………………)

Example:

Create table Bank(Custid int,Cname varchar(50),Bal decimal(7,2) Not Null,Constraint Cusid_UQ Unique(Custid))

Primary Key Constraint

While creating a primary key constraint we need to keep in the mind that a table can contain only one primary key which can be present in on a single column or multiple columns also.

Creating a Primary key in Column Level:

Create table Bank(Custid int Constraint Cusid_PK Primary Key, Cname varchar(50), Bal decimal(7,2) Not Null)

Creating a Primary key in table level:

create table Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Custid_PK Primary Key(Custid))

Check Constraints

If we want to check the values present in a column to be according to a specified value we use this constraint.

For Example:

Creating check constraint in column level:

Create table Bank(Custid int,Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Bal_CK Check(Bal>=1000))

Creating check constraint in table level:

Create table Bank(Custid int,Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Bal_CK Check(Bal BETWEEN 1000 AND 5000))

Foreign Key Constraint

It is a column or combination of columns that are used to establish and enforce a link between the data in two tables.

In a foreign key reference, a link is created between two tables when the columns in a table reference the columns that hold the primary key of another table, which becomes a foreign key in the first table.

Creating the Foreign key constraints in column level:

Create table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate DateTime, sal money, Comm money, Deptno int Constraint Deptno_Ref References Dept(Deptno))

Creating the Foreign key constraints in table level:

Create table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate DateTime, sal money, Comm money, Deptno int Constraint Deptno_RefForeign Key(Deptno) References Dept(Deptno))

This is about Constraints in SQL Server.

Add Primary Key Constraint to an existing table in sql server

Now, we will see how to add Primary Key Constraint to an existing table in SQL server through sql query. We can drop Primary Key Constraint by writing a query as well as we can drop by using Object Explorer.

By using SQL Query:

Below is the query to add a primary key to an existing table:

ALTER TABLE Employees 
   ADD CONSTRAINT PK_Employees_ID PRIMARY KEY (ID)

Here ID is the Column name and PK_Employees_ID is the Constraint name.

Similarly, if you want to make 2 columns as the primary key then you have to write the query like below:

ALTER TABLE Employees 
   ADD CONSTRAINT PK_Employees_IDAGE PRIMARY KEY (ID, AGE)

Here both ID and AGE column will be the primary key.

By Using Object Explorer:

Right-click on the table -> Click on Design or Right-click on the column for which you want to make primary key and then click on Modify.

The above step will open the table in Table Designer mode.

Now Right-click the row with the primary key and choose Set Primary Key as shown in the fig below:

Add Primary Key Constraint to an existing table in sql server

Here ID was the primary key column. But the default Constraint name will come as “PK_YourTableName”.

Drop Primary Key Constraint in sql server

Now, we will see how to drop a Primary Key Constraint of a table in SQL server. We can drop Primary Key Constraint by writing a query as well as we can drop by using Object Explorer.

By using Query:

Below is the query to remove or drop a primary key:

ALTER TABLE Employees
DROP CONSTRAINT PK_Employees_ID
GO

Here PK_Employees_ID is the Constraint name.

Using Object Explorer:

Right click on the table -> Click on Design or Right click on the Primary Key column and then click on Modify.

The above step will open the table in Table Designer mode.

Now Right-click the row with the primary key and choose to Remove Primary Key as shown in the fig below:

Drop Primary Key Constraint in sql server

Here ID was the primary key column.

Create a database by command in sql server

Now, let us check how to create a database by using the command in sql server.

To create a database sql server provides create database command that has the following syntax.

Syntax:

Create database <dbname>

When you create a database using the above syntax one data file and one log file is automatically created for the database in the default location of sql server i.e, C:\program files\Microsoft SQL server\MSSQL 10.MSSQL SERVER\MSSQL\DATA

Sql server provides a stored procedure named sp_helpdb that takes database name as an argument and returns details of that database like datafile and logfile created for it and memory allocated for those files.

Ex: sp_helpdb’DB1’

To create a database by specifying datafile and logfile specification manual, use the following syntax.

Syntax:

Create database <dbname>
On(<datafile specification>)
Logon(<logfile specification>)

Example:

create database DB
on (name=db_dat,filename=’d:\db_dat.mdf’,size=4,filegrowth=2,maxsize=unlimited)
logon(name=db_log,filename=’d:\db_log.ldf’,size=2,file growth=1,maxsize=300)

Creating a sql database in GUI:

To create a database in GUI below are the steps.

Within Object Explorer right click on the “databases” category and choose a new database that opens “new database” dialog box.

Then within the dialog box provide a database name which will automatically create one data file and one log file with default specifications, which you can modify.

After making the required changes to the specification of the files click on the “Ok” button to complete creating a database.

This is how we can create a database in sql server.

Get database server name and version in sql server

Now we will see, how to get the database server name and version in SQL server Below is the SQL query to get the server name of the SQL server.

select @@SERVERNAME As 'Server Name'

Below is the sql query to get the version of the sql server:

Select @@VERSION As 'SQL Server Version'

The result will come like below:

Get database server name and version in sql server

How to take backup in SQL Server using management studio?

Now, let us see how to take backup of a database using SQL Server from SQL Server management studio.

To take backup to follow below steps:

Step-1:
Right-click on the database then Select Tasks -> Back up… as shown in the figure below:

How to take backup in SQL Server using management studio

Step-2:
Now it will open a dialog box, thereby default it will take the path of
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\

But if you want you can change that. For this click on Add then it will Select Backup Destination dialog box.

There you can select the new path and click on OK as shown in the figure below:

take backup in SQL Server using management studio

Step-3:
After that, the path will be selected like below. Then click on Ok, it will take the back in the selected location.

steps to take backup in SQL Server using management studio

This is how we can take backup in sql server through sql server management studio.

Restore database in sql server using management studio

Now we will see, how to restore the database using the SQL Server management studio.

For this follow the below steps to restore database in sql server using management studio

Step-1:
First, create a database by right click on the database -> New database and from the new database dialog box give a name (Here I have given AspDotNetHelp) and click on OK.

Step-2:
After creating a database, Right-click on the Database -> Task -> Restore -> Database as shown in the figure below.

restore database in sql server

Step-3:
This will open the Restore Database dialog box. In the dialog box To database, select the database (AspDotNetHelp) to which you want to restore. Then from the specify the source and location of backup sets to restore, select from the device. And there select the backup file. For the click on the … button and this will open the Specify Backup and there click on Add as shown in the figure below:

how to restore database in sql server

Step-4:
After clicking on Ok, and then select both checkboxes as shown in the figure below: Before click on Ok, go through the Step-5.

steps to restore database in sql server

Step-5:
In that dialogbox click on Options and select the checkbox Overwrite the existing database (WITH REPLACE) and click on OK. And after that click on OK in step-4.

restore database in sql server using management studio

This is how we can restore database in sql server.

sys.objects type in SQL Server

sys.objects contains very much useful information about each user-defined, schema-scoped object that is created within a database.

If you will run the below query then you will get all the information:

SELECT * FROM sys.objects

Here we will see the types and corresponding description for various objects in SQL Server.

  • AF = Aggregate function (CLR)
  • C = CHECK constraint
  • D = DEFAULT (constraint or stand-alone)
  • F = FOREIGN KEY constraint
  • FN = SQL scalar function
  • FS = Assembly (CLR) scalar-function
  • FT = Assembly (CLR) table-valued function
  • IF = SQL inline table-valued function
  • IT = Internal table
  • P = SQL Stored Procedure
  • PC = Assembly (CLR) stored-procedure
  • PG = Plan guide
  • PK = PRIMARY KEY constraint
  • R = Rule (old-style, stand-alone)
  • RF = Replication-filter-procedure
  • S = System base table
  • SN = Synonym
  • SO = Sequence object
  • SQ = Service queue
  • TA = Assembly (CLR) DML trigger
  • TF = SQL table-valued-function
  • TR = SQL DML trigger
  • TT = Table type
  • U = Table (user-defined)
  • UQ = UNIQUE constraint
  • V = View
  • X = Extended stored procedure

Refer this MSDN article for more information.

Get Column names of a table in SQL Server

Now, we will see how to get all the column names from a table in SQL Server. Below is the query that will retrieve the column names of a table in SQL server.

select COLUMN_NAME from information_schema.columns
where table_name = 'CountryMaster'
order by ordinal_position

Check the figure below:

Get Column names of table in SQL Server

Also if you will run the below query, then you will get some additional details:

select COLUMN_NAME,* from information_schema.columns
where table_name = 'CountryMaster'
order by ordinal_position

This is how we can retrieve column names from a table in sql server.

Increase column size in sql server

Below is the sql query to increase column size in table in sql server.

Below is the script to increase the column size of a table in SQL server. It will increase the Name column size in the Employees table.

use TestDB
ALTER TABLE Employees ALTER COLUMN Name varchar(256)

Here Employees is the table name.

Name is the column name which was of size 50.

Once you execute the script, the size of Name column will become 256.

Rename database in sql server

Now, we will see how to rename an existing database in SQL server

To rename a database use the following sql query.

ALTER DATABASE TestDB
Modify Name = TestDBNew ;

Here TestDB is the old database name and TestDBNew is the new database name.

The database could not be exclusively locked to perform the operation rename database in sql server

Here, let us understand how to fix error The database could not be exclusively locked to perform the operation which comes while renaming an existing database in SQL server

While renaming a database server, I got an error as The database could not be exclusively locked to perform the operation.

The error comes because other users using the same database. So we should make the database so that it can be used by yourself only. And after rename, you can change back so that other users can use this.

So to rename the DB I need to follow the below 3 steps sequentially and the error is gone.

ALTER DATABASE TestDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE TestDB
Modify Name = TestDBNew ;
ALTER DATABASE TestDBNew
SET MULTI_USER WITH ROLLBACK IMMEDIATE

Now you should be able to successfully rename the DB as well as you should not get any error.

How to assign column value to variable sql server

Here, we will see how to assign column value to a variable in SQL Server.

To create a variable in SQL Server 2008, we have to use keyword declare like below:

Declare @UserID INT
Declare @UserName VARCHAR(100)

Similarly, you can declare multiple variables at a time like below:

Declare @UserName VARCHAR(100), @FirstName VARCHAR(50)

To assign a column value to a variable we can use like below:

Select @UserID=UserID from UserMaster where FirstName='Bijay'

Also, you can assign multiple values like below:

Select @UserID=UserID, @UserName=UserName from UserMaster where FirstName='Bijay'

Below is a code that will first declare a variable, then it will assign some value and then it select the value.

DECLARE @Name VARCHAR(50);
SET @Name = 'My Name is Kumar';
SELECT @Name;

This is how, we can assign column value to variable sql server.

Add default date to column in SQL Server

Now we will see, how to insert default DateTime to a column in SQL server in management studio as well as by using query.

As we know through the use of GETDATE() function we can get the current date time in SQL server 2008.

Below is the query to add default date value to a column in sql server.

CREATE TABLE Employees
(
EmpId int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL,
JoiningDate DateTime NOT NULL DEFAULT GETDATE()
)

Check the below figure to add default value using Management Studio.

Add default date to column in SQL Server

This is how we can add default date to a datetime column in sql server table.

Add new column to existing table in sql server

Now, we will discuss how to add a new column to an existing table in SQL server.

You can add columns easily to an existing table in the sql server database table.

Below is a query which will add a DateTime type column which will not accept a NULL value and put default value as the current DateTime.

Use TestDB
ALTER TABLE Employees
ADD ModifiedDate DateTime NOT NULL DEFAULT(Getdate())
GO

Below is the query that will add a varchar type column to the sql server database table which can take NULL values.

Use TestDB
ALTER TABLE Employees
ADD Location varchar(200)null
GO

Check the figure to know about the sql server table structure.

Add new column to existing table in sql server

This is how we can add column to an existing table in sql server.

Rename table name or column name in SQL server

Here we will discuss how to rename a table name or a column name in SQL server through SQL query.

We can rename by using the sp_rename stored procedure.

sp_rename changes the name of the user-defined objects like table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

If the return value is 0 (Zero) means success and if a nonzero number means its a failure.

But it is not recommended to rename stored procedures, triggers, user-defined functions, or views, instead, we should drop the object and re-create it with the new name.

Rename a Table in SQL Server

You can rename a table by using the below query:

sp_rename Employees, EmployeesInfo

Here Employees is the old name and EmployeesInfo is the new name.

Rename a Column in SQL Server Table

Below is the command to rename a column:

sp_rename 'Employees.Name', 'EmployeeName', 'COLUMN';

Here Employees is the table name, Name is the old column name and EmployeeName is the new column name.

This is how we can rename a table name and a column name in sql server.

How to trim spaces in column values in sql server

Now, we will see, how to get trims values of a column in SQL server.

Suppose you have a column name as firstname of varchar type and if it contains any white spaces then when you run the select sql statement like below,

Select firstname from employees

Then it will return all the firstname of all the employees but also the whitespaces will come if any with the names.

So to trim the spaces you can write the select sql statement like below:

SELECT LTRIM(RTRIM(firstname)) AS FirstName from employees

This will trim the whitespaces from the column values in sql server table.

How to get distinct data from table in sql server

Now, we will see how to get distinct data from tables in sql server.

Distinct‘ keyword is used to eliminate duplicate values from a table in the output of a select statement in SQL server. It can be used only once in the select statement that also immediately next to the keyword ‘select’.

Example:

Select distinct deptno from Employee
Select distinct deptno,job from Employee

Query to get records between two dates from tables in sql server

Now, we will discuss how to retrieve records between two dates in sql server.

Retrieve a record from last 7 days:

Suppose you have an employee table and JoiningDate is a column and you want to retrieve who are the employees joined in last 7 days, then you can query like below:

select * from Employees WHERE JoiningDate BETWEEN GetDate()-7 AND GetDate()

Retrieve record between particular dates:

Below is the query to retrieve records between particular dates.

SELECT * FROM Employees WHERE CONVERT(VARCHAR,JoiningDate,105)
BETWEEN CONVERT(VARCHAR,’30-08-2013′,105) AND CONVERT(VARCHAR,’05-09-2013′,105)

Retrieve record for particular month and year:

Below is the query that will retrieve records for particular month and year.

The below query will retrieve record for the month of july and year of 2013:

SELECT * FROM Employees
   WHERE MONTH(JoiningDate) = 7 AND YEAR(JoiningDate) = 2013

Get weekday name in sql server

Now, we will see how to get the weekday name in sql server using sql query.

By using the DATEPART() function we can get the weekday name. The DATEPART() function is used to return a single part of a date or time, such as year, quarter, month, day, week, weekday, hour, minute, second, millisecond etc.

Here DATEPART() tooks two parameter like below:

DATEPART ( datepart , date )

For datepart parameter values check this msdn article.

date: valid date or datetime expression.

Below is the query that will return weekday name, like if today is Friday it will return Friday.

Begin
      Declare @week int
      Set @week=Datepart(dw,GetDate())
      Select case @week
      When 1 then 'Sunday'
      When 2 then 'Monday'
      When 3 then 'Tuesday'
      When 4 then 'Wednesday'
      When 5 then 'Thursday'
      When 6 then 'Friday'
      Else 'Saturday'
    End
End

Out put:

Get weekday name in sql server

How to find the difference in retrieving data with and without using index in sql server

Now, we will see how to find the difference in retrieving data with and without using Index in sql server.

Step1:
Create a table with the name Employee2 from the table Employee1 as follow.

Select * into Employee2 from Employee1

Step2:
Write the following insert statement to insert rows into the table Employee2 from Employee2 itself.

Repeatedly execute the below statement until you get thousands of rows in the table Employee2.

Insert Employee2 select * from Employee2

Step3:
Write the below select statement on table Employee2 to find the employees working in Department 30.

Select that statement and use the shortcut CTRL+L and you will get the Estimated Execution Plan.

Within the Estimated Execution Plan place the mouse pointer over table Scan icon and note down the Estimated CPU Cost.

Select * from Employee2 where DeptNo=30 (ex: –0.126235 milisecond)

Step4:
Create a clustered index on DeptNo column of Employee2 table as follows.

Create clustered index DeptNoidx on Employee2 (DeptNo)

Step5:
Write the select statement of table Employee2 again to find the employees working in Department 30, select the statement and use the shortcut CTRL+L to get Estimated Execution Plan and within the Estimated

Execution Plan place the mouse pointer over clustered index seek icon and note down the Estimated CPU Cost Which will be less than the Estimated CPU Cost of the same statement when there is no index on DeptNo column, which indicates that the data retrieved will be fast with index.

Select * from Employee2 where DeptNo=30 (ex: –0.045213)

Modify table structure in GUI in Sql Server

Now, we will see how to modify table structure in GUI in sql server.

To modify the table structure in GUIwithinn Object Explorer follow the below steps.

Right click on the table and chose “Design”, that opens the design of the table. To change the datatype of a column directly click on the datatype of the column you want to modify, then modify the datatype.

To add or remove the not null constraint use Allow Nulls check box provided with each column.

To add a column to the table at the end of existing columns in the table you will have an empty column to specify the column name, datatype and whether or not to allow nulls.

To add constraints in the table follow the same procedure as adding constraints to the table while creating the table in GUI.

To delete the primary key constraint right click on the primary key column and choose to remove the primary key.

To delete the default constraint, click on the column on which you want to delete the default constraint. Go to column properties and remove the default value specified for the column at the property default value or binding.

To delete unique, check constraint and foreign key constraint right click on the table and open the corresponding dialog box where you will have the button ‘remove’ to delete the constraint.

To delete a column, select the entire column by clicking on the button available on left of the column and then press the delete button on the keyboard.

After making all required changes to the structure of the table, click on the Save button in the toolbox to applying changes to the table and then close the dialog box.

While loop in SQL Server

Now, we will see how to use while loop in SQL server. Below is the syntax to use While Loop in SQL Server:

DECLARE @flag INT
SET @flag = 0
WHILE @flag <= 5
BEGIN
SET @flag = @flag + 1
PRINT ‘You are in ‘ + CAST (@flag AS NVARCHAR(10)) +’ Loop’
END

The out put will come as shown in the figure below.

While loop in SQL Server

How to use If else in sql server

Let us see now, how to use If Else statement in sql server. We can use conditional statements in sql server and widely used while we are using stored procedures.

You can also use nested is else statements in sql server also.

Syntax: (from msdn)

IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

Example:

Here in the below example, I have used one if else statement. It will check whether @EmployeeID >0 or not. If @EmployeeID>0 then it will execute one statement else it will execute another statement.

USE [TestDB] GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetEmployees] @EmployeeID Int
AS
BEGIN
BEGIN TRY

if(@EmployeeID > 0)
BEGIN
SELECT * FROM Employee WHERE EmployeeID=@EmployeeID
END
ELSE
BEGIN
SELECT * FROM Employee
END

END
GO

This is how we can use if else in sql server.

Case statement in SQL Server

Now, we will see Case statement in SQL Server.

Syntax:

CASE expression
WHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] […]] [ELSE expressionN] END

Example:

select Case Gender when 0 then 'Male' else 'Female' end as Gender from UserMaster where UserID=1

The above query will return Male of Gender field is 0 else it return Female.

Also Check below example where you can have multiple WHEN statements:

select SiteURL,SiteName =
CASE SiteID
WHEN 1 THEN 'EnjoySharePoint.com'
WHEN 2 THEN 'SharePointSky.Com'
WHEN 3 THEN 'Fewlines4Biju.com'
ELSE 'No Site Available'
END from WebSite

This is how we can use Case statement in SQL Server.

SET XACT_ABORT ON in sql server

Now we will see how to use SET XACT_ABORT in sql server.

SET XACT_ABORT specifies whether sql server automatically rolls back the current transaction if an sql statement raises a runtime error.

This uses mostly when you are using transactions in sql server stored procedures.

SET XACT_ABORT can be set to ON or OFF, If it sets to ON, then if a T-sql statement raises a run-time error, the entire transaction is terminated and rolled back.

But if you set this to OFF, then only the T-SQL statement that raised the error is rolled back and the transaction continues processing.

You can write the statement before the BEGIN TRANSACTION statement like below:

USE [TestDB] GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Employees] @Name nvarchar (256) ,
@Age Int

AS
BEGIN
BEGIN TRY

SET XACT_ABORT ON

BEGIN TRANSACTION

–*************************************
INSERT INTO Employees (Name,Age) VALUES ( @Name, @Age )
–*************************************
COMMIT TRANSACTION
RETURN 1
— Sucessful
END TRY
BEGIN CATCH
RETURN -111
END CATCH
END

GO

This is how we can use SET XACT_ABORT ON in sql server.

The service did not start due to logon failure in sql server

I got the error message while trying to start MSSQLSERVER service. The detailed error message is: Windows could not start the SQL Server (MSSQLServer) service on Local Computer.
Error 1069: The service did not start due to login failure.

Normally you will get this error if you have changed your network password.

To fix the error follow the below steps:

Go to start -> run and then type services.msc this will open the Services window. Search for SQL Server (MSSQLSERVER).

Then right-click on that go to properties as shown in the figure below.

The service did not start due to logon failure

This will open the Properties dialog box, Navigate to the Log On tab and change the password and shown in the figure below.

The service did not start due to logon failure in sql server

Then try starting the service it should work.

This is how we can fix an error The service did not start due to logon failure in sql server.

Like operator examples in SQL Server

Now, we will discuss Like operator examples in SQL Server. Below are some example of Like statement in SQL Server:

Example-1:
The below statement will return record whose FirstName starts with B, like Bijay, Biju etc.

SELECT * FROM Employees WHERE FirstName LIKE 'B%'

Example-2:
The below statement will return record whose FirstName ends with B, like TamannaA

SELECT * FROM Employees WHERE FirstName LIKE '%A'

Example-3:
The below statement will return record whose FirstName contains word JA, like BiJAy, SanJAy, etc.

SELECT * FROM Employees WHERE FirstName LIKE '%JA%'

Example-4:
The below statement will return record whose FirstName starts with A,B,C or D. like Ajay, Bijay, China, Denmark, etc.

SELECT * FROM Employees WHERE FirstName LIKE '[abcd]%'

Example-5:
The below statement will return record whose FirstName starts between B to T like
Bijay
Sanjay
Tanmayee
Tamanna

SELECT * FROM Employees WHERE FirstName LIKE '[B-t]%'

This is how we can use Like operator examples in SQL Server.

View in sql server

Now, we will discuss what is view in sql server?

  • A view in the SQL server is like a virtual table. It can have rows and columns which are fields of one or more database tables.
  • Views are stored in the database. A view can have a maximum of 1,024 columns.
  • It provides security mechanisms to the SQL server. Suppose you do not want to show all the columns of a table to some users then you can make a view based on the table with limited columns that you want to share.

You can also use all conditions like where clause, order by or group by while creating a view in SQL server.

Syntax:
You can create a view like below:

Create View vEmployeeDetails As
Select ID, Name, Location from Employees

You can also use any condion if you want like

Create View vEmployeeDetails As
Select ID, Name, Location from Employees where ID>100

Through management studio it is very easy to create view.

First open the database in Sql server 2008 management studio.

Then navigate to the Views folder, Right click on the Views folder and then click on New view… as shown in the figure below:

view in sql server

Then it will open the window to select tables and select columns.

Some points about view:

  • The name of the view should not be the same as the base table name.
  • A view can be created only if there is SELECT permission on its base table.
  • A trigger or an index cannot be defined in a view.
  • A SELECT INTO statement cannot be used in the view.

Optimization Tips in sql server

Below are few optimization tips in sql server.

  • Use views and stored procedures instead of heavy-duty queries.
  • This can reduce network traffic because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also because you can restrict user access to table columns they should not see.
  • Try to use constraints instead of triggers, whenever possible.
  • Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible.
  • Use table variables instead of temporary tables.
  • Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.
  • Try to use UNION ALL statement instead of UNION, whenever possible.
  • The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.
  • Try to avoid using the DISTINCT clause, whenever possible.
  • Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.
  • Try to avoid using SQL Server cursors, whenever possible.
  • SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated sub-query or derived tables, if you need to perform row-by-row operations.
  • Try to avoid the HAVING clause, whenever possible.
  • The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without the HAVING clause. This can improve the performance of your query.

Event ID: 3760 Database could not be access issue

This sql server tutorial explains, how to fix an error, Event ID: 3760 Database could not be access issue. Sometimes you will get this issue: Event ID: 3760 Database could not be accessed issue. So you can follow the below steps to debug.

To fix the issue follow the below steps:

  • On the database server, in the Services snap-in, verify that the SQL Server (MSSQLSERVER) service is running.
  • Log onto Microsoft SQL Server Management Studio as the account provided in the error details and click Connect and then Database Engine. Type the server name provided in the error details and click Connect.
  • Verify that the database exists
  • If the SQL Server database is not present or accessible on the computer that is running SQL Server, restore the database from a backup and reconnect it to SharePoint Server 2010.

Sql Server 2014 Wait on the Database Engine recovery handled failed

Now, we will see how to fix error, Sql Server 2014 Wait on the Database Engine recovery handled failed. Check the SQL server error log for potential causes.

Recently while installing SQL server 2014 in my windows server r2 machine, I got the below error which says:

The following error has occurred:
Wait on the Database Engine recovery handle failed. Check the SQL server error log for potential causes

The error looks like below:

Wait on the Database Engine recovery handle failed. Check the SQL server error log for potential causes

I tried the below approach and it worked fine for me. In the Server Configuration wizard for the SQL Server Database Engine service by default, the account name comes as “NT Service\MSSQLSERVER and it looks like below:

Sql Server 2014 Wait on the Database Engine recovery handled failed

Then I changed the above account to NT AUTHORITY\NETWORK SERVICE account and it worked fine for me.

Reporting services catalog database file existence failed

Sometimes while installing SQL server 2008, you might face some validation errors like Reporting services catalog database file existence failed.

The solution is really simple. Follow the below solution:

Open the folder “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA”

Delete the below 4 files from the folder

  • ReportServer.mdf
  • ReportServer_log.LDF
  • ReportServerTempDB.mdf
  • ReportServerTempDB_log.LDF

After deleting the above files, click Re-run in the setup window. It should work now. You should not get any validation error.

Saving changes is not permitted sql server

Now, we will discuss how to fix error Saving changes is not permitted in SQL Server. This error usually comes when you try to modify in table structure and save it.

The full error message is “Saving changes is not permitted. The changes you ave made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the options Prevent saving changes that require the table to be re-created”.

For this in the management studio click on Tools and then Options as shown in the figure below:

Saving changes is not permitted sql server

Then in the Options dialog box select Designers from the left side and after that uncheck the option: “Prevent saving changes that require table re-creation” and click on OK as shown in the figure below:

Saving changes is not permitted sql server

After that try saving, the error should not come “Saving changes is not permitted”.

Here we learned the below sql server tutorials:

  • What is sql server?
  • SQL Server installation
  • Change authentication mode after installation of SQL Server
  • Get version in sql server
  • Transaction in SQL Server
  • Constraints in SQL Server
  • Add Primary Key Constraint to an existing table in sql server
  • Drop Primary Key Constraint in sql server
  • Create a database by command in sql server
  • Get database server name and version in sql server
  • How to take backup in SQL Server using management studio?
  • Restore database in sql server using management studio
  • sys.objects type in SQL Server
  • Get Column names of a table in SQL Server
  • Increase column size in sql server
  • Rename database in sql server
  • The database could not be exclusively locked to perform the operation rename database in sql server
  • How to assign column value to variable sql server
  • Add default date to column in SQL Server
  • Add new column to existing table in sql server
  • Rename table name or column name in SQL server
  • How to trim spaces in column values in sql server
  • How to get distinct data from table in sql server
  • Query to get records between two dates from tables in sql server
  • Get weekday name in sql server
  • How to find the difference in retrieving data with and without using index in sql server
  • Modify table structure in GUI in Sql Server
  • While loop in SQL Server
  • How to use If else in sql server
  • Case statement in SQL Server
  • SET XACT_ABORT ON in sql server
  • The service did not start due to logon failure in sql server
  • Like operator examples in SQL Server
  • View in sql server
  • Optimization Tips in sql server
  • Event ID: 3760 Database could not be access issue
  • Sql Server 2014 Wait on the Database Engine recovery handled failed
  • Reporting services catalog database file existence failed
  • Saving changes is not permitted sql server
Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

Bijay Kumar

I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SPGuides.com

>