SQL Server Tips

  • 重建数据库

当系统数据库路径变化导致服务无法启动时,通过以下命令可以重建数据库(以SQL SERVER 2019为例):

cd "C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\SQL2019"
setup /ACTION=rebuilddatabase /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=windows_administrator /sapwd=windows_password
  • 脚本方式创建外键
ALTER TABLE [dbo].[tStudent]  WITH CHECK ADD  CONSTRAINT [FK_tStudent_tClass] FOREIGN KEY([ClassID])
REFERENCES [dbo].[tClass] ([ClassID])
GO

首先,要确定待添加的外键字段在当前表中必须不能为空,否则会添加失败,如果已为空,则先修改其为不空

ALTER TABLE [dbo].[tStudent] ALTER COLUMN [ClassID] NVARCHAR(50) NOT NULL;

如果添加提示如下类型错误,可以尝试将上面语句中的 WITH CHECK 改成 WITH NOCHECK

Msg 547, Level 16, State 0, Line 1
ALTER TABLE 语句与 FOREIGN KEY 约束"FK_xxxx"冲突。该冲突发生于数据库...
  • UI 方式创建外键

SSMS –> Table (Object Explorer) –> Right Click –> Design(Popup) –> Table Design(Menu) –> Relationships… -> Add -> Tables and Columns Specification…

  • 修改列数据类型

Customers表为例,现在希望修改字段CompanyName的数据类型为nvarchar(100),直接设计修改保存会提示不允许保存的错误:

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

用 SQL 方式则可以更改:

alter table customers alter column CompanyName nvarchar(100) not null
  • 重建表结构

Customers表为例,现在希望把字段ContactName改为Not Null,直接设计修改保存也会提示不允许保存的错误。简单有效的方法是:

复制备份表

select * into Customers2 from Customers

删除原始表

drop table Customers

重建表结构

CREATE TABLE [dbo].[Customers] (
	[CustomerID] [nchar](5) NOT NULL,
	[CompanyName] [nvarchar](40) NOT NULL,
	[ContactName] [nvarchar](30) NOT NULL,
	[ContactTitle] [nvarchar](30) NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[Region] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[Phone] [nvarchar](24) NULL,
	[Fax] [nvarchar](24) NULL,
    CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

导入数据

insert into Customers select * from Customers2
  • 设立 Linked Server

SSMS -> Server Objects -> Linked Servers -> Right Click -> Select New Linked Server...

General Tab -> 输入 Linked Server 的主机名(或域名)

Security Tab -> 输入 Remote Login 用户名和密码

添加完成后测试

select * from your_linked_server_hostname.dbo.orders
  • 设置级联更新或删除
ALTER TABLE [dbo].[tableA]  WITH CHECK ADD  CONSTRAINT [FK_XXX] FOREIGN KEY([FieldName])
REFERENCES [dbo].[tableB] ([FieldName])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

或者

SSMS -> Database -> Tables –> 选择Table点击右键 -> Design –> Table Designer –> Relationships… -> 选择外键 -> INSERT And UPDATE Specification –> Delete / Update Rule –> Cascade