January 2012 - Technology with Vivek Johari

SQL Server- Output clause

January 22, 2012January 28, 2019 vivekjohari28 Comments

Output Clause:- The SQL SERVER 2005 gives us an Output Clause which gives us the information about each row affected by the Insert, Update , Delete and Merge statement. It is more useful than @@scope_Identity and @@Identity column since these global variables gives us the information about the last inserted identity column value, but the Output clause gives us […]

CTE command In Sql Server

January 17, 2012February 1, 2019 vivekjohari31 Comments

Common table expression (CTE):- Common table expression or CTE can be described as the temporary, named record set return by the execution of the query (insert, delete, update, select or update view statement). It is not stored as an object in the database and it last only till the execution of the SQL Query. It […]

SQL Server-Table Variable

January 10, 2012January 28, 2019 vivekjohari4 Comments

Table variable:- SQL Server provides an variable known as table variable which is used to store data in a similar way as we store data in physical tables but with some limitations. Like other SQL variable, it is also declare with the help of the Declare keyword with @ prefix. The Syntax of declaring a table variable is […]

SQL Server – Local temporary table

January 7, 2012January 28, 2019 vivekjohariLeave a Comment

Local temporary table:- Local temporary table is created in the tempdb and it is visible to the current user’s session only. It remains exists till the current user session is connected. Once the user connection is disconnected it gets destroyed. Since the local temporary table is created in the tempdb, whenever we use temporary tables […]

SQL Server – Global temporary tables

January 7, 2012April 27, 2018 vivekjohari31 Comments

Global temporary table:- Global temporary table is created in the tempdb and it is visible to all other sessions as well. Global temporary tables are only destroyed when the current user disconnected and all the sessions referring to it closed. It means no user sessions refers it. Since the global temporary table is created in the tempdb, whenever we […]

How to change the database collation

January 2, 2012April 27, 2018 vivekjohari31 Comments

To change the collation of an database following commands can be used: ALTER DATABASE [database name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE [database name] COLLATE Newcollation ALTER DATABASE [database name] SET MULTI_USER For example , suppose if the name of your database is learningdb and you want to change the collation to the collation SQL_Latin1_General_CP1257_CI_AS, then following commands can be used:- ALTER DATABASE learningdb SET SINGLE_USER WITH ROLLBACKIMMEDIATE ALTER DATABASE learningdb COLLATE SQL_Latin1_General_CP1257_CI_AS ALTER DATABASE learningdb SET MULTI_USER

How to rename a Table in Sql Server

January 2, 2012April 27, 2018 vivekjohari32 Comments

To rename a column in a SQL Table following command can be reused: SP_RENAME ‘old table name’, ‘New table name’ For example, if we want to rename the table from employee to employeemaster, following command can be used. SP_RENAME ’employee’, ’employeemaster’ This command will rename the table from employee to employeemaster.