使用用户自定义类型 CLR UDT

一些复合类型进行范式分解是没有必要的，尤其是一些统一模型的情况下

1. SET NOCOUNT ON
2. DECLARE @i TimeBalance
3. SET @i = CAST('D/2015-1-1/7' AS TimeBalance)
4. SELECT @i
5. SELECT @i.ToString()
6. SELECT @i.ToTimeString()
7. SELECT @i.Distance() AS Distance;
8.  
9. WITH a AS ( SELECT '100' s ,
10.                         '' m
11.                UNIONALL
12.                SELECT '281474976710693' s ,
13.                         '37次' m
14.                UNIONALL
15.                SELECT 'Y/2015-1-5/1' s ,
16.                         '包年' m
17.                UNIONALL
18.                SELECT 'M/2015-1-5/11' s ,
19.                         '包个月' m
20.                UNIONALL
21.                SELECT 'D/2015-1-1/7' s ,
22.                         '包天' m
23.                UNIONALL
24.                SELECT '1500438729850887' s ,
25.                         '包天' m
26.                UNIONALL
27.                SELECT 'H/2015-1-5 8:30/10' s ,
28.                         '包小时' m
29.              )
30.     SELECT m + '' + s ,
31.             t.ToString() string ,
32.             t.ToHex() hex ,
33.             t.ToTimeString() timeString ,
34.             t.ToValue() value ,
35.             t.Distance() distance
36.     FROM ( SELECT s ,
37.                         m ,
38.                         CAST (s AS TimeBalance) t
39.               FROM A
40.             ) tt

相关资料：

TSql vs. SQL CLR Performance Analysis

CLR 集成的性能

操作 UDT 数据

1.      using System;
2. using System.Data;
3. using System.Data.SqlClient;
4. using System.Data.SqlTypes;
5. using Microsoft.SqlServer.Server;
6.  
7. /// <summary>
8. /// 时间钱包余额类型
9. ///
10. /// </summary>
11. /// <remarks>
12. /// 基于长整型，结构如下:
13. /// 包时类型2B，起始时间UTC秒数4B，时间总长2B（单位同类型决定）
14. /// <p>包时类型：0不包时1秒2分3时4日5月6年</p>
15. /// </remarks>
16. /// <see cref="https://msdn.microsoft.com/en-us/library/ms131086.aspx"/>
17. [Serializable]
18. [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
19. publicstruct TimeBalance : INullable
20. {
21.     publicoverridestring ToString()
22.     {
23.         switch (type)
24.         {
25.             case 0:
26.             case 1:
27.                 return ToValue().ToString();
28.             default:
29.                 returnstring.Format("{0}/{1}/{2}", (TimeEnum)type, UTCSecondToDateTime(startSecond).ToString("yyyy-MM-dd HH:mm:ss"), value);
30.         }
31.  
32.  
33.     }
34.     publicstring ToHex()
35.     {
36.         return type.ToString("X4") +"" + startSecond.ToString("X8") + value.ToString("X4");
37.     }
38.     publicstring ToTimeString()
39.     {
40.  
41.         switch (type)
42.         {
43.             case 0:
44.             case 1:
45.                 return ToValue().ToString();
46.             default:
47.                 TimeEnum typeEnum = (TimeEnum)type;
48.                 string format = "";
49.                 switch (typeEnum)
50.                 {
51.                     case TimeEnum.S:
52.                     default:
53.                         format = "HH:mm:ss";
54.                         break;
55.                     case TimeEnum.Mi:
56.                         format = "dd HH:mm";
57.                         break;
58.                     case TimeEnum.H:
59.                         format = "MM-dd HH";
60.                         break;
61.                     case TimeEnum.D:
62.                         format = "yyyy-MM-dd";
63.                         break;
64.                     case TimeEnum.M:
65.                         format = "yyyy-MM";
66.                         break;
67.                     case TimeEnum.Y:
68.                         format = "yyyy";
69.                         break;
70.  
71.  
72.                 }
73.                 returnstring.Format("{0}/{1}/{2}", (TimeEnum)type, UTCSecondToDateTime(startSecond).ToString(format), EndTime().ToString(format));
74.  
75.         }
76.     }
77.  
78.     publiclong ToValue()
79.     {
80.         switch (type)
81.         {
82.             case 0:
83.             case 1:
84.                 returnvalue;
85.  
86.             default:
87.                 return ((long)type << 48) + ((long)startSecond << 16) + value;
88.         }
89.     }
90.  
91.     publicstatic TimeBalance Parse(SqlString s)
92.     {
93.         if (s.IsNull)
94.             return Null;
95.         try
96.         {
97.             TimeBalance u = new TimeBalance();
98.  
99.             if (s.Value.Contains("/"))
100.             {
101.                 u.type = (short)(TimeEnum)Enum.Parse(typeof(TimeEnum), (s.Value.Split('/')[0]));
102.                 u.startSecond = DateTimeToUTCSecond(DateTime.Parse(s.Value.Split('/')[1]));
103.                 u.value = long.Parse(s.Value.Split('/')[2]);
104.             }
105.             else
106.             {
107.  
108.                 byte[] bs = BitConverter.GetBytes(long.Parse(s.Value));
109.                 u.type = BitConverter.ToInt16(bs, 6);
110.                 switch (u.type)
111.                 {
112.                     case 0:
113.                         u.value = long.Parse(s.Value);
114.                         break;
115.                     case 1:
116.                         u.value = BitConverter.ToInt16(bs, 0);
117.                         break;
118.                     default:
119.  
120.                         u.startSecond = BitConverter.ToInt32(bs, 2);
121.                         u.value = BitConverter.ToInt16(bs, 0);
122.                         break;
123.                 }
124.             }
125.  
126.             return u;
127.         }
128.         catch (Exception ex)
129.         {
130.             thrownew Exception(s.Value + "/r/n" + ex.ToString());
131.         }
132.     }
133.  
134.  
135.  
136.  
137.     publicbool IsNull
138.     {
139.         get
140.         {
141.             // 在此处放置代码
142.             return _null;
143.         }
144.     }
145.  
146.     publicstatic TimeBalance Null
147.     {
148.         get
149.         {
150.             TimeBalance h = new TimeBalance();
151.             h._null = true;
152.             return h;
153.         }
154.     }
155.  
156.  
157.     publiclong Distance()
158.     {
159.         returnvalue;
160.  
161.     }
162.  
163.     public DateTime EndTime()
164.     {
165.         short v = (short)(value & 0xffff);
166.         TimeEnum typeEnum = (TimeEnum)type;
167.         switch (typeEnum)
168.         {
169.             // case TimeEnum.N:
170.  
171.             case TimeEnum.S:
172.             default:
173.                 return UTCSecondToDateTime(startSecond).AddSeconds(v);
174.             case TimeEnum.Mi:
175.                 return UTCSecondToDateTime(startSecond).AddMinutes(v);
176.             case TimeEnum.H:
177.                 return UTCSecondToDateTime(startSecond).AddHours(v);
178.             case TimeEnum.D:
179.                 return UTCSecondToDateTime(startSecond).AddDays(v);
180.             case TimeEnum.M:
181.                 return UTCSecondToDateTime(startSecond).AddMonths(v);
182.