Sql Server Materials

 USE [master] GO IF OBJECT_ID('dbo.sp_hexadecimal') IS NOT NULL     DROP PROCEDURE dbo.sp_hexadecimal GO CREATE PROCEDURE dbo.sp_hexadecimal     @binvalue [varbinary](256)     ,@hexvalue [nvarchar] (514) OUTPUT AS BEGIN     DECLARE @i [smallint]     DECLARE @length [smallint]     DECLARE @hexstring [nchar](16)     SELECT @hexvalue = N'0x'     SELECT @i = 1     SELECT @length = DATALENGTH(@binvalue)     SELECT @hexstring = N'0123456789ABCDEF'     WHILE (@i < =  @length)     BEGIN         DECLARE @tempint   [smallint]         DECLARE @firstint  [smallint]         DECLARE @secondint [smallint]         SELECT @tempint = CONVERT([smallint], SUBSTRING(@binvalue, @i, 1))         SELECT @firstint = FLOOR(@tempint / 16)         SELE...

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[fn_hexadecimal]  (     -- Add the parameters for the function here      @binvalue varbinary(256) )RETURNS VARCHAR(256) AS BEGIN     DECLARE @charvalue varchar(256)     DECLARE @i int     DECLARE @length int     DECLARE @hexstring char(16)     SELECT @charvalue = '0x'     SELECT @i = 1     SELECT @length = DATALENGTH (@binvalue)     SELECT @hexstring = '0123456789ABCDEF'      WHILE (@i  < = @length)      BEGIN       DECLARE @tempint int       DECLARE @firstint int       DECLARE @secondint int       SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))       SELECT @firstint = FLOOR(@tempint/16)       SELECT @secondint = @tempint - (@firstint*16)       SELECT ...

  -- Clear the query plan cache (don't execute this in production!) dbcc freeproccache go   -- CPU intensive query over adventureworks database select TransactionId , ProductId ,              ReferenceOrderId , ReferenceOrderLineId ,              TransactionDate , TransactionType ,              Quantity , ActualCost ,              ModifiedDate from production . TransactionHistory        order by modifiedDate desc Now, let’s check the most cpu intensive queries in our system: SELECT        -- using statement_start_offset and        -- statement_end_offset we get the query text        -- from inside the entire batch ...