Monday, 14 May 2012

What are the requirements of sub-queries ?



* A sub-query must be enclosed in the parenthesis.


* A sub-query must be put on the right hand of the 

comparison operator.


* A sub-query cannot contain an ORDER BY clause.


* A query can contain more than one sub-query.

Wednesday, 25 April 2012

When to use HAVING and WHERE in SQL SERVER?


When to use HAVING and WHERE in SQL SERVER?

HAVING and WHERE  are the clauses which are used to filter the data in a select quey.

Some differences between HAVING and WHERE clauses are:

  • HAVING is generally used along with the group by clause. WHERE clause is used independently.
  • Purpose of the HAVING clause is to work on the aggregate functions. WHERE clause can be used without aggregate functions also.
  • HAVING behaves similar to WHERE clause if there is now GROUP BY in the Query.




Tuesday, 27 March 2012

Issue Performing a SQL Server 2008 Differential Backup After a Canceled Full Backup


This issue was painful. It started off with a canceled release because the differential backup didn’t finish as expected. The idea was to perform a differential backup just before the release so I’d have a safety net in case something tragic happened. I had run a test diff a few hours earlier and it finished in 35 minutes. There wasn’t a lot of activity on the database before the release so I figured worst case the pre-release diff would take under 1.5 hours. Boy was I wrong!
After 2 hours the diff was showing only 7.5% complete and the file was 3x the size of my test diff from earlier. At that rate it wouldn’t be finished for over 25 hours, a full backup only takes 11! Frustrated that I had to cancel the release, I was determined to figure out why this happened.
Differential backups, backup all 64k extents that have changed since the last full backup. I was fairly confident that only a small amount of data had changed since the full backup. Running the script in Paul Randal’s post How Much of the database has changed since the last full backup?confirmed that only 0.33% of the extents had changed.

After exchanging sever tweets . I remembered that the regularly scheduled full backup job had started before the release and I had canceled it since I was going to be doing the diff. I didn’t think anything of it at the time, but that ended up being the issue.
I reenacted the scenario against a smaller 2GB test database. Here is how it played out.
§  Full backup – 2GB file in 40 seconds
§  Differential backup – 1MB file in 0 seconds
§  Full backup canceled after 20 seconds
§  Differential backup – 1GB file in 55 seconds
The output from the second diff was:
Processed 241608 pages for database test, file test_Data’ on file 1. 
Processed 1 pages for database ‘test’, file ‘test_Log’ on file 1.
 
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 241609 pages in 55.501 seconds (34.009 MB/sec).
Even after the second diff, Paul’s script said that only 4 out of 32000 extents had been modified. So, the canceled full backup does something that Paul’s script doesn’t detect, and SQL Server thinks all of the extents it touches have been modified.
This behavior doesn’t seem right to me. Ideally, the canceled full shouldn’t impact my ability of doing a diff, otherwise, it should return an error (or at least a warning) when I attempt a diff since the diff will be very large and may take longer than the full backup.
In case you were wondering, I did a test restore of the full backup and the 2nd diff and it worked fine.

Friday, 16 March 2012

Get blocking query on Sql server Database



I have  come across situations where I am troubleshooting for extended blocking. 
But unfortunately the queries causing the extended
 blocking finish execution before I can login to the server. 
 So I have written a script which will capture all the information that I need. 
 I save the output to a file via a job and I email the output to myself. 
 This way I have a record of the queries that cause extended blocking.


I have set up the following script in a job and have configured the job to save 
the output to a file. Following is a sample of the output which tells me w
hich query is causing blocking and which one is being blocked.










SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
set nocount on
DECLARE @RefCount varchar(100)
DECLARE @sql1 VARCHAR(4000),@sql2 VARCHAR(4000)
DECLARE @querynum varchar(100)
DECLARE @sqlcmd varchar(8000)
DECLARE @request_status varchar(50)
SET @querynum=1


DECLARE RefCursor CURSOR FOR


SELECT
distinct(l.request_SESSION_Id)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL))


OPEN RefCursor
FETCH NEXT FROM RefCursor INTO @RefCount
WHILE @@FETCH_STATUS = 0
BEGIN
Select 'Query #'+@querynum+'
run by spid : '+@RefCount
SET @request_status=(SELECT
distinct(l.request_status)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND ( l.resource_description = l1.resource_description OR
(l.resource_description IS NULL AND l1.resource_description IS NULL))
and l.request_session_id=@RefCount)
IF (@request_status='WAIT')
begin
     PRINT 'This is the SPID that is being blocked'
    end
else
begin
     PRINT 'This is the SPID that is causing the blocking'
     end
select ''
SET @sqlcmd='Select hostname from sysprocesses where spid='+@RefCount
EXEC (@sqlcmd)


SET @sqlcmd='Select loginame from sysprocesses where spid='+@RefCount
EXEC (@sqlcmd)
SET @querynum=@querynum+1
SET @sql2='DBCC INPUTBUFFER('+@RefCount+') with no_infomsgs'
SELECT 'Running DBCC INPUTBUFFER for SPID '+@RefCount
EXEC (@sql2)



SELECT '-----------------------------------------------------------------'


FETCH NEXT FROM RefCursor INTO @RefCount
END
CLOSE RefCursor
DEALLOCATE RefCursor
GO

importing images into a table.


 importing images into a   table.

 importing images to database table using a T-SQL.

I invested some time and found that OPENROWSET is the solution which is the best way to open any non sql data sources.

 T-SQL listing given below  helped me to resolve the issue. Note that image folder should be accessible from the SQL server. If the image folder is  in the remote location, either you have to copy to the server or map the folder in SSMS
--Target Table where data to be imported

CREATE TABLE userinfo
  (
     USER_ID      INT NOT NULL IDENTITY(1, 1),
     logonname    VARCHAR(200),
     profileimage VARBINARY(MAX)
  ) GO --Table to process the filesCREATE TABLE imagelist
  (
     imgfilename VARCHAR(200)
  ) GO --Put all file name in a table for easy processing
DECLARE @SQL AS NVARCHAR(2000) SET @SQL =N'xp_cmdshell ''dir d:\USerprofileImage /B''' INSERT INTO imagelist(imgfilename) EXEC sp_executesql @SQL
GO
--Import data into target table
DECLARE @SQL AS NVARCHAR(2000) DECLARE @ImgFilename AS VARCHAR(200) DECLARE filelist CURSOR FOR
  SELECT imgfilename
  FROM   imagelist OPEN filelist FETCH NEXT FROM filelist INTO @ImgFilename WHILE ( @@FETCH_STATUS = 0 )
  BEGIN
      SET @SQL = 'insert into  UserInfo(LogonName,ProfileImage)
select
reverse(substring(REVERSE('''+@ImgFilename +''') ,charindex(''.'',REVERSE('''+@ImgFilename+''' ),1)+1,LEN('''+@ImgFilename+'''))) ,
(SELECT img.bulkcolumn FROM OPENROWSET(BULK ''d:\USerprofileImage\'+@ImgFilename +''',SINGLE_BLOB) AS img)'
       EXEC sp_executesql @SQL
       FETCH next FROM FileList INTO @ImgFilename
  END
CLOSE filelist DEALLOCATE filelist
GO--Drop the worktable
DROP TABLE imagelist

Wednesday, 7 March 2012

The Anatomy of a SQL Statement



SQL statements always begin with a command (a word, or group of words, that describes what action the statement will initiate). The command can be called the verb of the SQL statement, as it always describes an action to be taken. Statements typically contain one or more clauses, which are formal modifiers that further describe the function of the SQL statement.

Table 3-2. Fundamental PostgreSQL commands
CommandDescription
CREATE DATABASECreates a new database
CREATE INDEXCreates a new index on a table column
CREATE SEQUENCECreates a new sequence in an existing database
CREATE TABLECreates a new table in an existing database
CREATE TRIGGERCreates a new trigger definition
CREATE VIEWCreates a new view on an existing table
SELECTRetrieves records from a table
INSERTAdds one or more new records into a table
UPDATEModifies the data in existing table records
DELETERemoves existing records from a table
DROP DATABASEDestroys an existing database
DROP INDEXRemoves a column index from an existing table
DROP SEQUENCEDestroys an existing sequence generator
DROP TABLEDestroys an existing table
DROP TRIGGERDestroys an existing trigger definition
DROP VIEWDestroys an existing table view
CREATE USERAdds a new PostgreSQL user account to the system
ALTER USERModifies an existing PostgreSQL user account
DROP USERRemoves an existing PostgreSQL user account
GRANTGrant rights on a database object to a user
REVOKEDeny rights on a database object from a user
CREATE FUNCTIONCreates a new SQL function within a database
CREATE LANGUAGECreates a new language definition within a database
CREATE OPERATORCreates a new SQL operator within a database
CREATE TYPECreates a new SQL data type within a database

Wednesday, 29 February 2012

SQL basic commands Syntax ?


SQL basic commands Syntax ?
SQL have 4 main commands

Select Command : Select [columns Name | * (for all column)] from [Table name] ;

Insert Command : Insert into [Table name](columns name) values (columns value)

Update Command : Update [Table Name] Set [column_name]= [Column_value]