Common MS SQL Server Concepts and Programming Interview Preparation Guide
Download PDF

Learn MS SQL Server programming with hundreds of Interview Questions and Answers and examples.

394 MS SQL Server Questions and Answers:

Table of Contents:

Common  MS SQL Server Job Interview Questions and Answers
Common MS SQL Server Job Interview Questions and Answers

1 :: PHP MSSQL - How To Create an Identity Column?

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);
?>

2 :: PHP MSSQL - How To Query Multiple Tables Jointly?

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);
?>

3 :: PHP MSSQL - How To Perform Key Word Search in Tables?

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

4 :: PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes?

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

5 :: PHP MSSQL - How To Include Date and Time Values in SQL Statements?

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

6 :: PHP MSSQL - How To Include Text Values in SQL Statements?

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 (''):

7 :: PHP MSSQL - How To Delete Existing Rows in a Table?

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.

8 :: PHP MSSQL - How To Update Existing Rows in a Table?

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.

9 :: PHP MSSQL - How To Loop through Returning Rows?

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);
?>

10 :: PHP MSSQL - What Is a Result Set Object Returned by mssql_query()?

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.

11 :: PHP MSSQL - How To Get the Number of Affected Rows?

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

12 :: PHP MSSQL - How To Insert Multiple Rows with a subquery?

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

13 :: PHP MSSQL - How To Insert Data with NULL Values?

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.

14 :: PHP MSSQL - How To Make a Column Nullable?

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:

15 :: PHP MSSQL - How To Insert Data into an Existing Table?

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

16 :: PHP MSSQL - How To Drop an Existing Table?

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.

17 :: PHP MSSQL - How To Create a New Table?

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.

18 :: How To List All Field Names in the Result Set using mssql_field_name()?

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

19 :: How To Retrieve Field Values using mssql_result()?

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

20 :: How To Loop through Result Set Objects using mssql_fetch_array()?

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

21 :: How To Retrieve Error Messages using mssql_get_last_message()?

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);
?>

22 :: How To Execute a SQL Statement using mssql_query()?

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.

23 :: How To Select an Exiting Database using mssql_select_db()?

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.

24 :: How To Disconnect from a SQL Server using mssql_close()?

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.

25 :: What Are Commonly Used MSSQL Functions in PHP?

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.
MS SQL Server Interview Questions and Answers
394 MS SQL Server Interview Questions and Answers