Monday, 21 July 2008

OVER Clause in SQL 2005

Converting some reports to SQL2005, I initially forgot the OVER clause can be used aith aggregate functions (had previously only used with RANK).
Saves using multiple joins and GROUP BY record sets >

SELECT   [Name] StateName
  ,[City]
  ,COUNT([Name]) OVER (PARTITION BY [City], [Name]) AS CityTotal
  ,COUNT([Name]) OVER (PARTITION BY [Name]) AS StateTotal
  ,COUNT([Name]) OVER (PARTITION BY [City], [Name]) / CONVERT(FLOAT,COUNT([Name]) OVER (PARTITION BY [Name])) * 100.0 AS PercentOfState
FROM Person.Address a
INNER JOIN Person.StateProvince s
 ON a.StateProvinceID = s.StateProvinceID

Sunday, 20 July 2008

Temporary Tables - Checking for existence

Checking for the existance of temporary tables...

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#tblDateList%')
   DROP TABLE #tblDateList
 
CREATE TABLE #tblDateList
   ( [Date] DATETIME NOT NULL,
     CONSTRAINT [PK_#tblDateList] PRIMARY KEY CLUSTERED ([Date] ASC) 
   ) ON [PRIMARY]

Thursday, 10 July 2008

Developer Permissions on SQL Agent Jobs

I wanted to grant developers permission to see SQL Agent jobs, but not to mess with them...

USE msdb;
GO
EXECUTE sp_addrolemember @rolename = 'role', @membername = 'username'
GO

Where role is -

SQLAgentReaderRole - allows users to see all jobs
SQLAgentUserRole - allows users to create jobs and to manage the jobs that they create.
SQLAgentOperatorRole - allows users all of the rights assigned to the SQLAgentReaderRole along with the permissions necessary to execute local jobs that they do not own.

Wednesday, 9 July 2008

SQL Server Security Vulnerability

4 security patches from MS were released yesterday...

SQL Server Security Update >

MS08-040 : SQL (patch to prevent elevation of privileges)
http://www.microsoft.com/technet/security/bulletin/ms08-040.mspx


The others >

MS08-037 : DNS (vista unaffected). All vendors (red Hat, Sun etc) released DNS patches yesterday.
http://www.microsoft.com/technet/security/bulletin/ms08-037.mspx

MS08-038 : Windows Explorer (remote code execution)  
http://www.microsoft.com/technet/security/bulletin/ms08-038.mspx

MS08-039 : Exchange    
http://www.microsoft.com/technet/security/bulletin/ms08-039.mspx

More @ http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9107838&pageNumber=1

Monday, 7 July 2008

SQL Server Query Plan Caching

What is in the SQL Server Query Plan Cache ?

SQL 2000
SELECT * FROM syscacheobjects 

SQL 2005+
SELECT * FROM SYS.DM_EXEC_CACHED_PLANS 

Summing it up (SQL 2005+)
SELECT
OBJTYPE AS Object_Type,
COUNT(*) as Plan_Count,
SUM(CAST(SIZE_IN_BYTES AS BIGINT))/1024/1024 AS Size_MB,
AVG(usecounts) as Average_Use_Count
FROM
SYS.DM_EXEC_CACHED_PLANS
GROUP BY
OBJTYPE

How do I clear those caches?
(CAREFUL! Don't use on production servers!)
-- drop cached data
DBCC DROPCLEANBUFFERS

-- clear procedure cache
DBCC FREEPROCCACHE   

-- only clear plans for adhoc sql
DBCC FREESYSTEMCACHE('SQL Plans')

Brilliant article on plans here >

http://sqlblogcasts.com/blogs/maciej/archive/2008/09/21/clearing-your-ad-hoc-sql-plans-while-keeping-your-sp-plans-intact.aspx

also - SQL Handle and Plan handle explained

Sunday, 6 July 2008

Object Schema Ownership

Detect which objects are owned by sql logins -
SELECT * FROM database.INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_OWNER IN (SELECT NAME FROM MASTER.DBO.SYSLOGINS)
GO
USE database;
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
GO

Thursday, 3 July 2008

TSQL : Tables without Non Clustered Indexes

Some simple queries to quickly reveal tables where no non clustered indexes have been created.

SQL 2005+
SELECT SCHEMA_NAME(schema_id) ,name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonClustIndex') = 0
ORDER BY 1, 2

SQL 2000
SELECT name
FROM sysobjects
WHERE type = 'U'
AND id NOT IN (SELECT id
 FROM sysindexes  
 WHERE indid > 1)