1. Getting the table and column information through information_schema
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Column_NAME = 'AddressId'
2. Creating a table with primary key and foreign key using sql commands
create Table License
(
LicenseId bigint,
LicenseTypeId bigint,
PaymentId bigint
primary key(LicenseId),
foreign key (LicenseTypeId) references [License Types](LicenseTypeId),
foreign key (PaymentId) references [Payment](PaymentId)
);
3. Difference between IsNulll and COALESCE a. ISNULL and COALESCE though equivalent, can behave differently.
b. An expression involving ISNULL with non-null parameters is considered to be NOT NULL,
c. while expressions involving COALESCE with non-null parameters is considered to be NULL.
1.ExamplesSELECT ISNULL(@foo, '123456789')
outputs to -
-----
12345 [truncates some values in Isnull]
SELECT
COALESCE(@foo, '123456789')
outputs to
---------
123456789 [no truncation]
2nd Exaamplecoalesce checks the three columns for null but Isnull checks only one column
e.g coalesce(col1, col2, col3)
Isnull(col1) is only allowed
4. to find duplicate values in sqlselect max(id), max(name) from testing group by name having count(*) >1
5. Primary Key and Unique Key differencePrimary key does not allow null values but unique allows null values
6. Deleting the Duplicate values in Sql TablesCreate table #temp
(
id int,
[name] varchar(50)
)
insert into #temp
select max(id), max(name) from testing group by name having count(*) >1
select * from #temp
delete testing where id in (select Id from #temp)
7. Passing array values to SP=============================
alter proc Array2sp
(
@names varchar(100)
)
as
declare @namesxml xml
DECLARE @XMLDocPointer INT
set @namesxml = '
' + replace(@names, ',', '') + '';
--select namesxml.item.value('valuess', 'int') as value1 from @namesxml.nodes('xml/root')
--as namesxml(item)
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @namesxml
--drop table temp
--select @namesxml
--create table temp(valuess varchar(20))
--insert into temp(valuess)
SELECT *
FROM OPENXML(@XMLDocPointer,'/xml/root',2)
with (valuess varchar(50) 'valuess')
--select * from temp
EXEC sp_xml_removedocument @XMLDocPointer
execution--exec Array2sp '1,2,3,4,5'
8.
Get the number of rows in a single column and single row use
"for xml path('')"
e.g. SELECT DocumentTypeDescription + ' / '
FROM OutPatientDocumentTypes
ORDER BY OutpatientDocumentId
FOR XML PATH('')
9.
using xquery to get the results from the xml column of the tableuse event
declare @docHandle xml
declare @handle int
set @docHandle=(select logxml from Import_Log where importid=142)
SELECT xref.value('@Importerror', 'varchar(100)') as ImportError, xref.value('@email', 'varchar(100)') as email FROM @docHandle.nodes('//logging/emaillistitems/listitem') R(xref)
where R is the Table and xref is the column Name
10. Comparing two xml columns in sql table - this is for multiple comparison
DECLARE @doc xml
DECLARE @doc1 xml
set @doc=(select [xmlCol1] from [xml1])
set @doc1=(select [xmlCol2] from [xml1])
Select
case when T1.c1.value('Id[1]', 'varchar(50)') = T2.c2.value('Id[1]', 'varchar(50)') then 1 else 0 end as matched,
case when T1.c1.value('Id[1]', 'varchar(50)') <> T2.c2.value('Id[1]', 'varchar(50)') then 1 else 0 end as nonmatched
From @doc.nodes('Categories/Category') T1(c1), @doc1.nodes('CategoryLevel/Category') T2(c2)
below sample is for single comparison [two xml columns in the sql table]
Select
-- case when xmlCol1.value('Categories[1]/Category[1]/CategoryName[1]', 'varchar(50)') = xmlCol2.value('CategoryLevel[1]/Category[1]/CategoryName[1]', 'varchar(50)') then 1 else 0 end as counts
-- From xml1
11. Newer way for comparing two xml columns in a sql tableDECLARE @doc xml
DECLARE @doc1 xml
set @doc=(select [xmlCol1] from [xml1])
set @doc1=(select [xmlCol2] from [xml1])
select (select count(*) as matchedwhere from @doc.nodes('Categories/Category') T1(c1) where T1.c1.value('Id[1]', 'varchar(20)') in
(select T2.c2.value('Id[1]', 'varchar(20)') from @doc1.nodes('CategoryLevel/Category') T2(c2))) as matchRow, count(*) as nomatched from @doc.nodes('Categories/Category') T1(c1)
where T1.c1.value('Id[1]', 'varchar(20)') not in (select T2.c2.value('Id[1]', 'varchar(20)') from @doc1.nodes('CategoryLevel/Category') T2(c2))
11. Encrypt text in sql
declare @hash varbinary(255)
set @hash=pwdencrypt('Dnn2O!!')
select @hash
SELECT pwdcompare ('Dnn2O!!', @hash)
If the value is one[1] then password are same
12. Get the values both null and non values for all columns
USE [mip_2.0.2]
GO
/****** Object: StoredProcedure [dbo].[usp_GetSearchUsers] Script Date: 07/26/2011 12:35:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetSearchUsers](@userName varchar(50), @firstName varchar(50), @lastName varchar(50), @city varchar(50), @ddlEmployementStatus varchar(50), @empType varchar(50), @doj varchar(50), @dor varchar(50), @attroneyLawFirm varchar(50))
as
SET ANSI_NULLS OFF
select * from tbl_user where
coalesce(user_name, 'null')=isnull(@userName, coalesce(user_name, 'null'))
and coalesce(first_name, 'null')=isnull(@firstName, coalesce(first_name, 'null'))
and coalesce(last_name, 'null')=isnull(@lastName, coalesce(last_name, 'null'))
and coalesce(city, 'null')=isnull(@city, coalesce(city, 'null'))
and coalesce(employeestatus_id_fk, 0)=isnull(@ddlEmployementStatus, coalesce(employeestatus_id_fk, 0))
and coalesce(employeetype_id_fk, 0)=isnull(@empType, coalesce(employeetype_id_fk, 0))
and coalesce(date_of_join, '1900-01-01')=isnull(@doj, coalesce(date_of_join, '1900-01-01'))
and coalesce(date_of_relieve, '1900-01-01')=isnull(@dor, coalesce(date_of_relieve, '1900-01-01'))
and coalesce(attorney_lawfirm_id_fk, 0)=isnull(@attroneyLawFirm, coalesce(attorney_lawfirm_id_fk, 0))
