Anil Singh | Code-sample.com

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

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

What is a trigger in SQL?  Why use triggers? A trigger is a special kind of operations that execute automatically when an event occurs in the database (tables and views) when you trying to INSERT , UPDATE OR DELETE operations in the tables and views. All the triggers are directly attached with the tables and views. Each of the tables has their own trigger. Noted Points :- 1.       We can’t create triggers against the system tables and views. 2.       The AFTER triggers can’t be defined on the views. 3.       You try to avoid using nested triggers. 4.       You try to avoid using recursive triggers. What types of trigger in SQL Server ? There are two types of Triggers available in the SQL that is DML Triggers : - i) After Trigger      ii) Instead of Trigger DDL Triggers There are three query actions that are used in S...

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

What is SQL view? The View is a virtual table, which not create physically, it create logically only. We insert, update & delete the records from a view. In view there are no any physical relations in the database and the view is a virtual table based on the result set of an SQL statement. Why use View instead of a Table? A table contains data but the View dose not stored set of data values in a database and the view is only a SELECT statement. A view can combine columns/rows from multiple tables. Views are work like a security layers and we can handle security issues. Views can be used as security mechanisms and its display only those data that you granting the permissions in this view. Advantages of View:- We can hide so of table columns. Views can model complex joins easily. Views are work like a security layers and we can handle security issues. Drawbacks of View: - When table is dropped or modified, view becomes inactive; i...

--================================ --CREATE FUNCTION WITH PARAMETERS. --================================ CREATE FUNCTION AddFun1 (@NumIst INT ,@NumpIInd INT ) RETURNS INT AS BEGIN DECLARE @Result AS INT SET @Result = @NumIst + @NumpIInd RETURN @Result END ----RESULT LOOKS LIKE, Print test . dbo .AddFun1( 10 , 20 ) --IS 30

What is a function in SQL Server? A SQL function is a set of statements that you can pass input values, perform an action and return the result and the result can be single value or a table value. When do you use SQL function? When I am writing an expression and I want to return some value in the SELECT statement from this expression that time I can use a function. Types of SQL functions, 1) System defined functions ·          Scalar Functions : - abs, round, upper, lower, trim and convert etc. ·          Aggregate Functions : - min, max, avg and count etc. 2) User defined functions The user defined functions are created in the user defined database. This user defined function can be “inline table valued function”, “scalar value function” or “multi statement table valued function”. Syntax:- CREATE FUNCTION < Scalar_Function_Name, sysname, FunctionName > ( ...

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

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