Anil Singh | Code-sample.com

Dear Readers, I’m so excited to sharing about the new upcoming  features of  SQL Server 2017 and upcoming 2018 features in advance!  The SQL Server lovers must read this post -  Stayed Informed  -  SQL Server Tutorials SQL Server 2017 Features - 1.      Added IDENTITY_CACHE option for ALTER DATABASE SCOPED CONFIGURATION 2.      Added Automatic database tuning 3.      Added graph database capabilities for handling many-to-many relationships 4.      Added CLR strict security - by default is Enabled and Its treats as SAFE and EXTERNAL_ACCESS. When it enabled is true, the PERMISSION_SET option in the CREATE and ALTER ASSEMBLY statements is ignored at run-time. 5.      Added SSIS Catalos for a new global property to specify the default mode for executing SSIS package 6.      Added Object level security to secure the metadata...

This below query is help us to compare two columns from two different tables and also for  two different database tables. Stayed Informed - End To End SQL Server with Q/A -- HOW TO COMPARE TWO COLUMNS FROM TWO DIFFERENT TABLES IN SQL? SELECT * FROM [DBO].TRANSLATION_1 AS T1 WHERE NOT EXISTS ( SELECT * FROM [DBO].TRANSLATION_2 AS T2 WHERE T1.Field_Name = T2.Field_Name and T1.Entity_Name = T2.Entity_Name ) -- HOW TO COMPARE TWO COLUMNS FROM TWO DIFFERENT DATABASE TABLES IN SQL? SELECT * FROM [DB1].[DBO].TRANSLATION_1 AS T1 WHERE NOT EXISTS ( SELECT * FROM [DB1].[DBO].TRANSLATION_2 AS T2 WHERE T1.Field_Name = T2.Field_Name and T1.Entity_Name = T2.Entity_Name ) I hope you are enjoying with this post! Please share with you friends. Thank you!!

The SQL “ NOT ” clause is used with “ WHERE ” conditions. The “ AND ”, “ OR ” and “ NOT ” operators are used to test two or more conditions in a SELECT , INSERT , UPDATE , or DELETE statements. The “ WHERE” clause with “ AND” operator requires that two conditions are true and an “ OR ” requires that one of two conditions is true and the “ NOT” negates the specified condition. Stayed Informed   -   37 Best SQL Server Interview Questions and Answers Syntax:- --THE WHERE WITH AND OPERATOR SELECT ColumnName, ColumnName FROM TableName WHERE Condition1 AND Condition2 --THE WHERE WITH OR OPERATOR UPDATE TableName SET ColumnName = value WHERE Condition1 OR Condition2 --THE WHERE WITH NOT OPERATOR DELETE TableName WHERE NOT Condition1 Examples:- --SELECT ALL COUNTRIES SELECT [Id] ,[CountryName] ,[CountryCode] FROM [test].[dbo].[Countries] Result:- Id CountryName CountryCode ------------------------------...

A “ HAVING ” Clause is use to filter a records using “ GROUP BY ” Clause and without GROUP BY clause, the HAVING work like a WHERE clause. Only the groups that meet the HAVING criteria will be returned. According to Wikipedia, A “ HAVING” clause in SQL specifies that an SQL SELECT   statement should only return rows where aggregate values meet the specified conditions. Stayed Informed   -   37 Best SQL Server Interview Questions and Answers Syntax:-  SELECT columnName FROM TableName WHERE condition GROUP BY columnName HAVING condition --WITH ORDER BY:- SELECT columnName FROM TableName WHERE condition GROUP BY columnName HAVING condition ORDER BY columnName Examples:- SELECT COUNT (Id) AS Counts, CountryName FROM Countries GROUP BY CountryName HAVING COUNT (Id) < 5 Result:- Counts CountryName -------------------- 1 Australia 1 India 1 Nepal 1 Rasia 1 USA Other Example, SELECT COUN...

The “ GROUP BY ” clause groups records into summary rows and returns one records for each group. It is also involves aggregates like COUNT , MAX , SUM , AVG and so on… The “ GROUP BY ” can be group by the single or multiple columns. Stayed Informed   -   37 Best SQL Server Interview Questions and Answers Syntax:- SELECT ColumnNames FROM TableName WHERE Condition GROUP BY columnNames Example:- SELECT DISTINCT CountryName, CountryCode FROM Countries GROUP BY CountryName, CountryCode Result:- CountryName CountryCode --------------------------- India IN Nepal NP Rasia RA USA US Stayed Informed   -  SQL Server Query and Examples I hope you are enjoying with this post! Please share with you friends. Thank you!!

The “ ORDER BY ” Clause is use to ensure a specific order and returns records that is no particular order. It’s allows sorting by signal or multiples columns. The Returned records can be “ ascending ” or “ descending ” order and the default order is ascending . Stayed Informed   -   37 Best SQL Server Interview Questions and Answers Syntax:- SELECT DISTINCT CountryName FROM Countries ORDER BY CountryName DESC Examples:- SELECT DISTINCT CountryName, CountryCode FROM Countries ORDER BY CountryName DESC Result:- CountryName CountryCode ---------------------------- USA US Rasia RA Nepal NP India IN Stayed Informed   -  SQL Server Query and Examples I hope you are enjoying with this post! Please share with you friends. Thank you!!

How to   E liminates Duplicate Rows? The “ DISTINCT ” returns only distinct value and DISTINCT “ eliminates duplicate records ” from the result set. Also “ operates ” only on a single column and not for multiple columns . The “ DISTINCT ” key can be used with aggregates like COUNT , AVG , and MAX , so on.. Stayed Informed   - 37 Best SQL Server Interview Questions and Answers Syntax :-  SELECT DISTINCT ColumnName, ColumnName FROM TableName OR -- USING COUNT AGGREGATE SELECT COUNT ( DISTINCT ColumnName) FROM TableName Examples:- SELECT DISTINCT CountryName FROM Countries ORDER BY CountryName Result:- CountryName CountryCode --------------------------- India IN Nepal NP Rasia RA USA US Stayed Informed   -  SQL Server Query and Examples I hope you are enjoying with this post! Please share with you friends. Thank you!!

In the LEFT OUTER JOIN , returns all the matched rows from the left table and matched rows from right table. If the right table is not matched then will return NULL values. I hope you are enjoying with this post! Please share with you friends!! Thank you!!!

--================================== --CREATE FUNCTION WITHOUT PARAMETER. --================================== CREATE FUNCTION AddFun () RETURNS INT AS BEGIN DECLARE @Result AS INT SET @Result = 2 + 3 RETURN @Result END --RESULT LOOKS LIKE, PRINT test . dbo .AddFun() --IS 5

The AND keyword is logical operator. It is used to filter table records with AND conditions. Syntax:  SELECT col1, col2 FROM table_name WHERE Condition_1 AND Condition_2 -- 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...

The ISNULL () function is used to replace NULL with the specified replacement value. This function contains only two arguments. The ISNULL () can only have one input. So, ISNULL () is faster than COALESCE (). The ISNULL () uses the first parameter type. The ISNULL () function is a T-SQL. Syntax:  SELECT ISNULL (NULL, 1) The COALESCE () can have multiple inputs and it will evaluate in order until one of them is not null. The COALESCE () function returns the first non-null value of its arguments. The COALESCE () not limited to arguments but must be of the same data type. The COALESCE () follows the CASE expression and returns the first non-null value. The COALESCE () is ANSI-Standard. Syntax:   SELECT COALESCE (NULL, NULL, 1, NULL) Summary: The NULL value for ISNULL () is converted to INT whereas for COAELSCE () you have to provide a type. a)       ISNULL(NULL, NULL) — Ruturns as int b)    ...

--SQL SERVER INLINE IF ELSE. --OLD DATABASE QUERY. SELECT (CASE Flag WHEN 1 THEN 'Yes' ELSE 'No' END) AS if_else_Result --NEW DATABASE QUERY. SELECT IIF( 1 = 1 , 'Yes' , 'No' ) AS if_else_Result; SELECT IIF( 1 = 2 , 'Yes' , 'No' ) AS if_else_Result; SELECT IIF( 'a' = 'a' , 'Yes' , 'No' ) AS if_else_Result; SELECT IIF( 'a' = 'b' , 'Yes' , 'No' ) AS if_else_Result; The Result looks like,

There are some differences between “ Temporary Tables ” (#tempTable) and “ Table Variables ” (@tempTable). Point 1: A Temp table (#tmp) can do all the DDL operations and it allows creating the indexes, altering and dropping. A Table variable (@tmp) is not allowed doing the DDL operations but can create the clustered index only. Point 2: A Temp table (#tmp) is easy to create and back up your data. A Variable table (@tmp) is easy to create but involves the extra effort for create the normal tables and then back up your data. Point 3: A Temp table (#tmp) result can be used by multiple users. A Variable table (@tmp) result can be used by the current user only. Point 4: A Temp table (#tmp) will be stored in the tempdb and create network traffic. If we have large amount of data in the temp table and it will create performance issue. A Table variable (@tmp) will be store in the physical memory for some of the data, and then later when the size increases it w...