SQL Information

An example of processing the rows within a CURSOR LOOP are as follows:-
/* Create a procedure to receive data for the table-valued parameter. */
step1: DECLARE csr_tvp CURSOR FOR select from @tvp
step2:OPEN csr_tvp
step3:FETCH NEXT FROM csr_tvp into parmslist
step4:WHILE @@FETCH_STATUS = 0 begin step3 end
step5:CLOSE csr_tvp;
step6:DEALLOCATE csr_tvp;


http://www.mssqltips.com/sqlservertip/2999/different-ways-to-find-sql-server-object-dependencies/

Find All Queries Run Against a Table

This script will show you all the queries that have run against a particular table since the last time SQL Server was rebooted. This query is good in helping to define indexes. This only works against SQL

2008.

SELECT DISTINCT TOP 1000
     ProcedureName       = OBJECT_SCHEMA_NAME(qt.objectid) + '.' + OBJECT_NAME(qt.objectid)
     ,SQLStatement       = SUBSTRING(
                                     qt.Text
                                     ,(qs.statement_start_offset/2)+1
                                     ,CASE qs.statement_end_offset
                                     WHEN -1 THEN DATALENGTH(qt.text)
                                     ELSE qs.statement_end_offset
                                     END - (qs.statement_start_offset/2) + 1
                                     )
     ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads
     ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads
     ,ExecutionCount     = qs.execution_count
     ,CPUTime            = qs.total_worker_time
     ,DiskWaitAndCPUTime = qs.total_elapsed_time
     ,MemoryWrites       = qs.max_logical_writes
     ,DateCached         = qs.creation_time
     ,DatabaseName       = DB_Name(qt.dbid)
     ,LastExecutionTime  = qs.last_execution_time
     --,sre.*
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 CROSS APPLY sys.dm_sql_referenced_entities(
         OBJECT_SCHEMA_NAME(qt.objectid) + '.' + OBJECT_NAME(qt.objectid)
         , 'OBJECT'
     ) sre
 WHERE qt.dbid = db_id() -- Filter by current database
 AND sre.referenced_schema_name + '.' + sre.referenced_entity_name = 'dbo.Table'

Find all the queries executed recently on a database


Columns:


SELECT object_name(object_id) AS 'TableName', [Name] AS 'ColumnName', Column_ID
FROM sys.all_columns  where name like '%Currency%'
ORDER BY TableName, Column_ID


Constraints

SELECT object_name(o.parent_object_id) AS 'ParentObject', s.name AS 'Schema', o.Name AS 'PrimaryKey'
FROM sys.objects o
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE o.Type IN ('C', 'D', 'UQ')
ORDER BY o.Name;
GO




SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure


Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

----Option 1
 SELECT DISTINCT so.name
 FROM syscomments sc
 INNER JOIN sysobjects so ON sc.id=so.id
 WHERE sc.TEXT LIKE '%tablename%'
 ----Option 2
 SELECT DISTINCT o.name, o.xtype
 FROM syscomments c
 INNER JOIN sysobjects o ON c.id=o.id
 WHERE c.TEXT LIKE '%tablename%'




  --‘%s parameter not valid for address type %s’, @pQtyPrint, @ptAddresstype'


 --when @ptActiontype='CT' then @ptTelex
--             when @ptActiontype in ('CB','CE','CP') then @ptEmail
--             when @ptActiontype in ('CN','CC') then @ptAddressline1,@pQtyPrint,@pDeliveryMethod,@pBuyDestID,@pSellDestID
--             when @ptActiontype='CX' then @pFileExportID






--end

--select case  when @ptActiontype='CF' then @ptAttention,@ptFax
--             when @ptActiontype='CT' then @ptTelex
--             when @ptActiontype in ('CB','CE','CP') then @ptEmail
--             when @ptActiontype in ('CN','CC') then @ptAddressline1,@pQtyPrint,@pDeliveryMethod,@pBuyDestID,@pSellDestID
--             when @ptActiontype='CX' then @pFileExportID








Retrieving Column Names of a Table in SQL Server 2012



 Information_ Schema

Information schema views provide an internal view of the SQL Server metadata independent of the system table. You can use the select statement with the Information Schema to retrieve a table's columns from

the news object or table. The following query will give the table's column names:

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'News'


question:How to change a stored proc's parameter datatype - without editing a CREATE PROC script

Let's suppose I inherit a database with 1000 stored procedures, most of which have a SMALLINT parameter named @CustomerID.

Now lets suppose our CustomerID column has exceeded the capacity of a SMALLINT and we need to convert it to an INT.

Modifying the columns in the related tables is easy enough- I can fire off a query against sys.coulmns that generates a series of ALTER TABLE statements which alter the CustomerID column to an INT.

But my stumbling block seems to be how I can modify the @CustomerID parameter for many stored procedures. I 'd really like to avoid having to script out all the procs and manually edit each one. I hope I am

missing an obvious easy/elegant solution!



ans:It sounds like you've got something working with SMO, but as a side note for anyone interested, the following will list all parameters of datatype 'smallint' for all user created stored procedures.

select schema_name(o.schema_id) schema_name, o.name obj_name, p.name param_name, t.name param_type
from sys.all_parameters p
 join systypes t on t.xusertype = p.user_type_id
 join sys.objects o on o.object_id = p.object_id
where o.type = 'P' -- type procedure
 and t.name = 'smallint' -- data type
 and o.is_ms_shipped = 0 -- user created
order by
 schema_name(o.schema_id),
 o.name;



question:data type size increase in one table,so we need to find all stored procedure which are using that data type?

 select distinct id,name from sys.syscomments a,sys.all_objects b where a.id=b.object_id and text like '%KeyType%char%'




nested stored procedure dependencies

DECLARE @DepTree TABLE (ObjNum int identity(1,1) not null, Name varchar(1000), DependsOn varchar(1000), ObjectType char(2), DepLevel smallint)

INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel)
SELECT DependsOn = S.Name, S.Name, ObjectType = S.XType, DepLevel = 0
FROM sys.sysobjects S
WHERE S.Name = 'pEqAccLookup' and S.xtype='P'

DECLARE @Name varchar(1000)
DECLARE @DependsOn varchar(1000)
DECLARE @DepLevel smallint
DECLARE @ObjNum int
SET @ObjNum = 1
WHILE EXISTS(SELECT 1 FROM @DepTree WHERE ObjNum = @ObjNum )
BEGIN
      SELECT @Name = Name, @DependsOn = DependsOn, @DepLevel = DepLevel FROM @DepTree WHERE ObjNum = @ObjNum

      -- this block finds objects that the current object of interest depends on (moving _down_ the dependency chain):
      IF @DepLevel >= 0
            INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel)
            SELECT DISTINCT S1.Name, DependsOn = S2.Name, ObjectType = S2.XType, DepLevel = @DepLevel + 1
            FROM sys.sysdepends DP
            JOIN sys.sysobjects S1 ON S1.ID = DP.ID and S1.xtype='P'
            JOIN sys.sysobjects S2 ON S2.ID = DP.DepID and S2.xtype='P'
            WHERE S1.Name = @DependsOn
            ORDER BY 1, 3, 2

      -- this block finds objects that depend on the current object of interest (moving _up_ the dependency chain):
      IF @DepLevel <= 0
            INSERT INTO @DepTree (Name, DependsOn, ObjectType, DepLevel)
            SELECT DISTINCT S2.Name, DependsOn = S1.Name, ObjectType = S2.XType, DepLevel = @DepLevel - 1
            FROM sys.sysdepends DP
            JOIN sys.sysobjects S1 ON S1.ID = DP.DepID and S1.xtype='P'
            JOIN sys.sysobjects S2 ON S2.ID = DP.ID and S2.xtype='P'
            WHERE S1.Name = @Name
            ORDER BY 1, 3, 2

      SET @ObjNum = @ObjNum + 1
END

SELECT * FROM @DepTree
================================================================================================================
--coalesce is used for non-null for calculation wages(totalsal based on hourly_wage, salary, and commission) and also used for one column data(all rows) ,"shown as a full string(as one row)
=====================================================================================================================

Sql Server: Convert table Column data into comma separated string or row

SQL SERVER – Create a Comma Delimited/Seprated string or row Using SELECT Clause From Table Column/fields

DECLARE @test NVARCHAR(max)
SELECT  @test = COALESCE(@test + ',', '') +  firstname FROM hr.Employees
SELECT firstname    = @test

Source:http://oops-solution.blogspot.in/2011/11/sql-server-convert-table-column-data.html
=================================================================================================
--In the following example, the wages table includes three columns that contain information about the yearly wages of the employees:
--the hourly wage, salary, and commission. However, an employee receives only one type of pay.
--To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and commission.
SELECT emp_id,CAST(COALESCE(hourly_wage * 40 * 52,
   salary,
   commission * num_sales) AS money) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';
GO
======================================
comparing COALESCE and ISNULL
The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.
1.
Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.

2.
Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the

highest precedence.

3.
The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE

with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values. This makes a difference if you are using

these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example


CREATE TABLE #Demo
(
col1 integer NULL,
col2 AS COALESCE(col1, 0) PRIMARY KEY,
col3 AS ISNULL(col1, 0)
);

-- This statement succeeds because the nullability of the
-- ISNULL function evaluates AS NOT NULL.

CREATE TABLE #Demo
(
col1 integer NULL,
col2 AS COALESCE(col1, 0),
col3 AS ISNULL(col1, 0) PRIMARY KEY
);

4.
Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a data type.

5.
ISNULL takes only 2 parameters whereas COALESCE takes a variable number of parameters.



Note:for checking;create table using primary key

nullability of the ISNULL function evaluates AS NOT NULL.
nullability of the COALESCE expression for col2 evaluates to NULL.


==========================================

UPDATE GenderSET SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END


2.create table Emptst ( Id int, Name varchar(50), DeptId int)
 create table Depttst ( Id int, Name varchar(50))

 select * from Emptst,Depttst

results is

--The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.

3.select case when null = null then 'Yup' else 'Nope' end as Result;

result:

NOPE

 4.Here is a query written to return the list of customers not referred by Jane Smith:
SELECT Name FROM Customers WHERE ReferredBy <> 2;
ans:
SELECT Name FROM Customers WHERE ReferredBy = NULL OR ReferredBy <> 2

============================================================================================
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup


--without use any index                     --       Table scan
--with cluster index                        --      now table scan converted to clustered index scan
--with non-clustered index(Where clause)    --      then key lookup is happened on execution plan
--Here problem is where clause is retreive from index and select columns data is retrieve from table scan(data page),so performance is de-greated.
--Key lookup ,it should be removed from the execution plan to improve performance

--To remove that key lookup-->2 methods:one is cover index(ie;select columns +where columns(Non clutered index),included columns(create non-clustered index)
--Best method is included columns(Creating an included column non-clustered index)


-- I have mentioned in my previous article that I prefer the Method 2. The reason is that method 2 has many advantages over method 1.

--1) Index can exceed the 900-byte limitation of the index key.

--2) Index can include datatypes that are not allowed as key columns – varchar(max), nvarchar(max) or XML.

--3) Size of the key index can be reduced, which improves the overall performance of the index operation.



CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex]
 (
 City
 ) INCLUDE (FirstName,ID) ON [PRIMARY]
 GO
Here ID is clustered index and where clause City is also non-clustered index


In earlier article I have suggested to include all columns but in reality in any non clustered index there is no need to include columns included in clustered index.
 --All non clusteredindex automatically contains pointers to clustered index any way.

IE;CREATE NONCLUSTERED INDEX [IX_OneIndex_Include3] ON [dbo].[OneIndex]
 (
 City
 ) INCLUDE (FirstName) ON [PRIMARY]
 GO

===================================

 SELECT ID, FirstName
 FROM OneIndex WITH (INDEX(IX_OneIndex_Include3))
 WHERE City = 'Las Vegas'
 GO

--Let us examine the execution plan and compare the query costs and also verify that if both the index usages are forcing index seek instead of index scan.

=============================================================================================================================
What is covering index?

It is an index that can satisfy a query just by its index keys without having needed to touch the data pages.

It means that when a query is fired, SQL Server doesn’t need to go to the table to retrieve the rows, but can produce the results directly from the index as the index covers all the columns used in query.


Well, summary is very simple that because of cover index, the performance of the query improves and IO decreases. Included index is another good concept, we will talk about it in later post.

Before Cover Index: IO Cost 0.006088
 logical reads 6, physical reads 3, read-ahead reads 16,
After Cover Index: IO Cost 0.0038657
 logical reads 3, physical reads 1, read-ahead reads 0


=====================================================================

--Table spool:stores data from i/p to temporary table in order to optimize rewinds
--Index spool:re-format from i/p to temporary index,which is then used for seeking with the supplied seek predicate
--clustered index seek:scannin particular ranges of rows from clustered index
--Assert:used to verify that specificied condition exists
==========================================================
T-SQL: Simplified CASE expression
1.ISNULL
 2.COALESCE
 3.IIF
 4.CHOOSE
============================================

-- Customers with orders handled by all employees from the USA
-- using literals
SELECT custid
FROM Sales.Orders
WHERE empid IN(1, 2, 3, 4, 8)
GROUP BY custid
HAVING COUNT(DISTINCT empid) = 5;

============


Orders with maximum orderdate for each employee
ans:correlated subquery(max(date) and empid join condition)

-- Comparing Current to Previous Year’s Number of Customers

use multi reference(left join)


===================================

The fact of the matter is that computer systems have always had the GIGO (garbage in garbage out) problem.


the SQL server needed to be restarted. The tables are initially created in the model database and then copied to tempdb after the first restart of SQL Server. This is one-off process as we built a new

server.
----------------------------------------------------------------------------------------------------------------

Change data capture in sql server

exec sys.sp_cdc_enable_db--for enabling CDC on dbase
--For CDC enabled on table
EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'MyOrders',
    @capture_instance = N'InsideTSQL2008';

-- =====================================================
-- Determine Whether a Table is Enabled for CDC Template
-- =====================================================
SELECT is_tracked_by_cdc ,*FROM sys.tables


--if u do any operations like Delete,insert,update
--once enabled cdc table will track all the data(by creating one table ie;schemanametablename_CT),it geather last value and updated value if u do update operation

-----------------------------------------------------------------------------------
there one primaryu key column in a table  6:13 PM
i wnat to create clustered index on that 6:14 PM
*i wnat to create non clustered index on that 6:14 PM
instead of clustered index 6:14 PM
ans: drop the primary key and then only clustered index can be dropped 6:14 PM
then create primary along with non clustered index

Ans:
create table tpk(id int primary key,name varchar(40))


ALTER TABLE [dbo].[tpk] DROP CONSTRAINT PK__tpk__3213E83F3864608B


ALTER TABLE [dbo].[tpk] ADD PRIMARY KEY  nonCLUSTERED
(
[id] ASC

)

----------
-- Row Number with Arbitrary Order

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS orderid,
  orderdate, custid, empid
FROM sales.Orders;

----------------------------------


--CHECK Constraint
---------------------------------------------------------------------
USE AdventureWorks2008;
GO
SELECT DISTINCT Status FROM Sales.SalesOrderHeader;
--Result: 5 (the status 5 is set for all orders)

--Let's create a CHECK constraint, which disallows values greather than 5 for the status column
ALTER TABLE Sales.SalesOrderHeader WITH CHECK ADD CONSTRAINT chkStatus CHECK (Status < 6);

--Check for rows with the status 4 or 6
SELECT * FROM Sales.SalesOrderHeader WHERE Status = 4;
SELECT * FROM Sales.SalesOrderHeader WHERE Status = 6;
--Result: 100% : 0% (in the first case Clustered Index Scan has been performed, for the second query
-- the table was not touched at all!)

--Clean-Up
ALTER TABLE Sales.SalesOrderHeader DROP CONSTRAINT chkStatus;


-------------------------------------

---------------------------------------------------------------------
--Data Type Conversions
---------------------------------------------------------------------

-- 3.1 nvarchar column and varchar argument - NOTHING happens

--VARCHAR column and NVARCHAR argument - CONVERSION needed
--Result: 5% : 95% (Conversion overhead is significant; non-unicode will be converted to Unicode)
--Logical Reads: 5 vs. 54

--Solution: Use the column data type for argument too, or explicitely convert the argument data type to the column data type
SELECT FirstName, LastName_Varchar FROM dbo.Contacts WHERE LastName_Varchar ='Atkinson';
SELECT FirstName, LastName_Varchar FROM dbo.Contacts WHERE LastName_Varchar = CONVERT(VARCHAR(50),N'Atkinson')


--Constraints and Performance
--Let's create a CHECK constraint, which disallows values greather than 5 for the status column
ALTER TABLE Sales.SalesOrderHeader WITH CHECK ADD CONSTRAINT chkStatus CHECK (Status < 6);
WHERE Status = 4;
WHERE Status = 6;
--Result: 100% : 0% (in the first case Clustered Index Scan has been performed, for the second query
-- the table was not touched at all!)

---------------------------------------------------------------------
--NON-SARG in the WHERE clause

--IN vs. BETWEEN
--Result: Query 1: 2 rows, 6 logical reads
-- Query 2: 2 rows, 3 logical reads (the same plan, but 2 scans for the query 1)

--Use Index for Aggregate Functions
SELECT MAX(TotalDue) FROM Sales.SalesOrderHeader;
--Result: Clustered Index Scan (1406 log. reads)

 --Create an index on the TotalDue and try again
IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name='IX901')
CREATE NONCLUSTERED INDEX IX901 ON Sales.SalesOrderHeader (TotalDue);
GO
SELECT MAX(TotalDue) FROM Sales.SalesOrderHeader;
--Result: Index IX_SalesOrderHeader_TotalDue Scan (2 log. reads)
--Conculsion: Select columns can be also index candidates if they are part of aggregate functions

--verify existance

--Result: 50% : 50% (SQL Server does not count the rows - it stops when the first has been found)

--But if you use local variable to hold COUNT(*), of course it will count the rows
IF ((SELECT COUNT(*)


-- =============================================
-- Add another Sql Server as linked server
-- =============================================
sp_addlinkedserver N'<server_name, sysname, server1>', N'SQL Server'
GO
-- Drop linked server
-- =============================================
sp_dropserver @server     = N'<server_name, sysname, server1>',
     @droplogins = <option, char(10), NULL>
   
-- Configure linked server
-- =============================================
sp_serveroption @server   = N'<server_name, sysname, server1>',
    @optname  = '<option_name, varchar(35), dist>',
    @optvalue = N'<option_value, nvarchar(128), OFF>'    

-- =============================================
-- List linked server
-- =============================================
sp_linkedservers
GO

-- Declare and using a KEYSET cursor
-- Declare and using a READ_ONLY cursor
--Declare and using an UPDATE cursor
-- Declare a SCROLL cursor and use various FETCH options
DECLARE <cursor_name, sysname, test_cursor> SCROLL CURSOR FOR
<select_statement, , SELECT * FROM pubs.dbo.authors>

OPEN <cursor_name, sysname, test_cursor>
-- Fetch  " " in the cursor.
FETCH FIRST FROM <cursor_name, sysname, test_cursor>
--FETCH First,last,prior,next,absoute(rownum),relative(rownum) from cursorname

CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>


---dbase

--==================================
-- Take database offline template
--==================================
ALTER DATABASE <Database_Name, sysname, Database_Name>
   SET OFFLINE
--Drop dbase
DROP DATABASE <Database_Name, sysname, Database_Name>

--=====================================
-- Detach database template
--=====================================
EXEC sp_detach_db @dbname = '<database_name, sysname, your_database_name>'
     ,@skipchecks = 'true'
     ,@KeepFulltextIndexFile = 'true'
   
--==================================
-- Bring database online template
--==================================
ALTER DATABASE <Database_Name, sysname, Database_Name>
   SET ONLINE
GO
--=====================================
-- Attach database template
--=====================================
IF NOT EXISTS(
  SELECT *
    FROM sys.databases
   WHERE name = N'<database_name, sysname, your_database_name>'
)
CREATE DATABASE <database_name, sysname, your_database_name>
ON PRIMARY (FILENAME = '<database_primary_file_path,,C:\Program files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\your_database_name.MDF>')
FOR ATTACH
GO


http://blog.sqlauthority.com/2013/02/25/sql-server-beginning-sql-2012-why-we-use-code-comments-abstract-from-joes-2-pros-volume-1/

my-stored-procedure-best-practices-checklist

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx

Source Tips

Which of Your Stored Procedures are Using the Most Resources?



https://www.simple-talk.com/sql/performance/which-of-your-stored-procedures-are-using-the-most-resources/


http://sqlserverplanet.com/dmvs/find-all-queries-run-against-a-table

SYS.OBJECTS


http://msdn.microsoft.com/en-us/library/ms190324.aspx


ALL STORED PROCEDURES

select * from sys.objects where  type_desc like '%procedure%'



The better way to display a list of stored procedures that reference a specific table or view, or to display a list of objects referenced by a stored procedure, is to use the sys.dm_sql_referencing_entities

and sys.dm_sql_referenced_entities dynamic anagement functions.


http://msdn.microsoft.com/en-us/library/bb630351.aspx


finding procedure metadata or(text)


select * from sys.syscomments where text like '%money%'

 select object_name('4987565')

 pABN_MonthlyTotal

Is there a way to query the database and retrieve a list of all stored procedures and their parameters?

SELECT SPECIFIC_NAME,PARAMETER_NAME,PARAMETER_MODE FROM INFORMATION_SCHEMA.PARAMETERS group by SPECIFIC_NAME,PARAMETER_NAME,PARAMETER_MODE


---------------------------------------------------------------

CREATE TABLE IsolationTests
(
    Id INT IDENTITY,
    Col1 INT,
    Col2 INT,
    Col3 INT
)
update te

INSERT INTO IsolationTests(Col1,Col2,Col3)
SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3


--1.Dirty Reads – This is when you read uncommitted data, when doing this there is no guarantee that data read will ever be committed meaning the data could well be bad.
--2.Phantom Reads – This is when data that you are working with has been changed by another transaction since you first read it in. This means subsequent reads of this data in the same transaction could well

be different.
-----------------------------------------------------
DBCC FREEPROCCACHE --procedure cache
dbcc DROPCLEANBUFFERS
dbcc freesessioncache --session login's
dbcc freesystemcache('all') --system related
--The above statement remove execution plans,so now execute sp's first time and analysis from sql profiler duration(column) and reads(column),write(column)
with(nolock)--common usage it's apply at row level
--with(readpast),if any one is lock some row for update and u want to see before updating which data is then use this statement in select stat
with(readpast)--it's apply at memory level /pages level of db

------------------------------------------------------
1.In Most Commercial RDBMS'es What does Lock Esacalation refer to?

ans:"Escalating Row Level Locks to Page Level, Page Level Locks to Table Level.
"
--------------------------------------------------------------------------------------'''


create proc empproc
as
begin
 begin tran
 create table dept
 ( deptno int primary key ,
  dname varchar(4))

 create table emp (
 ename varchar(4) ,
 eno int  ,
 deptno int FOREIGN KEY REFERENCES DEPT(DEPTNO)   )



INSERT INTO DEPT VALUES( 10, 'prod')
 INSERT INTO DEPT VALUES( 20, 'QA')
 INSERT INTO DEPT VALUES( 30, 'Acct')

 insert into emp values ('KK', 101, 10)
 insert into emp values ('KV', 102, 10)
 insert into emp

values ('Gopi', 202, 20)
 insert into emp values ('KP', 203, 20)
 insert into emp values ('GT', 301, 30)
 insert into emp values ('NR', 302, 30)


 rollback
end
After the 'execute  empproc' is executed will DDL

statements be rolled back as well? i.e will the tables be dropped?
"


ans:Yes, everything is rolled back


2.Name two disadvantages of Locking Include?
ans:1. Performance Overhead 2. Deadlocks.

----------------

Prior to continuing with this article let us first look into few synonyms for LF and CR.

Line Feed – LF – \n – 0x0a – 10 (decimal)

Carriage Return – CR – \r – 0x0D – 13 (decimal)

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
 PRINT ('SELECT FirstLine AS FL SELECT SecondLine AS SL' )
 GO

 DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
 PRINT ('SELECT FirstLine AS FL ' +@NewLineChar + 'SELECT SecondLine AS SL'+@NewLineChar )
 GO

It's working for only Print statment,but not for Select

--------------------------------

--IDENTITY(Column) property and then describes the sequence object

The IDENTITY property has no cycling support. This means that after you reach the maximum value in the type,
the next insertion will fail due to an overflow error.



SELECT SCOPE_IDENTITY() AS SCOPE_IDENTITY,--last identity value(ur session and current scope)
@@IDENTITY AS [@@IDENTITY],----last identity value(ur session and regardless of scope)
IDENT_CURRENT('Sales.MyOrders') AS IDENT_CURRENT;--accepts a table as input and returns the last identity
--value generated in the input table regardless of session.



create proc identP1
@pname varchar(20)
as
begin

insert into identtab1 values(@pname)
exec identP2
@pname
select SCOPE_IDENTITY()--return Proc1 identity value,@@IDENTITY --return Proc2 identity value
end
go


3. TRUNCATE TABLE Sales.MyOrders;
--Next, query the current identity value in the table.
SELECT IDENT_CURRENT('Sales.MyOrders') AS [IDENT_CURRENT];

--To reseed the current identity value, use the DBCC CHECKIDENT command, as follows.
DBCC CHECKIDENT('Sales.MyOrders', RESEED, 4);


4.Also, you can reseed the property value.To guarantee uniqueness you must use a constraint like PRIMARY KEY or UNIQUE.

5.INSERT INTO Sales.MyOrders(custid, empid, orderdate) VALUES(3, -1, '20120620');--its failed due check option constraints not satified,but identity id value is genereated(ie;4 to 5),which we won't expect

identity Issue and resolution is Sequnce Object and below is the statement

--is for invoicing systems; gaps between invoice numbers are not allowed. In such cases, you
--need to create an alternative solution, such as storing the last-used value in a table.
--The IDENTITY property has no cycling support. This means that after you reach the maximum
--value in the type, the next insertion will fail due to an overflow error. To get around this,
--you need to reseed the current identity value before such an attempt is made.

============================================


--The sequence object doesn’t suffer from
--many of the limitations of the IDENTITY property, which include the following:
--The IDENTITY property is tied to a particular column in a particular table. You cannot
--remove an existing property from a column or add it to an existing column. The
--column has to be defined with the property.
--Sometimes you need keys to not conflict across different tables. But IDENTITY is tablespecific.
--Sometimes you need to generate the value before using it. With the IDENTITY property,
--this is not possible. You have to insert the row and only then collect the new value with a function.
--You cannot update an IDENTITY column.
--The IDENTITY property doesn’t support cycling.
--A TRUNCATE statement resets the identity value.
--The sequence object doesn’t suffer from these limitations. This section explains how to
--work with the object and shows how it doesn’t suffer from the same restrictions as IDENTITY.


Sequnece

--There are a number of properties that you can set, all with default options in case you
--don’t provide your own. The following are some of the properties and their default values:
--INCRE MENT BY Increment value. The default is 1.
--MINVALUE The minimum value to support. The default is the minimum value in the
--type. For example, for an INT type, it will be -2147483648.
--MAXVALUE The maximum value to support. The default is the maximum value in
--the type.
--CYCLE | NO CYCLE Defines whether to allow the sequence to cycle or not. The default
--is NO CYCLE.
--START WITH The sequence start value. The default is MINVALUE for an ascending
--sequence (positive increment) and MAXVALUE for a descending one.

================================Merge==================================

Merge into Sales.Myorders with(holdlock) as trg
using(values(@custid,@empid,@orderdate))as src
(custid,empid,orderdate) on trg.orderdate=src.orderdate
when matched then update
set trg.custid=src.custid,
trg.empid=src.empid,
trg.orderdate=src.orderdate
when not matched then
insert values(src.custid,src.empid,src.orderdate);

====================ISNULL==============================

TGT.custid = SRC.custid OR (TGT.custid IS NULL AND SRC.custid IS NOT
NULL) OR (TGT.custid IS NOT NULL AND SRC.custid IS NULL).
=====================================

WHEN NOT MATCHED BY SOURCE THEN
DELETE;



================================

What’s interesting about the USING clause where you define the source for the MERGE
--operation is that it’s designed like the FROM clause in a SELECT statement. This means that you
--can define table operators like JOIN, APPLY, PIVOT, and UNPIVOT; and use table expressions like
--derived tables, CTEs, views, inline table functions, and even table functions like OPENROWSET
--and OPENXML.

=============================================================
1. What is the purpose of the ON clause in the MERGE statement?
2. What are the possible actions in the WHEN MATCHED clause?
3. How many WHEN MATCHED clauses can a single MERGE statement have?
Quick Check Answer
1. The ON clause determines whether a source row is matched by a target row,
and whether a target row is matched by a source row. Based on the result of
the predicate, the MERGE statement knows which WHEN clause to activate and
as a result, which action to take against the target.
2. UPDATE and DELETE.
3. Two—one with an UPDATE action and one with a DELETE action.

======================================OUTPUT CLAUSE
IF OBJECT_ID('Sales.MyOrders') IS NOT NULL DROP TABLE Sales.MyOrders;
CREATE TABLE Sales.MyOrders
(
orderid INT identity(1,1)NOT NULL
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY,
custid INT NOT NULL
CONSTRAINT CHK_MyOrders_custid CHECK(custid > 0),
empid INT NOT NULL
CONSTRAINT CHK_MyOrders_empid CHECK(empid > 0),
orderdate DATE NOT NULL
);

INSERT INTO Sales.MyOrders(custid, empid, orderdate)
OUTPUT
 inserted.custid, inserted.empid, inserted.orderdate
SELECT custid, empid, orderdate
FROM Sales.Orders
WHERE shipcountry = N'Norway';

DELETE FROM Sales.MyOrders
OUTPUT deleted.orderid
WHERE empid = 1;


UPDATE Sales.MyOrders
SET orderdate = DATEADD(day, 1, orderdate)
OUTPUT
inserted.orderid,
deleted.orderdate AS old_orderdate,
inserted.orderdate AS neworderdate
WHERE empid = 7;
===================================================

1. How many OUTPUT clauses can a single statement have?
2. How do you determine which action affected the OUTPUT row in a MERGE
statement?
Quick Check Answer
1. Two—one with INTO and one without INTO.
2. Use the $action function.







=========================
-------------------------------------------------------------------------------
CREATE TABLE Department
 (
 DepartmentID INT PRIMARY KEY,
 DepartmentName VARCHAR(30)
 )


CREATE TYPE DeptType AS TABLE
 (
 DeptId INT, DeptName VARCHAR(30)
 );
 GO

--Important points  to remember :Table-valued parameters(TVP)

---TVP must be passed as READONLY parameters to SQL routines. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a TVP in the body of a routine.

---  You cannot use a TVP as target of a SELECT INTO or INSERT EXEC statement. A TVP can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

 CREATE PROCEDURE InsertDepartment
 @InsertDept_TVP DeptType READONLY
 AS
 INSERT INTO Department(DepartmentID,DepartmentName)
 SELECT * FROM @InsertDept_TVP;



GO


--3. Declare a table type variable and reference the table type.
DECLARE @DepartmentTVP AS DeptType;
--4. Using the INSERT statement and occupy the variable.
INSERT INTO @DepartmentTVP(DeptId,DeptName)
 VALUES (1,'Accounts'),
 (2,'Purchase'),
 (3,'Software'),
 (4,'Stores'),
 (5,'Maarketing');

 --5. We can now pass the variable to the procedure and Execute.
 EXEC InsertDepartment select DeptId from @DepartmentTVP;
 GO 100

 select * from Department

 --Conclusion:

--TVP is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table or passing so many parameters. It helps in using complex

business logic in single routine. They reduce Round Trips to the server making the performance better.


-----------------------------------

CREATE TABLE Department
 (
 DepartmentID INT PRIMARY KEY,
 DepartmentName VARCHAR(30)
 )


CREATE TYPE DeptType AS TABLE
 (
 DeptId INT, DeptName VARCHAR(30)
 );
 GO

--Important points  to remember :Table-valued parameters(TVP)

---TVP must be passed as READONLY parameters to SQL routines. You cannot perform DML operations like UPDATE, DELETE, or INSERT on a TVP in the body of a routine.

---  You cannot use a TVP as target of a SELECT INTO or INSERT EXEC statement. A TVP can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

 CREATE PROCEDURE InsertDepartment
 @InsertDept_TVP DeptType READONLY
 AS
 INSERT INTO Department(DepartmentID,DepartmentName)
 SELECT * FROM @InsertDept_TVP;

GO

--3. Declare a table type variable and reference the table type.
DECLARE @DepartmentTVP AS DeptType;
--4. Using the INSERT statement and occupy the variable.
INSERT INTO @DepartmentTVP(DeptId,DeptName)
 VALUES (1,'Accounts'),
 (2,'Purchase'),
 (3,'Software'),
 (4,'Stores'),
 (5,'Maarketing');

 --5. We can now pass the variable to the procedure and Execute.
 EXEC InsertDepartment @DepartmentTVP;
 GO

 select * from Department

 --Conclusion:

--TVP is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table or passing so many parameters. It helps in using complex

business logic in single routine. They reduce Round Trips to the server making the performance better.

 alter PROCEDURE selectDepartment
 @selectDept_TVP DeptType READONLY
as
 BEGIN
    SET NOCOUNT ON
    declare @vpnDeptId integer,
            @vpnDeptName    varchar(50)
         

    DECLARE csr_tvp CURSOR FOR
    SELECT DeptId  , DeptName    
 
    FROM @selectDept_TVP

    OPEN csr_tvp

    FETCH NEXT FROM csr_tvp
    INTO @vpnDeptId , @vpnDeptName

    WHILE @@FETCH_STATUS = 0
    BEGIN
     
        -- Perform LOOP processing on creating the next
        -- etc
        print @vpnDeptId
print @vpnDeptName
        -- fetch next item from LOOP
        FETCH NEXT FROM csr_tvp
    INTO @vpnDeptId , @vpnDeptName

    END

    CLOSE csr_tvp;
    DEALLOCATE csr_tvp;
  END


  DECLARE @DepartmentTVP AS DeptType;
--4. Using the INSERT statement and occupy the variable.
INSERT INTO @DepartmentTVP(DeptId,DeptName)
 VALUES (1,'Accounts'),
 (2,'Purchase'),
 (3,'Software'),
 (4,'Stores'),
 (5,'Maarketing');

 --5. We can now pass the variable to the procedure and Execute.
 EXEC selectDepartment @DepartmentTVP;
 GO

 tablevariable add

 exec (


 exec test

 create proc

 DECLARE @temp TABLE(Col1 INT)
 INSERT INTO @temp (Col1)
 SELECT TOP 3000 ROW_NUMBER() OVER(ORDER BY a.name)
 FROM sys.all_objects a
 CROSS JOIN sys.all_objects b
 --------------------------------------------------------------------------

/********************************************
 *   TEST 1: @Table Variable insertion
 *********************************************/
 DECLARE  @SalesOrderDetail TABLE (
     [SalesOrderID]          [INT],
     productid    [INT]
 )

INSERT INTO @SalesOrderDetail
 (
     [SalesOrderID],
     productid  
)
 SELECT TOP 10
     [OrderID],
     productid
 FROM [Sales].OrderDetails

DELETE @SalesOrderDetail

GO 10000    -- loop the above batch 10k times

/********************************************
 *   TEST 2: #temp table insertion
 *********************************************/
     SELECT TOP 10
         [SalesOrderID],
         [SalesOrderDetailID]
     INTO #sod
     FROM [Sales].[SalesOrderDetail]

    DROP TABLE #sod

GO 10000    -- loop the above batch 10k times

sp_help Sales.SalesOrderDetail

DECLARE @T1 TABLE(col1 INT);
INSERT @T1 VALUES(1);
SELECT * FROM @T1;

alter procedure testsales
as
begin
SELECT distinct
     [OrderID],
     productid
 FROM [Sales].OrderDetails WHERE orderid=10248 and productid=11

 end

alter procedure testsaleproc1
as
begin
--drop table #T1
--DECLARE @T1 TABLE([OrderID] INT);
create table #T1([OrderID] INT,productid int);
INSERT #T1([OrderID],
 productid)
 exec testsales;
 declare @vqty as int,@vproductid as int;
--INSERT @T1([OrderID]
--     ) exec testsales;
--SELECT * FROM @T1;

 select @vorderID= OrderID,@vproductid=productid from #T1
print @vorderID
print @vproductid
exec testsales_id @orderid=@vorderID
--exec testsales
end

exec testsaleproc1
ALTER procedure testsaleproc2
as
begin

--DECLARE @T1 TABLE([OrderID] INT);

create table #T2([OrderID] INT,productid int);
INSERT #T2([OrderID],
 productid)
 exec testsales

exec testsales_id EXEC dbo.sp_executesql N'SELECT OrderID FROM #T2'

--exec testsales
end




exec testsaleproc2

alter procedure testsales_id(@orderid varchar(12))
as
begin
select orderid,productid from Sales.OrderDetails where orderid=convert(int,@orderid)
end


exec testsales_id 'select 10248'

exec testsales_id
@orderid=10248

p1 orderid 10248
p2 where p1 orderid


convert (char, round (@TotalBuyTradeValue,0))

------------------------------------------------------------
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup

-- Create Table OneIndex with few columns
CREATE TABLE OneIndex (ID INT,
 FirstName VARCHAR(100),
 LastName VARCHAR(100),
 City VARCHAR(100))
 GO
 -- Insert One Hundred Thousand Records
 INSERT INTO OneIndex (ID,FirstName,LastName,City)
 SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
 'Bob',
 CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
 ELSE 'Brown' END,
 CASE
 WHEN ROW_NUMBER() OVER (ORDER BY a.name)%1000 = 1 THEN 'Las Vegas'
 WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
 WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
 WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
 ELSE 'Houston' END
 FROM sys.all_objects a
 CROSS JOIN sys.all_objects b
 GO

 --Now let us run following select statement and check the execution plan.
 ---------Table Scan with out index---------------------
SELECT ID, FirstName
 FROM OneIndex
 WHERE City = 'Las Vegas'--Table Scan
 GO
 --As there is no index on table,
 --scan is performed over the table.
 --We will create a clustered index on the table and check the execution plan once again.

 -- Create Clustered Index

 CREATE CLUSTERED INDEX [IX_OneIndex_ID] ON [dbo].[OneIndex]
 (
 [ID] ASC
 ) ON [PRIMARY]
 GO


--Now, run following select on the table once again.
 -----------------------------Now table scan is converted to cluster index scan-----------------
SELECT ID, FirstName
 FROM OneIndex
 WHERE City = 'Las Vegas'
 GO
--It is clear from execution plan that as a clustered index is created on the table,
--table scan is now converted to clustered index scan.
--In either case, base table is completely scanned and there is no seek on the table.

--Now, let us see the WHERE clause of our table.
-- From our basic observation, if we create an index on the column that contains the clause, a performance improvement may be obtained.
-- Let us create non-clustered index on the table and then check the execution plan.

-- Create Index on Column City As that is used in where condition
 CREATE NONCLUSTERED INDEX [IX_OneIndex_City] ON [dbo].[OneIndex]
 (
 [City] ASC
 ) ON [PRIMARY]
 GO
--After creating the non-clustered index(Where clause), let us run our select statement again and check the execution plan.
SELECT ID, FirstName
 FROM OneIndex
 WHERE City = 'Las Vegas'
 GO


-- As we have an index on the WHERE clause,
--the SQL Server query execution engine uses the non-clustered index to retrieve data from the table.
-- However, the columns used in the SELECT clause are still not part of the index, and to display those columns,
--the engine will have to go to the base table again and retrieve those columns.
-- This particular behavior is known as bookmark lookup or key lookup.

--There are two different methods to resolve this issue. I have demonstrated both the methods together;
--however, it is recommended that you use any one of these methods for removing key lookup. I prefer Method 2.


--Method 1: Creating non-clustered cover index.

--In this method, we will create non-clustered index containing the columns,
--which are used in the SELECT statement, along with the column which is used in the WHERE clause.

CREATE NONCLUSTERED INDEX [IX_OneIndex_Cover] ON [dbo].[OneIndex]
 (
 City, FirstName, ID
 ) ON [PRIMARY]
 GO

--Once the above non-clustered index, which covers all the columns in query, is created, let us run the following SELECT statement and check our execution plan.

SELECT ID, FirstName
 FROM OneIndex
 WHERE City = 'Las Vegas'
 GO

--From the execution plan, we can confirm that key lookup is removed the only index seek is happening.
--As there is no key lookup, the SQL Server query engine does not have to go to retrieve the data from data pages
--and it obtains all the necessary data from index itself.

--Method 2: Creating an included column non-clustered index.

--Here, we will create non-clustered index that also includes the columns, which are used in the SELECT statement, along with the column used in the WHERE clause. In this method, we will use new syntax

introduced in SQL Server 2005. An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index.

CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex]
 (
 City
 ) INCLUDE (FirstName,ID) ON [PRIMARY]
 GO

--From the execution plan, it is very clear that this method  also removes the key lookup as well.

SELECT ID, FirstName
 FROM OneIndex
 WHERE City = 'Las Vegas'
 GO

 ---In summary, Key lookup, Bookmark lookup or RID lookup reduces the performance of query,
 -- and we can improve the performance of query by using included column index or cover index.

 --http://blog.sqlauthority.com/2009/10/07/sql-server-query-optimization-remove-bookmark-lookup-remove-rid-lookup-remove-key-lookup/


 --Part2:
 CREATE NONCLUSTERED INDEX [IX_OneIndex_Cover2] ON [dbo].[OneIndex]
 (
 City, FirstName, ID
 ) ON [PRIMARY]
 GO

 CREATE NONCLUSTERED INDEX [IX_OneIndex_Include2] ON [dbo].[OneIndex]
 (
 City
 ) INCLUDE (FirstName,ID) ON [PRIMARY]
 GO

 SELECT ID, FirstName
 FROM OneIndex WITH (INDEX(IX_OneIndex_Cover2))
 WHERE City = 'Las Vegas'
 GO
 SELECT ID, FirstName
 FROM OneIndex WITH (INDEX(IX_OneIndex_Include2))
 WHERE City = 'Las Vegas'
 GO

-- I have mentioned in my previous article that I prefer the Method 2. The reason is that method 2 has many advantages over method 1.

--1) Index can exceed the 900-byte limitation of the index key.

--2) Index can include datatypes that are not allowed as key columns – varchar(max), nvarchar(max) or XML.

--3) Size of the key index can be reduced, which improves the overall performance of the index operation.


--Part 3

CREATE NONCLUSTERED INDEX [IX_OneIndex_Cover3] ON [dbo].[OneIndex]
 (
 City, FirstName
 ) ON [PRIMARY]
 GO

--Method 2: Creating included column non-lustered index.

CREATE NONCLUSTERED INDEX [IX_OneIndex_Include3] ON [dbo].[OneIndex]
 (
 City
 ) INCLUDE (FirstName) ON [PRIMARY]
 GO


 SELECT ID, FirstName
 FROM OneIndex WITH (INDEX(IX_OneIndex_Cover3))
 WHERE City = 'Las Vegas'
 GO
 SELECT ID, FirstName
 FROM OneIndex WITH (INDEX(IX_OneIndex_Include3))
 WHERE City = 'Las Vegas'
 GO

  SELECT ID, FirstName
 FROM OneIndex WITH (INDEX(IX_OneIndex_Cover2))
 WHERE City = 'Las Vegas'
 GO
 SELECT ID, FirstName
 FROM OneIndex WITH (INDEX(IX_OneIndex_Include2))
 WHERE City = 'Las Vegas'
 GO

 ---In earlier article I have suggested to include all columns but in reality in any non clustered index there is no need to include columns included in clustered index.
 --All non clusteredindex automatically contains pointers to clustered index any way.

 --Let us examine the execution plan and compare the query costs and also verify that if both the index usages are forcing index seek instead of index scan.


 drop table test1_pk
 create table test1_pk(id int primary key,phone int)
 CREATE NONCLUSTERED INDEX [IX_test1_nonclust] ON test1_pk
 (
 ID
 ) ON [PRIMARY]
 GO

 CREATE CLUSTERED INDEX [IX_test1_clust] ON test1_pk
 (
 phone
 ) ON [PRIMARY]
 GO

 insert into test1_pk values(5)
 select * from test1_pk

 go
 CREATE PROC uspStringWithoutQuotes

  @Param NVARCHAR(20) = Hello

AS

PRINT @Param + '!';

GO
exec uspStringWithoutQuotes default


DECLARE @expr_1 NVARCHAR(10) ,

        @expr_2 NVARCHAR(10) ;



SET @expr_1 = NULL ;

SET @expr_2 = N'Saeid' ;



SELECT @expr_1 AS expr_1,

       @expr_2 AS expr_2,

       ISNULL(@expr_1, @expr_2) AS [ISNULL Result]
     
go
DECLARE @Val_1 INT ,

        @Val_2 NVARCHAR(10) ;



SET @Val_1 = NULL ;

SET @Val_2 = 'Saeid' ;



SELECT @Val_1 AS [Value 1],

       @Val_2 AS [Value 2],

       ISNULL(@Val_1, @Val_2) AS [ISNULL Result]    
--When the data types of two arguments are different, if they are implicitly convertible, SQL Server converts one to the other, otherwise returns an error. Executing follow code results an error as

illustrated in output figure.    
go
DECLARE @Val_1 INT ,

        @Val_2 NVARCHAR(10) ;



SET @Val_1 = NULL ;

SET @Val_2 = '500' ;



SELECT @Val_1 AS [Value 1],

       @Val_2 AS [Value 2],

       ISNULL(@Val_1, @Val_2) AS [ISNULL Result]
go
--Implicit conversion may lead to data truncation. This will happen if the length of expr_1 data type is shorter than length of expr_2 data type. So it is better to convert explicitly if needed. In the next

example first output column suffers from value truncation while second will not.


DECLARE @Val_1 NVARCHAR(2) ,

        @Val_2 NVARCHAR(10) ;



SET @Val_1 = NULL ;

SET @Val_2 = 'Saeid' ;



SELECT ISNULL(@Val_1, @Val_2) AS [ISNULL Result],

       ISNULL(CONVERT(NVARCHAR(10), @Val_1), @Val_2) AS [ISNULL Result with explicit convert]



--There are few rules to determine output column's data type generated via ISNULL. The next code illustrates these rules:

go
IF OBJECT_ID('dbo.TestISNULL', 'U') IS NOT NULL

  DROP TABLE dbo.TestISNULL ;



DECLARE @Val_1 NVARCHAR(200) ,

        @Val_2 DATETIME ;



SET @Val_1 = NULL ;

SET @Val_2 = GETDATE() ;



SELECT ISNULL('Saeid', @Val_2) AS Col1,

       ISNULL(@Val_1, @Val_2) AS Col2,

       ISNULL(NULL, @Val_2) AS Col3,

       ISNULL(NULL, NULL) AS Col4    

INTO dbo.TestISNULL

WHERE 1 = 0 ;

GO



SELECT COLUMN_NAME ,

       DATA_TYPE ,

       CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = N'dbo'

  AND TABLE_NAME = N'TestISNULL'      


  --Follow code illustrates the rules to determine output column data type generated via ISNULL:
 go

IF OBJECT_ID('dbo.TestISNULL', 'U') IS NOT NULL

  DROP TABLE dbo.TestISNULL ;



DECLARE @Val_1 NVARCHAR(200) ,

        @Val_2 DATETIME ;



SET @Val_1 = NULL ;

SET @Val_2 = GETDATE() ;



SELECT ISNULL('Saeid', @Val_2) AS Col1,

       ISNULL(@Val_1, @Val_2) AS Col2

INTO dbo.TestISNULL

WHERE 1 = 0 ;

GO



SELECT COLUMN_NAME ,

       IS_NULLABLE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = N'dbo'

  AND TABLE_NAME = N'TestISNULL' ;
   


===============================================================================================

---------------------------------------------------------------------
-- Clearing the Cache
---------------------------------------------------------------------

-- Clearing data from cache
DBCC DROPCLEANBUFFERS;

-- Clearing execution plans from cache
DBCC FREEPROCCACHE; -- ( plan_handle | sql_handle | pool_name )
GO

-- Clearing execution plans for a particular database
DBCC FLUSHPROCINDB(<dbid>);
GO

DBCC FREESYSTEMCACHE(<cachestore>) -- 'ALL', pool_name, 'Object Plans', 'SQL Plans', 'Bound Trees'
GO



-- STATISTICS IO
---------------------------------------------------------------------

-- First clear cache
DBCC DROPCLEANBUFFERS;

---------------------------------------------------------------------
-- Measuring Runtime of Queries

-- First clear cache
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
---------------------------------------------------------------------------------------------------------------------

-- Observe level of fragmentation
SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats
(
  DB_ID('tempdb'),
  OBJECT_ID('dbo.T1'),
  1,
  NULL,
  NULL
);

-- Get index linked list info
DBCC IND('tempdb', 'dbo.T1', 0);
GO

--------------------------------------------------------------------------

-- Make sure you clear the cache before running each solution
DBCC DROPCLEANBUFFERS;

====================================================================
You can improve your SQL Server Compact 4.0 application performance by optimizing the queries you use. The following sections outline techniques you can use to optimize query performance.
1. Improve Indexes
2. Choose What to Index
3. Use the Query Optimizer
4. Understand Response Time vs. Total Time
5. Rewrite Subqueries to Use JOIN
6. Use Parameterized Queries
7. Query Only When You Must
========================================================================================

Subquery Rules

A subquery is subject to the following restrictions:
•
The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).

•
If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.

•
The ntext, text, and image data types cannot be used in the select list of subqueries.

•
Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.

•
The DISTINCT keyword cannot be used with subqueries that include GROUP BY.

•
The COMPUTE and INTO clauses cannot be specified.

•
ORDER BY can only be specified when TOP is also specified.

•
A view created by using a subquery cannot be updated.

•
The select list of a subquery introduced with EXISTS, by convention, has an asterisk (*) instead of a single column name. The rules for a subquery introduced with EXISTS are the same as those for a standard

select list, because a subquery introduced with EXISTS creates an existence test and returns TRUE or FALSE, instead of data.


===========================================================USEFUL QUERIES============================================================

SELECT 15 --o/p:15
SELECT $ /*o/p:0.00*/
SELECT COUNT(*) --o/p: is 1
SELECT COUNT('7')--o/p: is 1
SELECT 'VIKAS' + 1 --Throw error(Conversion failed when converting the varchar value 'VIKAS' to data type int.)
SELECT 'VIKAS' + '1' --o/p: VIKAS1
SELECT (SELECT 'VIKAS')--O/P: is VIKAS
SELECT SELECT 'VIKAS'--O/P is Throw error
SELECT * FROM 'Country' --Throw error
SELECT * FROM Country , EmployeeDetail-- Output will be cross join of both tables
SELECT COUNT(*) + COUNT(*) --o/p is 2
SELECT 'VIKAS' FROM Country--Display "VIKAS" as many rows in Country table
SELECT SUM(1+2*3)--o/p is 7
SELECT MAX(1+2*3)--o/p is 7
SELECT MAX(1,3,4)--o/p :throw error:max function requires 1 argument(s)
SELECT MAX('VIKAS')--o/p is VIKAS
Select Count(SELECT CountryID FROM Country)--Throw error


Join condition


--if tbl_1 values rows:1,1,tbl_2 values 1,1,1,--What will be the output of the following query(ineer join,left outer join,right outer join,full outer join,cross join as same o/p)


--66. What will be the output of the following query.(Related Tables : Table_1,Table_2),so equal data in both tables than data,otherwise null with id condition satisified than corresponding tbl data and

other tbl is null,where as id &name condition then both tbl data is null only



SELECT A.A FROM (SELECT 1 A, 2 B) A
JOIN (SELECT 1 A,1 B)B ON A.A = B.B--o/p:1

SELECT B.A FROM (SELECT 1 A) A

JOIN (SELECT 1 A, 2 B)B ON A.A = B.A--o/p:1

SELECT B.A FROM (SELECT 1 A) A

JOIN (SELECT 1 A, 2 B)B ON A.A = B.B--:o/p is nothing

SELECT * FROM (SELECT 1 A UNION ALL SELECT 2 B) A

JOIN (SELECT 1 A,2 B UNION ALL SELECT 1 A, 1 B)B ON A.A = B.B--o/p is 2,1(A header) :1,1(A header): 2,1(B header)

SELECT * FROM (SELECT 1 A UNION ALL SELECT 2 B) A

JOIN (SELECT 1 A,2 B UNION ALL SELECT 1 A, 1 B)B ON A.A = B.A--o/p is 1,1(A header) :1,1(A header): 2,1(B header)



This query will give the result,combined all the rows data with comma sepearte


DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM employee
SELECT @Names


query1:

This will give list from  results like "@gmail.com
Select STUFF('ravuripradeep@gmail.com',1,CharIndex('@','ravuripradeep@gmail.com') ,'')

query2:
This will give list from  results like before"@gmail.com ie;pradeep

select SUBSTRING('pradeep@gmail.com',1, CHARINDEX('@','pradeep@gmail.com')-1)


method2:
select SUBSTRING([Email Address],1,PATINDEX ( '%@%',[Email Address])-1) from tbl_sample

_________________________________________________________________________________________________
I really like @Juliett's solution! I would just use a CTE to get all the invalid characters:

;WITH CTE AS
(
  SELECT SUBSTRING(@badStrings, 1, 1) AS [String], 1 AS [Start], 1 AS [Counter]
  UNION ALL
  SELECT SUBSTRING(@badStrings, [Start] + 1, 1) AS [String], [Start] + 1, [Counter] + 1
  FROM CTE
  WHERE [Counter] < LEN(@badStrings)
)

SELECT @teststring = REPLACE(@teststring, CTE.[String], '') FROM CTE

SELECT @teststring
_______________________________________________________________________________________


/*=============================================================================
-----------------CLEAR THE LOGS WHEN DISK SIZE IS FULL------------------------
==============================================================================*/

/*1)These Statements are used to clear the LOGS when disk size is full.
    Provide the database name which is full and run these scripts.*/

--backup log CTM with truncate_only
--backup log tempdb with truncate_only

--use tempdb
CHECKPOINT
BACKUP LOG tempdb WITH NO_LOG
Go




sp_who2
kill 104



SELECT
    p1.SPID AS blockedSPID, p2.SPID AS blockingSPID,p1.open_tran,*
FROM
    master..sysprocesses p1
    JOIN
    master..sysprocesses p2 ON p1.blocked = p2.spid



 SELECT
    [s_tst].[session_id],
    [s_es].[login_name] AS [Login Name],
    DB_NAME (s_tdt.database_id) AS [Database],
    [s_tdt].[database_transaction_begin_time] AS [Begin Time],
    [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
    [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
    [s_est].text AS [Last T-SQL Text],
    [s_eqp].[query_plan] AS [Last Plan]
FROM
    sys.dm_tran_database_transactions [s_tdt]
JOIN
    sys.dm_tran_session_transactions [s_tst]
ON
    [s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
    sys.[dm_exec_sessions] [s_es]
ON
    [s_es].[session_id] = [s_tst].[session_id]
JOIN
    sys.dm_exec_connections [s_ec]
ON
    [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
    sys.dm_exec_requests [s_er]
ON
    [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
    sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
    sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY
    [Begin Time] ASC;
GO

-----------------------------------------------------------------------------------------

-----------------------------------------------  
          -- Start transaction  
          -----------------------------------------------  
          if @@trancount = 0
   BEGIN
    begin transaction BlockTrans
    select @vTrans = 1  
   END


last
select @vErrorValue = @@error

          -----------------------------------------------  
          -- Commit or rollback transaction depending  
          --  on success of procedure  
          -----------------------------------------------  

          if (@vErrorValue = 0)  
          begin  
              if (@vInTrans =1 )    
               begin    
                    commit transaction BlockTrans    
               end    
          end  
          else  
          begin
    if (@vInTrans =1 )    
    begin  
     rollback transaction BlockTrans  --has to be before the pErrorHandler    
    end
    if @@trancount = 0    
     execute pErrorHandler @vErrorValue  
          end  

          return @vErrorValue


---------------
pGetVssPathsForMultipleSps:passing multiple values as a parameter like paramarray('test,test2,test3')

 select LTRIM(rtrim(''''+replace('test,test2',',',''',''')+''''))--'test','test2'




===================================================================


with(nolock)
Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read.

Dirty reads are possible. Only applies to the SELECT statement

Dirty Read is a process of reading database record without locking the record being read.

Stand the risk of reading an uncommitted row that is subsequently rolled back. Resulting in reading of local copy of record that is not consistent with what is stored in database.


SELECT keydesc FROM ci with(nolock)--even if any one using transactions in the corresponding table,it will show before commit/rollback perfomed by user,corresponding record updated value will be

available,while reading is available,after sometime,user is rollback then,we don't have any info to get previous data

--if we run with out with(nolock) condition,if any one performing transcation for that table,to update one record,but not use the commit/rollback state,if we want to select the corresponding record,it will

executing,but not provide the data,if transaction is complete then select query is in execution then it will provide the data


1.SQL Server Update Query does not use Index(http://stackoverflow.com/questions/3524871/sql-server-update-query-does-not-use-index)
update tmp
        set    BuySell = a.BuySell
        from   #tmpClientStmt1 tmp,
               sample1 a  with (index = PK_sample1)
               where a.FirmID=tmp.FirmID

2.a seek is not always faster than a scan. And yes, I know that this is a silly example







I have a update query that runs slow (see first query below). I have an index created on the table PhoneStatus and column PhoneID that is named IX_PhoneStatus_PhoneID. The Table PhoneStatus contains 20

million records. When I run the following query, the index is not used and a Clustered Index Scan is used and in-turn the update runs slow.
UPDATE PhoneStatus  
SET RecordEndDate = GETDATE()  
WHERE PhoneID = 126
 If I execute the following query, which includes the new FROM, I still have the same problem with the index not used.
UPDATE PhoneStatus  
SET RecordEndDate = GETDATE()
FROM Cust_Profile.PhoneStatus  
WHERE PhoneID = 126
 But if I add the HINT to force the use of the index on the FROM it works correctly, and an Index Seek is used.
UPDATE PhoneStatus  
SET RecordEndDate = GETDATE()
FROM Cust_Profile.PhoneStatus WITH(INDEX(IX_PhoneStatus_PhoneID))
WHERE PhoneID = 126  
 Does anyone know why the first query would not use the Index?

Update

In the table of 20 million records, each phoneID could show up 10 times at the most





PATINDEX :use wildcards,where as CHARINDEX cannot.
--CHARINDEX cannot be used with text, ntext, and image data types.

--------------------------------------------------------------------------------

Subqueries

Subqueries can be self-contained—

namely, independent of the outer query;

or they can be correlated—namely, having a reference to a column from the table in the outer query.

 Interms of the result of the subquery, it can be scalar, multi-valued, or table-valued.

If you want, you can highlight the inner query and run it independently. This makes the troubleshooting of problems with self-contained subqueries easier compared to correlated subqueries.

Note that if what’s supposed to be a scalar subquery returns in practice more than one value, the code fails at run time. If the scalar subquery returns an empty set, it is converted to a NULL.

Correlated Subqueries

Correlated subqueries are subqueries where the inner query has a reference to a column from

the table in the outer query. They are trickier to work with compared to self-contained subqueries

because you can’t just highlight the inner portion and run it independently.

SELECT categoryid, productid, productname, unitprice FROM Production.Products AS P1 WHERE unitprice = (SELECT MIN(unitprice) FROM Production.Products   AS P2 WHERE P2.categoryid =  P1.categoryid);

Table Expressions

Table expressions are named queries. You write an inner query that returns a relational result

set, name it, and query it from an outer query. T-SQL supports four forms of table expressions:

■■ Derived tables

■■ Common table expressions (CTEs)

■■ Views

■■ Inline table-valued functions

The first two are visible only to the statement that defines them. As for the last two, you

preserve the definition of the table expression in the database as an object; therefore, it’s

reusable, and you can also control access to the object with permissions.

Note that because a table expression is supposed to represent a relation, the inner query

defining it needs to be relational. This means that all columns returned by the inner query

must have names (use aliases if the column is a result of an expression), and all column

names must be unique. Also, the inner query is not allowed to have an ORDER BY clause.

(Remember, a set has no order.) There’s an exception to the last rule: If you use the TOP or

OFFSET-FETCH option in the inner query, the ORDER BY serves a meaning that is not related

to presentation ordering; rather, it’s part of the filter’s specification.

=====================================================================================================web Links==================================

http://www.interviewquestionspdf.com/2014/07/complex-joins-sql-joins-queries.html


http://saurabhsinhainblogs.blogspot.in/2013/04/sql-profiler-vs-server-side-trace.html

http://colleenmorrow.com/2011/08/22/index-maintenance-rebuild-vs-reorg/


http://saurabhsinhainblogs.blogspot.in/search?q=Difference+


https://social.msdn.microsoft.com/Forums/sqlserver/en-US/15ff45d9-80ed-4822-9724-bec89e5970e8/how-to-handle-bad-rows-in-ssis?forum=sqlintegrationservices