Archive

Archive for the ‘SQL’ Category

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

December 9, 2014 Leave a comment

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

Advertisements
Categories: SQL Tags: , ,

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

December 9, 2014 Leave a comment

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.

Categories: SQL Tags: , ,

Search text in SP and Function

January 7, 2014 Leave a comment

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%’

Categories: SQL

Update statment using Joins

September 24, 2013 Leave a comment

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

Categories: SQL

Table-Valued User-Defined Functions with Temp table

January 31, 2013 Leave a comment

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

January 30, 2013 Leave a comment

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?

December 4, 2012 Leave a comment

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;
%d bloggers like this: