ssis questions

1	What is the MaxConcurrentExecutables property on a Package level?	SQL Server Integration Services Pacakge level property that defines that how many tasks can run simultaneously(parallel).
		here -1 ,that means that It will be able to run Total Tasks=Number of processors+2.MaxConcurrentExecutables set to default value (-1)
		Setting MaxConcurrentExecutables property value to low number to share Resources
		Setting MaxConcurrentExecutables to higher value to run more Tasks in Parallel
2	What is the Engine Thread property of Data Flow Task?	The EngineThreads property is a property of each Data Flow task. ,The default is 5 in SQL Server 2005 and 10 in SQL Server 2008, with a minimum value of 2.
		One thing we want to be clear about EngineThreads is that it governs both source threads (for source components) and work threads (for transformation and destination components). Source threads and work threads are both engine threads created by the Data Flow’s scheduler
3	What are the Precedence Constraints in SSIS, and where and why have you used them?	Precedence constraint is used to direct the package execution flow. there are two options available to control the flow of package execution. They are Constraint and Expression.
		Constraint are predefined conditional check in the package that we can use to link between control flow tasks.
		Expressions are used to evaluate the user expressions to control flow of package execution.
		we can also use these two options with different combinations of logical condition such as AND and OR as given in the section “Multiple constraint”
4	What is the difference between the Success and the Completion value of Precedence Constraint?	A success constraint will only allow the process to continue to the following task if the preceding task completed successfully, whereas a completion constraint will allow the process to continue as soon as the preceding task has  completed, regardless of the outcome.
5	What is the DelayValidation property of Data Flow Task? Why does one use this property?	indicate wheather the validation of the exectuable delayed until runtime.
6	What is RetainSameConnection Property on Connection? Why is it used?	Once the RetainSameConnection is set to true then the connection will remain and will be used by the entires tasks group without having the necessity to recreate new connection for each task
7	If we create a temp table in SSIS Package and want to use it in other tasks, which properties do we need to use?	on the tasks and connection manager. The first property is a connection manager property. If you right click the OLEDB Connection Manager and choose properties you will see a property called RetainSameConnection. This must be set to "True". RetainSameConnection means that the temp table will not be deleted when the task is completed
		The second property we must change is the Execute SQL Task property. If you right click on each task (Create Temp Table and Query) and choose properties you will see a property called DelayValidation. This must be set to "True". DelayValidation means that the task will not check if the table exists upon creation.
8	What is data Viewer in SSIS? Is data viewer available in Control Flow or Data Flow?	Data viewers can be used any time we want to see the data that is actually in the pipeline;  they stop the package temporarily until we shut them.  Also remember that the Row Count shape can be used as a “Dead End”.  It is useful during development when we want to see the output from a dataflow, but don’t want to update a table or file with the data.  available in DATA flow
9	I am running my package for debugging and I do not want to load data into any destination. Which transformation can I use to ensure that the data goes nowhere else?
10	What is the difference between Checkpoint and Breakpoint in SSIS?	We will be using Break Point in SSIS Package to view variable values at run time.Here are the variable I have used in package. I have changed the values for VarArchiveFullPath and VarSourceFullPath variables.  --Expressions are written on these variable those should evaluate at run time and change the values of these variable.
		checkpoints, which lets you restart the package if it fails for any reason. During package execution, the last successfully completed task or container is noted in a checkpoint file, and the checkpoint file is removed if the package completes successfully. But if the package fails before completing, the checkpoint file remains available as a reference to the location from which to restart the package.
11	Will my package run successfully by using SQL Server Agent if I have data viewers and Breakpoint enabled?
12	What are different ways to execute your SSIS Package? Can I run a SSIS Package by using a Stored Procedure?	1. Run the package programmatically.Scripts are written using vb or C#.The main drawback is,the SSIS package should be installed in the same machine where the application runs.
		2.Execute SSIS Package using DTEXEC.EXE Command Line Utility.
		3.Execute SSIS Package using DTEXECUI.EXE Utility
		4. Executing SSIS Package using SQL Server Agent Job
	What is the variable value at runtime?	5 different ways to set values for variables
		To summarize, here are the values I set for the variable varCubeName using 5 different ways: 1.Cube Name in Value: in the Variable Editor in the Value field  2.Cube Name in Expression: in the Variable Editor in the Expression field  3.Cube Name in Package Configuration: in the package configuration file  4.Cube Name from Execute SQL Task: in the Execute SQL Task 5.Cube Name in Script Task: in a scrip task
		Conclusion on the precedence order  1.Cube Name in Expression: the value is set in the Variable Editor in the Expression field. The value set in this way has the highest precedence order and overwrites the values in all other ways. 2.Cube Name from Execute SQL Task: the value is set in the Execute SQL Task. It has the second highest precedence order. Cube Name in Script Task has the same precedence order. 3.Cube Name in Package Configuration: the value is set in the package configuration file. It can only overwrites the variable’s default value.  4.Cube Name in Value: the value is set in the Variable Editor in the Value field. This value is usually called the default value, because it can be overwritten by all the proceeding ways during runtime.
		•If variable expression is sufficient, use variable expression only. •If you need to combine different ways to set variable values at runtime, only use these two combinations. Default value +  Execute SQL Task, or Default value +  Script Task
	w many ways can you do INSERT-UPDATE?	I’ve always wanted to do a benchmark comparison so I can proudly say that my “normal” way is the best in terms of run time.  1.STAGE-TSQL: Use the data flow to bring the raw data into staging, and use do the INSERT-UPDATE in TSQL. This is my “normal” way. And the best way according to my testing. The name STAGE-TSQL implies: 1) two steps are involved, 2) raw data is staged first, 3) INSERT-UPDATE are done in TSQL only.  2.UPDATE-ALL: Only one step is involved. INSERT-UPDATE is done in one data flow step. 1) Transformation OLE DB Command is used for UPDATE, 2) Destination OLE DB Destination is used for INSERT.  3.UPDATE-STAGE: Two steps are involved. 1) INSERT is done in the data flow step, 2) but the matching rows are saved to a staging table, and UPDATE is done in TSQL using the matching rows.  4.UPDATE-DELTA: similar to number 2 UPDATE-ALL. In stead of directly sending all matching rows to transformation OLE DB Command for UPDATE, the Script Component transformation is used to determine if there are actually changed rows. Send data to Transformation OLE DB Command only if there are changes in the matching rows. ex1:trun_stg_TSQL,Stag_TSQL(DFT),Trg_TSQL ex2:trg_mychangingtable_upd_all(DFT)-(1oledb_Src,2lookup,3nomatch o/p oledb_Dest,4.match o/p oledbcmd) ex3:trun_upd_stg,trg_mychangingtable_upd_stg(DFT)(1oledb_Src,2lookup,3nomatch o/p oledb_Dest,4.match o/p oledbdest_Stg),trg_mychangingtable_upd ex4:(1oledb_Src,2lookup,3nomatch o/p oledb_Dest,4.match o/p>sc(script component transf(check any changed data)-> oledbcmd,else moved to rowsampling(ignore))
	Lookup	lookup is case senstive I’ve always chosen to use the Upper function for comparison and preserve the case for data for two good obvious reasons. 1.I like Full cache and  2.As a developer, how the servers are configured is totally out of my control Note (provided the SQL Server uses case-insensitive collation, which is the default setting of SQL Server).

Very Useful

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
    

Change data capture