本文目錄列表: 1、SQL Server表設計視圖中的數據類型列展示效果 2、模擬實現類似的數據類型顯示效果 3、測試效果 4、總結語 5、參考清單列表 本文目錄列表: 1、SQL Server表設計視圖中的數據類型列展示效果 2、模擬實現類似的數據類型顯示效果 3、測試效果 4、總結語 5、參考清 ...
本文目錄列表: 1、SQL Server表設計視圖中的數據類型列展示效果 2、模擬實現類似的數據類型顯示效果 3、測試效果 4、總結語 5、參考清單列表 1、SQL Server表設計視圖中的數據類型列展示效果
在SQL Server 2012的表設計視圖中可以看到如下圖的效果: 如上圖所示紅色矩形框圈住的數據類型列中展示的效果,特別針對用戶定義數據類型也顯示了其基本數據類型。 這種針對欄位列顯示的數據類型效果很直接,很容易看到其數據類類型是什麼,甚至看能看到精度或小數位,最重要的是顯示了用戶定義數據類型的基本數據類型。有時候在資料庫設計中往往定義通用的用戶定義數據類型,如身份證,手機號碼,性別,名稱等等通用的用戶定義數據類型。 有時候將表欄位列導出來保存doc、excel以及html,針對欄位列的數據類型顯示往往需要4個欄位列來展示,如下圖所示: 上圖所示的就不太很直觀的看出該欄位的數據類型的詳細信息,特別該欄位列的數據類型為用戶定義的數據類型時,更無從看出來它的基本數據類型。 註意:在SQL Server中sysname數據定義數據類型預設是系統定義的,在表設計視圖中找不到該該數據類型,只能通過DDL命令來定義屬於sysname的欄位列。 2、模擬實現類似的數據類型顯示效果
為了將欄位列顯示的數據類型展示的更有清晰簡單,本人就模擬SQL Server 表設計視圖中的欄位列的展示效果的實現功能。 實現一個標量函數,支持將基本數據類型大小寫的功能,具體的T-SQL代碼如下:
IF OBJECT_ID(N'[dbo].[ufn_GetDisplayDataTypeName]', 'FN') IS NOT NULL BEGIN DROP FUNCTION [dbo].[ufn_GetDisplayDataTypeName]; END GO --================================== -- 功能: 獲取欄位列顯示的數據類型名稱 -- 說明: 支持用戶定義類型,可以運行於SQL Server 2005+ -- 創建: yyyy-MM-dd hh:mm-hh:mm XXX 創建內容描述 -- 修改: yyyy-MM-dd hh:mm-hh:mm XXX 修改內容描述 --================================== CREATE FUNCTION [dbo].[ufn_GetDisplayDataTypeName] ( @intUserTypeID AS INT -- 用戶類型ID ,@sntMaxLength AS SMALLINT -- 最大位元組長度 ,@tntPrecision AS TINYINT -- 精度 ,@tntScale AS TINYINT -- 小數位 ,@bitIsUpperDisplay AS BIT = 0 -- 是否大寫顯示(預設小寫顯示) ) RETURNS NVARCHAR(270) AS BEGIN SET @bitIsUpperDisplay = ISNULL(@bitIsUpperDisplay, 0); DECLARE @nvcDataTypeName AS NVARCHAR(128) ,@nvcBaseDataTypeName AS NVARCHAR(128) ,@nvcSuffixDisplayName AS NVARCHAR(14) SELECT @nvcDataTypeName = N'' ,@nvcBaseDataTypeName = N'' ,@nvcSuffixDisplayName = N'' SELECT @nvcDataTypeName = [name] ,@nvcBaseDataTypeName = (CASE WHEN (@nvcDataTypeName = N'sysname' OR [is_user_defined] = 1) THEN TYPE_NAME([system_type_id]) ELSE @nvcDataTypeName END) FROM [sys].[types] WHERE [user_type_id] = @intUserTypeID; SET @nvcDataTypeName = (CASE WHEN @nvcDataTypeName <> @nvcBaseDataTypeName THEN @nvcDataTypeName + N':' ELSE N'' END); SET @nvcBaseDataTypeName = CASE @bitIsUpperDisplay WHEN 1 THEN UPPER(@nvcBaseDataTypeName) ELSE @nvcBaseDataTypeName END; SET @nvcSuffixDisplayName = (CASE WHEN @nvcBaseDataTypeName = N'char' THEN QUOTENAME(RTRIM(CAST(@sntMaxLength AS VARCHAR(4))), N')') WHEN @nvcBaseDataTypeName = N'varchar' THEN (CASE WHEN @sntMaxLength = -1 THEN N'(MAX)' ELSE QUOTENAME(RTRIM(CAST(@sntMaxLength AS VARCHAR(4))), N')') END) WHEN @nvcBaseDataTypeName = N'nchar' THEN QUOTENAME(RTRIM(CAST(@sntMaxLength / 2 AS VARCHAR(4))), N')') WHEN @nvcBaseDataTypeName = N'nvarchar' THEN (CASE WHEN @sntMaxLength = -1 THEN N'(MAX)' ELSE QUOTENAME(RTRIM(CAST(@sntMaxLength / 2 AS VARCHAR(4))), N')') END) WHEN @nvcBaseDataTypeName = N'binary' THEN QUOTENAME(RTRIM(CAST(@sntMaxLength AS VARCHAR(4))), N')') WHEN @nvcBaseDataTypeName = N'varbinary' THEN (CASE WHEN @sntMaxLength = -1 THEN N'(MAX)' ELSE QUOTENAME(RTRIM(CAST(@sntMaxLength / 2 AS VARCHAR(4))), N')') END) WHEN @nvcBaseDataTypeName IN (N'numeric', N'decimal') THEN QUOTENAME(RTRIM(CAST(@tntPrecision AS VARCHAR(4))) + N',' + RTRIM(CAST(@tntScale AS VARCHAR(4))), N')') WHEN @nvcBaseDataTypeName IN (N'time', N'datetime2', N'datetimeoffset') THEN QUOTENAME(RTRIM(CAST(@tntScale AS VARCHAR(4))), N')') ELSE N'' END); RETURN @nvcDataTypeName + @nvcBaseDataTypeName + @nvcSuffixDisplayName; END GO
3、測試效果
演示上面標量函數的展示效果,以下定義若幹用戶定義數據類型來驗證標量函數dbo.ufn_GetDisplayDataTypeName的效果。 以下準備要驗證效果的數據如下:
CREATE TYPE [dbo].[fd_udt_digest] FROM [NVARCHAR](255) NULL GO CREATE TYPE [dbo].[fd_udt_id] FROM [CHAR](15) NOT NULL GO CREATE TYPE [dbo].[fd_udt_username] FROM [NVARCHAR](20) NULL GO CREATE TYPE [dbo].[UFemail] FROM [VARCHAR](100) NULL GO CREATE TYPE [dbo].[UFFlag] FROM [CHAR](1) NULL GO CREATE TYPE [dbo].[UFhyperlink] FROM [VARCHAR](100) NULL GO CREATE TYPE [dbo].[UFMedia] FROM [IMAGE] NULL GO CREATE TYPE [dbo].[UFreference] FROM [VARCHAR](30) NULL GO CREATE TYPE [dbo].[UFtext] FROM [NTEXT] NULL GO CREATE TYPE [dbo].[UFUID] FROM [UNIQUEIDENTIFIER] NULL GO CREATE TYPE [dbo].[userdecimal] FROM [DECIMAL](28, 6) NOT NULL GO CREATE TYPE [dbo].[udtProduct] AS TABLE ( ProductID INT NOT NULL ,UnitPrice DECIMAL(9, 2) NOT NULL ,Quantity INT NOT NULL PRIMARY KEY ([ProductID] ASC) ) GO CREATE TABLE [dbo].[ScaleDataTypeTable]( [ColDecimal] [NUMERIC](18, 2) NULL, [ColNumeric] [NUMERIC](9, 2) NULL, [ColFloat] [FLOAT] NULL, [ColReal] [REAL] NULL, [ColTime] [TIME](5) NOT NULL, [ColDateTime2] [DATETIME2](3) NULL, [ColDateTimeOffset] [DATETIMEOFFSET](5) NULL, [ColSqlVariant] [SQL_VARIANT] NULL, [ColMoney] [MONEY] NULL, [ColSmallMoney] [SMALLMONEY] NULL, [Col1] [dbo].[fd_udt_digest] NULL, [Col2] [dbo].[fd_udt_id] NULL, [Col3] [dbo].[fd_udt_username] NULL, [Col4] [dbo].[UFemail] NULL, [Col5] [dbo].[UFFlag] NULL, [Col6] [dbo].[UFhyperlink] NULL, [Col7] [dbo].[UFMedia] NULL, [Col8] [dbo].[UFreference] NULL, [Col9] [dbo].[UFtext] NULL, [Col10] [dbo].[UFUID] NULL, [Col11] [dbo].[userdecimal] NULL, [Col12] HIERARCHYID NULL, [Col13] GEOMETRY NULL, [Col14] GEOGRAPHY NOT NULL, [Col15] CHAR(10) NOT NULL, [Col16] VARCHAR(25) NOT NULL, [Col16Max] VARCHAR(MAX) NOT NULL, [Col17] NCHAR(16) NOT NULL, [Col18] NVARCHAR(32) NOT NULL, [Col18Max] NVARCHAR(MAX) NOT NULL, [ColBigint] BIGINT NOT NULL, [ColInt] INT NOT NULL, [ColSmallint] INT NOT NULL, [ColTinyint] TINYINT NOT NULL, [ColBit] BIT NOT NULL, [Col19] DECIMAL(9,7) NOT NULL, [Col20] MONEY NOT NULL, [Col21] SMALLMONEY NOT NULL, [Col22] TIMESTAMP NOT NULL, [Col24] UNIQUEIDENTIFIER NOT NULL, [Col25] IMAGE NOT NULL, [Col26] TEXT NOT NULL, [Col27] NTEXT NOT NULL, [Col28] BINARY(8) NOT NULL, [Col29] VARBINARY(8) NOT NULL, [Col29Max] VARBINARY(MAX) NOT NULL, [ColMxml] XML NULL, [Col30] DATE NOT NULL, [Col31] DATETIME NOT NULL, [Col32] SMALLDATETIME NOT NULL ) ON [PRIMARY]; GO CREATE TABLE [dbo].[UpdateDataTable] ( UpdateDataTableId INT NOT NULL, [Col23] ROWVERSION NOT NULL, [Colname] sysname NOT NULL ) ON [PRIMARY]; GO
基本數據類型小寫展示的效果的T-SQL代碼和效果截圖如下:
SELECT T2.[name] AS [table_name] ,[T1].[name] AS [column_name] ,TYPE_NAME([T1].[user_type_id]) AS [column_datatype] ,[dbo].[ufn_GetDisplayDataTypeName]([T1].[user_type_id], [T1].[max_length], [T1].[precision], [T1].[scale], 0) AS [column_display_datatype] ,[T1].[max_length] ,[T1].[precision] ,[T1].[scale] FROM [sys].[all_columns] AS T1 INNER JOIN [sys].[all_objects] AS T2 ON [T1].[object_id] = [T2].[object_id] WHERE [T2].[name] IN (N'ScaleDataTypeTable', N'UpdateDataTable') --AND EXISTS (SELECT 1 FROM [sys].[types] WHERE ([name] = N'sysname' OR [is_user_defined] = 1) AND [user_type_id] = [T1].[user_type_id]); GO
基本數據類型大寫展示的效果的T-SQL代碼和效果截圖如下:
SELECT T2.[name] AS [table_name] ,[T1].[name] AS [column_name] ,TYPE_NAME([T1].[user_type_id]) AS [column_datatype] ,[dbo].[ufn_GetDisplayDataTypeName]([T1].[user_type_id], [T1].[max_length], [T1].[precision], [T1].[scale], 1) AS [column_display_datatype] ,[T1].[max_length] ,[T1].[precision] ,[T1].[scale] FROM [sys].[all_columns] AS T1 INNER JOIN [sys].[all_objects] AS T2 ON [T1].[object_id] = [T2].[object_id] WHERE [T2].[name] IN (N'ScaleDataTypeTable', N'UpdateDataTable') --AND EXISTS (SELECT 1 FROM [sys].[types] WHERE ([name] = N'sysname' OR [is_user_defined] = 1) AND [user_type_id] = [T1].[user_type_id]); GO
4、總結語
這個功能剛開始自己使用了2個標量函數,針對sys.types每次查詢最多調用四次;第二版本則集中到一個標量函數中,還是針對sys.types每次查詢最多調用4次;第三版則重構和優化為sys.types每次查詢最多2次;第4版本繼續重構代碼將函數返回的結果分為三個部分的聯結,這樣重構更簡單明瞭;最後一版本增加了針對基本數據類型的大小寫功能。 都說代碼重構,這個功能的實現我確實感受到了重構和優化的效果,以上五個版本下來,有確實感覺到真正做好一件事情確實要好好的思考和動手實踐的,不然很容易要麼頭腦遲鈍要麼眼高手低等不好的習慣養成的。近來一直在看資料庫和商業智能方便的數據集,更印證了知道的越多,不知道和迫切想瞭解的就更多。如何將理論更好的指導實踐以及實踐更好的印證理論的可行性,需要更多的身體力行。 儘量要求自己每周至少發一篇博文和大家一起討論學習進步。雖然自己也在轉載和記錄工作中或是自己動手實踐的東西,有時候真的感覺時間有些不夠用,需要更多的汲取其他優秀人員的技能和理論,才能謹慎的寫下自己的所思所想,如果不周或認知不正確的地方也請各位海涵。近來學習到的東西太多,才發現之前發佈的的東西有不正確的地方,慢慢地我根據現有的認知來更正。 5、參考清單列表
無。