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]