22,209
社区成员
发帖
与我相关
我的任务
分享
--1. 增加主键(无主键,可能导致堵塞)
ALTER TABLE [dbo].[bbuild1] ADD CONSTRAINT [PK_bbuild1] PRIMARY KEY NONCLUSTERED ([no])
--2. 将 NULL 值记录更新为 '1900-01-01', 避免为NULL的情况
UPDATE [dbo].[bbuild1] SET [time] ='1900-01-01' WHERE [time] IS NULL
--3. 修改为非NULL列
ALTER TABLE [dbo].[bbuild1] ALTER COLUMN [time] SMALLDATETIME NOT NULL
--4. 增加聚集索引
CREATE CLUSTERED INDEX ix_bbuild1_time ON [dbo].[bbuild1] ([time])
--5. 增加非聚集索引
CREATE INDEX ix_bbuild1_area_build_unit_room ON [dbo].[bbuild1] ( area, build, unit, room )
SELECT *
FROM [tin].[dbo].[bbuild1] where area='b' and build='1' and unit='1' and room='1001'
USE [tin]
GO
/****** Object: Table [dbo].[bbuild1] Script Date: 07/03/2018 09:39:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[bbuild1](
[no] [bigint] IDENTITY(1,1) NOT NULL,
[time] [smalldatetime] NULL,
[area] [varchar](2) NULL,
[build] [varchar](10) NULL,
[unit] [varchar](10) NULL,
[room] [varchar](10) NULL,
[id] [float] NULL,
[t] [float] NULL,
[tsp] [float] NULL,
[te] [float] NULL,
[tc] [float] NULL,
[tp] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[bbuild1] ADD CONSTRAINT [DF_bbuild1_time] DEFAULT (getdate()) FOR [time]
GO