Tag: SQL Server

SQL Server : Query For A Quick Overview Of Your Partitions

— =============================================

— This is a basic query intended to provide

— a quick look at partition info.  

— =============================================

 

SELECT b.name as ‘table_name’, a.partition_number,

a.[rows] as ‘row_count’, a.partition_id,

a.object_id, a.hobt_id,

b.schema_id, c.name as ‘schema_name’, 

a.index_id, b.create_date, b.modify_date

FROM  sys.partitions AS a INNER JOIN

sys.objects AS b ON a.object_id = b.object_id

INNER JOIN sys.schemas AS c On c.schema_id = b.schema_id              

WHERE LOWER(b.name) = ‘yourPartitionedTableName’

–pull only first index for a simpler view

and a.index_id < 2

ORDER BY b.name, partition_number;

 

— =============================================

–Helpful info for a deeper dive

— =============================================

 

SELECT * FROM SYS.partition_range_values ;

SELECT * from sys.partitions;

SELECT * FROM SYS.partition_functions;

SELECT * FROM sys.partition_schemes;

SELECT * from sys.schemas;

SELECT * FROM sys.filegroups;

 

–query sys.partitions to view and verify that the table contains partitions as expected

SELECT * FROM sys.partitions where OBJECT_NAME(Object_Id)=‘yourPartitionedTableName’;

SELECT * FROM sys.objects where OBJECT_NAME(Object_Id)=‘yourPartitionedTableName’;

SQL Server : Use XQuery On XML Data Types

XQuery is to XML what SQL is to database tables.   XQuery is designed to query XML data – not just XML files, but anything that can appear as XML, including databases.  In SQL Server, there are five methods that you can use to speed up your xquery to a column of type “xml:”  query(), value(), exist(), modify(), and nodes().  I’ll cover the first, and most commonly used, three.
For the purpose of this example, I will use the following sample xml in field “XmlField :”
<msg>
  <head>  
    <Id>X9356G356Y</Id>
  </head>
  <body>
    <ReturnedResponse>
      <Response>
        <ReturnCode>0</ReturnCode>      
        <Message>SUCCESS</Message>
      </Response>    
      <Customer>
   <CustomerId>CID459283</CustomerId>
        <State>NV</State>
 <Name>
  <FirstLast>Name1_Name2 </FirstLast>
        </Name>
      </Customer>
    </ReturnedResponse>
  </body>
</msg>
–XPATH Examples
–.value
SELECT  A.TransDate, A.MainId,
XmlField.value(‘/msg[1]/body[1]/ReturnedResponse[1]/Customer[1]/CustomerId[1]’, ‘VARCHAR(25)’) as xCustomerId
FROM TABLE2 B WITH (NOLOCK)
INNER JOIN TABLE1 A WITH (NOLOCK) ON B.APk = A.MainId
WHERE
A.MainId = ‘HI05491882’
–.exist
SELECT  A.TransDate, A.MainId,
XmlField.value(‘/msg[1]/body[1]/ReturnedResponse[1]/Customer[1]/CustomerId[1]’, ‘VARCHAR(25)’) as xCustomerId
FROM TABLE2 B WITH (NOLOCK)
INNER JOIN TABLE1 A WITH (NOLOCK) ON B.APk = A.MainId
WHERE
A.MainId = ‘HI05491882’
AND XmlField.exist(‘/msg/body/ReturnedResponse/Customer/CustomerId’) = 1
–.query
SELECT XmlField.query(‘/msg/body/ReturnedResponse/Customer/CustomerId’)
FROM TABLE2 B WITH (NOLOCK)
INNER JOIN TABLE1 A WITH (NOLOCK) ON B.APk = A.MainId
WHERE
A.MainId = ‘HI05491882’

SQL Server: Get RowCount Of All Tables Without the Overhead

Avoid overhead with this completely awesome SQL Server query that returns the row-count of all tables without actually having to query each table.  Instead, it just pulls from the statistics:

SELECT ‘[‘ + SCHEMA_NAME(t.schema_id)+ ‘].[‘ + t.name + ‘]’ AS full_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id =i.id AND i.indid < 2

SQL Server: Get First and Last Days Of Month

DECLARE @min DATETIME
DECLARE @max DATETIME
DECLARE @now DATETIME
SET @now = GETDATE();

–get first day of last month:

SET @min = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(DATEADD(mm,1,@now))-1),DATEADD(mm,-1,@now)),101) 

–get the last day of last month

SET @max = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(@now)),@now),101) 

–get the first day of current month
SET @min = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(@now)-1),@now),101)  

–get the last day of current month

SET @max = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(DATEADD(mm,1,@now))),DATEADD(mm,1,@now)),101)