welcome Netizen

Share Your Knowledge.It is a way to achieve immortality

Wednesday, November 1, 2017

How to use Table-Valued Parameters in sql?

Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.


  • Do not acquire locks for the initial population of data from a client.
  • Provide a simple programming model.
  • Enable you to include complex business logic in a single routine.
  • Reduce round trips to the server.
  • Can have a table structure of different cardinality.
  • Are strongly typed.
  • Enable the client to specify sort order and unique keys.
  • Are cached like a temp table when used in a stored procedure. Starting with SQL Server 2012, table-valued parameters are also cached for parameterized queries.

  • SQL Server does not maintain statistics on columns of table-valued parameters.
  • Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
  • You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.
  •  Compared to bulk operations that have a greater startup cost than table-valued parameters, table-valued parameters perform well for inserting less than 1000 rows.

/* Create a table type. */ CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); GO /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation @TVP LocationTableType READONLY AS SET NOCOUNT ON INSERT INTO AdventureWorks2012.Production.Location (Name ,CostRate ,Availability ,ModifiedDate) SELECT *, 0, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType; /* Add data to the table variable. */ INSERT INTO @LocationTVP (LocationName, CostRate) SELECT Name, 0.00 FROM AdventureWorks2012.Person.StateProvince; /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO

Friday, September 29, 2017

How to improve SQL Performance when searching by date

If you have a datetime column in a WHERE clause, and you need to convert it or use a data function, try to push the function to the literal expression.

For the below two query , the First one take more query cost rather than the Second one,

SELECT OrderID FROM dbo.Orders WHERE DATEADD(day, 15, 
OrderDate) = '07/23/1996'

SELECT OrderID FROM Orders WHERE OrderDate = DATEADD(day, 

-15, '07/23/1996')

In below Figure you can check the query cost

How to create Dynamic Pivot Table in SQL?

Here below code, which describes to create Dynamic Pivot view from the Table.

Create table yourtable (itemID INT, part CHAR(1))

INSERT INTO yourtable VALUES(1,'A'),(1,'B'),(2,'A'),(2,'A'),(2,'A'),(3,'C')

DECLARE @colsSorted AS NVARCHAR(2000), @sql AS NVARCHAR(4000)

select @colsSorted = STUFF((select DISTINCT ', '
+ quotename( Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) ,']')
FROM yourtable
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @colsSorted

Set @sql=N' if object_id(''anewtable'',''U'') is not null drop table anewtable ; with mycte as (SELECT ItemID, '+ @colsSorted + ' FROM (
Select ItemID,Part, Cast(ROW_NUMBER() OVER(PARTITION BY itemID ORDER BY part) as varchar(3)) as Cols

FROM yourtable
) src
PIVOT (Max(part) for Cols IN ('+ @colsSorted +')) pvt )
Select *   into aNewtable
from mycte;'
 --print @sql
 exec sp_executesql @sql;

 select * from aNewtable

 select * from yourtable

drop table yourtable

Happy coding!!!!!!!!!!!!!!!!!!