下载rpm包

https://packages.microsoft.com/rhel/7/

安装SQL Server

snippet.bash

$ sudo rpm -ivh mssql-server-14.0.600.250-2.x86_64.rpm
+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+

snippet.bash

 $ sudo /opt/mssql/bin/mssql-conf setup
接受license，设置admin password：
admin password: pwd2017

客户端以及工具安装

snippet.bash

rpm -ivh unixODBC-2.3.1-11.el7.x86_64.rpm
rpm -ivh msodbcsql-13.1.9.0-1.x86_64.rpm
rpm -ivh mssql-tools-14.0.6.0-1.x86_64.rpm

启停服务

snippet.bash

$ sudo systemctl stop mssql-server.service
$ sudo systemctl start mssql-server.service
$ sudo systemctl restart mssql-server.service
$ sudo systemctl status mssql-server.service

修改数据库存放位置

默认存放位置为/var/opt/mssql/data，可以修改为其它位置：

snippet.bash

$ sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /data/tpcds/mssql
$ sudo systemctl restart mssql-server.service

sqlcmd交互执行sql文件(source)

snippet.sql

1> :r /home/yz/sqlserver/scripts/SQL/1.sql
2> GO

sqlcmd执行sql文件

snippet.sql

-- 创建数据库：
$ sqlcmd -S localhost -U sa -P 'pwd2017' -dtpch1t -i /opt/wf/sqlserver/CREATE.sql

加载数据

snippet.bash

bcp nation  in /data/tpch/100s/nation.tbl   -c  -S localhost -U sa -P 'pwd2017' -d tpch100s -t "|"

查看sqlserver版本

snippet.sql

1> SELECT @@version;
2> GO

show database

snippet.sql

SELECT name FROM master.dbo.sysdatabases
-- 或者：
EXEC sp_databases

show tables

snippet.sql

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

To show only tables from a particular database:

snippet.sql

SELECT TABLE_NAME FROM <DATABASE_NAME>.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
-- 或者：
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='dbName'

clear the SQL buffer and plan cache

snippet.sql

CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO 

显示查询统计

snippet.sql

SET STATISTICS PROFILE ON
SET STATISTICS IO ON 
SET STATISTICS TIME ON  -- 显示查询耗时

全名：Database Console Commands。顾名思义“数据库控制台命令”。

DBCC到底有多少个命令，你应该知道，凡是控制台，大多都会提供一个help命令，比如cmd界面，又或者是mongodb的console。

公开和未公开的dbcc命令，加起来大约有100个，但是你要问我这些dbcc命令怎么用。我也告诉不了你，毕竟我的脑子没有那么发达去记这些无聊的东西，再说也不想花时间专门的去记这些东西，就像英语的记单词一样。但是办法还是有的。

如何记住DBCC命令？

联机丛书

联机丛书可以说是sqlserver之母，在这个世界上你不会找到有比它还要权威，还要全的资料了，你可以点击这里看看它的神威，然后你就可以键入dbcc，去寻找你需要寻找的东西，从下图中也看到，dbcc大概也是分为4类的，比较遗憾的是，未公开的dbcc命令在联机丛书上是找不到的。

help('xxx')

如果你对命令的用法有了大概认知，但是一时忘了怎么赋参数，这时候你就可以用help('xxx')来帮助你节省开发时间，比如说buffer和ind命令。

Q：删除数据库时提示“Cannot drop database XXX because it is currently in use”：

snippet.sql

1> DROP DATABASE IF EXISTS tpch100s;
2> GO
Msg 3702, Level 16, State 4, Server 95c0db9200ad, Line 1
Cannot DROP DATABASE "tpch100s" because it IS currently IN USE.

A：删除表前执行：

snippet.sql

1> ALTER DATABASE tpch100s SET single_user WITH ROLLBACK immediate
2> GO
Nonqualified transactions are being rolled back. Estimated ROLLBACK completion: 0%.
Nonqualified transactions are being rolled back. Estimated ROLLBACK completion: 100%.

Q：加载过程中提示以下错误：

snippet.txt

Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Could not allocate space for object 'dbo.lineitem'.'idx_lineitem' in database 'tpch300s' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegrou
p, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

A：原因：

在确认不是磁盘空间不足的情况下，检查当前SQL Server是否为Express版本。 Express一般包含以下限制（不同版本不尽相同）：

• 10 GB limit on Database Size: (SQL Server Standard allows for up to 524 PB).
• CPU limited to lesser of 1 socket or 4 cores: (SQL Server Standard allows for 4 sockets or 24 cores).
• Maximum buffer pool memory (per instance) limited to 1410 MB: (SQL Server Standard is 128 GB).
• Maximum columnstore segment memory (per instance) limited to 352 MB: (SQL Server Standard is 32 GB).
• Maximum memory-optimized data size (per database) limited to 352 MB: (SQL Server Standard is 32 GB).

Q：RedHat 7频繁启动服务限制

使用RedHat 7测试SQL Server时，脚本频繁启动和停止mssql-server，报以下错误：

Job for mssql-server.service failed because start of the service was attempted too often. See "systemctl status mssql-server.service" and "journalctl -xe" for details.
To force a start use "systemctl reset-failed mssql-server.service" followed by "systemctl start mssql-server.service" again.

A：这是因为SQL Server禁止了频繁启停服务，可以这样解决：

snippet.bash

$ sudo systemctl show -p FragmentPath mssql-server
FragmentPath=/usr/lib/systemd/system/mssql-server.service
$ sudo vi /usr/lib/systemd/system/mssql-server.service
[Service]
StartLimitBurst=0
$ systemctl reset-failed mssql-server.service
$ sudo systemctl daemon-reload

Q：先见列存加载和先建行存加载再转换到列存性能对比，哪个更快？

A：以TPC-H 1s测试对比：

• 列存，使用bcp工具加载耗时01:30s；
• 行存，使用bcp工具加载耗时01:08s，但是从行存到列存转换耗时53s，总时间02:01s。

因此，前者方式更快。

Q：count()函数统计大表行数，报错“Arithmetic overflow error converting expression to data type int.”

A：是因为count计数上限是2^32，可以使用以下方法：

snippet.sql

SELECT count_big(*) FROM lineitem;
-- 或者，
SELECT count_big(1) FROM lineitem;
-- 或者，
SELECT SUM(CAST(1 AS BIGINT)) FROM lineitem;

1. SQL SERVER EXPRESS: LIMITATIONS OF THE FREE VERSION OF SQL SERVER
2. Compare SQL Server 2017 editions