Category Archives: SQL

Which SQL statement is faster? (EXISTS or IN …)


Based on Question asked in MSDN Forum

  1.  IN is efficient when most of the filter criteria is in the sub-query.
  2.  EXISTS is efficient when most of the filter criteria is in the main query.
  3.  Usually IN has the slowest performance.

For more you can refer Join vs Exists vs In (SQL)IN vs. JOIN vs. EXISTS

Which SQL statement is faster? (HAVING vs. WHERE…)


If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.

You can use HAVING but recommended you should use with GROUP BY.

SQL Standard says that WHERE restricts the result set before returning rows and HAVING restricts the result set after bringing all the rows. So WHERE is faster.

Search text in SP and Function


SELECT SOBJ.NAME, SYSC.TEXT
FROM SYS.SYSOBJECTS SOBJ
JOIN SYS.SYSCOMMENTS SYSC ON SOBJ.ID = SYSC.ID
WHERE SYSC.TEXT LIKE ‘%Text to search%’

Update statment using Joins


update t1
set t1.totalHrsWorked=cast(DATEDIFF(MINUTE , t2.startDate, t2.endDate) *0.0166667 AS decimal(10,2))
from Table1 t1
inner join Table2 t2
on t1.Id = t2.Id
where t1.Id between 1 and 100

Table-Valued User-Defined Functions with Temp table


Create FUNCTION GetEmployee()
RETURNS  @rtnTable TABLE
(
    — columns returned by the function
    ID UNIQUEIDENTIFIER NOT NULL,
    Name nvarchar(255) NOT NULL
)
AS
BEGIN
DECLARE @myTable table (id uniqueidentifier, name nvarchar(255))

insert into @myTable
select from your stuff

–This select returns data
insert into @rtnTable
SELECT ID, name FROM @myTable
return
END

you can’t access Temporary Tables from within a SQL Function. You will need to use table variables so essentially. After select the value to return it as table formate you have to insert the selected records  to ‘@rtnTable’ which returns in table structure.

The DELETE statement conflicted with the REFERENCE constraint in membership table


To delete a user from the database i used ‘Membership.DeleteUser(user)‘ while doing smoke test it is working in development environment but not in testing environment.

The DELETE statement conflicted with the REFERENCE constraint “FK__aspnet_Us__UserI__47489C52”. The conflict occurred in database “TestDB”, table “dbo.aspnet_UsersInRoles”, column ‘UserId’.

After analysis the ‘aspnet_Users_DeleteUser‘ procedure i came to know all the views for aspnet membership are missing

using ‘SELECT * FROM sysobjects where type = ‘V’

Refrence: Roles and Views in the Application Services Database for SQL Server

How to give a database access to specific users only?


SINGLE_USER allows any single user with permission to access the database.

ALTER DATABASE [Database Name] SET SINGLE_USER

ALTER DATABASE [Database Name] SET Multi_USER
RESTRICTED_USER is the one that limits access to sysadmin, dbowner, or dbcreator

ALTER DATABASE adventureworks
SET restricted_user WITH ROLLBACK IMMEDIATE;

Backup Time Information For a Database


select dbinfo.Name , COALESCE(CONVERT(VARCHAR(20),backuplog.backup_finish_date,100 ),”) as BackupTime
FROM sys.sysdatabases dbinfo
LEFT OUTER JOIN msdb.dbo.backupset backuplog ON backuplog.database_name = dbinfo.name
where dbinfo.Name=’DataBase Name’

Get Row counts of all tables in a database


SELECT sysobjects.name TableName,sysindexes.rows TotalNoRow

FROM sysobjects inner join sysindexes ON sysobjects.id = sysindexes.id

WHERE type = ‘U’AND sysindexes.IndId < 2

order by sysindexes.rows desc

Restore Database


Restore the logical file from the backup file

RESTORE FILELISTONLY
FROM DISK = ‘Backup file full path\filename.extension’

Restore the database in same path as in the backup database path and file name

Restore database DBName

to disk=’Backup file full path\filename.extension’

Restore the database with different logical name and path

RESTORE DATABASE DBName
FROM DISK = ‘Backup file full path\filename.extension’
WITH MOVE ‘YourMDFLogicalName’ TO ‘New Restore file full path\filename.extension’,
MOVE ‘YourLDFLogicalName’ TO ‘New Restore file full path\filename.extension’

Reference:

Pinal Dave (http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/)

Pinal Dave (http://blog.SQLAuthority.com)

%d bloggers like this: