SqlServer SourceCode - sqlserverdude.blogspot.com

SQL Management Studio Editions

2009-09-10T01:26:00.003-07:00

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?

2009-09-10T01:26:00.001-07:00

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

2009-09-10T01:25:00.006-07:00

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

2009-09-10T01:25:00.005-07:00

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

2009-09-10T01:25:00.003-07:00

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?

2009-09-10T01:25:00.001-07:00

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?

2009-09-10T01:24:00.002-07:00

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

2009-09-10T01:24:00.001-07:00

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 ?

2009-09-10T01:23:00.004-07:00

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

2009-09-10T01:23:00.003-07:00

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?

2009-09-10T01:23:00.001-07:00

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.

2009-03-02T23:28:00.001-08:00

Specify the indexed columns in the WHERE clause of query.

What is OLTP(OnLine Transaction Processing)

2009-03-02T23:25:00.000-08:00

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

2009-03-02T23:24:00.000-08:00

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.

2009-03-02T23:23:00.001-08:00

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

2009-03-02T23:21:00.000-08:00

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

2009-03-02T23:20:00.001-08:00

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?

2009-03-02T23:19:00.001-08:00

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

2009-03-02T23:16:00.000-08:00

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.

2009-03-02T23:15:00.001-08:00

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

2009-03-02T23:13:00.002-08:00

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

2009-03-02T23:13:00.001-08:00

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

2009-03-02T23:12:00.001-08:00

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?

2009-03-02T23:11:00.001-08:00

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

2009-03-02T23:10:00.000-08:00

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