How to Restart the SQL Server Service instance?

To restart the SQL Server Instance , we can do it many ways.

Let's explore the ways.

Using SQL Services:

Click the start button and type services.msc as shown below.

 To  open the services you can also enter the services.msc in Run command as shown below.

 Select the SQL service, right click the service and select the restart option to restart the SQL services as shown below.

After selecting the restart option ,SQL service start to restart as shown below.

 You can also use the below option to restart the SQL service as shown below. (Highlighted in Red Colour).

Now the SQL service Restarted Successfully.

Using SQL Server Management Studio:

Open the SQL server management studio and connect to the SQL server instance which you want to restart.

After connecting the SQL server Instance ,right click the Server and select restart option to restart the SQL service as shown below.

The SQL Server management studio will ask for confirmation to restart the Service and click Yes to restart as shown below.

Now the SQL service Restarted Successfully.

Using Command Prompt:

Run the command Prompt with administrator  and use the Net Start option to start the SQL service and Use Net Stop option to stop the SQL service as shown below.

Replace the "SQL Server <SQLEXPRESS>" with your SQL server instance name as shown below.

After executing the above commands the SQL service will be restarted.

Using SQL Server Configuration Manager:

Open the SQL server configuration manager as shown below.

The SQL server configuration manager will appear and select the SQL server instance which you want to restart.
After selecting the instance , right click the instance and select the restart option as shown below.

The SQL service will be started to restart as shown below.

Finally the SQL service will be restarted.

Use of LTRIM and RTRIM function in SQL server ?

LTRIM FUNCTION

LTRIM function is used to remove the leading blanks(i.e towards right side) from the character expression

Syntax:

LTRIM(CHARACTER EXPRESSION)

Example:

SELECT LTRIM('           SQL SERVER')

Output:

SQL SERVER

RTRIM FUNCTION

RTRIM function is used to remove the trailing blanks(i.e towards left side) from the character expression

Syntax:

RTRIM(CHARACTER EXPRESSION)

Example:

SELECT RTRIM('SQL SERVER                ')

Output:

SQL SERVER

Use of REVERSE function in SQL server ?

REVERSE function is used to reverse the order of a sting value

Syntax:

REVERSE(STRING EXPRESSION)

Example:

SELECT REVERSE('JK SQL SERVER BLOG SPOT')

Output:

TOPS GOLB REVRES LQS KJ

Use of REPLICATE function in SQL server ?

REPLICATE function is used to repeat the sting value a specified no of times

Syntax:

REPLICATE(STRING EXPRESSION,INT EXPRESSION)

Example:

SELECT REPLICATE('SQL',5)

Output:

SQLSQLSQLSQLSQL

Use of UPPER and LOWER function in SQL server ?

UPPER FUNCTION

UPPER function is used to get the Upper case character of the given character expression

Syntax:

UPPER(CHARACTER EXPRESSION)

Example:

SELECT UPPER('jk sql server blog spot')

Output:

JK SQL SERVER BLOG SPOT

LOWER FUNCTION

LOWER function is used to get the Lower case character of the given character expression

Syntax:

LOWER(CHARACTER EXPRESSION)

Example:

SELECT LOWER('JK SQL SERVER BLOG SPOT')

Output:

jk sql server blog spot

Use of Left and Right function in SQL server ?

LEFT FUNCTION

Left function is used to get the left most character of the expression based on the given length

Syntax:

LEFT(CHARACTER EXPRESSION,LENGTH)

Example:

SELECT LEFT('SQL SERVER',3)

Output:

SQL

RIGHT FUNCTION

RIGHT function is used to get the right most character of the expression based on the given length

Syntax:

RIGHT(CHARACTER EXPRESSION,LENGTH)

Example:

SELECT RIGHT('SQL SERVER',6)

Output:

SERVER

Use of ASCII and char function in SQL server ?

ASCII FUNCTION

ASCII (American Standard Code for Information Exchange) function is used to get the ASCII value of the given character expression

Syntax:

ASCII(CHARACTER EXPRESSION)

Example:

SELECT ASCII ('A')

Output:

65

CHAR FUNCTION

CHAR function is used to convert an ASCII value to a character

Syntax:

CHAR(INT)

Example:

SELECT CHAR (65)

Output:

A

Difference between Replace Function and Stuff function in SQL server ?

Replace:

• Replace function will replace all exact occurrence of the string in a character expression. 
• Replace function will take 3 parameters as input.

Stuff: 

• Stuff function will replace only the exact occurrence of the string in a character expression based on the start and length values.
• Stuff function will take 4 parameters as input.

The above points are the major difference between Replace and stuff function.

If you want to replace all the string occurrence then prefer replace function ,if not so then go with stuff function.

Use of STUFF function in SQL Server?

Stuff function is used to replace the occurrence of the String in a Expression based on the start value and length value.

Syntax : 

STUFF ([CHARACTER EXPRESSION],[START],[LENGTH],[REPLACEMENT STRING]

Example :

SELECT STUFF('JK SQLBLOG',7,1,'SERVER ') 

In above example replacement string 'SEVER' will be Inserted in the character expression.

It search for the character position 7 in the character expression and after that it will insert the replacement string.

Output :

JK SQLSERVER LOG

Use of Replace function 

http://jksqlserver.blogspot.ae/2016/03/use-of-replace-function-in-sql-server.html

Replace Vs Stuff

http://jksqlserver.blogspot.ae/2016/04/difference-between-replace-function-and.html

How to add Primary key in the existing table in SQL server ?

Primary key is a kind of constraint in SQL server .To add the primary key in the existing table , you can do it in two ways.

1. Using SQL Management Studio
2. Using T-SQL Query

Using SQL Management Studio:

Step1:

Expand the database ,right click the required table and click the design option as shown below.

Step2:

Right click the required column and select the option as set the primary key as shown below.

Step3:

After clicking the option you can find the Key icon in the selected column as shown below.

Primary key is created successfully in StudentDetail table using SQL Management studio.

Using T-SQL Query:

Refer : http://jksqlserver.blogspot.ae/2015/09/how-to-create-primary-key-in-sql-server.html

How to validate the date-time in SQL server ?

TO validate the date-time in SQL server,use the following syntax.

Syntax:

ISDATE(Expression)

Return Type:

INT

Example:

Select ISDATE('2016-04-16 12:20:51.550')

Output :

1

SELECT ISDATE('16/04/2016')

Output:

0

Explanation:

ISDATE function will return 1 if the expression is a valid date,else it will return 0.

How to Get the Current date in SQL server ?

To get the current date and time in SQL server,use the following syntax.

Syntax:

Getdate()

Example:

Select Getdate()

Output:

2016-04-16 12:09:52.613

Now the SQL server installed system data and time will be displayed.

How to get the definition of objects in SQL server ?

 Following options are used to get the definition of objects in SQL server.

TABLE:

SYNTAX:

SP_HELP [TABLE NAME]

EXAMPLE:

SP_HELP StudentDetail

In above example we can find some of the table details in Results tab.

Name --> Refers to table Name
Created Date--> Refers to table created date
Column Name --> Refers the column names in table
Type --> Refers to Data type
Computed --> Refers to the computed column
Length --> Refers the Column length
Nullable --> Refers to nullable column or not
Collation --> Refers to the column collation
Identity --> Refers to the identity column
RowGuidCol --> Refers to the RowGuidCol column
Index Name --> Refers to the Index Name for the index column
Index key --> Refers to the index column name
Constraint type --> Refers to the constraint type such as primary key constraint ,foreign key constraint,default constraint etc.

Database Objects:

SYNTAX:

SP_HELPTEXT [Database Objects]

EXAMPLE:

SP_HELPTEXT pGetStudentDetail

Database objects such as stored procedures ,functions ,triggers and views.
For all objects (except tables) we can use the above syntax to get the definition.

How to take database full backup in SQL server ?

To take the full backup of a database , I recommend you to follow the below steps.

Step 1:

Right click the database which you want to take backup.In below screenshot I am going to take backup of StudentForm database.

select database --> Right Click --> Tasks --> Back up

Step 2:

After selecting the Backup option, backup database window will appear, you can change the database in source option as shown below.

Step 3:

After selecting the required database ,choose the FULL backup type  as shown below.

Step 4:

You can set the expiry date for the database backup by using the backup set option as shown below.

The default backup path for the database will be C drive (i.e. SQL server installed drive)

To change destination path , select and click remove button as shown above.

Step 5:

After removing the path click add button.Then the select backup destination pop up window will appear as shown below.

Select the button which is highlighted in Red colour as shown above.

Step 6:

Now locate database files window will appear.

Here choose the required path and enter the backup file name in file name text box as shown below.

Finally do not forget to add the extension (.bak) in backup file name and then click OK button.

Step 7:

Now you can notice that the backup path has been changed as shown below.

Then click OK button.

Step 8:

After some moments (Restoring time depends on database size) you can see the pop up window as shown below.

Now the database backup is completed.

Step 9:

You can see the database backup in the given destination path as shown below.

How to Validate the T-SQL query without executing in SQL server?

Without executing the T-SQL query we can validate the Query in SQL server by using the following Syntax

Syntax:

SET NOEXEC ON

QUERY

SET NOEXEC OFF

Example:

SET NOEXEC ON

SELECT * FROM STUDENTFORM

SET NOEXEC OFF

In above example,before select statement we have set the noexec to ON and after select statement

we have set noexec to OFF.

If we execute the above statement it will validate the query rather than executing the statement.

So using this NOEXEC statement we can validate the query without worrying about the syntax.

Finally do not forget to OFF the NOEXEC statement.

You can use update or delete statement instead of select statement in above example.

How to Copy a table data from one table to another new table in SQL server ?

To Copy the table data from one table to another new table (Without create statement) in SQL server

Use the following syntax

Syntax : 

SELECT * INTO TARGET_TABLE FROM [DATABASENAME].[SCHEMANAME].EXISTING TABLE NAME [WHERE CONDITION]

Example :

SELECT * INTO StudentDetail_Temp from Sample.dbo.StudentDetail where UID <= 100

StudentDetail_Form --> Refers to Target table

Sample --> Refers to Database name

dbo --> Refers to Schema Name

StudentDetail  --> Refers to Existing table name

After executing the above T-SQL statement,the new table StudentDetail_Temp  will be created with same table structure of StudentDetail table.

In above example we are copying the data where UID less than or equal to 100.

So the new table will contain only UID value less than or equal to 100.

Note : "Where" condition is not mandatory in select statement.

This way of creating a table will be very helpful in Production database to immediately take the backup of particular table before bulk update or Delete statement.

How to Change the Default Database in SQL Server ?

While opening a new query window,Initially the selected database will be Master 

To change the Default database in SQL server while login use the following syntax.

Syntax : 

EXEC SP_DEFAULTDB 'LOGIN NAME','DATABASE NAME'

Example :

EXEC SP_DEFAULTDB 'SA','SAMPLE'

Login Name -- Refers to the SQL server login Name

Database Name -- Refers to the default database Name

After executing the above system stored procedure ,the default database will be changed from master database to Sample database.

In next login the default selected database will be Sample.

How to find the database Name in SQL Server ?

In a Single SQL Server Instance ,it is possible to have multiple databases.

Each database has its own unique ID.

If you know the database ID then

refer the following syntax 

Syntax:

DB_NAME('Database ID')

Example:

SELECT DB_NAME(5)

Output:

Sample

For the ID : 5 the Name of the database is Sample.