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…
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>' …
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),…
When trouble shouting error’s thrown by T-SQL, naming your primary and foreign keys, proves to be handy. When creating tables with T-SQL, I use the naming format PK_MyTable_MyColumn1_MyColumn2 and FK_MyTable_MyColumn3_MyColumn4: if object_id('[dbo].[Address]') is null begin…
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…
A colleague of mine (Mattijs ter Heijde) created a simple script to verify Microsoft SQL Server DBCC RESEED does not take existing ID’s into account, here’s the T-SQL Code:create table #test (id int not null…
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,…
If you want to drop all connections to a database by using T-SQL, connect Microsoft SQL Server Management Studio to the master database, open a new query editor window and execute the following T-SQL code:…
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…
CONVERT(VARCHAR(8),GETDATE(),108) Result 14:12:30 Other examples print convert(varchar(30),getdate(),121) -- Result: 2011-05-09 12:04:31.883 print convert(varchar(30),getdate(),113) -- Result: 29 Apr 2011 13:41:23:327