One more definition of Group by for T-SQL

Hi everybody,

today I want to give one more definition of Group by of SQL language which for me is very explanatory.

Group by statement produces a record for each unique combination of group by columns list and ommiting other columns of tables.

Also you can apply aggregate functions to other columns. Aggregate functions may be count, sum, avg, mean, max, etc.

And each of the functions will be executed against columns of groupped records.

Logical sequence of T-SQL operations

Hello everybody,

Today I want to note that SQL Server has following order of processing:

  1. From
  2. Where
  3. Group by
  4. Having
  5. Select
  6. [Expressions]
  7. [Distinct]
  8. Order By
  9. [TOP/OFFSET-FETCH]

By looking on this order you may understand why in C# LINQ from goes first and not select. That is because C# is imperative language, while SQL is declarative.

How to increase display size of items in SQL server management studio

Hello everybody,

today I want to document one interesting feature of SQL Server management studio. Some time it happens, that you work with big XML data files.

For example if you have sql statement like this:

SELECT @xm=(
select rowid, count(rowid) as IdsNumber from SOLine s where s.rowid like 'fdsafewvvcxkfdsla'fslf%'
group by rowid
for xml raw, root)
select @xm

 

in SQL server management studio and would like to increase size that is workable by SQL server management studio. I found that following value in system registry is very helpful:

HKCU\Software\Microsoft\SQL Server Management Studio\14.0_Config\XmlEditor\MaxFileSizeSupportedByLanguageService

set it's value to 100 and you'll have option to open 100 M xml files

MS SQL find table by data record

Some time it happens that you need to find some string in the MS SQL database. 

Here I found very helpful ms sql code which helps to find any string in database.

At stackoverflow I found optimization of that script, which I copy/pasted below:

 

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM @Results
END