How to extract IDENTITY_SEED and IDENTITY_INCREMENT from autoval column in syscolumns

/0 Comments/in /by

There are many hints on the internet that in the autoval column in the system table syscolumns in MS SQL Server 2000 it is stored the identity seed and identity increment values for columns that have been set as identity. Problem is there are little clues on how to extract those values from that column, because the data is stored as a binary value.

After some testing on my side I found that the autoval column can split in four parts, two of which represent IDENTITY_SEED and IDENTITY_INCREMENT values. But there is a catch – those binary data of each segment must be first reversed and then the values can be extracted. Here is the script:

T-SQL:

SELECT tCol.[name]
        , IDENTITY_INCREMENT = CAST(CAST(REVERSE(SUBSTRING(autoval, 5, 4)) AS VARBINARY(8)) AS BIGINT)
        , IDENTITY_SEED = CAST(CAST(REVERSE(SUBSTRING(autoval, 9, 4)) AS VARBINARY(8)) AS BIGINT)
FROM [syscolumns] tCol
WHERE tCol.autoval IS NOT NULL

You would ask: “Why the heck to we need such complex query when there are built in function like OBJECTPROPERTY and COLUMNPRPERTY that retrieve those values?”. Well if you are working with linked servers, and you want to retrieve the values from the remote server and you are connected to the local one, those functions do not work 😉

Simple Tile Horizontally / Tile Vertically functionality in WPF

/0 Comments/in , , /by

Here is the main idea: We will have a grid with three rows and three columns. The middle column/row will be used to store the GridSplitter that will allow us to resize columns/rows once we are in one of the tile modes. So basically when we are in Tile Horizontally we will span the textboxes (the same can be doen with any other control) to take all the three columns. And if we are in Tile Vertically mode we will change the columns of the controls and will Span the controls so they take all the rows.
But enough talking, lets get to work:

XAML:

<Grid>
    <Grid.ColumnDefinitions>
        <ColumnDefinition />
        <ColumnDefinition Width="3" />
        <ColumnDefinition />
    </Grid.ColumnDefinitions>
    <Grid.RowDefinitions>
        <RowDefinition/>
        <RowDefinition Height="3" />
        <RowDefinition/>
    </Grid.RowDefinitions>

    <TextBox x:Name="txtTextBoxTop"
             Grid.Row="0" Grid.Column="0"                
             Grid.ColumnSpan="3"
             />

    <GridSplitter x:Name="split"
                  Grid.Row="1" Grid.Column="0"
                  Grid.ColumnSpan="3"
                  HorizontalAlignment="Stretch"
                  VerticalAlignment="Stretch"
                  ResizeDirection="Rows"
                  />

    <TextBox x:Name="txtTextBoxBottom"
             Grid.Row="2" Grid.Column="0"                
             Grid.ColumnSpan="3"
             />
</Grid>

And here is the code that will change to one of the modes:

VB Code Behind:

Private Sub cmdTileHorizontally_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
    Grid.SetColumn(txtTextBoxTop, 0)
    Grid.SetRow(txtTextBoxTop, 0)
    Grid.SetColumnSpan(txtTextBoxTop, 3)
    Grid.SetRowSpan(txtTextBoxTop, 1)

    Grid.SetColumn(split, 0)
    Grid.SetRow(split, 1)
    Grid.SetColumnSpan(split, 3)
    Grid.SetRowSpan(split, 1)
    split.ResizeDirection = GridResizeDirection.Rows

    Grid.SetColumn(txtTextBoxBottom, 0)
    Grid.SetRow(txtTextBoxBottom, 2)
    Grid.SetColumnSpan(txtTextBoxBottom, 3)
    Grid.SetRowSpan(txtTextBoxBottom, 1)
End Sub

Private Sub cmdTileVerically_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
    Grid.SetColumn(txtTextBoxTop, 0)
    Grid.SetRow(txtTextBoxTop, 0)
    Grid.SetColumnSpan(txtTextBoxTop, 1)
    Grid.SetRowSpan(txtTextBoxTop, 3)

    Grid.SetColumn(split, 1)
    Grid.SetRow(split, 0)
    Grid.SetColumnSpan(split, 1)
    Grid.SetRowSpan(split, 3)
    split.ResizeDirection = GridResizeDirection.Columns

    Grid.SetColumn(txtTextBoxBottom, 2)
    Grid.SetRow(txtTextBoxBottom, 0)
    Grid.SetColumnSpan(txtTextBoxBottom, 1)
    Grid.SetRowSpan(txtTextBoxBottom, 3)
End Sub

It is simple as that! And as a bonus you even get resizing 🙂

Import XML File to SQL Table

/100 Comments/in , /by

Here is a brief example of importing an XML file into SQL Server table. This is accomplished by using the BULK option of OPENROWSET to load the file, and then utilizing the XQuery capabilities of SQL Server to parse the XML to normalized table format. This example requires SQL server 2005 or SQL Server 2008.

First, the following XML is saved to XML file C:Products.xml.

<Products>
  <Product>
    <SKU>1</SKU>
    <Desc>Book</Desc>
  </Product>
  <Product>
    <SKU>2</SKU>
    <Desc>DVD</Desc>
  </Product>
  <Product>
    <SKU>3</SKU>
    <Desc>Video</Desc>
  </Product>
</Products>

Next, a table named Products is created to store the XML data.

CREATE TABLE Products(
 sku INT PRIMARY KEY,
 product_desc VARCHAR(30));

Finally, the following statement will load the XML file, parse the XML elements to columns, and insert into the Products table:

INSERT INTO Products (sku, product_desc) 
SELECT X.product.query('SKU').value('.', 'INT'),
       X.product.query('Desc').value('.', 'VARCHAR(30)')
FROM ( 
SELECT CAST(x AS XML)
FROM OPENROWSET(
     BULK 'C:Products.xml',
     SINGLE_BLOB) AS T(x)
     ) AS T(x)
CROSS APPLY x.nodes('Products/Product') AS X(product);

Here are the results:

SELECT sku, product_desc 
FROM Products;
 
/*
 
Results:
 
sku         product_desc
----------- -------------
1           Book
2           DVD
3           Video
 
*/

Top N by Group

/6 Comments/in /by

It is a very common request to select the top N items per group. Examples are the top 2 sales agents per region, the last two orders per customer, etc. There are various techniques to accomplish this. On SQL Server 2005 and 2008 this normally involves CROSS APPLY, TOP, and the ranking functions.

Here are a couple examples of solving this problem utilizing the ranking functions in SQL Server 2005/2008. These methods are very simple and efficient, at the same time providing flexibility to manage ties.

CREATE TABLE Loans (
  loan_nbr INT NOT NULL PRIMARY KEY,
  loan_date DATETIME NOT NULL,
  loan_amount DECIMAL(12, 2) NOT NULL
              DEFAULT 0.0,
  customer_nbr INT NOT NULL,
  loan_type CHAR(1) NOT NULL
            DEFAULT 'P'
            CHECK (loan_type IN ('P',  -- personal
                                 'B')) -- business
  );
 
INSERT INTO Loans 
VALUES (1, '20080801', 12000.00, 2, 'P'),
       (2, '20080805', 15700.00, 1, 'B'),
       (3, '20080610', 12000.00, 3, 'P'),
       (4, '20080401', 5000.00, 1, 'P'),
       (5, '20080715', 25000.00, 4, 'B'),
       (6, '20080610', 25000.00, 5, 'P'),
       (7, '20080501', 1000.00, 6, 'P'),
       (8, '20080810', 6000.00, 7, 'B'),
       (9, '20080815', 2000.00, 8, 'B'),
       (10, '20080815', 1000.00, 9, 'P'),
       (11, '20080715', 5500.00, 10, 'P'),
       (12, '20080615', 1000.00, 11, 'B'),
       (13, '20080820', 6000.00, 12, 'P'),
       (14, '20080510', 28000.00, 6, 'B'),
       (15, '20080815', 2000.00, 10, 'P'),
       (16, '20080810', 1500.00, 8, 'P'),
       (17, '20080817', 10000.00, 10, 'B'),
       (18, '20080816', 2500.00, 9, 'P');   
 
-- Top 3 loans by loan type (no ties)
SELECT loan_nbr, loan_type, loan_amount, rk 
FROM (SELECT loan_nbr, loan_type, loan_amount,
             ROW_NUMBER() OVER(PARTITION BY loan_type
                               ORDER BY loan_amount DESC) AS rk
      FROM Loans) AS L
WHERE rk <= 3;
 
-- Top 3 loans by loan type (with ties)
SELECT loan_nbr, loan_type, loan_amount, rk 
FROM (SELECT loan_nbr, loan_type, loan_amount,
             DENSE_RANK() OVER(PARTITION BY loan_type
                               ORDER BY loan_amount DESC) AS rk
      FROM Loans) AS L
WHERE rk <= 3;
 
-- Latest loan for each customer
SELECT customer_nbr, loan_nbr, loan_type, loan_amount, loan_date
FROM (SELECT loan_nbr, loan_type, loan_amount, 
             customer_nbr, loan_date,
             ROW_NUMBER() OVER(PARTITION BY customer_nbr
                               ORDER BY loan_date DESC) AS rk
      FROM Loans) AS L
WHERE rk = 1;

Aggregate Window Functions

/6 Comments/in /by

The addition of the OVER clause in SQL Server was a great enhancement to the T-SQL language. Using the ranking functions has helped solve an array of problems in a very efficient manner. While there is a huge benefit of the ranking functions, it is often overlooked that the OVER clause supports now aggregate window functions. This means that the window aggregate function (SUM, AVG, COUNT, MIN, MAX) computes a value for each row from a result set derived from the window (partition). That opens an opportunity to solve problems using new methods.

Here are a couple examples to demonstrate using aggregate window functions with the OVER clause.

CREATE TABLE Loans (
  loan_nbr INT NOT NULL PRIMARY KEY,
  loan_date DATETIME NOT NULL,
  loan_amount DECIMAL(12, 2) NOT NULL
              DEFAULT 0.0,
  customer_nbr INT NOT NULL,
  loan_type CHAR(1) NOT NULL
            DEFAULT 'P'
            CHECK (loan_type IN ('P',  -- personal
                                 'B')) -- business
  );
 
INSERT INTO Loans 
VALUES (1, '20080801', 12000.00, 2, 'P'),
       (2, '20080805', 15700.00, 1, 'B'),
       (3, '20080610', 12000.00, 3, 'P'),
       (4, '20080401', 5000.00, 1, 'P'),
       (5, '20080715', 25000.00, 4, 'B'),
       (6, '20080610', 25000.00, 5, 'P'),
       (7, '20080501', 1000.00, 6, 'P'),
       (8, '20080810', 6000.00, 7, 'B'),
       (9, '20080815', 2000.00, 8, 'B'),
       (10, '20080815', 1000.00, 9, 'P'),
       (11, '20080715', 5500.00, 10, 'P'),
       (12, '20080615', 1000.00, 11, 'B'),
       (13, '20080820', 6000.00, 12, 'P'),
       (14, '20080510', 28000.00, 6, 'B'),
       (15, '20080815', 2000.00, 10, 'P'),
       (16, '20080810', 1500.00, 8, 'P'),
       (17, '20080817', 10000.00, 10, 'B'),
       (18, '20080816', 2500.00, 9, 'P');   
 
-- Aggregate window functions with the OVER clause
SELECT loan_nbr, loan_type, loan_amount, customer_nbr,
       SUM(loan_amount) OVER(PARTITION BY customer_nbr) AS total_customer_loans,
       AVG(loan_amount) OVER(PARTITION BY customer_nbr) AS avg_customer_loan_amt,
       MAX(loan_amount) OVER(PARTITION BY customer_nbr) AS max_customer_loan_amt,
       MIN(loan_amount) OVER(PARTITION BY customer_nbr) AS min_customer_loan_amt,
       COUNT(*) OVER(PARTITION BY customer_nbr) AS count_customer_loans
FROM Loans;
 
-- Calculate percent for current loan based on total customer loans
-- and total of all loans
SELECT loan_nbr, loan_type, loan_amount, customer_nbr,
       loan_amount /
       SUM(loan_amount) OVER(PARTITION BY customer_nbr) AS percent_of_customer_loans,
       loan_amount /
       SUM(loan_amount) OVER() AS percent_of_all_loans
FROM Loans;
 
-- Get customers (and all their loans) with more than 2 loans
SELECT customer_nbr, loan_nbr, loan_amount, cnt
FROM (SELECT customer_nbr, loan_nbr, loan_amount,
             COUNT(*) OVER(PARTITION BY customer_nbr) AS cnt
      FROM Loans) AS L
WHERE cnt >= 2;

Resources:

OVER Clause
http://msdn.microsoft.com/en-us/library/ms189461.aspx

Paging with Ranking Functions

/0 Comments/in /by

Paging through result sets is a very common need in applications. It provides a convenient way to deliver small chunks of data to the client application, minimizing network traffic and allowing end users to browse data in page size format.

There are different methods to accomplish the paging, both on client and server side. The introduction of the ranking functions in SQL Server 2005 (and SQL Server 2008) provides another efficient tool to implement paging.

The following example demonstrates paging utilizing the ROW_NUMBER function. Here it helps to generate sequence for each row ordered by the loan date column, and the sequence is later used to split the result set into pages.

CREATE TABLE Loans (
  loan_nbr INT NOT NULL PRIMARY KEY,
  loan_date DATETIME NOT NULL,
  loan_amount DECIMAL(12, 2) NOT NULL
              DEFAULT 0.0,
  customer_nbr INT NOT NULL,
  loan_type CHAR(1) NOT NULL
            DEFAULT 'P'
            CHECK (loan_type IN ('P',  -- personal
                                 'B')) -- business
  );
 
INSERT INTO Loans 
VALUES (1, '20080801', 12000.00, 2, 'P'),
       (2, '20080805', 15700.00, 1, 'B'),
       (3, '20080610', 12000.00, 3, 'P'),
       (4, '20080401', 5000.00, 1, 'P'),
       (5, '20080715', 25000.00, 4, 'B'),
       (6, '20080610', 25000.00, 5, 'P'),
       (7, '20080501', 1000.00, 6, 'P'),
       (8, '20080810', 6000.00, 7, 'B'),
       (9, '20080815', 2000.00, 8, 'B'),
       (10, '20080815', 1000.00, 9, 'P'),
       (11, '20080715', 5500.00, 10, 'P'),
       (12, '20080615', 1000.00, 11, 'B'),
       (13, '20080820', 6000.00, 12, 'P'),
       (14, '20080510', 28000.00, 6, 'B'),
       (15, '20080815', 2000.00, 10, 'P'),
       (16, '20080810', 1500.00, 8, 'P'),
       (17, '20080817', 10000.00, 10, 'B'),
       (18, '20080816', 2500.00, 9, 'P');   
 
-- Paging
DECLARE @page_size INT = 5;
DECLARE @page_nbr  INT = 4;
 
WITH LoansRanked (loan_date, loan_amount, loan_type, seq) 
AS
(SELECT loan_date, loan_amount, loan_type,
        ROW_NUMBER() OVER (ORDER BY loan_date, loan_nbr)
FROM Loans)
SELECT loan_date, loan_amount, loan_type, seq 
FROM LoansRanked
WHERE seq > (@page_nbr - 1) * @page_size 
  AND seq <= @page_nbr * @page_size;
 
/*
 
Results (4th page which contains only 3 rows):
 
loan_date               loan_amount   loan_type seq
----------------------- ------------- --------- ----
2008-08-16 00:00:00.000      2500.00  P         16
2008-08-17 00:00:00.000     10000.00  B         17
2008-08-20 00:00:00.000      6000.00  P         18
 
*/