FileStream

The is is test

What is an instance of SQL Server

On a given server we can run multiple SQL Server services, each with their own ports, logins, and databases. Each of these services is called an "instance" of SQL Server. You can further categorize these as the "primary instance" and "named instances." The primary instance can be accessed using just the server name or IP address. The named instances, on the other hand, are accessed by appending a backslash and the instance name. For instance, to connect to the instance called "MyDB" on the local server, use: "127.0.0.1\MyDB". In SQL Server 2005, we can have up to 50 instances running simultaneously on a server.

During the query execution it is always the best practice to consider how many logical reads are occuring. This is one of most useful data to look into while performance tuning. Below is the useful information regarding logical and physical reads (source: sql-server-performance.com)

Logical Reads: This is the most useful piece of data provided by the SET STATISTICS IO or the SET STATISTICS TIME commands. As you probably know, before SQL Server can do anything with data, that data must be in its data cache. In addition, you probably know that when SQL Server reads data from the data cache, it reads it in 8K pages.So what is a logical read? A logical read is the number of pages SQL Server had to read from the data cache in order to produce the results specified by your query. SQL Server will never read any data pages more or less than it needs when executing a query. Because of this, the exact same number of logical reads will always occur when the same query runs against the exact same data on SQL Server.Why is knowing the number of logical reads SQL Server takes to execute a query important to your query tuning? Because it is the one thing that never changes from one execution of a query to another execution of the same query. And because of this, it is the perfect gauge to use when you are query tuning to determine if you are succeeding or failing.
As you tune a query, if the number of logical reads goes down, then you know that your query is using less server resources and will result in increased performance. And if the number of logical reads goes up, then whatever you are doing to “tune” your query is hurting the performance of your query. The fewer the logical reads performed by a query, the more efficient it is, and the faster it will perform, assuming all other things are held equal.

Physical Reads: What I am about to tell you here may be a little confusing, but it should eventually make sense once you think about it. First of all, a physical read refers to when SQL Server must go to disk to get data pages it needs to put into the data cache before it can execute the query. As I mentioned above, all data must be in the data cache before SQL Server can execute a query. Whenever SQL Server begins to execute a query, the first thing it does is to check and see if the data pages it needs are in the data cache. If they are, great. But if they are not, then SQL Server must go to disk and then move the pages it needs from disk into the data cache.As you can imagine, it takes a lot more server resources for SQL Server to perform a physical read than a logical read. Ideally, for best performance, we should do our best to avoid physical reads when we can.Now here is the part that is confusing. You should ignore physical reads when performance tuning your queries, instead focusing only on logical reads. How can this be, I just said that physical reads were much more resource intensive than logical reads?
This is a true statement, but the number of physical reads SQL Server uses to execute a query cannot be reduced by query tuning. Reducing physical reads is also an important task for the DBA, but it is a focus on whole server tuning, not query tuning. When you tune queries, you have no control over the size of the data cache or how busy the server is, or whether or not the data your query needs is in the data cache or on disk. The only thing you can directly control through query tuning is the number of logical reads that are required to produce the results you need.
Because of this, you can safely ignore the Physical Read data provided by the SET STATISTICS IO command. (Side Note: One way to help reduce physicals reads, and speed up your SQL Server, is to ensure your server has plenty of physical RAM.)

Consider Logical reads

Whenever we are tuning a query, it is important to look how many logical reads are happening during the query execution. Below is a informative descritption regarding logical reads.

Logical Reads: This is the most useful piece of data provided by the SET STATISTICS IO or the SET STATISTICS TIME commands. As you probably know, before SQL Server can do anything with data, that data must be in its data cache. In addition, you probably know that when SQL Server reads data from the data cache, it reads it in 8K pages.So what is a logical read? A logical read is the number of pages SQL Server had to read from the data cache in order to produce the results specified by your query. SQL Server will never read any data pages more or less than it needs when executing a query. Because of this, the exact same number of logical reads will always occur when the same query runs against the exact same data on SQL Server.Why is knowing the number of logical reads SQL Server takes to execute a query important to your query tuning? Because it is the one thing that never changes from one execution of a query to another execution of the same query. And because of this, it is the perfect gauge to use when you are query tuning to determine if you are succeeding or failing.
As you tune a query, if the number of logical reads goes down, then you know that your query is using less server resources and will result in increased performance. And if the number of logical reads goes up, then whatever you are doing to “tune” your query is hurting the performance of your query. The fewer the logical reads performed by a query, the more efficient it is, and the faster it will perform, assuming all other things are held equal.

Physical Reads: What I am about to tell you here may be a little confusing, but it should eventually make sense once you think about it. First of all, a physical read refers to when SQL Server must go to disk to get data pages it needs to put into the data cache before it can execute the query. As I mentioned above, all data must be in the data cache before SQL Server can execute a query. Whenever SQL Server begins to execute a query, the first thing it does is to check and see if the data pages it needs are in the data cache. If they are, great. But if they are not, then SQL Server must go to disk and then move the pages it needs from disk into the data cache.As you can imagine, it takes a lot more server resources for SQL Server to perform a physical read than a logical read. Ideally, for best performance, we should do our best to avoid physical reads when we can.Now here is the part that is confusing. You should ignore physical reads when performance tuning your queries, instead focusing only on logical reads. How can this be, I just said that physical reads were much more resource intensive than logical reads?
This is a true statement, but the number of physical reads SQL Server uses to execute a query cannot be reduced by query tuning. Reducing physical reads is also an important task for the DBA, but it is a focus on whole server tuning, not query tuning. When you tune queries, you have no control over the size of the data cache or how busy the server is, or whether or not the data your query needs is in the data cache or on disk. The only thing you can directly control through query tuning is the number of logical reads that are required to produce the results you need.
Because of this, you can safely ignore the Physical Read data provided by the SET STATISTICS IO command. (Side Note: One way to help reduce physicals reads, and speed up your SQL Server, is to ensure your server has plenty of physical RAM.)

Parsing a String of elements

Adding another handy User defined function, this time you can parse a string of elements and store them in a table of your choice,and then start playing with the data :). Here is the function........

Function

CREATE FUNCTION ParseString (@String varchar(8000),@Delimiter char(1))
RETURNS @Return TABLE (col varchar(200))
AS
BEGIN
DECLARE @i int
,@StringSize int
,@StringList varchar(2000)

SELECT @i=1
SELECT @StringSize=LEN(@String)

WHILE @i <= @StringSize BEGIN IF (charindex(@Delimiter,substring(@String,@i,1)) = 0) BEGIN SELECT @StringList=ISNULL(@StringList,'')+substring(@String,@i,1) if (@i = @StringSize) BEGIN INSERT @Return SELECT @StringList END END ELSE BEGIN IF (charindex(@Delimiter,substring(@String,@i,1)) > 0)
INSERT @Return SELECT @StringList
SELECT @StringList=''
END

SELECT @i=@i+1

END

RETURN

END


Example usage:
The way you can use


Declare @Temp TABLE
(Names varchar(10))

INSERT @Temp (Names)

SELECT col as 'Names' FROM ParseString('Siva,Kumar,Deepak',',')
SELECT * FROM @Temp

Listing all database names along with tables in a server

The following stored proc is a result of one of my requirements for my current work. I hope this will be of some help to co programmers too.


CREATE PROC ListTablesRows
as
BEGIN
SET NOCOUNT ON
DECLARE @db_id int,@DB_Name varchar(200)
DECLARE @sqlstr nvarchar(500)
CREATE TABLE #ListInfo
(
DBName varchar(200) NULL,
TNAME varchar(500) NULL,
ROWS int
)
DECLARE FindTbls_cursor CURSOR FOR
SELECT dbid, DB_NAME(dbid) AS DB_NAME
FROM master..sysdatabases WHERE dbid not in (1,2,3,4)
ORDER BY dbid
OPEN FindTbls_cursor
FETCH NEXT FROM FindTbls_cursor
INTO @db_id, @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstr = N'INSERT #ListInfo (DBName,TNAME,ROWS)'
SET @sqlstr = @sqlstr+ N' SELECT '''+@DB_NAME+''' as [Database Name], so.NAME as [Table Name],si.Rows as Rows '
SET @sqlstr = @sqlstr+ N' FROM '+@DB_NAME+'..SYSOBJECTS so '
SET @sqlstr = @sqlstr+ N' INNER JOIN '+@DB_NAME+'..sysindexes si ON so.id=si.id AND si.indid < 2 WHERE type=''U'''
EXEC sp_executesql @sqlstr
FETCH NEXT FROM FindTbls_cursor
INTO @db_id, @DB_name
END
CLOSE FindTbls_cursor
DEALLOCATE FindTbls_cursor
SELECT DBName as 'DatabaseName',TNAME as 'Table',Rows FROM #ListInfo
Drop table #ListInfo
END

A handy SP that selects a tables data for a quick view

Some times we just want to have a look at the tables data. I developed a custom stored proc which selects the data without writing full t-sql query. You just need to specify the table name as the parameter. You can also see the no.of rows that you want from table by specifying the number of rows value beside the table name. If you want to use it , compile the below proc in master database.

CREATE Proc sp_qry(@SQLString1 NVARCHAR(500))
as
DECLARE @SQLString NVARCHAR(500),@NoRec int

IF charindex(',',@SQLString1) <> 0
BEGIN
SELECT @NoRec=substring(@SQLString1,charindex(',',@SQLString1)+1,len(@SQLString1))
END

IF @NoRec > 0
BEGIN
SELECT @SQLString1=substring(@SQLString1,1,charindex(',',@SQLString1)-1)
SElect @SQLString = N'SELECT TOP '+Cast(@NoRec as nvarchar)+N' * From ' + CHAR(13)
END
ELSE
BEGIN
SELECT @SQLString = N'SELECT * From ' + CHAR(13)
END
SElect @SQLString = @SQLString + @SQLString1 + CHAR(13)
EXEC sp_executesql @SQLString



Then go to Tools-->Customize in the quer analyser. Select your own shortcut key and mention the sp name sp_qry in the stored procedure column.

Ex: sp_qry 'authors' : Returns the resultset of entire tables data
Ex: sp_qry 'authors,3' : Returns only the three rows of the table


I am sure this will be of help to us in our daily encounters. Have fun

Altering a primary key column

Altering columns is something we do frequently while working on big databases. If we are altering columns to the same datatype, then operation is smooth. If the altering column happens to be a primary key, We will recieve the following error.

ALTER TABLE ALTER COLUMN failed because one or more objects access this column.

In such situation, we need to perform the following steps.

Drop the existing PRIMARY KEY on the column.

Alter the column

Reimpose the PRIMARY KEY on the column.

Sample Code:
In the below code block, Table Test has Primary Key imposed on TestID column with varchar(5). Now we need to alter the data length to 10. The following are the steps.


ALTER TABLE Test DROP Constraint PK_TestID
ALTER TABLE Test ALTER COLUMN TestID varchar(10) NOT NULL
ALTER TABLE Test ADD Constraint PK_TestID Primary key


Little tips that helps........:)