Tag: T-SQL

And the answer is: don’t use select top 1, instead use the row_number() function and the OVER clause.

Lets assume we have a table structure like:

Table [Observation]

Multiple observations during a TimeSpan on 1 barcode are collected into 1 [Observation] record.

if object_id('[dbo].[Observation]') is null
begin
  create table [dbo].[Observation]
  (
      [Id]                int not null identity(1,1)  constraint PK_Observation_Id primary key,
      [Barcode]            varchar(max) not null,
      [Postcode]        varchar(max) not null,
      [Date]            date not null,
      [Count]            int not null,
      [TimeSpanId]        int not null constraint FK_Observation_TimeSpanId foreign key references TimeSpan(Id)
  )
end
go

if not exists(select top 1 1 from [Observation])
begin
    insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN10000','4000AA','2012-06-18',1,1)
    insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN10001','4000AA','2012-07-02',1,1)
    insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN20000','4000AA','2012-05-29',2,2)
    insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN30000','4000AA','2012-06-18',2,3)
end

Table [TimeSpan]

The table TimeSpan contains timespans of 2 hours.

if object_id('[dbo].[TimeSpan]') is null
begin
  create table [dbo].[TimeSpan]
  (
      [Id]                int not null identity(1,1)  constraint PK_TimeSpan_Id primary key,
      [Name]            varchar(max),
      [Start]            time(7),
      [End]                time(7),
      [IsMostImportant] int not null default(0)
  )
end
go

if not exists(select top 1 1 from [TimeSpan])
begin
    insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('07:00 - 09:00','07:00:00','09:00:00',0)
    insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('10:00 - 12:00','10:00:00','12:00:00',0)           
    insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('13:00 - 15:00','13:00:00','15:00:00',0)
end

The most important TimeSpan

The first TimeSpan with the most- and youngest observations.

Update Query

The column [IsMostImportant] can be updated by using the following query:

declare    @DateNow date
set @DateNow = '2012-07-12'
set nocount on                                -- SET NOCOUNT ON, added to prevent extra result sets from interfering with SELECT statements.
set datefirst 1                               -- Monday is first day of the week.

update [TimeSpan]
set [IsMostImportant] = 1
from [TimeSpan] ts
inner join
(
    select
        ob3.[RowNumber],
        ob3.[TimeSpanId]
    from
    (
        select
            row_number() over(partition by ob2.[Postcode] order by ob2.[Count] desc, ob2.[DateDiffInDays] asc) as [RowNumber],
            [Count],
            [DateDiffInDays],
            [Postcode],
            [TimeSpanId]
        from 
        (
            select
                sum(ob.[Count]) as [Count],
                sum(datediff(day, ob.[Date], @DateNow) * ob.[Count]) as [DateDiffInDays],
                ob.[Postcode],
                ob.[TimeSpanId]
            from Observation ob
            inner join TimeSpan ts on ob.TimeSpanId = ts.Id
            group by ob.[Postcode], ob.[TimeSpanId]
        ) ob2
    ) ob3
    where ob3.[RowNumber] = 1
) ob4
on ts.[Id] = ob4.[TimeSpanId]

Result

If you want to get table schema / structure information with T-SQL, you have several options, the most commonly used by me are:

Using the system stored procedure sp_help

exec sp_help ‘<your table name>’

Using a custom query

-- Dump table schema / structure info
declare @tabelObjectId int
set @tabelObjectId = Object_ID(N'<Your table name>')

select
  'Column_name' = ac.name,
  'Type'        = type_name(ac.user_type_id),
  'Length'            = convert(int, ac.max_length),
  'Nullable'        = case when ac.is_nullable = 0 then 'No' else 'Yes' end,
  'Identity'    = case when ac.is_identity = 0 then 'No' else 'Yes' end,
  'PK'          = case when exists(
                    select 1 from sys.index_columns ic
                    inner join sys.columns c  on  ic.object_id = c.object_id
                                              and c.column_id = ic.column_id
                    where ic.object_id = @tabelObjectId and c.name = ac.name
                  ) then 'Yes' else 'No' end,
  'FK'          = case when exists(
                    select 1 from sys.foreign_key_columns fc
                    inner join sys.columns c  on  c.object_id = parent_object_id
                                              and c.column_id = fc.parent_column_id
                    where fc.parent_object_id = @tabelObjectId and c.name = ac.name
                  ) then 'Yes' else 'No' end
from sys.all_columns ac where ac.object_id = @tabelObjectId

Result for a table

Found the solution at:

http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure

The following T-SQL code executes the system stored procedure sp_helpfile and stores the result in the table variable @temp.

declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp  Exec sp_helpfile;
select * from @temp;

Well for ages, I created mine stored procedures like:

if exists (select 1 from sys.objects where name = 'GetSales' and type = 'p')
begin
    drop procedure [Reporting].[GetSales]
end
go

create procedure [Reporting].[GetSales]
    @parameter1 int,
    @parameter2 int
as
begin
    select @parameter1, @parameter2    
end
go

, but there is a slightly shorter notation:

if object_id('[Reporting].[GetSales]') is not null
begin
    drop procedure [Reporting].[GetSales]
end
go

create procedure [Reporting].[GetSales]
    @parameter1 int,
    @parameter2 int
as
begin
    select @parameter1, @parameter2    
end
go

You’re never to old to learn .

Just a simple example of creating a table by using T-SQL:

if not exists(select 1 from sys.objects where object_id = object_id(‘Product’))

begin

  create table [dbo].[Product]

  (

    [Id] [int] not null identity (1, 1),

    [Code] [nvarchar] (max) not null,

    [ManufacturerId] [int] not null,

    constraint [PK_Product_Id] primary key (Id),

    constraint [FK_Manufacturer_ManufacturerId] foreign key ([ManufacturerId]) references [dbo].[Manufacturer] ([Id])

  ) on [primary]

end

If you want to search in for a specific value in all columns of a Microsoft SQL Server Database, you can use the following T-SQL stored procedure:

CREATE PROC SearchAllTables(  
        @SearchStr nvarchar(100)  
 ) AS  
 BEGIN  
        CREATE TABLE #Results (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  
        drop table #Results
 END