Anil Singh | Code-sample.com

“WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value”? 1.       Where IS NULL ? 2.       Where IS NOT NULL ? 3.       Where ANY VALUE ( NULL AND NOT NULL )? A “ NULL ” is a special value that signifies “ no value ” and comparing a column to NULL using the ( = ) operator is undefined. Instead use WHERE IS NULL or WHERE IS NOT NULL. Stayed Informed   -   37 Best SQL Server Interview Questions and Answers Syntax:-  -- WITH IS NULL SELECT ColumnName FROM TableName WHERE ColumnName IS NULL -- WITH IS NOT NULL SELECT ColumnName FROM TableName WHERE ColumnName IS NOT NULL Examples:- --SELECT ALL COUNTRIES SELECT * FROM Countries Result:- Id CountryName CountryCode ----------------------------------- 1 India IN 2 Nepal NP 3 USA US 4 Rasia NULL 5 Australia AUS Query With ...

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!!!

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...

--================================== --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,

Before create a symmetric key, you first setup your database’s master key and certificate and this master key and certificate help us to protect of your  symmetric  key store using the  ALGORITHM   AES_256. In the below examples, I am using a default master key password is System!123 and the certificate name is Certificate_demo . The following steps as given below, --====================================== --MASTER KEY ENCRYPTION WITH PASSWORD --======================================= CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'System!123' GO --=============================== --OPEN MASTER KEY --============================== OPEN MASTER KEY DECRYPTION BY PASSWORD = 'System!123' GO --============================= --CREATE CERTIFICATE --================================== CREATE CERTIFICATE Certificate_demo WITH SUBJECT = 'Certificate_demo' GO --================================================...

Hello everyone, I am going to share the query and functions for Encrypt and Decrypt GUID, Text, String using SQL Server. Table of Content:- 1.       Query for Encrypt and Decrypt. 2.       Create Function for Encrypt 3.       Create Function for Decrypt 4.       How to use Encrypt and Decrypt functions in SQL? 5.       Query Result. QUERY FOR  ENCRIPT & DECRIPT :- DECLARE @encrypt_Token VARBINARY ( 255 ) DECLARE @decrypt_Token NVARCHAR ( 255 ) SELECT @encrypt_Token = ENCRYPTBYPASSPHRASE ( 'TOKEN' , '997AEBEC-2362-40DA-A506-9F0E965BC4AE' ) SELECT @encrypt_Token AS GUID_TO_ENCRYPT SET @decrypt_Token = ( SELECT CONVERT ( VARCHAR ( 100 ), DECRYPTBYPASSPHRASE ( 'TOKEN' , @encrypt_Token ))) SELECT @decrypt_Token AS DECRYPT_TO_GUID FUNCTION - ENCRIPT TOKEN STRING :- CREATE FUNCTION FNC_ENCR...

Add a column, with a default value, to an existing table in SQL Server Syntax: ALTER TABLE TABLE_NAME             ADD COL_NNAME COL_TYPE {NOT NULL OR NULL}                         CONSTRAINT CONSTRAINT_NAME                                     DEFAULT DEFAULT_VALUE WITH VALUES For example ALTER TABLE CUSTOMER             ADD NAME VARCHAR ( 255 ) NOT NULL                         CONSTRAINT CUSTOMER_CONSTRAINT_NAME    ...

Today's, I am going to share the code sample for exception handling in SQL Server; The SQL Server exception handling is very similar to the Microsoft C# and C++ etc. Error Handling Mechanism :- The two types of error handling in SQL Server that is 1.       @@ERROR 2.       TRY CATCH Block 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. In the SQL Server, The  TRY CATCH  are catches all errors and store in the @ ERRORS  variable and raise the errors using the  RAISERROR (). Following functions are used in CATCH block, ...