MS SQL Server Concepts and Programming Interview Questions & Answers

Download PDF

Many tables require an ID column to assign a unique ID number for each row in the table. For example, if you have a table to hold forum member profiles, you need an ID number to identify each member. To allow SQL Server to automatically assign a new ID number for each new record, you can define the ID column with IDENTITY attribute as shown in the following sample script:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "CREATE TABLE ggl_users ("
. " id INTEGER IDENTITY NOT NULL"
. ", name VARCHAR(80) NOT NULL"
. ", email VARCHAR(80) NULL"
. ", time DATETIME NULL"
. ")";
$res = mssql_query($sql, $con);
if (!$res) {
print("Table creation failed with error: ");
print(" ".mssql_get_last_message()." ");
} else {
print("Table ggl_users created. ");
}

mssql_close($con);
?>

If you want to query information stored in multiple tables, you can use the SELECT statement with a WHERE condition to make an inner join. Assuming that you have 3 tables in a forum system: "users" for user profile, "forums" for forums information, and "posts" for postings, you can query all postings from a single user with a script as shown below:

<?php
$con = mssql_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

$userID = 101;
$sql = "SELECT posts.subject, posts.time, users.name,"
. " forums.title"
. " FROM posts, users, forums"
. " WHERE posts.userID = ".$userID
. " AND posts.userID = users.id"
. " AND posts.forumID = forums.id";
$res = mssql_query($sql, $con);
while ($row = mssql_fetch_array($res)) {
print($row['subject'].", ".$row['time'].", "
.$row['name'].", ".$row['title']." ");
}
mssql_free_result($res);

mssql_close($con);
?>

The simplest way to perform key word search is to use the SELECT statement with a LIKE operator in the WHERE clause. The LIKE operator allows you to match a text field with a keyword pattern specified as '%keyword%', where (%) represents any number of any characters. Any single quote (') in the keyword needs to be protected by replacing them with two single quotes (''). The tutorial exercise below shows you how to search for records whose "notes" contains "e":

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$key = "e";
$key = str_replace("'", "''", $key);
$sql = "SELECT id, url, notes FROM ggl_links"
. " WHERE notes LIKE '%".$key."%'";
$res = mssql_query($sql, $con);
while ($row = mssql_fetch_array($res)) {
print($row['id'].", ".$row['url'].", "
. $row['notes']." ");
}
mssql_free_result($res);

mssql_close($con);
?>

If you run this script, you will get something like this:

102, www.GlobalGuideLine.com, Nice site.
202, www.yahoo.com, It's another search engine!
301, netsc

You have seen a lots of Websites are displaying past times in days, hours and minutes. If you want to do this yourself, you can use the DATEDIFF() SQL function The following tutorial exercise shows you how to use DATEDIFF() to present a past time in days, hours, and minutes:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$submit_time = "2007-05-29 04:09:49";
$sql = "SELECT 'Posted '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())/(24*60))"
. " + ' days, '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())%(24*60)/60)"
. " + ' hours, and '"
. " + CONVERT(VARCHAR(40),"
. " DATEDIFF(minute, '$submit_time',"
. " GETDATE())%60)"
. " + ' minutes ago.'";
print(" $sql ");

$res = mssql_query($sql, $con);
if (!$res) {
print("SQL statement failed with error: ");
print(" ".mssql_get_last_message()." ");
} else

If you want to provide date and time values in a SQL statement, you should write them in the format of "yyyy-mm-dd hh:mm:ss", and quoted with single quotes ('). The tutorial exercise below shows you two INSERT statements. The first one uses a hard-code date value. The second one uses the date() function to return a date value representing current date and time.

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$notes = "Added long time ago!";
$time = "1999-01-01 01:02:03";
$sql = "INSERT INTO ggl_links (id, url, notes, time)"
. " VALUES ("
. " 301, 'netscape.com', '".$notes."', '".$time."')";
if (!mssql_query($sql, $con)) {
print("SQL statement failed with error: ");
print(" ".mssql_get_last_message()." ");
} else {
print("1 rows inserted. ");
}

$notes = "Added today!";
$time = date("Y-m-d H:i:s");
$sql = "INSERT INTO ggl_links (id, url, notes, time)"
. " VALUES ("
. " 302, 'myspace.com', '".$notes."', '".$time."')";
if (!mssql_query

Text values in SQL statements should be quoted with single quotes ('). If the text value contains a single quote ('), it should be protected by replacing it with two single quotes (''). In SQL language syntax, two single quotes represents one single quote in string literals.

The tutorial exercise below shows you two INSERT statements. The first one will fail, because it has an un-protected single quote. The second one will be ok, because a str_replace() is used to replace (') with (''):

If you want to remove a row from a table, you can use the DELETE statement with a WHERE clause to identify the row. The following sample script deletes one row:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "DELETE FROM ggl_links WHERE id = 1102";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error: ");
print(" ".mssql_get_last_message()." ");
} else {
$number_of_rows = mssql_rows_affected($con);
print("$number_of_rows rows deleted. ");
}
mssql_close($con);
?>

If you run this script, you will get something like this:

1 rows deleted.

If you run it again, no rows will be deleted. And you will get something like this:

0 rows deleted.

Updating existing rows in a table requires to run the UPDATE statement with a WHERE clause to identify the row. The following sample script updates one row with two new values:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "UPDATE ggl_links SET notes='Nice site.', counts=8"
. " WHERE id = 102";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error: ");
print(" ".mssql_get_last_message()." ");
} else {
$number_of_rows = mssql_rows_affected($con);
print("$number_of_rows rows updated. ");
}

mssql_close($con);
?>

If you run this script, you will get something like this:

1 rows updated.

The best way to query tables and loop through returning rows is to run a SELECT statement with the mssql_query() function, catch the returning object as a result set, and loop through the result with mssql_fetch_array() function in a while loop as shown in the following sample PHP script:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "SELECT id, url, time FROM ggl_links";
$res = mssql_query($sql,$con);
while ($row = mssql_fetch_array($res)) {
print($row['id'].",".$row['url'].",".$row['time']." ");
}
mssql_free_result($res);

mssql_close($con);
?>

A result set object is a logical representation of data rows returned by mssql_query() function on SELECT statements. Every result set object has an internal pointer used to identify the current row in the result set. Once you get a result set object, you can use the following functions to retrieve detail information:

* mssql_free_result($res) - Closes this result set object.
* mssql_num_rows($res) - Returns the number rows in the result set.
* mssql_num_fields($res) - Returns the number fields in the result set.
* mssql_fetch_row($res) - Returns an array contains the next row indexed by field positions. The internal pointer is moved to the next row too.

If you insert multiple rows with a single INSERT statement, you can use the mssql_rows_affected() function to find out how many rows were inserted. mssql_rows_affected($connection) returns the number of affected rows of the last INSET, UPDATE or DELETE statement.

The following tutorial script shows you report back the number of rows inserted properly:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "INSERT INTO ggl_links"
. " SELECT id+1000, REVERSE(url), notes, counts, time"
. " FROM ggl_links WHERE id > 1000";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error: ");
print(" ".mssql_get_last_message()." ");
} else {
$number_of_rows = mssql_rows_affected($con);
print("$number_of_rows rows inserted. ");
}

mssql_close($con);

If you run this script, you should get:

2 rows inserted

If want to insert rows into a table based on data rows from other tables, you can use a subquery inside the INSERT statement as shown in the following script example:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "INSERT INTO ggl_links"
. " SELECT id+1000, REVERSE(url), notes, counts, time"
. " FROM ggl_links";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error: ");
print(" ".mssql_get_last_message()." ");
} else {
print("Multiple rows inserted. ");
}

mssql_close($con);

If you run this script, the table should have 4 rows now. And you will get:

Multiple rows inserted

There are two ways to provide NULL value to a column in an INSERT statement:

* Include the column in the statement, but specify keyword NULL as the value.
* Exclude the column from the statement.

The following tutorial exercise inserts two rows. Both of them have NULL values. But they were inserted differently:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "INSERT INTO ggl_links"
. " (id, url, notes, counts, time)"
. " VALUES (101, 'www.GlobalGuideLine.com',"
. " NULL, NULL, NULL)";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error: ");
print(" ".mssql_get_last_message()." ");
} else {
print("One data row inserted. ");
}

$sql = "INSERT INTO ggl_links (id, url) VALUES ("
. " 102, 'www.GlobalGuideLine.com')";
$res = mssql_query($sql,$con);
print("Another data row inserted. ");

mssql_close($con);
?>

If you run this script, you will get:

One data row inserted.
Another data row inserted.

Based on the testing result from the previous tutorial you can find out that there is a big difference in the column definition when running CREATE TABLE statement with mssql_query():

* If CREATE TABLE is executed through mssql_query() and "NULL/NOT NULL" keyword is omitted in column definition, mssql_query() will assume NOT NULL.
* If CREATE TABLE is executed directly on SQL Server and "NULL/NOT NULL" keyword is omitted in column definition, SQL Server will use NULL as the default.

Now you have to modify the CREATE TABLE statement to create "ggl_links" again by adding NULL to columns: notes, counts, and time:

If you want to insert a row of data into an existing table, you can use the INSERT INTO statement as shown in the following sample script:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "INSERT INTO ggl_links (id, url) VALUES ("
. " 101, 'www.GlobalGuideLine.com')";
$res = mssql_query($sql,$con);
if (!$res) {
print("SQL statement failed with error: ");
print(" ".mssql_get_last_message()." ");
} else {
print("One data row inserted. ");
}

mssql_close($con);
?>

If you run this script, unfortunately, you will get an error:

SQL statement failed with error:
The statement has been terminated.

So what is wrong with the statement? The error message does not give any details. You need to take this statement to SQL Server Management Studio to try it:

USE GlobalGuideLineDatabase
GO

INSERT INTO ggl_links (id, url) VALUES (
101, 'www.GlobalGuideLine.com')
GO
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'notes',
table 'GlobalGuideLineDatabase.dbo.ggl_links

If you need to delete a table created before, you can run the DROP TABLE SQL statement using the mssql_query() function, as shown in the following sample script:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

# dropping an existing table
$sql = "DROP TABLE ggl_links";
$res = mssql_query($sql,$con);
print("Table ggl_links dropped. ");

# creating a new table
$sql = "CREATE TABLE ggl_links ("
. " id INT NOT NULL"
. ", url VARCHAR(80) NOT NULL"
. ", notes VARCHAR(1024)"
. ", counts INT"
. ", time DATETIME"
. ")";
$res = mssql_query($sql,$con);
print("Table ggl_links created. ");

mssql_close($con);
?>

If you run this script, "ggl_links" will be dropped and created again:

Table ggl_links dropped.
Table ggl_links created.

If you want to create a table in the SQL Server database, you can run the CREATE TABLE SQL statement using the mssql_query() function, as shown in the following sample script:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

# creating a new table
$sql = "CREATE TABLE ggl_links ("
. " id INT NOT NULL"
. ", url VARCHAR(80) NOT NULL"
. ", notes VARCHAR(1024)"
. ", counts INT"
. ", time DATETIME"
. ")";
$res = mssql_query($sql,$con);
if (!$res) {
print("Table creation failed with error: ");
print(" ".mssql_get_last_message()." ");
} else {
print("Table ggl_links created. ");
}

mssql_close($con);
?>

If you run this script for the first time and there is no existing table called ggl_links in the database, you will get:

Table ggl_links created.

If you run it again, you will get:

Table creation failed with error:
There is already an object named 'ggl_links' in the
database.

The result set object returned by a SELECT statement also contains column (field) names, lengths and types. You can use mssql_field_name(), mssql_field_length() and mssql_field_type() to get those information. The tutorial exercise below shows a good example:

$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "SELECT * FROM sys.objects"
. " WHERE type_desc='USER_TABLE'";
$res = mssql_query($sql, $con);
print("Result set columns: ");
for ($i=0; $i

Run this PHP script, you will see a list columns (fields):

Result set columns:
name, char, 255
object_id, int, 4
principal_id, int, 4
schema_id, int, 4
parent_object_id, int, 4
type, char, 2
type_desc, char, 120
create_date, datetime, 8
modify_date, datetime, 8
is_ms_shipped, bit, 1
is_published, bit, 1
is_schema_published, bit, 1

Once the result set is captured in an object, you can think of it as a "table" with rows and columns (fields). You can use mssql_result() to retrieve the value of any given row and column (field) with this formats:

$value = mssql_result($res, $row, $column);
#- $row is the row number, starting with 0
#- $column is the column number, starting with 0

The tutorial PHP script below shows you how to list tables in the database with multiple field values:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "SELECT * FROM sys.objects"
. " WHERE type_desc='USER_TABLE'";
$res = mssql_query($sql, $con);
print("User Tables: ");
for ($i=0; $i

If you run this script, you will get something like:

User Tables:
ggl_rates, 85575343, USER_TABLE
ggl_team, 165575628, USER_TABLE
ggl_random, 821577965, USER_TABLE
ggl_links_indexed, 1061578820, USER_TABLE
ggl_links, 1093578934, USER_TABLE
ggl_links_copy, 1253579504, USER_TABLE
tipBackup2, 2121058592, USER_TABLE

If the returning output of a query statement is captured in a result set object, you can use mssql_fetch_array() to loop through each row in the output.

The tutorial PHP script below shows you how to list tables in the database:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

$sql = "SELECT * FROM sys.objects"
. " WHERE type_desc='USER_TABLE'";
$res = mssql_query($sql, $con);
print("User Tables: ");
while ($row = mssql_fetch_array($res)) {
print(" ".$row{'name'}." ");
}
mssql_free_result($res);

mssql_close($con);
?>

If you run this script, you will get something like:

User Tables:
ggl_rates
ggl_team
ggl_random
ggl_links_indexed
ggl_links
ggl_links_copy
tipBackup2

When you call mssql_query() to execute a SQL statement, and the execution failed on the SQL Server, you can use mssql_get_last_message() function to retrieve the error messages.

The tutorial script below shows you a good example:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

# dropping a table
$sql = 'DROP TABLE fyi.center';
$res = mssql_query($sql, $con);
if (!$res) {
print("Execution failed: ");
print(" Error: ".mssql_get_last_message()." ");
} else {
print("Execution was successful. ");
}

mssql_close($con);
?>

Once you have created a connection object, you can use the mssql_query() function to send a SQL statement to the SQL Server linked to the connection object for execution.

Here is a simple PHP script that creates a new schema and a new table:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);

# creating a new schema
$sql = 'CREATE SCHEMA ggl';
mssql_query($sql, $con);

# creating a new table
$sql = 'CREATE TABLE globalguideline (name VARCHAR(80))';
mssql_query($sql, $con);

mssql_close($con);
?>

If you run this script for the first time, it will execute those two statements correctly for you. But if you run it again, you will some warning messages:

Warning: mssql_query(): message: There is already an object
named 'ggl' in the database. (severity 16)
in C: estglobalguideline.php on line 7

Warning: mssql_query(): message: CREATE SCHEMA failed
due to previous errors. (severity 16)
in C: estglobalguideline.php on line 7

The messages are very clear and easy to understand.

The first thing after you have created a connection object to the SQL Server is to select the database where your tables are located, by using the mssql_select_db() function. If your MSSQL server is offered by your Web hosting company, they will assign an empty database to you and provide you the database name. You should use this name to select this empty database as your current database. The following script shows you how to select a database called "GlobalGuideLineDatabase".

To test the mssql_select_db() function, try the following script:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
mssql_select_db('GlobalGuideLineDatabase', $con);
$sql = 'SELECT * FROM sys.tables';
if ($res = mssql_query($sql, $con)) {
print(mssql_num_rows($res) . " tables in database. ");
} else {
print("SQL failed. ");
}
mssql_close($con);
?>

You will get something like this:

10 tables in database.

When you call mssql_connect(), it will return an object representing the connection to the SQL Server. This connection object will be used by subsequent MSSQL function calles. When you are done with this connection, you should close it to free up resources by calling the mssql_close() function.

The tutorial script below shows you how to call mssql_connect() and mssql_close() to connect and disconnect to the SQL Server:

<?php
$con = mssql_connect('LOCALHOST','sa','GlobalGuideLine');
if (!$con) {
print("Connection failed with error. ");
} else {
print("The SQL Server connection object is ready. ");
mssql_close($con);
}
?>

If you run this script and get this output: "The SQL Server connection object is ready", your connection to the SQL Server are working.

Note that 'sa' and 'GlobalGuideLine' used in this script are system administrator login name and password. You may use any other login name and password defined on the SQL Server.

If you look at the PHP 5 manual, you will see a group of functions listed under the Microsoft SQL Server Functions section. The commonly used functions are:

mssql_connect ? Open MS SQL server connection mssql_close ? Close MS SQL Server connection mssql_select_db ? Select MS SQL database mssql_query ? Send MS SQL query mssql_num_rows ? Gets the number of rows in result mssql_fetch_row ? Get row as enumerated array mssql_fetch_assoc ? Returns an associative array of the current row in the result mssql_fetch_array ? Fetch a result row as an associative array, a numeric array, or both mssql_fetch_object ? Fetch row as object mssql_free_result ? Free result memory mssql_num_fields ? Gets the number of fields in result mssql_field_name ? Get the name of a field mssql_field_type ? Gets the type of a field mssql_field_length ? Get the length of a field mssql_fetch_field ? Get field information mssql_get_last_message ? Returns the last message from the server mssql_rows_affected ? Returns the number of records affected by the query

Some of the functions will be discussed in this tutorial collection.