MSSQL - How to get rows in random sort order

Sometimes it is necessary to get the query result sorted in random order.

The first idea is to use a random number generator:

SELECT * FROM MyTable ORDER BY RAND();

But if we run this query several times, we can see that the rows are returned each time in the same order.
There is another function that returns something random - NEWID().
This function creates a unique value of type uniqueidentifier (GUID).

SELECT * FROM MyTable ORDER BY NEWID();

If you need to return a set of 100 random rows (Table Sample), you can do following:

SELECT TOP(100) * FROM MyTable ORDER BY NEWID();

MSSQL - Query Templates - GROUP_CONCAT

Today I will show how to implement aggregation of strings in MSSQL  (similar to MySQL GROUP_CONCAT). My favorite way is with usage of FOR XML expression.

Imagine that we have, such a database:



We need to build a list of table fields so, that if a field with the same name is in multiple tables, then the second column in result set will contain alphabetically sorted and comma separated set of table names. For example, in our case, for all fields terminating by '_ID':



To solve this task, we will use the data from INFORMATION_SCHEMA tables.
In the first step, we will get a list of COLUMN-TABLE combinations:

SELECT DISTINCT
    COLUMN_NAME,
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
ORDER BY
    COLUMN_NAME, TABLE_NAME

now the same, but as XML:

SELECT DISTINCT
    COLUMN_NAME,
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
ORDER BY
    COLUMN_NAME, TABLE_NAME
FOR XML RAW

Now for each field name we need get own list of tables. Result should be also in XML form.
For this we need first a list of unique field names:

SELECT DISTINCT
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
ORDER BY
    COLUMN_NAME

now combine this query with a query that returns list of COLUMN-TABLE combinations:

SELECT DISTINCT
    [COLUMN] = C1.COLUMN_NAME,
    [TABLEs] = (
            SELECT C2.TABLE_NAME
            FROM INFORMATION_SCHEMA.COLUMNS C2
            WHERE
                C2.COLUMN_NAME = C1.COLUMN_NAME
            ORDER BY TABLE_NAME
            FOR XML RAW
        )
FROM INFORMATION_SCHEMA.COLUMNS C1
ORDER BY [COLUMN]

We obtain the following table:



It now remains:
- Filter out the unnecessary tables (sysdiagrams)
- Leaving only the fields that terminating by '_ID'
- Replace the start and end XML tags with delimiter (comma)

SELECT DISTINCT
    [COLUMN] = C1.COLUMN_NAME,
    [TABLEs] = REPLACE(REPLACE(REPLACE(
        (
            SELECT T=C2.TABLE_SCHEMA + '.' + C2.TABLE_NAME
            FROM INFORMATION_SCHEMA.COLUMNS C2
            WHERE
                C2.TABLE_NAME != 'sysdiagrams'
                AND
                C2.COLUMN_NAME = C1.COLUMN_NAME
            ORDER BY 1
            FOR XML RAW
        ),
        '"/><row T="', ', '),
        '<row T="', ''),
        '"/>', '')
FROM INFORMATION_SCHEMA.COLUMNS C1
WHERE
    C1.TABLE_NAME != 'sysdiagrams'
    AND
    C1.COLUMN_NAME LIKE '%_ID'
ORDER BY [COLUMN]

The result of the query:



It remains to convert all of this in the form of T-SQL template:

USE <DataBaseName, sysname, tempdb>;
SELECT DISTINCT
    [COLUMN] = C1.COLUMN_NAME,
    [TABLEs] = REPLACE(REPLACE(REPLACE(
        (
            SELECT T=CASE C2.TABLE_SCHEMA
      WHEN SCHEMA_NAME()
      THEN ''
      ELSE C2.TABLE_SCHEMA + '.'
     END + C2.TABLE_NAME
            FROM INFORMATION_SCHEMA.COLUMNS C2
            WHERE
                C2.TABLE_NAME != 'sysdiagrams'
                AND
                C2.COLUMN_NAME = C1.COLUMN_NAME
            ORDER BY T
            FOR XML RAW
        ),
        '"/><row T="', ', '),
        '<row T="', ''),
        '"/>', '')
FROM INFORMATION_SCHEMA.COLUMNS C1
WHERE
    C1.TABLE_NAME != 'sysdiagrams'
    AND
    C1.COLUMN_NAME LIKE <ColumnNamePattern, sysname, '%'>
ORDER BY [COLUMN]

Save it in the list of SQL-templates.
Now we can use this template to find the fields of the same name in different tables:

About templates and usage of the system tables you can read here:
MSSQL - Query Templates
MSSQL - Query Templates - usage of system tables

.NET - Extend ListControl with GetSelectedItems and GetSelectedValues

Sometimes we need to get from ListBox or CheckBoxList the all selected items. Unfortunately there is only one ready to use function: GetSelectedIndices().
With the help of a very simple extensions we can add two additional functions, GetSelectedItems() and GetSelectedValues():

using System.Collections.Generic;
using System.Linq;
using System.Web.UI.WebControls;

namespace TypeExtensions
{
    // ReSharper disable InconsistentNaming
    public static class System_Web_UI_ListControl
    // ReSharper restore InconsistentNaming
    {
        /// <summary>
        /// Returns ListControl selected items.
        /// </summary>
        public static List<ListItem> GetSelectedItems(this ListControl listControl)
        {
            var selectedItems = listControl.Items
                .OfType<ListItem>()
                .Where(listItem => listItem.Selected)
                .ToList();
            return selectedItems;
        }

        /// <summary>
        /// Returns ListControl selected values.
        /// </summary>
        public static List<string> GetSelectedValues(this ListControl listControl)
        {
            var selectedItems = listControl.GetSelectedItems();
            var selectedValues = selectedItems
                .Select(listItem => listItem.Value)
                .ToList();
            return selectedValues;
        }
    }
}

Simply add Namespace of this class to your namespace usages and you will become two additional functions:


These extension functions are working for all ListControl-based classes.

MSSQL - Query Templates - usage of system tables

Sometimes you have to find all the tables (their names), which have the field with the specified name.
The easiest way to do this is through queries to the system tables.
Because this task occurs frequently, I have created T-SQL template:

-- Find Tables by Column-Name
USE <DataBaseName, sysname, tempdb>;

SELECT
    [SCHEMA] = schemas.name,
    [TABLE]  = tables.name,
    [COLUMN] = columns.name
FROM
    sys.schemas
    INNER JOIN sys.tables ON
        schemas.schema_id = tables.schema_id
    INNER JOIN sys.columns ON
        tables.object_id  = columns.object_id
WHERE
    schemas.name IN ('<schema_name, sysname, dbo>')
    AND
    tables.name  LIKE '<TableNamePattern, sysname, %>'
    AND
    columns.name LIKE '<ColumnNamePattern, sysname, %_ID>'
ORDER BY
    [SCHEMA],
    [TABLE],
    [COLUMN];

Save this template to your templates folder with for example following name:
"SysTables - Sch-Tb-Clm". After replacing of placeholders you will see something like this:

-- Find Dimension-Tables with Code-Columns
USE AdventureWorksDW2008R2;

SELECT
    [SCHEMA] = schemas.name,
    [TABLE]  = tables.name,
    [COLUMN] = columns.name
FROM
    sys.schemas
    INNER JOIN sys.tables ON
        schemas.schema_id = tables.schema_id
    INNER JOIN sys.columns ON
        tables.object_id  = columns.object_id
WHERE
    schemas.name IN ('dbo')
    AND
    tables.name  LIKE 'Dim%'
    AND
    columns.name LIKE '%Code'
ORDER BY
    [SCHEMA],
    [TABLE],
    [COLUMN];

The result of execution will be something like this:

MSSQL - Query Templates

Often working with the database we have to write lot of typical queries.
In this case it is convenient to create templates of such requests.
SQL Server Management Studio (SSMS) gives us that opportunity.

First of all, make sure that you have activated the tab "Templates Explorer":
[Menu] - [View] - [Template Explorer] or <Control>+<Alt>+<T>:


SSMS will show us lot of ready-to-use templates:


For example in the folder "SQL Server Templates" - "Stored Procedure" template "Create Procedure Basic Template". Click on it with double click or click with right mouse button and select from the context menu item "Open". SMS will open a new T-SQL Scripting window with this text:


In this script, there are some expressions in the form of <Name, DataType, DefaultValue>.
You can fill/replace these placeholders with help of corresponding dialog.
Just call a [Menu]-[Query]-[Specify Values for Template Parameters...]:


or press the button on the toolbar:

In the dialog, you can set new values ​​for the placeholders or leave the default values:


After you click [OK], the placeholders will be replaced with the appropriate values​​:



You can extend templates directory with your custom templates. Just call a context menu for the appropriate folder:


Or copy the folder with your templates (for example @inovex) in this folder:
C:\Users\<Your User>\AppData\Roaming\Microsoft\Microsoft SQL Server\<MSSQL Version>\Tools\Shell\Templates\Sql\



In future articles, I will show examples of some useful templates.

MSSQL - Function to format detailed error message

-- Drop Function if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.fnGetDetailedErrorMessage'))
    DROP FUNCTION dbo.fnGetDetailedErrorMessage;

GO
/*=============================================
Author:
    Yuri Abele
Changes:
    11.06.2013 - Yuri Abele - initial
Description:
    Function to format last error as detailed message

Usage: Log last error
    BEGIN TRY
        DECLARE @i INT;
        PRINT 'Try division by zero'
        SET @i = 1/0;
        PRINT 'After'
    END TRY
    BEGIN CATCH
        -- Display error
        PRINT dbo.fnGetDetailedErrorMessage();
        IF @@TRANCOUNT > 0 ROLLBACK;
    END CATCH;
=============================================*/
CREATE FUNCTION dbo.fnGetDetailedErrorMessage()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN ('ERROR:
        ErrorNumber        = ' + CAST(ERROR_NUMBER() AS VARCHAR(20)) + ',
        ErrorSeverity    = ' + CAST(ERROR_SEVERITY() AS VARCHAR(20)) + ',
        ErrorState        = ' + CAST(ERROR_STATE() AS VARCHAR(20)) + ',
        ErrorProcedure    = "' + ISNULL(ERROR_PROCEDURE(), '') + ',
        ErrorLine        = ' + CAST(ERROR_LINE() AS VARCHAR(20)) + ',
        ErrorMessage    = "' + ERROR_MESSAGE() + '"' + CHAR(13) + CHAR(10));
END;
GO

BEGIN TRY
    DECLARE @i INT;
    PRINT 'Try division by zero'
    SET @i = 1/0;
    PRINT 'After'
END TRY
BEGIN CATCH
    -- Display error
    PRINT dbo.fnGetDetailedErrorMessage();
    IF @@TRANCOUNT > 0 ROLLBACK;
END CATCH;

Results of execution:

MSSQL - Convert list to table #2 (array of string items)

Often, when working with databases there is a need to pass a parameter with set of elements.
Function described here (TABLE-Value UDF) converts a string in the form "aaa;bbb;ccc" to tabular form.
Additionally could be removed empty items.
In case if list has non-unique items, the function returns the serial number for each items group (RANK).

-- Drop Function if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.tf_array2table'))
    DROP FUNCTION dbo.tf_array2table;
GO
/*=============================================
Author:
    Yuri Abele
Changes:
    11.06.2013 - Yuri Abele - initial
Description:
    Function to convert list of Values to Table
 
Usage: Get all items
    SELECT item_index, item_value = IIF(item_value = '', '---', item_value), item_rank
    FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 0);
Usage: Get all non-empty items
    SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1);
Usage: Get all non-empty items and filter non-unique
    SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1)
    WHERE item_rank=1;
=============================================*/
CREATE FUNCTION dbo.tf_array2table
(
    @array NVARCHAR(MAX), -- List of delimited values
    @delim NCHAR(1),      -- Delimiter
    @remove_empty BIT     -- Flag to remove empty values
)
-- Container for Array Items
RETURNS @data TABLE(
    item_index INT IDENTITY(1,1), -- 1-based index of item
    item_value NVARCHAR(MAX),     -- item value
    item_rank  INT                -- item rank if items are not unique
)
AS BEGIN
    -- Container for XML
    DECLARE
        @xml_text NVARCHAR(MAX),
        @xml XML;
 
    IF @remove_empty = 1 BEGIN
        -- Remove empty inner items
        WHILE (CHARINDEX(@delim + @delim, @array, 0) > 0) BEGIN
            SET @array = REPLACE(@array, @delim + @delim, @delim);
        END;
        -- Remove empty left item
        IF(LEFT(@array, 1) = @delim) BEGIN
            SET @array = SUBSTRING(@array, 2, LEN(@array)-1)
        END;
        -- Remove empty right item
        IF(RIGHT(@array, 1) = @delim) BEGIN
            SET @array = SUBSTRING(@array, 1, LEN(@array)-1)
        END;
    END;
    
    -- Prepare XML-Text
    SET @xml_text = N'<L><I>' +
        REPLACE(@array, @delim, N'</I><I>') +
        N'</I></L>';
    
    -- Convert Array to XML
    SET @xml = CAST(@xml_text AS XML);
 
    DECLARE @temp_data TABLE(
        item_index INT IDENTITY(1,1),
        item_value NVARCHAR(MAX)
    )
    -- Extract Array Items to Table-Variable
    INSERT INTO @temp_data(item_value)
    SELECT
        item_value = item.value('.', 'NVARCHAR(MAX)')
    FROM
        @xml.nodes('//I') XMLDATA(item);
    
    -- Calculate Rank for each item (to find non-unique items)
    INSERT INTO @data(item_value, item_rank)
    SELECT
        item_value,
        item_rank=RANK() OVER(PARTITION BY item_value ORDER BY item_index, item_value)
    FROM
        @temp_data
    ORDER BY
        item_index;
    
    RETURN;
END;
GO

Usages:

-- Get all items
SELECT item_index, item_value = IIF(item_value = '', '---', item_value), item_rank
FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 0);

Result

item_index	item_value	item_rank
1	---	1
2	ddd	1
3	bbb	1
4	ccc	1
5	---	2
6	---	3
7	ccc	2
8	ddd	2
9	eee	1
10	---	4

-- Get all non-empty items
SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1);

Result

item_index	item_value	item_rank
1	ddd	1
2	bbb	1
3	ccc	1
4	ccc	2
5	ddd	2
6	eee	1

-- Get all non-empty items and filter non-unique
SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1)
WHERE item_rank=1;

Result

item_index	item_value	item_rank
1	ddd	1
2	bbb	1
3	ccc	1
6	eee	1