SQL Resource Online: May 2005

Thursday, May 26, 2005

Enable or Disable Triggers

Specifies that trigger_name is enabled or disabled. When a trigger is disabled it is still defined for the table; however, when INSERT, UPDATE, or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled

Syntax:

Enable a specific trigger
ALTER TABLE <table_name> ENABLE TRIGGER <trigger_Name>

Disable a specific trigger
ALTER TABLE <table_name> DISABLE TRIGGER <trigger_Name>

Enable all triggers
ALTER TABLE <table_name> ENABLE TRIGGER ALL

Disable all triggers
ALTER TABLE <table_name> DISABLE TRIGGER ALL

Wednesday, May 25, 2005

SQL Server TechCenter

The SQL Server TechNet site has been republished as a TechCenter. Just as MSDN DevCenters focus on information for developers, TechCenters focus on information for IT professionals such as DBAs. The site layout of the SQL Server TechCenter is similar to those already published for Exchange and Windows Server 2003. The TechCenters will evolve over time to incorporate customer feedback and deliver new information as it is published.

One requirement for TechCenters is to organize their content into 8 content areas, such as Getting Started and Operations. SQL Server customers often work in one technology at a time (Database Engine, Analysis Services, DTS, etc.) and want to focus on content for that technology. The SQL Server TechCenter uses a technique from the Windows Server TechCenter to support both navigation paths:

Browse by Task

Has one navigation page for each content area, with links to Web content relevant to IT professionals performing those types of tasks. To support technologies, the sets of links on each task navigation page are organized by technology. There is also a Technical Library fly-out in the left-hand column that provides links to the Browse by Task pages.

Browse by Technology

Has one navigation page for each technology, with links to Web content relevant to that technology. To support the content areas, the sets of links on each task navigation page are organized by task. There is also a Technologies fly-out in the left-hand column that provides links to the Browse by Technology pages.

The new TechCenter should improve your ability to find IT professional information for SQL Server. The new navigation pages will, for the first time, give IT professionals a set of links to all the major SQL Server content on the Web, including links to the relevant major nodes in the copy of the SQL Server Books Online in the MSDN Library.

SQL Server 2000 Licensing FAQ

Review this FAQ to find answers to some common questions about licensing SQL Server 2000.
Select a question from the list to show the answer. You can also view the answers to all of the questions by selecting the following check box

Tuesday, May 24, 2005

Primary Key vs Unique Key

Primary Key creates a clustured Index on a table when it is created
Unique Key creates a non-clustured Index on a table when it is created

Primary Key doesn't allow NULL values
Unique Key allows only one NULL value

Thursday, May 19, 2005

Maximum Capacity Specifications

The first table specifies maximum capacities that are the same for all editions of Microsoft® SQL Server™ 2000. The second and third tables specify capacities that vary by edition of SQL Server 2000 and the operating system.

This table specifies the maximum sizes and numbers of various objects defined in Microsoft SQL Server databases, or referenced in Transact-SQL statements. The table does not include Microsoft SQL Server 2000 Windows® CE Edition

Wednesday, May 18, 2005

SQL Server 2000 Resource Kit

SQL Server Resource Kit is designed for database administrators and developers who already use SQL Server and want some helpful tips, advanced techniques, and useful tools or samples to enhance their work with SQL Server 2000. It is the premier guide for deploying, managing, optimizing, and troubleshooting SQL Server 2000

Tuesday, May 17, 2005

Change owner of the database

To change the owner of the present database, use the stored procedure sp_changedbowner

Syntax:
sp_changedbowner '<login>'

The ownership for databases

Master
Model
Tempdb

cannot be changed

Only members with sysadmin role can execute the stored procedure sp_changedbowner

Wednesday, May 11, 2005

Error Handling in SQL Server

This article focuses on how SQL Server – and to some extent ADO – behave when an error occurs. If you are relatively new to SQL Server, I recommend that you start with Implementing.... The article here gives a deeper background and may answer more advanced users' questions about error handling in SQL Server

Tuesday, May 10, 2005

Remove Spaces - TRIM

There is no single function to remove leading and trailing spaces from the field. For this you need to:

Remove Training spaces using LTRIM(<column>)
Remove Leading spaces using RTRIM(<column>)

SELECT LTRIM(RTRIM(<column>))

Editions of SQL Server 2000

Microsoft® SQL Server™ 2000 is available in these editions

Find the Version of SQL Server

To find the version of the SQL Server, use the global variable @@version

SELECT @@version

Enable or Disable a Constraint

-- Enable all table constraints
ALTER TABLE <table_name> CHECK CONSTRAINT ALL

-- Disable all table constraints
ALTER TABLE <table_name>NOCHECK CONSTRAINT ALL

-- Disable a specific constraint
ALTER TABLE <table_name>NOCHECK CONSTRAINT <constraint_name>

-- Enable a specific constraint
ALTER TABLE <table_name>CHECK CONSTRAINT <constraint_name>

Monday, May 09, 2005

Rename user-created objects in SQL Server

Rename user-created object (for example, table, column, or user-defined data type) in the present database:

Syntax:
sp_rename oldName, newName, [Object]

Rename Table
sp_rename 'Employee', 'Employees'
or
sp_rename 'Employee', 'Employees', 'TABLE'

Rename Column
sp_rename 'EName', '[Emp Name]', 'COLUMN'

List Available Databases

To List the datbases available in your SQL Server and show the path of .MDB file:

SELECT [name] [Database], [filename] [File Path]
FROM master..sysdatabases

: Name: Chandra Ananthapatnayakuni; Location: Hyderabad, Andhra Pradesh, India

I did my Engineering in Computer Science and Engineering from (Vellore Engineering College) Madras University in 1998. Since the Third year of my Engineering I was working on various Microsoft Technologies such as VB, VC++ and SQL Server. I have more than 8 years of IT experience My core areas of interest are Query Optimization and Performance Tuning. Presently I am working for Computer Sciences Corporation, Hyderabad. Prior to CSC I worked for Wipro and IBM Global Services, Bangalore. I actively participate in Microsoft SQL Server Newsgroups and User groups from Hyderabad and Bangalore I am founder and publisher of my site www.sqlresource.com, which I just started working on. You can find my blogs on http://chanduas.blogspot.com/

View my complete profile

SQL Resource Online