Feeds:
Posts
Comments

Declare @FromDate Date = '2009/01/01',
		@ToDate Date = '2012/02/15'

	;WITH cteDate as
	(
		SELECT 
			DATEADD(QQ,DATEDIFF(QQ,0,@ToDate),0) MaxDate
	)
	, cteDerived as 
	(	SELECT
			1 AS RID, 
			DATEADD(QQ,DATEDIFF(QQ,0,@FromDate),0) MinDate
		UNION ALL 
		SELECT
			RID + 1,
			DATEADD(QQ,1,MinDate)
		FROM
			cteDerived
		WHERE
			DATEADD(QQ,1,MinDate) <= (Select MaxDate From cteDate)
	)
	Select 
		'Q' + CAST(RID as Varchar(3)) as [Quarter], 
		MinDate as FromDate, 
		DATEADD(QQ,1,MinDate)-1 as ToDate 
	FROM 
		cteDerived
	OPTION (MAXRECURSION 0) 

Advertisements

Below script is tested for SQL 2005/2008 R2

SELECT  
	Tab.[name] AS TableName,
	Ind.[name] AS IndexName,
          Ind.type_desc,
	SUBSTRING(( SELECT  ', ' + AC.name
				FROM    
					sys.[tables] AS T 
					INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
					INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
					AND I.[index_id] = IC.[index_id]
					INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
					AND IC.[column_id] = AC.[column_id]
				WHERE   
					Ind.[object_id] = I.[object_id]
					AND Ind.index_id = I.index_id
					AND IC.is_included_column = 0
				ORDER BY 
					IC.key_ordinal
				FOR 
				XML PATH('')
				), 2, 8000) AS KeyCols,
	SUBSTRING(( SELECT  ', ' + AC.name
				 FROM    
					sys.[tables] AS T
					INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
					INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
					AND I.[index_id] = IC.[index_id]
					INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
					AND IC.[column_id] = AC.[column_id]
				 WHERE   
					Ind.[object_id] = I.[object_id]
					AND Ind.index_id = I.index_id
					AND IC.is_included_column = 1
				 ORDER BY 
					IC.key_ordinal
				 FOR
				 XML PATH('')
				 ), 2, 8000) AS IncludeCols
 FROM    
	sys.[indexes] Ind
	INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
WHERE 
	Tab.name ='Tablename' --Comment where clause to get all indexes for particular database
ORDER BY 
	TableName

Happy learning..

The SET is executing in the EXEC-created scope which does not affect the environment calling EXECUTE.

Try the following:

CREATE TABLE Patient 
	(
		PatientID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
		DemographicInfo VARCHAR(Max)
	);
GO

INSERT INTO Patient Values ('Albert 42 Male'),('David 48 Male'),('Sonia 41 Female')

SELECT * FROM Patient

DELETE FROM Patient WHERE PatientID = 2

INSERT INTO Patient (PatientID, DemographicInfo) values (2, 'NEW Demographic Info'); 

It will not allow you to insert PatientID as it’s Identity_Insert is off and throw below exception

“Cannot insert explicit value for identity column in table ‘Patient’ when IDENTITY_INSERT is set to OFF.”

So you need to run below statement first

EXEC ('SET IDENTITY_INSERT Patient ON');
INSERT INTO Patient (PatientID, DemographicInfo) values (2, 'NEW Demographic Info'); 

But again it will throw same exception because each dynamic query has its own scope. So you need to execute all statements together.

exec ('SET IDENTITY_INSERT Patient ON; INSERT INTO Patient (PatientID, DemographicInfo) values (2, 'NEW Demographic Info');'); 
Go - This will work

Cheers,

Follow the below steps in SSMS

Tools –> Options –> Environment –> Keyboard –> Query shortcut

Ctrl+F1 - select top 5 * from
ctrl+2  - Select count(1) from

Open the new query window and just type the name of table, select it and press the shortcut given above. The result is displayed.

Besides this below are the default shortcut given by SSMS.

Alt+F1  sp_help -- Select table name/Object name and hit shortcut key. It will give result of the table structure alongwith datatype and datalength
Ctrl+1   sp_who -- This shortcut will show you status of connection for each database and details of user details i.e. hostname, dbname, login name etc.

You can set any shortcut provided in the query shortcut window for any sql statement.

You can also define shortcuts in BIDS. i.e. to resize and allign the ControlFlow/ DataFlow items.

RAID

RAID stands for Redundant Array of Inexpensive (or Independent) Disks. It is a collection of disk drives working together to optimize fault tolerance and performance. There are various RAID levels, but only the RAID levels significant to SQL Server are described here.

RAID0 (simple striping): Simplest configuration of disks that stripe the data. RAID0 does not provide any redundancy or fault tolerance. Data striping refers to sequentially writing data in a round-robin style up to a certain stripe size, a multiple of a disk sector (usually 512 bytes). Data striping yields good performance because multiple disks are concurrently servicing the I/O requests. The positive points for RAID0 are the cost, performance, and storage efficiency. The negative impact of no redundancy can outweigh its positive points.

RAID1 (simple mirroring): This configuration creates an exact copy or mirror of all the data on two or more disks. This RAID level gives good redundancy and fault tolerance, but poor storage efficiency. To fully take advantage RAID1 redundancy, it is recommended to use independent disk controllers (referred to as duplexing or splitting). Duplexing or splitting removes single-point failures and allows multiple redundant paths.

RAID5 (striping with parity): RAID5 uses block-level striping where parity is distributed among the disks. RAID5 is the most popular RAID level used in the industry. This RAID level gives fault tolerance and storage efficiency. However, RAID5 gives a larger negative impact for all write operations, especially sequential writes.

RAID10 (stripe of mirrors): RAID10 is essentially many sets of RAID1 or mirrored drives in a RAID0 configuration. This configuration combines the best attributes of striping and mirroring: high performance and good fault tolerance. For these reasons, we recommend using this RAID level. However, the high performance and reliability level is the trade-off for storage capacity.

Note that in all levels of RAID configuration, the storage efficiency is always limited to a multiple of the smallest drive size.

Summarized I/O Activity of RAID Levels and Their Recommended Use

RAID Levels

RAID0

RAID1

RAID5

RAID10

Reliability

Lowest.

Lack of fault tolerance results in data loss.

Very good.

Even better with duplexing.

Good.

Can tolerate single machine fault.

Excellent.

Storage Efficiency

100%

50%

>50%, <100%

(#drives -1/#drives)

50%

Random Read

Excellent

Fair

Worst of the RAID levels but better than a single drive.

Excellent.

Excellent.

Random Write

Excellent.

Fair.

Worse than a single drive but better than some RAID levels.

Fair.

Generally better with larger stripe sizes.

Very good.

Sequential Read

Excellent.

Fair.

Comparable to a single drive.

Very good.

Generally, better with smaller stripe sizes

Excellent.

Sequential Write

Excellent.

Good.

Better than other RAID levels.

Fair.

Very good.

Cost

Lowest.

Moderate.

Relatively high cost due to redundant drives; however, no expensive controller required

Moderate.

High.

Recommended use

Good for non-critical data or stagnantly updated data that gets backed up regularly or any data requiring fast write performance at very low cost. Great for testing.

Good for data that requires high fault tolerance at relatively low hardware cost (redundancy using parity requires more expensive hardware). Best for log files.

Very good for Read only data.

Data requiring high performance for both read and write and excellent reliability while trading off storage efficiency and cost.

Reference : http://technet.microsoft.com/en-us/library/cc966414.aspx#_RAID

Happy learning!

Here, take a look at Itzik Ben-Gan’s flow chart of Logical Query Processing. It is also explained in “Inside Microsoft Sql server 2008 t-sql Querying” book.

Logical Query Processing

Logical Query Processing Phases

Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN described here by Pinal.

Microsoft SQL Server Query Processor Internals and Architecture

A very useful article on Microsoft SQL Server Query Processor Internals and Architecture on MSDN site. Know how SQL Server processes your queries, and how and when it compiles them or re-compiles them, will allow you to write better applications.

A great post on Horizontal partitioning in SQL 2008 by Vishal. This is step by step instruction to create partition in SQL.

Difference Between Vertical Partition and Horizontal Partition

Vertical partitioning
• Case: Large Patient table. Lots of columns, including picture.
• You could: Have a table for critical columns.
• Leave all the less used and BLOB columns in a second table.

Horizontal partitioning
• Case: Sales table for last few years.
• Problem: Weekly full backup of 1 billion rows.
• Problem: INSERTs blocked by queries on historical data.
• You could: Create multiple tables with partition views (PV).
• Uses constraints to implement the partitioning rules.
• You could: Create partitioned tables (PT) with read-only file groups.
• Uses a partition function and a partition scheme.
• Saves in management, backup. Could have different SLAs per year.

For more on partitioning Click here.