Local Variables vs. Parameterized Stored Procedures in SQL Server

Kendra (from Brent Ozar) has a nice video that explains why when testing a parametrized stored procedure’s script by replacing the parameters with local variables_ for example:

The SP:

ALTER PROCEDURE dbo.parameterizeMe

       @CustomerNo int

AS

       SELECT COUNT(*) from dbo.Customers Where CustomerNo=@CustomerNo

GO

exec dbo.parameterizeMe 0

Testing it with:

DECLARE @CustomerNo int=0;

SELECT COUNT(*) from dbo.Customers Where CustomerNo=@CustomerNo

Why when doing so, sometimes the execution plans differs and the performance becomes worse? …

She said that the reason is that because the local variable does not behave as the parameter in the stored procedure does à that is because in the case of the stored procedure, the Query Optimizer estimated the number of rows much much more accurately than the execution plan of query with the local variable à and that is because in the case of stored procedure parameter, the Query Optimizer sniffs the parameter that went in and looked at how many rows actually has the @CustmerNo of value 5, however in the case of the local variable, the Query Optimizer estimates the number of rows that it thinks it will process by: multiplying the overall density of the CustomerNo column by the number of rows in the table, that _most of the times_ gets a number far from the actual number of rows the variable @CustomerNo  of value 5 has in the table, which makes the Query Optimizer generates a wrong execution plan… by the way you can get those statistics using something like:

DBCC SHOW_STATISTICS('Customers','KI_Customers_CustomerNo')

So, and to test the stored procedure using the same execution plan it will use in production, you can create a temporary stored procedure! … as the following:

CREATE PROCEDURE #parameterizeMe

       @CustomerNo int

AS

       SELECT COUNT(*) from dbo.Customers Where CustomerNo=@CustomerNo

GO

Watch this for a better way to explain the subject:Local Variables vs. Parameterized Stored Procedures in SQL Server

SQL Server Table Partitioning without Enterprise Edition

The idea is to create a table for each group of rows from your original big table (based on period, for example), then union them all in a view. You can use that view in any DML operation... this is explained in:

• SQL Server Partitioning without Enterprise Edition by Barry King
• or Using Partitioned Views in Books Online

Statistics About Queries Ran On SQL Server Since Last Restart

SELECT

       SUBSTRING(dest.TEXT,

                 (deqs.statement_start_offset/2)+1,(

CASE deqs.statement_end_offset WHEN -1 THEN

                        DATALENGTH(dest.TEXT)

                  ELSE deqs.statement_end_offset

                  END - deqs.statement_start_offset)/2) + 1)

AS statement_text, 

deqs.last_execution_time ,deqs.execution_count,deqs.last_worker_time as [Last CPU time],

deqs.last_physical_reads , deqs.last_logical_reads,deqs.last_logical_writes,

deqs.last_elapsed_time

   FROM sys.dm_exec_query_stats AS deqs

            CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

Note:
■ Logical Reads — Represents the number of pages read from the data cache.
■ Physical Reads — If the required page is not in cache, it will be read from disk.
■ Elapsed Time  — CPU time + waits

Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

In Management Studio, Tools -> Options...-> Desigers tab, change "Transaction time-out after" to the number of second that you want. In the following example I've modify it to 3 minutes:

Error: Computed column 'X' in table 'Y' cannot be persisted because the column is non-deterministic.

This error could appear when you try to add a persisted computed column to a table, for example:

or

Percentage AS [dbo].[fn_getPercentage](Total) PERSISTED

you may get an error like:

Computed column 'Actor' in table 'Tmp_OrgData' cannot be persisted because the column is non-deterministic.

The reason is that the function that fills this column is not flagged to be deterministic (a deterministic function is the function that returns the same value every time it is executed with the same parameters values; so GETDATE() is non-deterministic function because it returns different value (date/time) every time we run it). And to mark a function as deterministic you have to add WITH SCHEMABINDING between RETURNS... and AS BEGIN... in the function definition. For example:

CREATE FUNCTION fn_getPercentage( @Total INT)

    RETURNS INT

WITH SCHEMABINDING  

AS

BEGIN

    RETURN SELECT @Total/countOfSomething FROM tblOrgData

END

Note: you cannot make a persisted column out of a recursive function.

A loop involving the member with the key [no.], was detected in the parent-child relationship between the attribute [child_id] and the attribute [parent_id].

As the error implies there is a loop in the parent-child hierarchy in one of the dimensions you are trying to process. For example if you have an Employee dimension and there is a parent-child relationship between employee_id and supervisor_id attributes, having this error means that you have in your source table _for example_ Ali as a supervisor for John but in another record John is ALSO the supervisor of Ali!.

To find such loop run the following query on your source table:

select * from      tableName tl1

         left join tableName tl2

         on tl1.child_id = tl2.parent_id

where tl1.parent_id = tl2.child_id

Fix Orphan Users After Backup / Restore Databases Across Servers

Sometimes when backup database from one server and restore it to another server for the purposes like migrating or moving database from production server to testing or vice versa, you probably face a problem that a login can't access that database even if you find that login in the Security/ Users folder of the database. This is because when you restore a database to another server the users became unmapped to any login, and you will find nothing in the "Login name" in the user property:

instead of something like:

Fixing this is simple you need to run:

EXEC sp_change_users_login 'Auto_Fix', [user]

for example and to fix the case in the above figures:

EXEC sp_change_users_login 'Auto_Fix', 'privuser'

Also the following is another way to fix it:

ALTER USER [theUsername] WITH LOGIN [theUsername] 

"Property IsLocked is not available for Login 'sa'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. " error fix

ALTER LOGIN [sa] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER LOGIN [sa] ENABLE

If Exists Update Else Insert

http://blogs.msdn.com/b/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx

Change Collation for all objects in a database

Declare

    @NewCollation varchar(255), @DBName sysname

Select  @NewCollation = 'SQL_Latin1_General_CP1_CI_AS', -- change this to the collation that you need

        @DBName = DB_NAME()

Declare

    @CName varchar(255), @TbleName sysname, @objOwner sysname, @Sql varchar(8000), @Size int, @Status tinyint, @Colorder int

Declare CurWhileLoop cursor read_only forward_only local

for Select

       QUOTENAME(C.Name)

      ,T.Name

      ,QUOTENAME(U.Name) + '.' +QUOTENAME(O.Name)

      ,C.Prec

      ,C.isnullable

      ,C.colorder

    From syscolumns C

      inner join systypes T on C.xtype=T.xtype

      inner join sysobjects O on C.ID=O.ID

      inner join sysusers u on O.uid = u.uid

    where T.Name in ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')

      and O.xtype in ('U')

      and C.collation != @NewCollation

    and objectProperty(O.ID, 'ismsshipped')=0

    order by 3, 1

open CurWhileLoop

SET XACT_ABORT ON

begin tran

fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder

while @@FETCH_STATUS =0

begin

  set @Sql='ALTER TABLE '+@objOwner+' ALTER COLUMN '+@CName+' '+@TbleName+ isnull ('('

+convert(varchar,@Size)+')', '') +' COLLATE '+ @NewCollation

+' '+case when @Status=1 then 'NULL' else 'NOT NULL' end

  exec(@Sql) -- change this to print if you need only the script, not the action

  fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder

end

close CurWhileLoop

deallocate CurWhileLoop

commit tran

Cannot process the object "EXEC [DB_Name].dbo.SP_Name". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

If you are trying to run a stored procedure using OPENROWSET, for example:

SELECT *

   FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','EXEC [DB_Name].dbo.SP_Name')

and you got the following error:

Cannot process the object "EXEC [DB_Name].dbo.SP_Name". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

to solve this, just add SET FMTONLY OFF as the following:

SELECT *

   FROM OPENROWSET('SQLNCLI','Server=(LOCAL);Trusted_Connection=Yes;Database=DB_Name','SET FMTONLY OFF
EXEC [DB_Name].dbo.SP_Name')

Convert number to text with fixed number of characters

If you have a field contains numbers and you want to read it as text with a fixed number of characters, for example: 1 to be 01 and 13 to keep it 13, you can do something like:

select right ('00'+ltrim(str(field)),2 )

Joining Tables With Different Collation

If you have two tables from two different databases each has a collation different than the other and you tried to join the two tables in one query...like

SELECT * FROM

      DB1..table1 LEFT JOIN DB2..table2

      on table1.id = table2.id

where the collation for DB1 is SQL_Latin1_General_CP1_CI_AS, and for DB2: Latin1_General_BIN
you will get the following error:

Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

to solve this in an easy way just modify it as:

SELECT * FROM

      DB1..table1 LEFT JOIN DB2..table2

      on table1.id = table2.id COLLATE SQL_Latin1_General_CP1_CI_AS

or you can set it to the default database collation, like:

SELECT * FROM

      DB1..table1 LEFT JOIN DB2..table2

      on table1.id = table2.id COLLATE database_default

There is another way in a blog, in a post titled "How to create SQL Server temp tables without collation problems", but i see this one easier, more practical, and faster.

Removing Duplicated Rows From a Table

To remove the repatriation and duplication of rows from a table follow the steps in the following example:

let's say that we have the following "orgTBL" table:

id name age address

1 Victor 29 LA

1 Victor 29 Dubai

2 Ali 23 London

2 Ali 23 Abu Dhabi

3 Lewis 29 Paris

1 Victor 29 Jerusalem

create a temporary table with the same structure as the orgTBL and name it tempTBL (for example):

select *

into #tempTBL

from orgTBL

where 1=0

fill the temporary table #tempTBL with a distinct selection of the key column or columns, in our example the id, name, and age, and null or empty values in the other columns, in our example the address column: 

insert into #tempTBL select distinct id,name,age,'' from orgTBL

now if you query #tempTBL you will get:

id name age address

1 Victor 29

2 Ali 23

3 Lewis 29

fill the rest of the columns (non-key column) in #tempTBL form ANY row in OrgTBL that has the same key columns values as the updated row in #tempTBL:

update #tempTBL set address =(select top 1 address from OrgTBL  where #tempTBL.id=OrgTBL.id and #tempTBL.name=OrgTBL.name and #tempTBL.age=OrgTBL.age )

empty OrgTBL:

Truncate table OrgTBL

refill OrgTBL from #tempTBL

insert into OrgTBL select * from #tempTBL

remove the temporary table:

Drop table #tempTBL

now if you query OrgTBL you will get:

id name age address

1 Victor 29 LA

2 Ali 23 London

3 Lewis 29 Paris