Shulou(Shulou.com)05/31 Report--

Editor to share with you about sqlserver tables, views, indexes how to create, modify, delete operations, I believe that most people do not understand, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

1. Table correlation

1. Create

USE [test] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo]. [Ceshi] ([id] [int] NOT NULL, [name] [varchar (30)] NULL, CONSTRAINT [PK_Ceshi] 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]) ON [PRIMARY] GO-- add field annotation EXEC sys.sp_addextendedproperty @ name=N'MS_Description', @ value=N' primary key', @ level0type=N'SCHEMA',@level0name=N'dbo', @ level1type=N'TABLE',@level1name=N'Ceshi', @ level2type=N'COLUMN',@level2name=N'id'GO-- modify field annotation EXEC sys.sp_updateextendedproperty @ name=N'MS_Description', @ value=N' primary key', @ level0type=N'SCHEMA' @ level0name=N'dbo', @ level1type=N'TABLE',@level1name=N'Ceshi', @ level2type=N'COLUMN',@level2name=N'id'GO

2. Modification

(1) modify the table name:

EXEC sp_rename 'table_name',' table_new_name'

(2) New fields:

ALTER TABLE table_name ADD column_name datatype

(3) modify the field name:

EXEC sp_rename 'table name .column _ name','new_column_name','column'

(4) modify the field type:

ALTER TABLE table_name ALTER COLUMN column_name datatype

(5) Delete fields:

ALTER TABLE table_name DROP COLUMN column_name

3. Delete

DROP TABLE `test`

Second, view dependence

1. Create

USE [test] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- comments uninstall here CREATE VIEW view_name AS SELECT column_name (s) FROM table_name WHERE conditionGO

2. Modification

ALTER VIEW view_name AS SELECT * FROM ceshi

3. Delete

DROP VIEW view_name

III. Index correlation

1. Create

(1) create a simple index on the table

USE [test] GOCREATE NONCLUSTERED INDEX index_name ON table_name (column_name ASC, column_name2 DESC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

(2) create a unique index on the table

USE [test] GOCREATE CLUSTERED INDEX index_name ON table_name (column_name ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

2. Delete

USE [test] GODROP INDEX index_name ON table_name WITH (ONLINE = OFF) GO is all the contents of this article entitled "how to create, modify and delete tables, views and indexes of sqlserver". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.