DIR Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
HTML https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
DIR Return to: General
*****************************************************
#Post#: 33--------------------------------------------------
Tips and Tricks - 1
By: srinivasma_exceldbp Date: March 24, 2014, 4:47 am
---------------------------------------------------------
-- Selecting max value columnwise
if object_id('tempdb..#temp') is not null
drop table #temp
create table #temp
(
[Name] varchar(10),
[m1] int,
[m2] int,
[m3] int,
[m4] int
)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N1', 34,
36, 37, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N2', 35,
31, 145, 67)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N3', 34,
65, 37, 34)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N4', 34,
78, 90, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N5', 34,
47, 0, 18)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N6', 89,
36, 56, 60)
SELECT *, (
SELECT MAX(val) FROM (
VALUES (m1), (m2), (m3), (m4)
) AS value(val)) AS MaxVal
FROM #temp
----------------------------------------------------------------
--------------
/*
ISNULL and COALESCE
COALESCE -- Returns the first nonnull expression among its
arguments
1. ANSI Complaint
2. Supports more than two arguments
.
4. If all inputs are the untyped NULL literal, error is returned
ISNULL -- Replaces NULL with the specified replacement value
1. TSQL specific
2. Supports only Two argumants
4. If the first input is an untyped NULL literal,
the data type of the result is the type of the second input.
If both inputs are the untyped literal, the type of the output
is INT.
*/
-- Coalesce
-- Test with NULL literal
select coalesce(NULL,NULL) -- literal
declare @a int,@b varchar(10) -- int is highere precedence
compared to varhcar
select @a = NULL, @b = NULL
--** Test with NULL variables
select coalesce(@a,@b) -- variables -- two variables
select coalesce(@a,NULL) -- single variables
select coalesce(NULL,@a) -- single variables
go
-- ISNULL
-- Test with NULL literal
select ISNULL(NULL,NULL) -- literal
declare @a int,@b varchar(10) -- int is highere precedence
compared to varhcar
select @a = NULL, @b = NULL
--** Test with NULL variables
select ISNULL(@a,@b) -- variables
go
-----------------------------------------
-- COALESCE -- The data type of a COALESCE expression is the
-- data type of the input argument with the highest data type
precedence
declare @a int,@b varchar(10) -- int is highere precedence
compared to varhcar
select @a = 1, @b = 'abcd'
declare @datatype sql_variant
select @datatype = coalesce (@a,@b)
select sql_variant_property(@datatype, 'basetype')
select @datatype = coalesce (@b,@a)
select sql_variant_property(@datatype, 'basetype')
go
-- ISNULL
-- The data type of an ISNULL expression is the data type of the
first input
declare @a int,@b varchar(10) -- int is highere precedence
compared to varhcar
select @a = 1, @b = 'abcd'
declare @datatype sql_variant
select @datatype = isnull (@a,@b)
select sql_variant_property(@datatype, 'basetype')
select
select sql_variant_property(@datatype, 'basetype')
select sql_variant_property(@datatype, 'maxlength')
go
-----------------------------------------------
declare @a varchar(5) ,@b varchar(10)
select @a = NULL,@b ='1234567890'
select coalesce(@a,@b)
select isnull (@a,@b)
/*
SQL_VARIANT_PROPERTY(@datatype, 'BaseType'),
SQL_VARIANT_PROPERTY(@datatype, 'Precision'),
SQL_VARIANT_PROPERTY(@datatype, 'Scale'),
SQL_VARIANT_PROPERTY(@datatype, 'MaxLength')
*/
-----------------------------------------
-- Query to return the parameters for a specified stored
procedure or function
SELECT SCHEMA_NAME(schema_id) AS schema_name
,o.name AS object_name
,o.type_desc
,p.parameter_id
,p.name AS parameter_name
,TYPE_NAME(p.user_type_id) AS parameter_type
,p.max_length
,p.precision
,p.scale
,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')
ORDER BY schema_name, o.object_name, p.parameter_id;
----------------------------------------------------------------
--------------
--Dynamic Pivot
CREATE TABLE dbo.sales(
SalesPerson VARCHAR(100) ,
STATE CHAR(2) ,
Amount INT
)
INSERT INTO dbo.sales
VALUES ( 'John', 'MD', 120 ),
( 'John', 'VA', 500 ),
( 'John', 'NY', 900 ),
( 'Jane', 'MD', 40 ),
( 'Jane', 'VA', 125 )
DECLARE @cols AS NVARCHAR(MAX) ,
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.State)
FROM dbo.sales c
FOR XML PATH(''),TYPE ).value('.',
'NVARCHAR(MAX)'), 1, 1, '')
SET @query = 'SELECT SalesPerson, ' + @cols + ' from
(
SELECT SalesPerson
, STATE
, AMOUNT
FROM dbo.sales
) x
PIVOT
(
MAX(amount)
FOR state IN ('
+ @cols + ')
) p '
EXECUTE(@query)
DROP TABLE dbo.sales
----------------------------------------------------------------
--------------
/*
* Optimising "Ends with" searches with REVERSE
*
* Accompanying SQL script
*/
-- create the table to store data and an index on the name
column
SELECT object_id,name, system_type_id INTO test_table FROM
master.sys.all_parameters
CREATE NONCLUSTERED INDEX ix_name ON test_table (name ASC)
-- perform and "ends with" search
SET STATISTICS IO ON
SELECT name FROM TEST_TABLE WHERE NAME LIKE '%ID'
-- add the computed column and associated index
ALTER TABLE test_table ADD name_REVERSED AS REVERSE(name)
CREATE NONCLUSTERED INDEX IX_REVERSED ON TEST_TABLE
(NAME_REVERSED ASC) INCLUDE (NAME)
-- search again
SELECT name FROM TEST_TABLE WHERE NAME_REVERSED LIKE 'DI%'
----------------------------------------------------------------
-------------------------------------------
*****************************************************