Anil Singh | Code-sample.com

What is @@IDENTITY in SQL? The @@IDENTITY is a system function which returns the last inserted identity value. All the @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT are similar functions because all are return the last inserted value into the table’s IDENTITY columns. The @@IDENTITY and SCOPE_IDENTITY return the current session last identity value but the SCOPE_IDENTITY returns the current scope value. What is the scope of @@IDENTITY? In the @@IDENTITY, there are no any limitations for a specific scope. Syntax : - @@IDENTITY    Return Type : - numeric ( 38 , 0 ) For example as, -- USE OF @@IDENTITY INSERT INTO ContactType(Code, Description, IsCurrent, CreatedBy, CreatedOn) VALUES ( 'IT-PROGRAMING' , 'This is a Prrogrammer!' , 1 , 'Anil Singh' , GETDATE()); GO SELECT @@ IDENTITY AS 'COL_IDENTITY' ; GO The Use of  SCOPE_IDENTITY :- -- DECLARE RETURN TABLE DECLARE @Return_Table TABLE (Cod...

What is @@ERROR in SQL? @@ERROR returns only current error information (error number and error) after T-SQL statements executed. @@ERROR returns 0, if the previous SQL statement has no errors otherwise return 1. @@ERROR is used in basic error handling in SQL Server and @@ERROR is a global variable of SQL and this @@ERROR variable automatically handle by SQL. If error is occurred set error number otherwise reset 0. It is work only within the current scope and also contains the result for the last operation only. Syntax : - @@ERROR   Return Type : - INT When we should use @@ERROR? 1.       While executing any stored procedures 2.       In the SQL statements like Select, Insert, Delete and Update etc. 3.       In the Open, Fetch Cursor. When we should use Try Catch Block? The Try Catch Block is generally used where want to catch errors for multiple SQL statements. ...

What is clustered Index? 1.       The clustered Index is created automatically on primary key column. 2.       One table can only create one and only clustered Index. 3.       Clustered index is sorting all the rows physically. 4.    Clustered index is works based on the Binary tree concept. How to Create Clustered Index? -- CREATE CLUSTERED INDEX CREATE CLUSTERED INDEX Indexname_EmlloyeeClust ON Employee (     [EmpName] ASC or DESC ,     [EmpDepartment] ASC or DESC ) -- OR -- CREATE CLUSTERED INDEX CREATE CLUSTERED INDEX Indexname_EmlloyeeClust ON Employee (    [EmpName] ASC ,    [EmpDepartment] ASC ) WITH ( PAD_INDEX   = OFF , STATISTICS_NORECOMPUTE   = OFF , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS ...

A stored procedure is a collection of SQL statements that has been created and stored in the database. It is a set of recompiled SQL statements that are used to perform a special task. Stored procedures create once a time and calls it n number of times and also reduces the network traffic and increase the performance. When do you use store procedure? I used store procedures in 1 of 3 scenarios, ·          Security, ·          Speed and ·          Transactions Types of SQL Procedures, 1.       System Stored Procedures 2.       User Defined Stored Procedures 3.       Extended Stored Procedures Syntax:- CREATE PROCEDURE < Procedure_Name, sysname, ProcedureName > -- Add the parameters for the stored procedure here < @Param1, sysname, @p1...

The ALL keyword is used to select all fields from a table using the asterisk " * " in a SQL SELECT statement. Syntax: SELECT ALL * FROM Table_name -- CREATE TABLE AND INSERT ROWS CREATE TABLE [dbo].[Tbl_Demo]( [ID] [ int ] IDENTITY( 1 , 1 ) NOT NULL , [Name] [ varchar ]( 500 ) NULL , [Age] [ int ] NULL , [IsActive] [ bit ] NULL , [IsDeleted] [ bit ] NULL , CONSTRAINT [PK_Tbl_Demo] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[Tbl_Demo] ON GO INSERT [dbo].[Tbl_Demo] ([ID], [Name], [Age], [IsActive], [IsDeleted]) VALUES ( 1 , N 'Anil Singh' , 30 , 1 , 0 ) GO INSERT [dbo].[Tbl_Demo] ([ID], [Name], [Age], [IsActive], [IsDeleted]) VALUES ( 2 , N 'Aradhya' , 3 , 1 , 0 ) GO INSERT [dbo].[Tbl_Demo] ([ID], [Name], [Age], [...

Hello everyone, I am  going to share the code sample for how to get the month form date-time using the SQL Server 2008 and higher versions of SQL Server. query for get month from sql date-time SELECT DATENAME ( MONTH ,  GETDATE ()) AS [MONTH]