Daily Dose of Yukon - SQL Server 2005 Tips

USE [tempdb];
GO
CREATE TABLE dbo.tblTest 
   (c1 uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    c2 uniqueidentifier NOT NULL DEFAULT NEWID());
GO
DECLARE @Counter INT
SET @Counter = 100
WHILE @Counter > 0
BEGIN
   INSERT INTO dbo.tblTest DEFAULT VALUES;
   SET @Counter = @Counter - 1
END
GO
SELECT * FROM dbo.tblTest;
GO
DROP TABLE dbo.tblTest;
GO

• You can use NEWSEQUENTIALID() to generate GUIDs to reduce page contention at the leaf level of indexes. Since the values are generated sequentially (instead of random values), NEWSEQUENTIALID() makes the column more suitable for indexing. Sequential values lead to less page splits, thus less fragmentation and clustered index performance should improve as they'll be inserted ascending and in sort order.
• When used with a ROWGUIDCOL column in Merge Replication, NEWSEQUENTIALID() can provide increased performance when making and tracking changes.

• NEWSEQUENTIALID() can only be used with DEFAULT constraints on table columns of type uniqueidentifier. It cannot be referenced in queries.
• It is possible to guess the value of the next generated GUID, and therefore access data associated with that GUID. If privacy is a concern, do not use this function.

• SQL Server Database Services (sqlservr.exe):
  
  Default instance: MSSQLSERVER
  
  Named instance: MSSQL$InstanceName
  
  SQL Server Database Engine.
• SQL Server Agent (SQLAGENT90.EXE):
  
  Default instance: SQLServerAgent
  
  Named instance: SQLAgent$InstanceName
  
  Executes jobs, monitors SQL Server, fires alerts, and allows automation of some administrative tasks.
• Analysis Services (msmdsrv.exe):
  
  Default instance: MSSQLServerOLAPService
  
  Named instance: MSOLAP$InstanceName
  
  The service that provides online analytical processing (OLAP) and data mining functionality for business intelligence applications.
• Reporting Services (ReportingServicesService.exe):
  
  Default instance: ReportServer
  
  Named instance: ReportServer$InstanceName
  
  Manages, executes, renders, schedules, and delivers reports.
• Notification Services (NSService.exe):
  
  Notification Services is a platform for developing and deploying applications that generate and send notifications. SQL Server Setup can install but will not configure Notification Services.
• Integration Services (MsDtsSrvr.exe):
  
  Default or named instance: MSDTSServer
  
  Provides management support for Integration Services package storage and execution.
• Full-Text Search (msftesql.exe):
  
  Default instance: MSFTESQL
  
  Named instance: MSFTESQL$instanceName
  
  Quickly creates full-text indexes on content and properties of structured and semi-structured data to allow fast linguistic searches on this data.
• SQL Server Browser (sqlbrowser.exe): Default or named instance: SQLBrowser
  
  The name resolution service that provides SQL Server connection information for client computers. This service is shared across multiple SQL Server and SSIS instances.
• SQL Server Active Directory Helper (sqladhlp90.exe):
  
  Default or named instance: MSSQLServerADHelper
  
  Publishes and manages SQL Server services in Windows active directory.
• SQL Writer (SQLWriter.exe):
  
  Allows backup and restore applications to operate in the Volume Shadow copy Service (VSS) framework. There is a single instance of the SQL Writer service for all SQL instances on the server.

• SQL Server
• SQL Server Agent
• Analysis Server
• Report Server
• Full-Text Search

• Notification Services
• Integration Services
• SQL Server Browser
• SQL Server Active Directory Helper
• SQL Writer

SELECT cpu_count AS [Logical CPUs], 
       hyperthread_ratio/cpu_count AS [Physical CPUs]
FROM sys.dm_os_sys_info;

ALTER LOGIN [sa] DISABLE;
GO

ALTER LOGIN [sa] WITH NAME = [super-user];
GO

sqlwb c:\1.sql -S DDGXP\JUNECTP -d AdventureWorks -E -nosplash

• ADO.NET 2.0 Feature Matrix
  
  
• Caching Improvements in ASP.NET Whidbey
  
  
• Data Access in ASP.NET 2.0
  
  
• An Overview of the New Services, Controls, and Features in ASP.NET 2.0
  
  
• Improved Caching in ASP.NET 2.0
  
  

SELECT AVG(SickLeaveHours) FROM HumanResources.Employee
TABLESAMPLE SYSTEM (20 PERCENT)
REPEATABLE(3);
GO