T-SQL小技巧
1、NULL值加法运算的问题。
如果NULL与其它值(如果varchar字符等)相加,则结果为NULL值,在一些组合字符的操作时这是不希望发生的,所以要使用ISNULL函数,当值为NULL时赋给一个默认的值。
例如:
DECLARE @location varchar(300)
SET @location=''
SELECT
@location=ISNULL(ri.location,''),
FROM job_ResumeInfo ri
WHERE ri.resumeid=@resumeid如果不使用ISNULL函数,而此时ri.location是NULL值的话,则永远都返回NULL值,如果加了ISNULL函数,则会返回''
2、如果表字段为可空,一定要进行DBNull判断。
private static bool IsDBNull(IDataReader dr, string name)
{
int index = dr.GetOrdinal(name);
return dr.IsDBNull(index);
}
private static string GetString(IDataReader dr, string name)
{
if(!IsDBNull(dr, name))
return (string)dr[name];
return null;
}
private static int GetInt32(IDataReader dr, string name)
{
if(!IsDBNull(dr, name))
return (int)dr[name];
return 0;
}
private static DateTime GetDateTime(IDataReader dr, string name)
{
if(!IsDBNull(dr, name))
return (DateTime)dr[name];
return DateTime.Now;
}
private static bool GetBoolean(IDataReader dr, string name)
{
if(!IsDBNull(dr, name))
return (bool)dr[name];
return false;
}
private static Guid GetGuid(IDataReader dr, string name)
{
if(!IsDBNull(dr, name))
return (Guid)dr[name];
return Guid.Empty;
}
3、如果原来装有SQL2000使用的是默认实例,现在又要安装SQL2005,则须为SQL2005配置一个自定义实例,要不不能进行SA登陆。如果使用自定义实例,则写连接字符串和企业管理器连接时,须在服务器名称后加上自定义实例的名称,如:CJB\SQL2005
如果NULL与其它值(如果varchar字符等)相加,则结果为NULL值,在一些组合字符的操作时这是不希望发生的,所以要使用ISNULL函数,当值为NULL时赋给一个默认的值。
例如:
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
2、如果表字段为可空,一定要进行DBNull判断。
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/images/OutliningIndicators/ExpandedBlockEnd.gif)
3、如果原来装有SQL2000使用的是默认实例,现在又要安装SQL2005,则须为SQL2005配置一个自定义实例,要不不能进行SA登陆。如果使用自定义实例,则写连接字符串和企业管理器连接时,须在服务器名称后加上自定义实例的名称,如:CJB\SQL2005