1.輸入結(jié)果等于輸出結(jié)果
2.類似Group By的聚合
3.非順序的訪問(wèn)數(shù)據(jù)
4.可以對(duì)于窗口函數(shù)使用分析函數(shù) 聚合函數(shù)和排名函數(shù)
5.簡(jiǎn)化SQL代碼 消除Join
6.消除中間表
窗口函數(shù)是整個(gè)SQL語(yǔ)句中最后被執(zhí)行的部分，窗口函數(shù)實(shí)在SQL查詢的結(jié)果集上進(jìn)行的，不會(huì)受到Group By，Hiving Where 字句的影響。
函數(shù)() Over (Partition By 列1，列2， order By 列3, 窗口字句) As 列別名
select [LoginId],gender,(select Count() from employee a where a.gender = b.gender) as genderTotal from employee b
select [LoginID],gender, Count() Over (PARTITION BY gender) AS genderTotal from employee
select loginId,first_value(loginId) OVER(PARTITION BY Organizationlevel
order by sickleaveHours desc rows between 1 preceding and 1 follwing) from employee
first_value(loginid) over (partition by organizationLevel order by sickleaveHours desc Range between unbounded preceding and current now) from employee
大表文件操作：
我們的表里都是只存放一天的數(shù)據(jù)。其中三張表數(shù)據(jù)較大。最大的一張表單表在5億行左右，隨時(shí)間處于波動(dòng)增長(zhǎng)狀態(tài)。目前碰到最多的一天單表有7億多行。另外兩張表數(shù)據(jù)量在0.5億到1億之間。其余的就是一些幾百萬(wàn)行~一千萬(wàn)行記錄的表。需要進(jìn)行十幾套不同條件維度的聚合計(jì)算，且這十幾套計(jì)算都會(huì)用到那三張大表。查詢里會(huì)涉及到多張大表連接操作。
我們目前的處理方式就是盡量精簡(jiǎn)存儲(chǔ)的數(shù)據(jù)量。各表數(shù)據(jù)存儲(chǔ)都不存在冗余的狀況，但是會(huì)導(dǎo)致分成多個(gè)表；數(shù)據(jù)類型盡量設(shè)置成存儲(chǔ)長(zhǎng)度較小的類型；對(duì)常用的連接、查詢列建立了合適的索引；對(duì)數(shù)據(jù)和索引都設(shè)置了頁(yè)壓縮模式減少存儲(chǔ)量。
在我們目前其他的查詢處理中，只要應(yīng)用了合適的索引+頁(yè)壓縮+適當(dāng)?shù)姆直?歷史數(shù)據(jù)歸檔)模式，可以解決絕大多數(shù)的性能問(wèn)題，不僅可以提升查詢性能也能提升插入性能。此處也有單表接近1億行的數(shù)據(jù)查詢處理，效果也很好。只是這里不存在幾張大表連接的情況。
數(shù)據(jù)庫(kù)日志滿了的解決方案：
提示：對(duì)于該問(wèn)題，主要檢是否限制了數(shù)據(jù)庫(kù)文件增長(zhǎng)
1.檢查你的磁盤剩余空間是否足夠,如果沒(méi)有磁盤剩余空間,則清理磁盤,騰出空間
2.檢查你的磁盤分區(qū)格式
如果是FAT16,則數(shù)據(jù)文件最大只能是2G；如果是FAT32,則數(shù)據(jù)文件最大只能是4G，改為NTFS分區(qū)則沒(méi)有這種限制
3.檢查一下你有沒(méi)有限制數(shù)據(jù)庫(kù)文件的大小
企業(yè)管理器--右鍵你的數(shù)據(jù)庫(kù)--屬性--文件增長(zhǎng)限制--如果有限制大小,取消限制
4.檢查你的SQL版本,如果你用MSDE,則限制了數(shù)據(jù)文件最大是2G
5.你也可以為 primary 組添加新的數(shù)據(jù)文件來(lái)解決這個(gè)問(wèn)題
alter database 庫(kù)名 add file(NAME = 邏輯文件名,FILENAME = c:實(shí)際文件名.ndf
另外，請(qǐng)注意TEMPDB的空間占用情況！該臨時(shí)數(shù)據(jù)庫(kù)應(yīng)該經(jīng)常清理！
清理日志的方法
當(dāng)SQL數(shù)據(jù)庫(kù)日志文件已滿，可以取消日志文件大小限制，當(dāng)然我們更希望定期對(duì)其壓縮，清除N天前的無(wú)用記錄
提供一種壓縮日志及數(shù)據(jù)庫(kù)文件的方法如下：
1.清空日志
DUMP TRANSACTION 庫(kù)名 WITH NO_LOG
2.截?cái)嗍聞?wù)日志：
BACKUP LOG 數(shù)據(jù)庫(kù)名 WITH NO_LOG
3.收縮數(shù)據(jù)庫(kù)文件(如果不壓縮,數(shù)據(jù)庫(kù)的文件不會(huì)減小
企業(yè)管理器--右鍵你要壓縮的數(shù)據(jù)庫(kù)--所有任務(wù)--收縮數(shù)據(jù)庫(kù)--收縮文件
--選擇日志文件--在收縮方式里選擇收縮至XXM,這里會(huì)給出一個(gè)允許收縮到的最小M數(shù),直接輸入這個(gè)數(shù),確定就可以了
--選擇數(shù)據(jù)文件--在收縮方式里選擇收縮至XXM,這里會(huì)給出一個(gè)允許收縮到的最小M數(shù),直接輸入這個(gè)數(shù),確定就可以了
也可以用SQL語(yǔ)句來(lái)完成
--收縮數(shù)據(jù)庫(kù)
DBCC SHRINKDATABASE(客戶資料)
--收縮指定數(shù)據(jù)文件,1是文件號(hào),可以通過(guò)這個(gè)語(yǔ)句查詢到:select * from sysfiles
DBCC SHRINKFILE(1)
4.為了最大化的縮小日志文件(如果是sql 7.0,這步只能在查詢分析器中進(jìn)行)
a.分離數(shù)據(jù)庫(kù):
企業(yè)管理器--服務(wù)器--數(shù)據(jù)庫(kù)--右鍵--分離數(shù)據(jù)庫(kù)
b.在我的電腦中刪除LOG文件
c.附加數(shù)據(jù)庫(kù):
企業(yè)管理器--服務(wù)器--數(shù)據(jù)庫(kù)--右鍵--附加數(shù)據(jù)庫(kù)
此法將生成新的LOG，大小只有500多K
或用代碼：
下面的示例分離 pubs，然后將 pubs 中的一個(gè)文件附加到當(dāng)前服務(wù)器。
a.分離
E X E C sp_detach_db @dbname = pubs
b.刪除日志文件
c.再附加
E X E C sp_attach_single_file_db @dbname = pubs,
@physname = c:Program FilesMicrosoft SQL ServerMSSQLDatapubs.mdf
5.為了以后能自動(dòng)收縮,做如下設(shè)置:
企業(yè)管理器--服務(wù)器--右鍵數(shù)據(jù)庫(kù)--屬性--選項(xiàng)--選擇"自動(dòng)收縮"
--SQL語(yǔ)句設(shè)置方式:
E X E C sp_dboption 數(shù)據(jù)庫(kù)名, autoshrink, TRUE
6.如果想以后不讓它日志增長(zhǎng)得太大
企業(yè)管理器--服務(wù)器--右鍵數(shù)據(jù)庫(kù)--屬性--事務(wù)日志
--將文件增長(zhǎng)限制為xM(x是你允許的最大數(shù)據(jù)文件大小)
--SQL語(yǔ)句的設(shè)置方式:
alter database 數(shù)據(jù)庫(kù)名 modify file(name=邏輯文件名,maxsize=20)
特別注意：
請(qǐng)按步驟進(jìn)行,未進(jìn)行前面的步驟,請(qǐng)不要做后面的步驟
否則可能損壞你的數(shù)據(jù)庫(kù).
一般不建議做第4,6兩步
第4步不安全,有可能損壞數(shù)據(jù)庫(kù)或丟失數(shù)據(jù)
第6步如果日志達(dá)到上限,則以后的數(shù)據(jù)庫(kù)處理會(huì)失敗,在清理日志后才能恢復(fù).
一種更簡(jiǎn)單的解決方法：
1、右建數(shù)據(jù)庫(kù)屬性窗口--故障還原模型--設(shè)為簡(jiǎn)單
2、右建數(shù)據(jù)庫(kù)所有任務(wù)--收縮數(shù)據(jù)庫(kù)
3、右建數(shù)據(jù)庫(kù)屬性窗口--故障還原模型--設(shè)為大容量日志記錄
Sql server日志相關(guān)查詢命令
1/查看數(shù)據(jù)庫(kù)空間使用情況
Exec sp_spaceused
Exec sp_spaceused N'dbo.tableName1'
Exec sp_spaceused N'dbo.tableName2'
2/查看數(shù)據(jù)庫(kù)所包含表
select * from sys.tables
select * from sys.databases
exec sp_tables
3查看日志空間使用情況
dbcc sqlpref(logspace)
4/ 查看日志情況
dbcc loginfo
查看具體日志：select * from sys.fn_dblog(null,null)
查看tempdb當(dāng)前大小
exec sp_helpdb tempdb
對(duì)tempdb進(jìn)行收縮
use tempdb
go
dbcc shrinkfile(tempdev, 1024)
use tempdb
go
dbcc shrinkfile(templog, 512)
sql解析原理
