Microsoft SQL Server DBA

SQL Server 2008 different editions...!

2012-01-26T17:47:00.001+05:30

SQL Server 2008 is available in the following editions:

􀁑 Enterprise Designed for the largest organizations and those needing to leverage the

full power of the SQL Server 2008 platform.

􀁑 Standard Designed for small and midsized organizations that do not need all the

capabilities available in SQL Server 2008 Enterprise.

􀁑 Workgroup Suitable for small departmental projects with a limited set of features.

􀁑 Express A freely redistributable version of SQL Server that is designed to handle

the needs of embedded applications as well as the basic data storage needs for

server-based applications, such as Web applications with a small number of users.

platform, please refer to the book Microsoft SQL Server 2008 Step by Step (Microsoft Press,

2008), which provides overview chapters on every SQL Server 2008 feature.

12 CHAPTER 1 Installing and Confi guring SQL Server 2008

􀁑 Compact Designed as an embedded database.

􀁑 Developer Designed for use by developers in creating SQL Server applications.

SQL Server 2008 Developer has all the features and capabilities as SQL Server 2008

Enterprise, except that it is not allowed to be used in a production environment.

􀁑 Evaluation Designed to allow organizations to evaluate SQL Server 2008. SQL Server

2008 Evaluation has all the features and capabilities as SQL Server 2008 Enterprise,

except that it is not allowed to be used in a production environment and it expires

after 180 days.

--
For more articles, Please Visit : http://microsoft-sql-server-dba.blogspot.com/

Good Microsoft SQL Server information sites.

2011-08-10T01:21:00.001+05:30

Good Microsoft SQL Server information sites.

http://www.mssqlcity.com/Articles/Replic/SetupSR/SetupSR.htm

http://www.w3schools.com/sql/

http://www.1keydata.com/sql/

http://vyaskn.tripod.com/iq.htm

http://users.telenet.be/pecop/sqlr/sqlbasics.html

http://blog.sqldbasupport.com/

http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

http://www.aspfaq.com/downloads.asp

http://www.java2s.com/Code/SQLServer/CatalogSQLServer.htm

--
For more articles, Please Visit : http://microsoft-sql-server-dba.blogspot.com/

Which system database of which we can't take the backup?

2011-08-06T21:39:00.001+05:30

We can't take the backup of the tempdb database and the tempdb database is a universal "scratch" area within SQL Server. The SQL Server engine utilizes tempdb as a temporary storage area when doing sorting and aggregation operations. Applications can also utilize tempdb through the use of temporary tables, procedures, and cursors. You can look at tempdb as serving a function similar to the paging file within Windows. Anything created within tempdb, by its very nature, is temporary. When you restart a SQL Server instance, tempdb is wiped out and re-created.

You should never create any object that is required to be persistent within tempdb, because you will lose anything stored in tempdb when the instance is restarted.

--
For more articles, Please Visit : http://microsoft-sql-server-dba.blogspot.com/

How to get list of Database Users

2011-08-06T21:30:00.001+05:30

The below query will list of out list of Database user's

use ReportServer

sp_helpuser

--
For more articles, Please Visit : http://microsoft-sql-server-dba.blogspot.com/

The basic differences between Primary Key and Unique key are as follows

2011-06-29T00:01:00.000+05:30

1) By default Primary Key will generate Clustured Index whereas Unique Key will Generate Non-Clustured Index.

2) Primary Key is a combination of Unique and NOT NULL Constraints so it can’t have duplicate values or any NUll Whereas for Oracle UNIQUE Key can have any number of NULL whereas for SQL Server It can have only one NULL

3) A table can have only one PK but It can have any number of UNIQUE Key.

Copy data from one table to another table

2011-06-26T17:07:00.001+05:30

The following is the query to copy the data from one table to another table.

select * into employee2 from employee1

Here Table employee1 is existing table and take backup in another table named : employee1. It will automatically will create the another table employee1 with the same structure.

Microsoft Database mirroring

2011-06-26T16:32:00.002+05:30

The database mirroring is implement only one database at time
The selected database must be in full recovery mode only (Other recovery mode won't work)
You can't mirror on system databases like (master, msdb, tempdb, model)

--
For more articles, Please Visit : http://microsoft-sql-server-dba.blogspot.com/

What is bit datatype and what's the information that can be stored inside a bit column?

2011-06-26T14:40:00.001+05:30

Bit datatype is used to store Boolean information like 1 or 0 (true or false). Until SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

--
For more articles, Please Visit : http://microsoft-sql-server-dba.blogspot.com/

What's the difference between a primary key and a unique key?

2011-06-26T14:36:00.001+05:30

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.

For more articles, Please Visit : http://microsoft-sql-server-dba.blogspot.com/

What are the steps you will take to improve performance of a poor performing query?

2011-06-20T02:49:00.000+05:30

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

Visit : http://microsoft-sql-server-dba.blogspot.com/

How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?

2011-06-20T02:28:00.001+05:30

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode.

Visit : http://microsoft-sql-server-dba.blogspot.com/

How to find the database ID

2011-06-08T21:47:00.001+05:30

How to find the database ID in MS Sql Server.

SELECT * FROM sysdatabases

Name dbid

----------------------------------

master 1

tempdb 2

model 3

msdb 4

These all are default database IDs in MS Sql Server which is assigned after installation.

Welcome to Microsoft SQL Server DBA

2011-06-04T23:03:00.001+05:30

Welcome to first post to Microsoft SQL Server DBA Blog...!