SQL Management Studio Editions

SQL Management Studio comes in two flavours:

1. SQL Management Studio

This edition comes with all versions of SQL Server 2005 except SQL Server 2005 Express Edition. This edition has various advanced features like import data from other database systems, export data to other databases etc.

2. SQL Management Studio Express

SQL Management Studio Express is part of SQL Server 2005 Express Advanced edition. You can download Management Studio Express as a separate tool also. Some of the key features like Import data, export data etc are missing in the Express edition of Management Studio.

For learning purposes and development purposes, the express edition serves the purpose. After all, it is FREE !

The non express edition of the management studio is also free when you purchase non express editions of SQL Server 2005, but you have to pay several thousand dollars for the SQL Server license.

What is SQL Management Studio?

As you already learned, the core part of SQL Server system is the 'server' which runs as a service. The service is a hidden application that runs in the background and it is hard to deal with this service directly.

SQL Server 2005 comes with a tool called 'Management Studio' which will help you manage your SQL Server. SQL Management Studio allows you to perform various actions on SQL Server including:

1. Create/Delete databases
2. Backup/restore databases
3. Attach/detach databases
4. Design tables and edit data
5. Execute queries against any database

SQL Management studio has a lot more features than explained above. Some of the main functionalists mentioned above are explained in coming chapters.

SQL Management Studio allows you to manage multiple instances of SQL Server installed on same computer or different computers. To manage a specific instance of SQL Server, you have to first connect to the instance using appropriate login and password. You need to install management studio only once even if you install multiple instances of SQL Server.

Limitations of SQL Server Express

SQL Server Express is a free, easy to use, redistributable version of SQL Server 2005 designed for building simple data-driven applications. SQL Server Express has most of the features offered by SQL Server full version, but with certain limitations. This makes SQL Server Express a good choice for small and medium scale applications.

Most of the limitations of SQL Server Express edition will not affect the small/medium scale applications.

Limitations of SQL Server Express Edition

1. Number of CPUs supported

SQL Server Express uses only one CPU at a time. It can be installed on a server with multiple CPUs, but it will use only one CPU at a time.

2. Maximum memory used

SQL Server Express uses a maximum of 1 GB memory for it's data buffer. So, if your server has severaql GB memeory, SQL Server Express cannot take advantage of it.

4. Database size limit

Maximum database size is limited to 4 GB (log file size is not counted)

5. Profiler in SQL Server Express

Profiler tool is not included with SQL Server Express editions.

6. Job Scheduler

Job Scheduling service is not available with SQL Server Express.

7. Import/Export

Data import and export feature is not available with SQL Server Express.

A complete feature comparison of various editions of SQL Server can be found at the Microsoft web site - http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

SQL Server 2005 Express

SQL Server Express is a free, easy to use, redistributable version of SQL Server 2005 designed for building simple data-driven applications. Developers can design schemas, add data, and query local databases, and perform most of the features supported by SQL Server 2005. If developers need more advanced database features, then SQL Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server.

Microsoft SQL Server 2005 Express Edition (SQL Server Express) is a database platform that is based on Microsoft SQL Server 2005. It is also a replacement for Microsoft Desktop Engine (MSDE).

SQL Server Express comes with a graphical user interface to manage the database system. It is called 'SQL Server Management Studio Express".

SQL Server Express and Management Studio Express are free to download and distribute. If you like to upgrade SQL Server Express databases to full version of SQL Server, it is very straight forward. Just install SQL Server 2005 and your databases and code will work without any changes.

You can download SQL Server Express and Management Studio Express from Microsoft website.

If you are looking for a free, powerful database system for your application, SQL Server Express if the way to go!

Manage SQL Server services using SQL Service Manager

In one of the previous chapters, you learnt how to use Windows Service Control Manager to start/stop SQL Server services.

Microsoft provides a special tool called 'SQL Server Service Manager'. This tool does almost the same thing - allow you to Start, Pause or Stop the SQL Server services.

The SQL Service Control Manager can manage SQL Servers installed onany computer in the same network. (Windows Service Control displays all services installed on the same computer).

The below image shows the screen shot of SQL Service Control Manager.



In the Server drop down, you can type or select the IP Address of any SQL Server in your network. When you select an IP Address, it will display all SQL Servers installed on that machine. Then you can choose the options like Start or Stop.

When SQL Service Control Manager is running, you can see an icon in the system tray as shown below:



The icon will have a green arrow when the currently selected SQL Server is running. If the currently selected SQL Server is stopped, it will have a red color arrow.

If the SQL Service COntrol Manager is not running, you can start the it from the programs menu > Microsoft SQL Server -> Service Manager.

How to start and stop SQL Server service?

A "service" is an application that can start automatically when the computer starts.

There are two start up modes:

1. Automatic - the service starts automatically when the computer starts.
2. Manual - the service is installed, but will not start until somebody manually start it.

By default, SQL Server is configured as "Automatic". So, SQL Server service starts when you start your computer. However, some developers will change it to "Manual" mode for various reasons. One example is, if you have a very slow computer, you may want to stop all unwanted applications running. So, you can go and stop the SQL Server service. Also, you can change it to "manual" mode so that it won't start automatically everytime you reboot your computer. However, when you want to run an application that need to access SQL Server, you have to go to the service control manager and start the service.

Service Control manager

"Service control manager" is a windows tool that allow you manage all services (including SQL Server service) installed on the computer.

To open the service control manager, go to Control Panel -> Administrative Tools -> Services.



You can see the list of all installed services and their current status. Right click on any service name and see the context menu. You can see various options like Start, stop, restart, pause etc.

The above image shows several service names starting with the word "SQL Server...". They are various services related to SQL Server. You can right click on the service name and select any task like Start or Stop.

SQL Server Instances?

SQL Server (service) can be installed multiple times on the same computer. Each installation is called an 'Instance'. When you install SQL Server each time, a new windows service is installed.

Each instance of the SQL Server can have it's own databases, security, users etc. Each instance one can be stopped and started without affecting other instances.

SQL Server 2005 allows to install up to 50 instances on the same computer.

Instances are classified to two categories:

1. Default instance (no name)
2. Named instance

You can have only one default instance and all other instances should have a unique name. You have the option of selecting default instance or named instance while installing SQL Server.

In most cases, you will need to install only one instance (default instance). Multiple instances are useful if you have multiple departments or multiple clients sharing the same computer and do need to manage their SQL Server instance without affecting other departments/clients.

You can access the default instance of SQL Server using one of the following names. Assume the IP Address of the computer is 192.168.2.100 and computer name is DevServer1.

(local)
. (dot)
localhost
127.0.0.1
DevServer1
192.168.2.100

Any of the above names can be used if the SQL Server is installed on the same computer. if it is remote computer, then you can access it using only the IP Address or computer name (last 2 options). The IP Address 127.0.0.1 is a spacial IP Address to represent the local computer. Even though this is not a real IP Address, you can represent the local computer using this Ip Address.

The named instances can be accessed by appending the name of the instance to one of the above with a backslash, as shown below:

(local)\INSTANCENAME
.\INSTANCENAME
localhost\INSTANCENAME
127.0.0.1\INSTANCENAME
DevServer1\INSTANCENAME
192.168.2.100\INSTANCENAME

SQL Server Editions

SQL Server 2005 Enterprise Edition

Enterprise Edition scales to very high performance levels required to support the

largest enterprise online transaction processing (OLTP), highly complex data

analysis, data warehousing systems, and Web sites.

SQL Server 2005 Standard Edition

SQL Server 2005 Standard Edition is best suited for small and medium sized

organizations. It scales to meet reasonable volume and load of data.

SQL Server 2005 Workgroup Edition

SQL Server 2005 Workgroup Edition is the data management solution for small

organizations that need a database with no limits on size or number of users.

SQL Server 2005 Developer Edition

SQL Server 2005 Developer Edition has all features of Enterprise edition, but it is

licensed to use only for development and testing purposes and not for deployment on

production systems.

SQL Server 2005 Express Edition

The SQL Server Express edition is a replacement for the MSDE (free database system).

This edition is based on SQL Server 2005 and offers a lot more features than MSDE.

Even though Microsoft claims that is edition can be used only for small

applications, a smart design and appropriate caching will make this a smart choice

for several types of applications. Several websites have switched from MS Access or

MySql to SQL Server 2005 Express since it is free and offers lot of features of SQL

Server 2005.

SQL Server Express is free and can be redistributed (subject to some agreement), and

functions as the client database, as well as a basic server database. SQL Server

Express is an ideal choice for deploying with small client-server applications as

well as small web sites.

SQL Server 2005 Compact Edition

SQL Server Compact Edition is the compact database server for smart devices. SQL

Server Compact Edition is capable of replicating data with SQL Server 2005 and SQL

Server 2000.

SQL Server 2005 Runtime Edition

SQL Server 2005 Runtime Edition is a special edition designed for Microsoft ISV

partners. Under the end user license agreement for SQL Server 2005 Runtime Edition,

an independent software vendor (ISV) may embed SQL Server code into their software

but end users are not allowed to use this instance of SQL Server to access from any

other applications.

Among various versions mentioned above, SQL Server 2005 Expression is the only free

version. Enterprise and standard editions can cost several thousand dollars

depending on the number of users and number of CPUs in the system.

What is MSDE ?

MSDE stands for Microsoft Desktop Engine. MSDE is replaced with Microsoft SQL Server

2005 Express.

MSDE is a free, light weight version of SQL Server. It lacks several advanced

features of SQL Server, but still lot of people use MSDE since it is a free version.

MSDE Facts

# Microsoft does not provide a graphical user itnerface (like SQL Enterprise

Manager) to manage the databases. However, there are several third party management

tools available. Or, you can use a console based interface provided by Microsoft.
# Performance throttling prevents too many users accessing database at the same time

(Microsoft does not want you to use MSDE for high traffic applications ! Instead,

they want you to buy SQL Server)
# MSDE is based on SQL Server engine, but is a scaled down version of SQL Server.
# Migrating from MSDE to SQL Server is very easy. All you have to do is, uninstall

MSDE and install SQL Serer. Your databases and code will work without any change.
# MSDE is not for sale as a separate product. It is available for royalty-free

redistribution by vendors under certain MSDE licensing conditions.
# MSDE allows a maximum database size of 2 GB
# MSDE can use a maximum of 2 GB RAM
# Supports only 2 CPUs on the server.
# Features like Full-text search, profiler, import/export wizards, index tuning

wizard etc not available.

MSDE is an outdated database system. If you are looking for free relational database

system, you may want to try Microsoft SQL Server 2005 Express edition.

Compare SQL Server and MS Access

Advantages of MS Access

MS Access is a light weight database managements system. Also, it provides it's own

designer so that you can generate reports and simple windows applications without

using another programming environment.

It is easy to deploy an MS Access database. Just copy the .MDB file to appropriate

location and you are done. There is no database attach/detach process.

Which one to choose - MS Access or SQL Server ?

MS Access is ideal only for applications that require very small databases. Even

though the maximum database size limit for MS Access is 1 GB, the performance

becomes poor if the size grows more than few hundred MBs.

If you expect your application database to grow more than 100 MB in future, you

should consider using SQL Server. It may not be an easy task to migrate to SQL

Server later. Eeven though SQL Server allow you to import your MS Access database to

SQL Server, you will have to do lot of code changes to take advantage of the SQL

Server features.

There will be major performance difference between MS Access and SQL Server. If you

are writing a critical application where performance is important, then you must

consider SQL Server instead of MS Access.

The new SQL Server 2005 Express is a free version and allow copy/deploy feature. You

can copy the database file and attach on the fly. It is a good idea to use SQL

Server 2005 Express if you cannot afford to purchase SQL Server license. It has much

better peformance and features compared to MS Access.

The bottom line is, choose MS Access only for small and non critical applications.

What is SQL Server?

SQL Server is one of the most popular and advanced database systems currently

available. SQL Server is provided by Microsoft.

Microsoft SQL Server is sometimes called as "Sequel Server". It can be managed using

Structured Query Language.

While MS Access is meant for small applications, SQL Server supports large

applications with millions of users or huge databases. SQL Server is much more

powerful than Access and provides several other advanced features and much better

security. SQL Server is compatible with MS Access. You can easily import/export data

between these two.

SQL Server is a Relational database where data is stored and retrieved very

efficiently.

How do you select a row using indexes.

Specify the indexed columns in the WHERE clause of query.

What is OLTP(OnLine Transaction Processing)

In OLTP - online transaction processing systems relational database design use the discipline of data
modeling and generally follow the Codd rules of data normalization in order to ensure absolute data
integrity. Using these rules complex information is broken down into its most simple structures (a table)
where all of the individual atomic level elements relate to each other and satisfy the normalization
rules.

What is the basic functions for master, msdb, model, tempdb databases

The Master database holds information for all databases located on the SQL Server instance and is the
glue that holds the engine together. Because SQL Server cannot start without a functioning master
database, you must administer this database with care.
The msdb database stores information regarding database backups, SQL Agent information, DTS
packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in
the instance.

What are the different types of replication? Explain.

The SQL Server 2000-supported replication types are as follows:
· Transactional
· Snapshot
· Merge
Snapshot replication distributes data exactly as it appears at a specific moment in time and does not
monitor for updates to the data. Snapshot replication is best used as a method for replicating data that
changes infrequently or where the most up-to-date values (low latency) are not a requirement. When
synchronization occurs, the entire snapshot is generated and sent to Subscribers.
Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data
modifications are made at the Publisher, the individual transactions are captured and propagated to
Subscribers.
Merge replication is the process of distributing data from Publisher to Subscribers, allowing the
Publisher and Subscribers to make updates while connected or disconnected, and then merging the
updates between sites when they are connected.
What are the OS services that the SQL Server ins

Properties of Sub-Query

A subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator, and
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.

What kind of User-Defined Functions can be created

There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-
Valued and Multi-statement Table-valued.

What is User Defined Functions?

User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters
and return a single scalar data value or a table data type.

Difference between Function and Stored Procedure

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as
Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF's can be though of as views that take parameters and can be used in JOINs and other
Rowset operations.

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely.Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

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.

How do you implement one-to-one, one-to-many and many-to-many

One-to-One relationship can be implemented as a single table and
rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into
two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with
the keys from both the tables forming the composite primary key of the
junction table.

What is denormalization and when would you go for it

As the name indicates, denormalization is the reverse process of
normalization. It's the controlled introduction of redundancy in to
the database design. It helps improve the query performance as the
number of joins could be reduced.

What is BCP? When do we use it?

BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.

What are the authentication modes in SQL Server

Windows mode and mixed mode (SQL & Windows). users names and passwords will stored in master db in the sysxlogins table.

What is a Linked Server

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.

When do you use SQL Profiler

SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..

What is a Join in SQL Server

Join actually puts data from two or more tables into a single result set.

What do you mean by COLLATION

Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.

What command do we use to rename a db

sp_renamedb ‘oldname’ , ‘newname’

What is the use of DBCC commands?

DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

How Can we use Truncate command on a table which is referenced by FOREIGN KEY?

We cannot use Truncate command on a table with Foreign Key because of referential integrity.

When do we use the UPDATE_STATISTICS command

This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What are cursors

Cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.

What is an Index

When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index. There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered. The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

What is a View

If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.

What is a Trigger

Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.

What is Normalization

A relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.

What is Normalization

A relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.

How to copy data from one table to another

select * into table2 from table1

How to Find all the Views within a Database

select * from sysobjects where xtype='v'

how to select the second largest salary from a table?

Select Top 1 from Employee where Salary Not IN ( select Top 1 from Employee order by Salary Desc)order by Salary Desc
OR
select max(sal) from employee where sal != (select max(sal) from employee)
OR
Select Max(salary)From EmployeeWhere salaryNOT IN (Select max(salary) From Employee)

Types of Constraints

1.PRIMARY constraint
2.FOREIGN constraint
3.UNIQUE constraint
4.CHECK constraint
5.DEFAULT constraint

Types of Joins

1.Natual Join
2.Outer Join
3.Self Join
4.Equi Join

Types of Transaction Isloation level in SQL server

Four Types of Transaction Isloation level

1)READ UNCOMMITTED
2)READ COMMITTED
3)REPEATABLE READ
4)SERIALIZABLE

Difference between Delete and Truncate

a..DELETE command can be rolled back but TRUNCATE cannot be rolled back
b..Delete is a DML command. Truncate is a DDL command.
c..Delete will delete one by one row and also return the no of row deleted but truncate will drop table and recreate it it will not return the no of rows deleted hence truncate is faster then delete
d.. DELETE we can specify WHERE clause to delete selective rows whereas with TRUNCATE we can’t.

Type of cursors

1)Static cursors
2)Dynamic cursors
3)Forward-only cursors
4)Keyset-driven cursors

Static cursors detect few or no changes but consume relatively few resources while scrolling, although they store the entire cursor in tempdb.
Dynamic cursors detect all changes but consume more resources while scrolling, although they make the lightest use of tempdb.
Keyset-driven cursors lie in between, detecting most changes but at less expense than dynamic cursors.

How Three command to get version or operating system related information

Select @@Version
Or
EXEC sp_MSgetversion
or
Exec xp_msver

How command to get all table names from a database

Select * from sysObjects where xtype='u'

What are sub-queries? Give example?

Sub-query means a Query within a Query.
This is the Example:
Select Employee_Id, Employee_name From Employees Where Employee_Id IN (Select Mgr_Id from Manager)