MSSQL精简空间——去掉空格（mssql 去掉空隔）
MSSQL精简空间——去掉空格
MSSQL精简空间，即在相关表的列上去掉多余的空格，这能够节省大量的存储空间，也能带来更好的性能提升。对于MSSQL，如何去掉空格呢？下面介绍一下。
一种实际的方法是在MSSQL中创建一个带有脚本的存储过程，运行该存储过程可以自动化整个过程：
CREATE PROCEDURE TrimSpace @tabname VARCHAR(256)
AS
BEGIN
DECLARE @trim_query VARCHAR(4000)
DECLARE cur CURSOR FOR
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @tabname
OPEN cur
FETCH NEXT FROM cur INTO @column_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @trim_query = ”
SET @trim_query = ‘UPDATE ‘ + @tabname
SET @trim_query = @trim_query + ‘ SET ‘ + @column_name
SET @trim_query = @trim_query + ‘ = RTRIM(‘ + @column_name + ‘) ‘
SET @trim_query = @trim_query + ‘ WHERE LEN(‘ + @column_name + ‘) – LEN(RTRIM(‘ + @column_name + ‘)) > 0 ‘
EXEC (@trim_query)
FETCH NEXT FROM cur INTO @column_name
END
CLOSE cur
DEALLOCATE cur
END
上面的存储过程中，依次取出需要去空格的列，然后运行RTRIM函数把该表记录中多余的右边空格去掉。下面，定义一个参数传入具体表名，执行存储过程，这里以某表“stu”为例：
EXEC TrimSpace ‘stu’
当然，还可以利用SQL脚本一次性把所有表的所有列都运行RTRIM函数来去掉空格：
DECLARE @trim_query VARCHAR(4000)
DECLARE cur_table CURSOR FOR
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
OPEN cur_table
FETCH NEXT FROM cur_table INTO @tabname
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_column CURSOR FOR
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @tabname
OPEN cur_column
FETCH NEXT FROM cur_column INTO @column_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @trim_query = ”
SET @trim_query = ‘UPDATE ‘ + @tabname
SET @trim_query = @trim_query + ‘ SET ‘ + @column_name
SET @trim_query = @trim_query + ‘ = RTRIM(‘ + @column_name + ‘) ‘
SET @trim_query = @trim_query + ‘ WHERE LEN(‘ + @column_name + ‘) – LEN(RTRIM(‘ + @column_name + ‘)) > 0’
EXEC (@trim_query)
FETCH NEXT FROM cur_column INTO @column_name
END
CLOSE cur_column
DEALLOCATE cur_column
FETCH NEXT FROM cur_table INTO @tabname
END
CLOSE cur_table
DEALLOCATE cur_table
用这样的方法，在MSSQL中可以一次性地处理所有表的所有列，从而去掉各列中格式错误或者多余的空格，从而节省空间，提升性能。
