Programming Stuff

10 Useful Queries - SQL Server 2005

1. To get all the Details of Primary Keys, Foreign Keys, Stored Procedures, TRIGGERS, Tables, Views Present in a Database:

SP_HELP [ OR ]

select name,xtype from sysobjects order by xtype

2. To Display all the System Tables, System Views, User Created Tables and User Created Views.

SP_TABLES

3. To get the List of All Triggers Applied on a …[/OR]

[ Full reading ]

1. What’s the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

2. Write a …

[ Full reading ]

How to rebuild Master Database?
Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.
In the Rebuild Master dialog box, click Browse.
In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was …

[ Full reading ]

What are the authentication modes in SQL Server? How can it be changed?
Windows mode and mixed mode (SQL & Windows).

To change authentication mode in SQL Server click Start, Programs, and Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration …

[ Full reading ]

What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

What are different types of Collation Sensitivity?
Case sensitivity
A and a, B and b, etc.

Accent …

[ Full reading ]

What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than …

[ Full reading ]

Ways to improve SQL performance

1. Normalizing:

Normalize your table’s schema in such a way that all tables are reduced in columns and are related to other table with some reference.

That will improve performance while you are fetching data from tables that will also reduce the fetching of redundant data.

2. Define Primary and Foreign keys:

Make relation of your table in such …

[ Full reading ]