Subscribe to this blog
James' SQL Footprint
Love SQL Server, Love life.
Archives: June 2012
Several useful T-SQL Function
1. Using the OVER clause with aggregate functions
The following sample is from BOL:
-------------------------------------
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
The following sample is from BOL:
-------------------------------------
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
0 comments, 669 reads
Posted in James' SQL Footprint on 30 June 2012
Rebuild index with "Alter Index Rebuild" or "DBCC DBREINDEX"
You can rebuild all index for a table with "Alter Index Rebuild" and "DBCC DBREINDEX" .
First, in BOL, for "DBCC DBREINDEX" , it mentions
This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify…
First, in BOL, for "DBCC DBREINDEX" , it mentions
This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify…
1 comments, 4,210 reads
Posted in James' SQL Footprint on 23 June 2012
Shrink transaction log file
Sometimes the shrink transaction log file doesn't work.
Before we troubleshoot it, we need to know how the log file works:
Transaction Log Physical Architecture
http://msdn.microsoft.com/en-us/library/aa174526(v=sql.80).aspxHere are some tools which can help us investigate the log file issues.
a. DBCC SQLPERF(logspace) : tell you how much transaction log is…
0 comments, 809 reads
Posted in James' SQL Footprint on 17 June 2012
Max Server Memory
Sakthivel Chidambaram recently created a calculator which can find out the max server memory value based on the input.
http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/19/cool-now-we-have-a-calculator-for-finding-out-a-max-server-memory-value.aspx
you can try the tool from http://blogs.msdn.com/b/sqlsakthi/p/max-server-memory-calculator.aspx
Then Jonathan Kehayias made a post to clarify why the calculator doesn't work
http://www.sqlskills.com/blogs/jonathan/2012/05/default.aspx
Jonathan has another great post relative the max server memory…
http://blogs.msdn.com/b/sqlsakthi/archive/2012/05/19/cool-now-we-have-a-calculator-for-finding-out-a-max-server-memory-value.aspx
you can try the tool from http://blogs.msdn.com/b/sqlsakthi/p/max-server-memory-calculator.aspx
Then Jonathan Kehayias made a post to clarify why the calculator doesn't work
http://www.sqlskills.com/blogs/jonathan/2012/05/default.aspx
Jonathan has another great post relative the max server memory…
0 comments, 1,692 reads
Posted in James' SQL Footprint on 2 June 2012
Setup windows firewall for SQL Server
On Windows 2008 R2, the windows firewall will turn on by default which causes connection issure for the remote client, here is a script which can turn off the firewall for all SQL Server default port. if there is a named instance, the script should be modified based on the correct setting.
1 comments, 1,139 reads
Posted in James' SQL Footprint on 1 June 2012