当前位置: >
> SQLServer语句大全(3)-------常用函数----统计函数----AVG--求平均&#
SQLServer语句大全(3)-------常用函数----统计函数----AVG--求平均&#
question411 & at
SQL Server 语句大全(3)-------常用函数 ----统计函数----
AVG --求平均值
COUNT--统计数目
MAX --求最大值
MIN --求最小值
SUM --求和
use pangu
selectavg(e_wage) as dept_avgWage
fromemployee
group bydept_id
--求工资最高的员工姓名
selecte_name
fromemployee
wheree_wage =
(selectmax(e_wage)
fromemployee)
--STDEV()函数返回表达式中所有数据的标准差
--STDEVP()函数返回总体标准差
--VAR()函数返回表达式中所有值的统计变异数
--VARP()函数返回总体变异数
---算术函数----
/***三角函数***/
SIN(float_expression)--返回以弧度表示的角的正弦
COS(float_expression)--返回以弧度表示的角的余弦
TAN(float_expression)--返回以弧度表示的角的正切
COT(float_expression)--返回以弧度表示的角的余切
/***反三角函数***/
ASIN(float_expression)--返回正弦是 FLOAT& 值的以弧度表示的角
ACOS(float_expression)--返回余弦是 FLOAT& 值的以弧度表示的角
ATAN(float_expression)--返回正切是 FLOAT& 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
--返回正切是float_expression1 /float_expres-sion2 的以弧度表示的角
DEGREES(numeric_expression)
--把弧度转换为角度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT& 类型
RADIANS(numeric_expression)--把角度转换为弧度返回与表达式相同的数据类
型可为
--INTEGER/MONEY/REAL/FLOAT& 类型
EXP(float_expression)--返回表达式的指数值
LOG(float_expression)--返回表达式的自然对数值
LOG10(float_expression)--返回表达式的以10& 为底的对数值
SQRT(float_expression)--返回表达式的平方根
/***取近似值函数***/
CEILING(numeric_expression)--返回&=表达式的最小整数返回的数据类型与表
达式相同可为
--INTEGER/MONEY/REAL/FLOAT& 类型
FLOOR(numeric_expression)--返回&=表达式的最小整数返回的数据类型与表达
式相同可为
--INTEGER/MONEY/REAL/FLOAT& 类型
ROUND(numeric_expression)--返回以 integer_expression& 为精度的四舍五入值返
回的数据
--类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT& 类型
ABS(numeric_expression)--返回表达式的绝对值返回的数据类型与表达式相同可
--INTEGER/MONEY/REAL/FLOAT& 类型
SIGN(numeric_expression)--测试参数的正负号返回 0& 零值 1& 正数或-1&负数返回
的数据类型
--与表达式相同可为INTEGER/MONEY/REAL/FLOAT& 类型
PI() --返回值为 p& 即 3.7936
RAND([integer_expression])--用任选的[integer_expression]做种子值得出 0-1& 间
的随机浮点数
----字符串函数----
ASCII()--函数返回字符表达式最左端字符的 ASCII& 码值
CHAR()--函数用于将 ASCII& 码转换为字符
--如果没有输入 0~ 255& 之间的 ASCII& 码值 CHAR&函数会返回一个 NULL& 值
LOWER()--函数把字符串全部转换为小写
UPPER()--函数把字符串全部转换为大写
STR()--函数把数值型数据转换为字符型数据
LTRIM()--函数把字符串头部的空格去掉
RTRIM()--函数把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING()--函数返回部分字符串
CHARINDEX(),PATINDEX()--函数返回字符串中某个指定的子串出现的开始位
SOUNDEX()--函数返回一个四位字符码
--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字
均只返回 0& 值
DIFFERENCE()--函数返回由 SOUNDEX& 函数返回的两个字符表达式的值的差
--0& 两个 SOUNDEX&函数返回值的第一个字符不同
--1& 两个 SOUNDEX&函数返回值的第一个字符相同
--2& 两个 SOUNDEX&函数返回值的第一二个字符相同
--3& 两个 SOUNDEX&函数返回值的第一二三个字符相同
--4& 两个 SOUNDEX&函数返回值完全相同
QUOTENAME()--函数返回被特定字符括起来的字符串
REPLICATE()--函数返回一个重复 character_expression& 指定次数的字符串
REVERSE()--函数将指定的字符串的字符排列顺序颠倒
REPLACE()--函数返回被替换了指定子串的字符串
SPACE()--函数返回一个有指定长度的空白字符串
STUFF()--函数用另一子串替换字符串指定位置长度的子串
----数据类型转换函数----
CAST()& 函数语法如下
CAST()(&expression& AS &data_ type&[ length ])
CONVERT()& 函数语法如下
CONVERT()(&data_ type&[ length ], &expression& [, style])
selectcast(100+99 as char) convert(varchar(12), getdate())
----日期函数----
DAY()--函数返回 date_expression& 中的日期值
MONTH()--函数返回 date_expression& 中的月份值
YEAR()--函数返回 date_expression& 中的年份值
DATEADD(&datepart&,&number& ,&date&)
--函数返回指定日期date& 加上指定的额外日期间隔 number& 产生的新日期
DATEDIFF(&datepart&,&number& ,&date&)
--函数返回两个指定日期在datepart& 方面的不同之处
DATENAME(&datepart&, &date&) --函数以字符串的形式返回日期的指定部分
DATEPART(&datepart&, &date&) --函数以整数值的形式返回日期的指定部分
GETDATE()--函数以 DATETIME& 的缺省格式返回系统当前的日期和时间
----系统函数----
APP_NAME()--函数返回当前执行的应用程序的名称
COALESCE()--函数返回众多表达式中第一个非 NULL& 表达式的值
COL_LENGTH($<$table_name'&,$<$column_name'&) --函数返回表中指定字段的长
度值
COL_NAME(&table_id&,&column_id&) --函数返回表中指定字段的名称即列名
DATALENGTH()--函数返回数据表达式的数据的实际长度
DB_ID(['database_name'])--函数返回数据库的编号
DB_NAME(database_id)--函数返回数据库的名称
HOST_ID()--函数返回服务器端计算机的名称
HOST_NAME()--函数返回服务器端计算机的名称
IDENTITY(&data_type&[,seed increment]) [AS column_name])
--IDENTITY()& 函数只在 SELECT INTO& 语句中使用用于插入一个 identity
column列到新表中
/*selectidentity(int, 1, 1) as column_name
intonewtable
fromoldtable*/
ISDATE()--函数判断所给定的表达式是否为合理日期
ISNULL(&check_expression&,&replacement_value&) --函数将表达式中的 NULL
值用指定值替换
ISNUMERIC()--函数判断所给定的表达式是否为合理的数值
NEWID()--函数返回一个 UNIQUEIDENTIFIER& 类型的数值
NULLIF(&expression1&,&expression2&)
--NULLIF& 函数在 expression1& 与 expression2&相等时返回 NULL& 值若不相等时
则返回expression1& 的值
本问题标题:
本问题地址:
温馨提示:本问题已经关闭,不能解答。
暂无合适的专家
&&&&&&&&&&&&&&&
希赛网 版权所有 & &&&&湘教QS2-164&&增值电信业务经营许可证湘B2-页面导航:
→ 正文内容 count(*)和Count(1)的区别
sql server中Select count(*)和Count(1)的区别和执行方式
在SQL Server中Count(*)或者Count(1)或者Count([列])或许是最常用的聚合函数。很多人其实对这三者之间是区分不清的。本文会阐述这三者的作用,关系以及背后的原理
&&& 在SQL Server中Count(*)或者Count(1)或者Count([列])或许是最常用的聚合函数。很多人其实对这三者之间是区分不清的。本文会阐述这三者的作用,关系以及背后的原理。
&&& 往常我经常会看到一些所谓的优化建议不使用Count(* )而是使用Count(1),从而可以提升性能,给出的理由是Count( *)会带来全表扫描。而实际上如何写Count并没有区别。
&&& Count(1)和Count(*)实际上的意思是,评估Count()中的表达式是否为NULL,如果为NULL则不计数,而非NULL则会计数。比如我们看代码1所示,在Count中指定NULL(优化器不允许显式指定NULL,因此需要赋值给变量才能指定)。
DECLARE @xx INT
SET @xx=NULL
SELECT COUNT(@xx) FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]
代码清单1.Count中指定NULL
&&&& 由于所有行都为NULL,则结果全不计数为0,结果如图1所示。
图1.显而易见,结果为0
&&& 因此当你指定Count(*) 或者Count(1)或者无论Count(‘anything')时结果都会一样,因为这些值都不为NULL,如图2所示。
图2.只要在Count中指定非NULL表达式,结果没有任何区别
&那Count列呢?
&&& 对于Count(列)来说,同样适用于上面规则,评估列中每一行的值是否为NULL,如果为NULL则不计数,不为NULL则计数。因此Count(列)会计算列或这列的组合不为空的计数。
那Count(*)具体如何执行?
&&& 前面提到Count( )有不为NULL的值时,在SQL Server中只需要找出具体表中不为NULL的行数即可,也就是所有行(如果一行值全为NULL则该行相当于不存在)。那么最简单的执行办法是找一列NOT NULL的列,如果该列有索引,则使用该索引,当然,为了性能,SQL Server会选择最窄的索引以减少IO。
&&& 我们在Adventureworks2012示例数据库的[Person].[Address]表上删除所有的非聚集索引,在ModifyDate这个数据类型为DateTime的列上建立索引,我们看执行计划,如图3所示:
图3.使用了CreateDate的索引
&&& 我们继续在StateProvinceID列上建立索引,该列为INT列,占4字节,相比之前8字节 DateTime类型的列更短,因此SQL Server选择了StateProvinceID索引。如图4所示。
图4.选择了更短的StateProvinceID索引
&&& 因此,如果某个表上Count(*)用的比较多时,考虑在一个最短的列建立一个单列索引,会极大的提升性能。
您可能感兴趣的文章:
上一篇:下一篇:
最 近 更 新
热 点 排 行
12345678910天极传媒:天极网全国分站
您现在的位置:
& &&SQL Server 2005提升总数统计性能
SQL Server 2005提升记录总数统计的性能Yesky 09:32
当我们想统计数据表的记录总数时,我们使用的T-SQL函数count(*) 。如果在一个包含了数百万行的大表中执行这个函数的话,,可以要花很长时间才能返回整个表的记录总数,这导致了查询性能的下降。
一、常规办法:采用Count ()函数
每个数据库管理员知道如何使用count(*) 函数。SQL 在执行这个函数时,为了返回总表的行计数,需要对索引/表进行完整的扫描。因此建议DBA们尽量避免针对整个表使用聚合函数count(*),因为它影响了数据库的性能。
下面我们来看个AdventureWorks数据库中的例子。
在查询分析器中执行下面的查询语句:
use&AdventureWorks go select&count&(*)&from&Sales.SalesOrderDetail
查询分析器执行后,显示有121317行。
当我们点击SQL Server 2005 Management Studio的工具栏上的“显示估计的执行计划”图标时,我们可以得到以下的图表:
图1:count(*)函数的执行计划
从右到左来看,我们可以了解到SQL语句的执行过程:
l 第一步中对整个表进行索引扫描,这是个非常耗时的过程(占81%)。
l 第二步中流聚合也较耗时(占19%)。
二、新方法:采用 row_count ()自定义函数
2005中的两个对象目录视图:sys.partitions和sys.allocation_units包含了这些信息,可以用来获取整个表的总记录数。
sys.partitions 目录视图
sys.partitions视图包含了数据库中每个连续分区的所有表和索引。SQL Server 2005中的所有表格和索引,即使他们没有明确划分,也包含这个视图的至少一个分区中。
数据库中所有表和索引的每个分区在表中各对应一行。即使 SQL Server 2005 中的所有表和索引并未显式分区,也认为它们至少包含一个分区。
在新的实现方法中,我们用到了sys. partitions 视图中的以下字段:
partition_id
分区的 ID。在数据库中是唯一的。
此分区所属的对象的 ID。每个表或视图都至少包含一个分区。
此分区所属的对象内的索引的 ID。
0表示是堆表中
1表示是集群表
此分区中的大约行数。
sys.allocation_units 目录视图
sys.allocation_units视图包含了数据库中一个连续为每个分配单元。
数据库中的每个分配单元都在表中占一行。
在新的实现方法中,我们用到了sys.allocation_units视图中的以下字段:
container_id
与分配单元关联的存储容器的 ID。
如果 type = 1 或 3,则 container_id = sys.partitions.hobt_id。
如果 type 为 2,则 container_id = sys.partitions.partition_id。
0 = 标记为要延迟删除的分配单元
分配单元的类型。
0 = 已删除
1 = 行内数据(所有数据类型,但 LOB 数据类型除外)
2 = 大型对象 (LOB) 数据(、ntext、 、xml、大型值类型以及 CLR 用户定义类型)
3 = 行溢出数据
在row_count这个新的用户自定义函数中,[sys.partitions]视图与[sys.allocation_units]视图相关联,并按照以下几个条件进行过滤:
l [sys.allocation_units].type = 1,所读取的行数据,不包含text, ntext,等类型的大型对象。
l [sys.partitions].index_id 属于0或者1 : 0表示是堆表中,1表示是集群表。
l [sys.partitions].不为空。
由于row_count这个自定义函数需要在每一个数据库中执行,因此将权限设置为Public。
用户自定义函数row_count具体描述,见下一节。
用户自定义函数row_count()代码
具体如下:
IF&EXISTS&(SELECT&name&FROM&dbo.sysobjects&WHERE&id&=&Object_id(N'[dbo].[row_count]')&) DROP&FUNCTION&[dbo].[row_count] GO CREATE&FUNCTION&dbo.row_count&(@table_name&sysname) --&@table_name&we&want&to&get&count RETURNS&bigint AS BEGIN DECLARE&@nn&bigint&--&number&of&rows IF&@table_name&IS&NOT&NULL BEGIN SELECT&@nn&=&(&p.rows&) FROM&sys.partitions&p LEFT&JOIN&sys.allocation_units&a&ON&p.partition_id&=&a.container_id WHERE p.index_id&in(0,1) and&p.rows&is¬&null and&a.type&=&1 and&p.object_id&=&object_id(@table_name) END RETURN&(@nn) END GO
row_count()自定义函数用法
调用row_count自定义函数时,需要提供类似.[ name]格式的数据表作为输入参数。
use&AdventureWorks go select&dbo.row_count&('Sales.SalesOrderDetail')
查询分析器返回的结果,也是121317行,与count(*)函数的执行结果相同,但速度更快,性能更佳。
SELECT&top&5&TABLE_SCHEMA,&TABLE_NAME,&(TABLE_SCHEMA&+'.'+TABLE_NAME)&'Full&table&name',dbo.row_count(TABLE_SCHEMA&+'.'+TABLE_NAME)&rows FROM&INFORMATION_SCHEMA.TABLES where&TABLE_TYPE&='BASE&TABLE' ORDER&BY&rows&desc
查询语句的执行结果见下表:
表1 INFORMATION_SCHEMA.TABLES 表的查询结果
这一功能主要是详细测试增加/删除行、截断表、批量插入/批量删除等操作,并返回精确统计数值。
三、两种实现方式性能评估
以下代码通过分别执行count(*)函数和row_count用户自定义函数,来进行性能对比:
select&dbo.row_count&('Sales.SalesOrderDetail') go select&count&(*)&from&Sales.SalesOrderDetail go
当审查执行计划时,我们可以发现第一个查询(row_count)的开销占整个批处理开销的7% ,估计子树大小的开销0.03,具体数值如下图所示:
图2:批处理中row_count的执行计划
第二个查询(count(*))的开销占整个批处理开销的93% ,估计子树成本是0.37,具体如下图所示:
图3:批处理中count(*)的执行计划
用户自定义函数row_count( )的性能值是最好的,消耗数据库的资源最少。Count(*)函数与新的用户自定义函数row_count的开销比为93:7,这意味着row_count的性能是count(*)函数性能的十倍以上。
这些对比数值,会随着表的记录数的变化而变化,但总的来说,上面的对比数据可以证明一点:新的用户自定义函数row_count()的性能,优于count(*)函数。
如果你需要统计整个表的总记录数,采用这个用户自定义函数,可以有效提升性能。
四、小结
内置函数count(*)在获取整个表的记录总数时,非常消耗时间,特别是在数据表中存在数以百万计条记录时,更为明显。
新的用户定义函数row_count,取决于对象目录视图提供准确的结果,与count(*)函数相比,速度更快,性能更佳。
这一功能可用于在SQL Server 2005及以上版本 ,因为从SQL Server2005开始,才有这些新的视图。
(作者:林善茂 IT专家网责任编辑:郑重)
欢迎在新浪微博上关注我们
办公软件IT新闻整机
document.write("");求快速统计SQL Server 某个库里所有表的方法,count() 函数很慢的。_百度知道
求快速统计SQL Server 某个库里所有表的方法,count() 函数很慢的。
提问者采纳
sysindexes&i;<,&as&as .id ,i.schema_id) .name&t;i;[Schema].tables&schema_name(t;as&=&[RowCount]from&and&t;iwhere select , .rows&as&t.indid .object_id&TableNas 
这是要在被查询的那个库上执行吗?
提问者评价
其他类似问题
按默认排序
其他1条回答
indid <.tables as t, sysindexes as iwhere t,用count()函数就必须为每个表生成一个动态SQL语句并执行.schema_id) as [Schema].tables视图。3。该方法利用了sysindexes 系统表提供的rows字段,忘记出处了,不会影响用户表的性能,再通过schema_name函数获取表的架构名,与其它查询结合使用.由于不访问用户表:1,从而保证为每个表返回一行;=1该方法连接了sys,才能得到结果:select schema_name(t,i.可以将该查询写成子查询.运行速度非常快、CTE或者视图。以下是在我的AdventureWorks数据库中运行该查询返回的部分结果.id and i,每个表至少有一个堆或者聚集索引。rows字段记录了索引的数据级的行数我们都知道用聚合函数count()可以统计表的行数:Schema TableName RowCount——————– ——————– ———–Sales Store 701Production ProductPhoto 101Production ProductProductPhoto 504Sales StoreContact 753Person Address 19614Production ProductReview 4Production TransactionHistory 113443Person AddressType 6该方法的优点有。解决方法的代码如下,写下来分享一下.name as TableN=1 只选聚集索引或者堆.indid <.rows as [RowCount]from sys.object_id = i,从中找出表名和schema_id。筛选条件i, t,不会在用户表上放置锁。如果需要统计数据库每个表各自的行数(DBA可能有这种需求)。以前在互联网上看到有一种很好的解决方法。2
为您推荐:
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁}