SQL XML

INSERT INTO  BL_NetworkMembers
   (ContactID, NetworksID) 
  SELECT
   Contacts.ContactID,
   Network.NetworksID
  FROM (
    SELECT
    NetworkCol.value(‘../../Email[1]‘,’VARCHAR(150)’) AS Email ,
    NetworkCol.value(‘../../UID[1]‘,’bigint’) AS UID,
    NetworkCol.value(‘NetworkID[1]‘,’int’) as NetworksID
    FROM @XMLString.nodes(‘//Contacts/Contact/Networks/Network’)  tabNetwork(NetworkCol)
    )Network
  JOIN @ContactTemp Contacts
  ON  Contacts.Email = Network.Email
  AND  Contacts.UID = Network.UID
  WHERE ISNULL(Network.NetworksID,”) <>”
==================================================================
SELECT  tab.col.value(”(..//../UID)[1]”,”bigint”) AS UID,
 tab.col.value(”ChildName[1]”,”VARCHAR(100)”) AS ChildName,
 tab.col.value(”ChildDOB[1]”,”SMALLDATETIME”) AS ChildDOB
 FROM @XMLString.nodes(”/TestInfo/MyChildren/MyChild”) tab(col)

 

–<TestInfo>
–<UID>45</UID>
–<DOB>1923-03-27T00:00:00</DOB>
–<Relegion>29</Relegion>
–<RaceEthinicity>4</RaceEthinicity>
–<AnnualIncome>14</AnnualIncome>
–<Gender>1</Gender>
–<RelationShipStatus>26</RelationShipStatus>
–<AnniversaryDate>April/DD/1931</AnniversaryDate>
–<SpouceDOB>March/DD/1933</SpouceDOB>
–<Children>2</Children>
– <MyChildren>
– <MyChild>
–<ChildName>test</ChildName>
–<ChildDOB>1908-03-07T00:00:00</ChildDOB>
–</MyChild>
– <MyChild>
–<ChildName>asdfaf</ChildName>
–<ChildDOB>1923-05-27T00:00:00</ChildDOB>
–</MyChild>
–</MyChildren>
– <MyLanguages>
– <MyLanguage>
–<LanguageID>1</LanguageID>
–<LanguageName>English</LanguageName>
–</MyLanguage>
– <MyLanguage>
–<LanguageID>2</LanguageID>
–<LanguageName>Chinese</LanguageName>
–</MyLanguage>
– <MyLanguage>
–<LanguageID>3</LanguageID>
–<LanguageName>Spanish</LanguageName>
–</MyLanguage>
–</MyLanguages>
–</TestInfo>
ROOT,path
————-

select
 UserDetails.UserName,
 UserDetails.AddressLine1,
 UserDetails.AddressLine2,
 UserDetails.UserID,
 states=(select  state,stateid from state for xml path(‘State’),type)
from  UserDetails 
for xml path (‘Record’), ROOT(‘Records’)
=================================================

Get all values from node ,not the first one
—————————————–

select UserID=@LoginUserID,FieldName=’Landline1′,SharingType=2,
   tab.col.value(‘(StatusContact/text())[1]‘,’VARCHAR(25)’) AS Active ,
   isnull(tab.col.value(‘(Id/text())[1]‘,’VARCHAR(25)’),0) AS sort
   FROM @XMLString.nodes(‘MingleCard/StatusCollections/Status/LandLine1/ContactStatus’)  tab(col)
   —-where tab.col.value(‘LandLine1Status[1]‘,’VARCHAR(25)’)=1

……
…….
<landline1>
- <ContactStatus>
  <StatusContact>1</StatusContact>
  <Id>1</Id>
  </ContactStatus>
- <ContactStatus>
  <StatusContact>1</StatusContact>
  <Id>2</Id>
  </ContactStatus>
- <ContactStatus>
  <StatusContact>1</StatusContact>
  <Id>3</Id>
  </ContactStatus>
  </landline1>

…….

 

===================================================================
 DATALENGTH
===================================================================
  SET @XMLROWCOUNT = (SELECT Result=”true”,
   WorkExperienceID=isnull(WorkExperienceID,””),
   CompanyName=isnull(CompanyName,””),WebsiteUrl=isnull(WebsiteUrl,””),IndustryID=isnull(IndustryID,””),
   CompanyServices=isnull(Services,””),
   StreetAddress=isnull(StreetAddress,””),City=isnull(City,””),Zipcode=isnull(Zipcode,””),JobTitle=isnull(JobTitle,””),
   CountryID=isnull(MingleCountryCode,””)
  FROM dbo.U_MyWorkExperience
  WHERE UID=@loginId and ProfessionalInfo=1
  FOR XML PATH(”ProfessionalInformation”))

 
   IF DATALENGTH(@XMLROWCOUNT) > 0
  BEGIN
   SELECT @XMLROWCOUNT
  END
  ELSE
  BEGIN
   SELECT Result=”true”,MingleCountryCode AS CountryID FROM U_MyRegistration WHERE UserID = @loginId FOR XML PATH(”ProfessionalInformation”)
   –Select Result = ”false” FOR XML PATH(”ProfessionalInformation”)
  END
—————————-@XMLResult.Modify xml—————————————————–
SELECT @RecCount = @XMLResult.value(‘count(/AccountsList/Records/Record/RowNum[1])’,'varchar(10)’)

set @XMLResult.modify(‘replace value of (/AccountsList[1]/Count/text())[1] with sql:variable(“@RecCount”)’)

——————————xml custom structure———————————-

declare @XMLResult xml
SET @XMLResult = (SELECT Result=’true’,
WorkExperienceID=isnull(WorkExperienceID,”),
MyJobTitle=isnull(JobTitle,”),
isnull(CompanyName,”) AS ‘Company/Name’,
isnull(WebsiteUrl,”) AS ‘Company/WebSite’,
isnull(Services,”) AS ‘Company/Services’,
isnull(IndustryID,”) AS ‘Company/Industry/IndustryID’,
(SELECT IndustryName FROM BL_IndustryTypes WHERE IndustryID = U_MyWorkExperience.IndustryID) AS ‘Company/Industry/IndustryName’,
isnull(StreetAddress,”) AS ‘Company/CompanyAddress/StreetAddress1′,
isnull(City,”) AS ‘Company/CompanyAddress/Country/CityName’,
isnull(MingleCountryCode,”) AS ‘Company/CompanyAddress/Country/CountryID’,
(SELECT CountryName FROM BL_Country WHERE MingleCountryCode = U_MyWorkExperience.MingleCountryCode) AS ‘Company/CompanyAddress/Country/CountryName’,
isnull(CompanyEmail,”) AS ‘Company/CompanyEmail/Email’,
isnull(CompanyPhone,”) AS ‘Company/CompanyPhone/Phone’
–City=isnull(City,”),Zipcode=isnull(Zipcode,”)
FROM dbo.U_MyWorkExperience
WHERE LoginUserID=2 and ProfessionalInfo=1
FOR XML PATH(‘ProfessionalInformation’))
select @XMLResult
=———————————————————————————-
OUTPUT
=———————————————————————————-

 declare @T table ( id bigint)
 insert   into w_sponsor(userid,SponsorLogo)
  OUTPUT Inserted.sponsorid into  @T 
 values (1,’1′)
 
=—-
 declare @T table ( id bigint)
 update w_sponsor set userid=1
 OUTPUT Inserted.sponsorid into  @T  where sponsorid >10
 
select * from  @T
=======================
CTE
=======================
WITH CTE_Orders AS

(

SELECT Orderid, Shipname, shippeddate ,ROW_NUMBER() OVER (ORDER BY shippeddate DESC) AS RowNumber

FROM orders

)

SELECT Orderid, Shipname, shippeddate FROM CTE_Orders WHERE RowNumber BETWEEN 1 AND 30  ORDER BY RowNumber ASC;

 

2) Makes querying hierarchical data easier

WITH sql_cte (emp)
AS (
 SELECT emp FROM sql_org WHERE mgr = ‘Joe’
UNION ALL
 SELECT a.emp FROM sql_org a JOIN sql_cte b ON b.emp = a.mgr )
SELECT * FROM sql_cte
=============================================
Error handling
=====================================================
BEGIN TRY
 – Generate a constraint violation error.
 DELETE FROM Production.Product
 WHERE ProductID = 980;
END TRY
BEGIN CATCH
 SELECT ERROR_NUMBER() AS ErrorNumber,
  ERROR_SEVERITY() AS ErrorSeverity,
  ERROR_STATE() as ErrorState,
  ERROR_MESSAGE() as ErrorMessage;
END CATCH

———————–update method ———————————————
UPDATE C SET C.FName = A.[First Name], C.LName=A.[Last Name],
   C.SourceType = A.SourceType,
   C.SourceTag = A.SourceTag   
  FROM U_MyContacts C
  INNER JOIN Mingle_ImportAddress.dbo.BL_ImportAddressForUpadte A WITH (NOLOCK)
  ON C.LoginUserID=A.UserID and C.LoginUserID= @LoginUserID
  AND [Email ID]=EMail and IsRedundantEmail=”true”
===========================converting email to bigint

SELECT @UID = convert(bigint,convert(varbinary(260),reverse(convert(varbinary(260),
       CAST((CONVERT(varchar,@PartnerId))+@AccountEmail
       AS varbinary(260)) ))))

SQL Server

differences between sqlserver2000 and 2005
1)-In SQL SERVER 2000 there where maximum 16 instances but in 2005 you can have up to 50 instances.

2)-Database mirror concept supported in SQL SERVER 2005 which was not present in SQL SERVER 2000.

3)-SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL

SERVER 2000.It was a separate installation for SQL Server 2000.

4)-SQL Server 2005 introduces a dedicated administrator connection (DAC) to access a running server even if the

server is not responding or is otherwise unavailable. This enables you to execute diagnostic functions or

Transact-SQL statements so you can troubleshoot problems on a server. which was not present in SQL SERVER 2000.

***********

Fill factor is the term associated with indexes actually with clustured indexes

Whenever a clustured index is created sql server physically orders data in basis of the clustured column. As

you must be aware that the data is sql server is stored in 8K data page. Fill factor value setting advice SQL

Server to leave specified amount of space free on each page inorder to accommodate new data  if no fill factor

is set SQL Server data page will be filled completely and incase a new record comes it will have to make space

in the data page by reorganizing rest of the pages which is an over head. And hence it is suggested to keep

reasonable value for fill factor considering future requirements.

CREATE CLUSTERED INDEX … FILLFACTOR = 33 creates a clustered index with a FILLFACTOR of 33 percent

If no value is specified, the default is 0.A fill factor value of 0 does not mean that pages are 0 percent

full.It is treated similarly to a fill factor value of 100 in that SQL Server creates clustered indexes with

full data pages and nonclustered indexes with full leaf pages
You can change the default FILLFACTOR setting by executing sp_configure.

If you set fill factor to 100, SQL Server creates both clustered and nonclustered indexes with each page 100

percent full. Setting fill factor to 100 is suitable only for read-only tables, to which additional data is

never added.

Creating a clustered index with a FILLFACTOR affects the amount of storage space the data occupies because SQL

Server redistributes the data when it creates the clustered index.

Smaller fill factor values cause each index to take more storage space, allowing room for subsequent insertions

without requiring page splits.

****************

Composite key:- A primary key that consistsof two or more
attributes is known as composite key

candidate key:- A key that uniquely identifies rows in a table .Any of identified candidate keys can be used as

primary key.

Alternate Key:- Any of the candidate keys that is not part
of the primary key is called an alternate key.It accepts null Values

*************

Magic Table in SQL Server:
Magic tables are used to put all the deleted and updated rows. We can retrieve the
column values from the deleted rows using the keyword “deleted”

DELETE Vtbl1
OUTPUT Deleted.KeyID, Deleted.Name INTO @DeletedTable WHERE KeyID > 3
SELECT * FROM @DeletedTable

( you can’t use magic table outside of the trigger in sql 2000 without output clause)

**********

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records

returned are unique from your union, use UNION ALL instead, it gives faster results.

***

The following example forces the current identity value in the EmployeeID column in the Employee table to a
value of 300

DBCC CHECKIDENT ("HumanResources.Employee", RESEED, 300);
**********

 Using Inline Query Hint

USE AdventureWorks
GO
SELECT c.ContactID
FROM Person.Contact c
WITH (INDEX(AK_Contact_rowguid))
INNER JOIN Person.Contact pc
WITH (INDEX(PK_Contact_ContactID))
ON c.ContactID = pc.ContactID
GO

option hint

===========

SELECT c.ContactID
FROM Person.Contact c
INNER JOIN Person.Contact pc
ON c.ContactID = pc.ContactID
OPTION (TABLE HINT(c, INDEX (AK_Contact_rowguid)),
TABLE HINT(pc, INDEX (PK_Contact_ContactID)))