How to edit identity field in sql?

     Usually the identity field does not allow the user to edit, when you right click on the table and select Edit.

     But we can edit edit this column through query. Through query we can temporary on the identity insert. Remember after edit the column you should off it, if not you cannot able to edit the identity field on other tables.

Example

SET IDENTITY_INSERT Table_Name ON

   INSERT Table_Name(IdentityColumnName, Col1,Col2) VALUES (54,value1,value2)

SET IDENTITY_INSERT Table_Name OFF

Read More

Display name fixed number of character remaining fill with specific character

     In the following example, I want to fill the name exactly 20 characters, if the character count is exceed 20 character remove remaining character and if the character is lesser than 20 character fill with blank space(left aligned).

Example

  SELECT convert(nvarchar(20),LEFT(CONVERT(NVARCHAR, 'Merbin Joe') + SPACE(20), 20)) Name

This will produce

 Merbin Joe

But you can't able to see the blank space's so you should need to identify the space using [] or other way.

SELECT quotename(convert(nvarchar(20),LEFT(CONVERT(NVARCHAR, 'Merbin Joe') + SPACE(20), 20))) Name

Read More