/ notes / sql

Sql server - Create a table, filled with fake data

The below script, creates a Sql Server table with 1000 records, filled with fake data.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MyTestTable]')) BEGIN
        CREATE TABLE [MyTestTable] (
            [ID] [int] IDENTITY(1,1) NOT NULL,
            [Txt] [nvarchar](50) NOT NULL,
            [DT] [datetime] NOT NULL,
            [Intx] [int] NOT NULL,
            [BigTxt] [nvarchar](max) NOT NULL,
            CONSTRAINT [PK_MyTestTable] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        ) 
        CREATE NONCLUSTERED INDEX IX_MyTestTable_Txt ON dbo.MyTestTable (Txt)   WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
        CREATE NONCLUSTERED INDEX IX_MyTestTable_Intx ON dbo.MyTestTable (Intx) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
GO
 
INSERT INTO  [MyTestTable] ([Txt], [DT], [Intx], [BigTxt]) 
    VALUES ( CONVERT(nvarchar(50), NEWID()), 
             GETUTCDATE(), 
             RAND()*10, 
             REPLICATE(CAST('a' as nvarchar(max)), 50*1000))
GO 1000

Query the data as you need:

SELECT COUNT(1) as RecordCount from [MyTestTable] 
SELECT TOP (20) [ID],[Txt], [DT], [Intx], LEN(BigTxt) as BigTxtLen FROM [MyTestTable]  ORDER BY ID DESC
DELETE from  [MyTestTable]  where [Intx]=8
SELECT COUNT(1) as RecordCount from [MyTestTable] 
GO
 
-- TRUNCATE TABLE [MyTestTable]
-- DROP TABLE     [MyTestTable]