by

SQL Server – Difference Between Primary Key and Unique Key In MS Sql Server

Both PRIMARY KEY and UNIQUE KEY enforces the Uniqueness of the values (i.e. avoids duplicate values) on the column[s] on which it is defined. Also these key’s can Uniquely identify each row in database table.

Primary Key:

  • Can be only one in a table
  • It never allows null values
  • Primary Key is a unique key identifier and can not be null
  • By default it adds a clustered index
  • A table can have only one PRIMARY KEY Column[s]
  • We can generated ID automatically with the help of Auto Increment field. Primary key supports Auto Increment value.
  • Primary key can be related with another table’s as a Foreign Key.

Unique Key:

  • Can be more than one unique key in one table
  • Unique key can have null values(only single null is allowed)
  • It can be a candidate key
  • Unique key can be null But only one Null value.
  • By default it adds a UNIQUE non-clustered index
  • A table can have more than one UNIQUE Key Column[s]
  • Unique Constraint doesn’t supports Auto Increment value.
  • Unique Constraint can not be related with another table’s as a Foreign Key.

Share this:
Share
by

SQL Server – STUFF function in MS SQL Server

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Syntax:
STUFF ( character_expression , start , length , replaceWith_expression )

Arguments:

character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

start
Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.


PolicyBazaar IN CPL - Personal Loan

length
Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint.

replaceWith_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of character_expression beginning at start.

Example:
SELECT STUFF(‘abcdef’, 2, 3, ‘ijklmn’)

Output:
aijklmnef

Share this:
Share
by

SQL Server – The power of sys.sp_MSforeachTable function in MS SQL Server

If we want to perform an action on each and every tables (as it is self explanatory “for each Table” ) in a database.

Common uses of this store procedure.

  1. Truncate all the tables
  2. Disable check constraints for all tables
  3. Run delete command on all the tables
  4. To get the size of all the tables in the database
  5. To rebuild all indexes of all the tables in the database
  6. Disable all constraints and triggers of all the tables in the database
  7. Delete all the data from all the tables in the database
  8. To RESEED all tables to 0
  9. To get the Number of Rows in all tables in a database
  10. Update the statistics of all the tables in a database
  11. Reclaim space from dropped variable-length columns in tables or indexed views of the database

MS Sql Server provides you with some undocumented stored procedure “sys.sp_MSforeachTable”. It allows you to process some code against each and every table in a single database. This is a system stored procedure which comes with SQL server by default. This function has ability to run single command or multiple commands against tables in a single database.

Syntax

sp_MSforeachtable [ @command1 = ] ‘command1’ [ , [ @replacechar = ] replacechar ] [ , [ @command2 = ] command2 ] [ , [ @command3 = ] command3 ] [ , [ @whereand = ] where_and_Condition ] [ , [ @precommand = ] precommand] [ , [ @postcommand = ] postcommand]

Parameter

Parameter Description
@command1 It is the first command to be executed by this Stored Procedure and the data type is nvarchar(2000).
@replacechar It is a character in the command string that needs to be replaced with the table name being processed. The default value of this parameter is a “?”.
@command2 @command2 and @command3 are two additional commands that can be run for each table. Here first Command1 is executing then command2 and then command3 will execute.
@command3
@whereand This parameter could be used to provide additional constraints to the command for helping to identify the rows in the sysobjects table that will be selected. Its data type is nvarchar(2000).
@precommand This command is to be run before processing any table. Its data type is nvarchar(2000).
@postcommand This command is to be run after the processing of all the tables. Its data type is nvarchar(2000).

sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure:

— Display the size of all tables in a database
EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”

— Display Number of Rows in all Tables in a database
EXEC sp_MSforeachtable ‘SELECT ”?”, Count(*) as NumberOfRows FROM ?’

— Diable trigger on all the tables
EXEC sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

— Disable referential integrity
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

— Enable referential integrity again
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

— To RESEED all table to 0, use this script
EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED, 0)
‘
— Update Statistics of all Tables in a database
EXEC sp_MSforeachtable ‘UPDATE statistics ? WITH ALL’


PaisaBazaar IN CPL - Credit Card

Share this:
Share