How to insert the Unicode characters in the table ?

If you want to insert the Unicode characters such as Arabic,Japanese & Chinese etc in the table we need to follow the below steps.

In the below example i tried to insert the Arabic characters in the ArabicTable.

After inserting the record if we try to select the data, then ArabicName column will be saved as ?????.

If you want to insert the Unicode characters in the table, then you need to insert the value using 'N' as shown below.

After executing the above statement, then the data will be saved as expected.

To save the Unicode characters in the column, the column should be of Nvarchar data type.

How to reseed the identity value in SQL server ?

In SQL server we can reseed the identity value of the table using DBCC Command.

Syntax

DBCC CHECKIDENT ('Table Name',[Reseed],[Reseed Value])

Example

In the following example the current value of identity column (UID) is 3.In the next insert i want to reseed the value to be 11.

Now i reseed the value of UID column to 11.After executing this command the following message will appear.

After executing the above command if we try to insert new record, the UID will be generated as 11 rather than 4.

Now we have the UID value of new row is 11.

In the above scenario the numbers between 4 to 10 will be skipped in this case.

If you want the next value to be 100, then you need to reseed the value as 99.It will always takes the value as reseed value plus one. (i.e) 99 + 1 = 100.

If the identity column is created as primary key, if you try to reseed the value of that column then it will create primary key violation error. 

we can reseed only the identity column in the table.

How to encrypt the stored procedure in SQL server ?

In SQL server we can encrypt the stored procedure using Encryption option.If Stored procedure is encrypted we cannot get the definition using Sp_helptext option.

It is very helpful to hide the logic behind the stored procedure from the end user.

But the user who created the SP have to save the definition manually otherwise we cannot get the definition of SP.

Example

The following stored procedure is created without encryption, so it is possible to get the definition.

But the following example is created using encryption keyword. So the following message will appear if you try to get the definition using Sp_helptext.

How to find the list of index in a table ?

SQL server provide system stored procedure to find the list of indexes in the Tables (or) View.

Syntax 

SP_HELPINDEX 'Object Name'

Example

The following example list the index name,description and its keys for the Employee table.

How to attach and Detach the Database in SQL Server ?

The Master data file (Mdf) and Log Transaction File (Ldf) of a database can be attached and detached to the same or different SQL server instances.

It is very helpful in moving the database files from one location to another location.

Example

In the following example, I am going to move the database file of studentdetail from one folder to another folder.

Initially the log files are located in E:/Data path.

Right click the database which you want to detach and select Tasks --->  Detach option as shown below.

Select the drop connection checkbox in the detach database wizard.

After Detaching the database , you can relocate the database files from E:/Data to E:/StudentData location as shown below.

Now we have replaced the files to another folder.

After relocating the files we need to attach the database using attach option as shown below.

Click Add button as shown below.

Select the Mdf file from the located path as shown below. 

Now we can see the database files path has been changed in the attach database wizard.

After clicking OK button you can see the attached StudentDetail database as shown below.

Now we have successfully detached and attached the database.

How to create comma separated values (CSV) in SQL server ?

Sometimes there will be a requirement to show the values in comma separated format in SQL server.

We can do it in many ways. Some of them are below.

Example: 

In the below example i am going to separate the first name column of person table with comma.

In the 1st method I used substring function to get the CSV.

In 2nd method i used ISNULL AND COALESCE function to get the result.

DENSE_RANK Function in SQL server ?

Dense Rank function returns the rank of each row within the partition without having any gaps in the ranking.

Syntax

DENSE_RANK () OVER ([PARTITION BY CLAUSE] ORDER BY CLAUSE)

Example

The following example get the dense rank value from the studentmarks table using the dense rank function and partition by clause.

The dense rank function will not skip the rank order even if the order by clause column having same value.

You can also use the dense rank function by removing the partition by clause as shown below.

In above example you can see the rank value of the student john is repeating two times since he scored the same marks in both subjects.In Case of dense rank the next rank value will not be skipped.

Rank function in SQL server ?

Rank function returns the rank of each row within the partition

Syntax

RANK () OVER ([PARTITION BY CLAUSE] ORDER BY CLAUSE)

Example

The following example get the rank value from the studentmarks table using the rank function and partition by clause.

You can also get the rank function by removing the partition by clause.

The rank function will skip the rank order if the order by clause column having same value.

In above example you can see the rank value of the student john is repeating two times since he scored the same marks in both subjects.So the rank 2 will be skipped in this case.