SQL Database: February 2016

Saturday, February 6, 2016

XML representation of data

XML RAW

SELECT * FROM Sales.Customer A
INNER JOIN Sales.MyOrders B ON A.CustomerID = B.customerId
FOR XML RAW

<row CustomerID="200" StoreID="1186" TerritoryID="1" AccountNumber="AW00000200" rowguid="7C96C878-A038-4B2F-825F-016C922D6407" ModifiedDate="2008-10-13T11:15:07.263" orderid="4" customerId="200"/>

XML AUTO

SELECT * FROM Sales.Customer A
INNER JOIN Sales.MyOrders B ON A.CustomerID = B.customerId
FOR XML AUTO

<A CustomerID="100" StoreID="1252" TerritoryID="6" AccountNumber="AW00000100" rowguid="E789F6DD-3159-4DDC-81A8-8571C571656E" ModifiedDate="2008-10-13T11:15:07.263">

</A>
<A CustomerID="200" StoreID="1186" TerritoryID="1" AccountNumber="AW00000200" rowguid="7C96C878-A038-4B2F-825F-016C922D6407" ModifiedDate="2008-10-13T11:15:07.263">



</A>

XML AUTO, ELEMENTS

SELECT * FROM Sales.Customer A

INNER JOIN Sales.MyOrders B ON A.CustomerID = B.customerId

FOR XML RAW

--FOR XML AUTO

FOR XML AUTO,Elements

<A>

</A>

<A>

</A>

Difference between temporary table and table variable

1. Creation

Temporary tables are created.

There are 2 types of temp tables
1. local - prefix with #
2. global - prefix with ##

create table #Temp1
(
column1 int not null
)

create table ##Temp1
(
column1 int not null
)

Table variable are decarled as opposed to being created. They are name with the sign @ as a prefix.

declare @TableTemp as table
(
column1 int not null
)

2. Scope

It is possible to create temporary local table in separate session with same name.
But it is not possible to create temporary global table in separate session with same name, there will be only one global temporary table in the whole database instance.

Local temporary table are visible throughout the level that created them, across batches, and in all inner levels of the call stack. If you dont drop the temporary table explicitly, it is destroyed when the level that created it terminates. But table variable is only visible to only the batch that declared them and are destroyed automatically at the end of the batch.

select column1 from #Temp1; -- it works from same session
select column1 from @Temp1; -- it works from same batch and same session

exec ('select column1 from #Temp1'); -- it works from same session

exec('select column1 from @Temp1'); -- it wont works even if it is in the same batch and session

3. Physical allocation

Both temporary table and table variable are created in tempdb. Here to be noted that, common table expression (CTE) is not a temp table, so you will not find it in tempdb.

select * from tempdb.sys.objects where name like '%#%'

4. Transaction

Temporary tables are similar to regular tables. Changes applied to a temporary table are undone if the transaction rolls back. But transaction are not undone if the transaction rolls back to a table variable.

5. Index

For temp table, if you define a constraint without naming, SQL server creates constraint with unique name. That's why it is recommended to use unnamed constraint in temporary table. But if you use constraint with a name, constraint will be created in tempdb. In that case, if you try to create a same named constraint from another session, it will be failed because of duplicate constraint name.

For table variable, SQL server doesn't allow explicit naming of constranint - not even in a single session.

5. Statistics

SQL server maintains distribution statistics (histograms) fro temporary tables but not for table variables.

SQL Database

Saturday, February 6, 2016

XML representation of data

Difference between temporary table and table variable

Pages

Search