SQL Archives - Page 3 of 6 - Dot Net Tricks

EXCEPT Clause in SQL Server

By Abdul Khursheed March 25, 2013

The Except Operator returns any distinct values from the left query that are not also found on the right query. The Except operator evaluates the output of two query expressions and returns the difference between the results. The result set contains all rows returned from the first query expression except those rows that are also returned from the second query expression. Syntax:- SELECT [TableName1].[ColumnName1] FROM TableName1 EXCEPT SELECT [TableName2].[ColumnName2] FROM TableName2

SQL

MERGE Statement with SQL Server 2008

By Abdul Khursheed March 25, 2013

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done inone pass of database table. This is quite an improvement in performance of database query

SQL

Like Operator Examples in SQL

By Abdul Khursheed March 25, 2013

Syntax of LIKE Operator : match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ] match_expression is valid expression of character string data type. Pattern is the pattern to search for in match_expression, escape_character is valid SQL Server expression of any of the data types of the character string data type category. escape_character has no default and must consist of only one character. Return type of like operator is Boolean. LIKE returns TRUE If the match_expression matches the specified pattern. Examples of LIKE Operator:

SQL

If Exists and Not Exists in SQL Server

By Abdul Khursheed March 25, 2013

Introduced with the keyword EXISTS, the subquery functions as an existence test. The WHERE clause of the outer query tests whether the rows that are returned by the subquery exist. subquery introduced with EXISTS has the following syntax: IF EXISTS ( SELECT *FROM ajay WHERE empid = 12 ) BEGIN print 'Valid Employee' END

SQL

Example of SQL SubQuery

By Abdul Khursheed March 25, 2013

Syntax - Subquery SELECT Statement WHERE ColumnName [Comparison Operator] (SELECT Statement / Values) Results of one Sql query or Sql statement as the input for another Select query, Insert Into query, Insert From query,Update query and Delete query. You also can use the result of Subquery as a search condition for using in the IN( ) function or EXISTS operator. Comparison Operator such as =, >, < , >=, <=, LIKE, IN

SQL

Example of WHILE Loop With CONTINUE and BREAK Keywords in SQL

By Abdul Khursheed March 25, 2013

SQL While sets a condition for the repeated execution of an SQL statement or statement block. The SQL statements are executed repeatedly as long as the specified condition is return TRUE. The WHILE loop can be controlled from inside the loop with the CONTINUE,BREAK and GOTO keywords. BREAK statement will exit you from the currently processing WHILE loop. GOTO statement will break out of all WHILE loops, no matter how many nested WHILE statements. CONTINUE statement will skips executing the rest of the statements between the CONTINUE statement and the END statement of the current loop and starts executing at the first line of the current WHILE loop.

SQL

Import CSV File Into SQL Server Using Bulk Insert in SQLServer

By Abdul Khursheed March 25, 2013

BULK INSERT Imports a data file into a database table or view in a user-specified format in SQL Server 2008 R2. Use this statement to efficiently transfer data between SQL Server and heterogeneous data sources. Arguments : database_name Is the database name in which the specified table or view resides. If not specified, this is the current database. schema_name Is the name of the table or view schema. schema_name is optional if the default schema for the user performing the bulk-import operation is schema of the specified table or view. table_name

SQL

DATEDIFF Function in SQL Server

By Abdul Khursheed March 25, 2013

The DATEDIFF Function is used to calculate the difference between two dates. So let's have a look at a practical example of how to use a DATEDIFF Function in SQL Server The DATEDIFF Function The SQL Server DATEDIFF Function is used to calculate the difference between two dates. Syntax

SQL

Difference between Clustered and Non-Clustered Index Data Structures

By Abdul Khursheed March 25, 2013

Indexes-Indexing is way to sort and search records in the table. It will improve the speed of locating and retrieval of records from the table.It can be compared with the index which we use in the book to search a particular record. In Sql Server there are two types of Index 1) Clustered Index 2) Non Clustered Index Clustered Index:- Clustered index physically stored the data of the table in the order of the keys values and the data is resorted every time whenever a new value is inserted or a value is updated in the column on which it is defined.

SQL

SQL Server CTE Basics

By Abdul Khursheed March 25, 2013

What is Common Table Expression (CTE)? A common table expression (CTE) is a temporary storage result set, which will be accessible within the next execution scope of a query. That means we didn’t get CTE result after the second query statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.