Anil Singh | Code-sample.com

The  @@ROWCOUNT  is a special variable of SQL. It will return the number of rows changed by the last statement. The @@RowCount is equal to (=) the number of rows changed by the last statement. Syntax : - @@ROWCOUNT Return Types : - INT What is the scope of @@RowCount? The @@RowCount is both the scope and connection safe and it is read only! For example as, -- DECLARE RETURN TABLE DECLARE @Return_Table TABLE (Code varchar ( 10 ) ,Message varchar ( 100 ), ID varchar ( 100 )) --UPDATE CUSTOMER QUERY UPDATE Customer SET Name = N 'Anil Singh' WHERE Id = 0786 --USE OF @@ROWCOUNT IF (@@ROWCOUNT > 0 ) BEGIN INSERT INTO @Return_Table (Code, Message, ID) SELECT 'OK' , 'SUCCESS' , SCOPE_IDENTITY() SELECT Code, Message, ID FROM @Return_Table END ELSE BEGIN INSERT INTO @Return_Table (Code, Message, ID) SELECT 'ERROR' , 'Warning - No rows updated.' , SCOPE_IDENTITY() SELECT Code, Message, ID FROM @R...

What  is SELF JOIN and when would you use it? A SELF JOIN is join which is used to join a table to itself. There is no SELF JOIN keyword and it is used to compare values in a column with other values in the same column in the same table. For Example, SELECT * FROM TableA t1 , TableA t2 WHERE t1 . Id = t2 . Id In the above query, WHERE clause could be any given expression as per you! I hope you are enjoying with this post! Please share with you friends!! Thank you!!!

What is Full Outer Join in SQL? Why you use? FULL OUTER JOIN work between with both the Joins  LEFT OUTER and RIGHT OUTER. If the condition is matched then returns matched rows otherwise returns NULL values. I hope you are enjoying with this post! Please share with you friends!! Thank you!!!

ANY keyword is used with a WHERE or HAVING clause. ANY keyword operates on sub-queries that return multiple values. ANY keyword returns true if any sub-query values matched the condition. Syntax: --SELECT TABLE ROWS and Used of ANY Keyword. SELECT Id, Name FROM [dbo].[Tbl_Demo] WHERE Id = ANY ( SELECT Id FROM [dbo].[Tbl_Demo] WHERE Id = 2 ) -- Result looks like, Id Name -------------- 2 Aradhya -- CREATE TABLE 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 --INSERT TABLE ROWS SET IDENTITY_INSERT [dbo].[Tbl_Demo] ON GO INSERT [dbo].[Tbl_Demo] ([I...

The AS keyword is used to assign an alias name of a column or a table Syntax: SELECT COL1, COL1 FROM Table_Name AS DEMO -- CREATE TABLE 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 --INSERT TABLE ROWS 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], [IsActive], [IsDeleted]) VAL...

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], [...