MS SQL Server

  Home  Databases Programming  MS SQL Server


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



394 MS SQL Server Questions And Answers

341⟩ How To Create a New Schema in a Database?

If you want to create a new schema in an existing database, you can use the "CREATE SCHEMA" statement as shown in the tutorial example below:

USE GlobalGuideLineDatabase;

GO

CREATE SCHEMA ggl;

GO

Command(s) completed successfully.

A new schema called "ggl" has been created in "GlobalGuideLineDatabase" database. "ggl" is an empty schema at this moment since no objects has been moved into "ggl" yet.

 219 views

342⟩ How To List All Schemas in a Database?

If you want see all existing schemas in the current database, you can use view sys.schemas as shown in the example below:

USE GlobalGuideLineDatabase;

GO

SELECT * FROM sys.schemas;

GO

name    schema_id principal_id

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

dbo 1 1

guest 2 2

INFORMATION_SCHEMA 3 3

sys 4 4

ggl 5 1

db_owner 16384 16384

db_accessadmin 16385 16385

db_securityadmin 16386 16386

db_ddladmin 16387 16387

db_backupoperator 16389 16389

db_datareader 16390 16390

db_datawriter 16391 16391

db_denydatareader 16392 16392

db_denydatawriter 16393 16393

(14 row(s) affected)

All schemas, except "ggl", in the list were created by SQL Server.

 219 views

343⟩ What Is a Schema in MS SQL Server 2005?

A schema is a container of database objects with the following interesting related rules:

* A schema may contain different object types, like tables, indexes, views, procedures, functions, etc.

* A database user can be assigned with a default schema.

* Object names must be prefixed with schema names when referencing schemas outside your default schema.

* Every schema has a single owner (a database user). A database user may own multiple schemas.

* If you login name is mapped to the owner of a schema, you have full permissions on this schema.

* To drop a schema, you need to empty the schema first.

 207 views

344⟩ How To Create an Multi-Statement Table-Valued Function?

To create a multi-statement table-valued function, you need to define a temporary table as the returning table in the function. INSERT statements should be used to insert data into the returning table.

The tutorial exercise below shows you a simple example of how to build a temporary table and make it as the returning table of a function:

USE GlobalGuideLineDatabase;

GO

CREATE FUNCTION Yearly_Stats(

@start_year INT, @end_year INT)

RETURNS @stats TABLE (year INT,

min INT, max INT, counts INT)

AS BEGIN

DECLARE @year INT;

SET @year = @start_year;

WHILE @year <= @end_year BEGIN

INSERT INTO @stats

SELECT @year AS year, MIN(counts) AS min,

MAX(counts) AS max, COUNT(*) AS counts

FROM fyi_links WHERE DATEPART(YEAR, created) = @year;

SET @year = @year + 1;

END;

RETURN;

END

GO

 224 views

345⟩ How To Provide Default Values to Function Parameters?

If you add a parameter when creating a stored procedure, you can provide a default value so that the execution statement is not required to pass input value to this parameter:

* To define a default value to a parameter when creating the function, you should use this format: "@parameter_name data_type = default_value".

* To use the default value of a parameter when executing the function, you should use the keyword DEFAULT as the input value for that parameter.

The tutorial exercise below shows you how provide default values to function parameters:

CREATE FUNCTION Age_In_Days (

@birth_date DATETIME,

@today DATETIME = NULL

)

RETURNS INT

AS BEGIN

IF @today IS NULL SET @today = GETDATE();

RETURN DATEDIFF(DAY, @birth_date, @today);

END;

GO

-- Default value is used

PRINT 'Age in days: '+STR(

dbo.Age_In_Days('01-Jan-2007', DEFAULT));

GO

Age in days: 138

-- Default value is not used

PRINT 'Age in days: '+STR(

dbo.Age_In_Days('01-Jan-2007', '11-May-2007'));

GO

Age in days: 130

-- Can not skip input values

-- even for parameters with default values

PRINT 'Age in days: '+STR(

dbo.Age_In_Days('01-Jan-2007'));

GO

Msg 313, Level 16, State 2, Line 1

An in

 222 views

346⟩ How To Create a New Table in a Given Schema?

When you create a new table, you can specify in which schema you want this table to be located by prefixing the table name with the schema name. In the tutorial example below, a new table "test" is created in schema "ggl":

USE GlobalGuideLineDatabase;

GO

CREATE TABLE ggl.test (id INT);

GO

Command(s) completed successfully.

SELECT t.name AS table_name, t.type_desc,

s.name AS schema_name

FROM sys.tables t, sys.schemas s

WHERE t.schema_id = s.schema_id

AND t.name = 'test';

GO

table_name type_desc schema_name

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

test USER_TABLE ggl

The last query confirms that table "test" is inside schema "ggl".

 235 views

347⟩ How To Transfer an Existing Table from One Schema to Another Schema in MS SQL Server?

If you want to move an existing table from one schema to another schema, you can use the "ALTER SCHEMA ... TRANSFER ..." statement as shown in the tutorial exercise below:

-- Login with "sa"

USE GlobalGuideLineDatabase;

GO

-- Confirming that "ggl_links" is in "dbo"

SELECT t.name AS table_name, t.type_desc,

s.name AS schema_name

FROM sys.tables t, sys.schemas s

WHERE t.schema_id = s.schema_id

AND t.name = 'ggl_links';

GO

table_name type_desc schema_name

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

ggl_links USER_TABLE dbo

-- Moving a table to a new schema

ALTER SCHEMA ggl TRANSFER ggl_links;

GO

-- Confirming that "ggl_links" is moved to "ggl"

SELECT t.name AS table_name, t.type_desc,

s.name AS schema_name

FROM sys.tables t, sys.schemas s

WHERE t.schema_id = s.schema_id

AND t.name = 'ggl_links';

GO

table_name type_desc schema_name

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

ggl_links USER_TABLE ggl

The last query confirms that table "ggl_links" is now in schema "ggl".

 320 views

348⟩ How To List All Objects in a Given Schema?

If you are wonder what objects are stored in a given schema as an object container, you can use view "sys.objects" to get a list of all objects in a schema. The tutorial exercise shows you how to list all objects in schema "ggl" and "dbo":

-- Login with 'sa'

USE GlobalGuideLineDatabase;

GO

-- What is "ggl"?

SELECT o.name, o.schema_id, o.type_desc 

FROM sys.objects o, sys.schemas s

WHERE o.schema_id = s.schema_id

AND s.name = 'ggl';

GO

name schema_id type_desc

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

test 5 USER_TABLE

ggl_links 5 USER_TABLE

UQ__ggl_links__4222D4EF 5 UNIQUE_CONSTRAINT

DF__ggl_links__creat__4316F928 5 DEFAULT_CONSTRAINT

SELECT o.name, o.schema_id, o.type_desc

FROM sys.objects o, sys.schemas s

WHERE o.schema_id = s.schema_id

AND s.name = 'dbo';

GO

 215 views

349⟩ What Is the Default Schema of Your Login Session in MS SQL Server?

When you login to a SQL Server and select a database to use, SQL Server will assign your login session a default schema. The schema name can be omitted when you refer to objects in the default schema. Here is what you should remember about default schema:

* The default schema of your login session in the current database is the default schema assigned to the current database level principal - database user.

* If you are referring to an object in the default schema, you do not need to specify the schema name.

* If you are referring to an object outside the default schema, you must specify the schema name.

The tutorial exercise below shows you how to verify your default schema:

-- Login with "ggl_login"

USE GlobalGuideLineDatabase;

GO

Changed database context to 'GlobalGuideLineDatabase'.

PRINT User_Name();

GO

ggl_User

SELECT name, default_schema_name

FROM sys.database_principals WHERE type = 'S';

GO

name    default_schema_name

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

dbo dbo

guest guest

INFORMATION_SCHEMA NULL

sys NULL

ggl_User dbo

The last query shows that the default schema for "ggl_login" in "GlobalGuideLineDatabase" is &qu

 197 views

350⟩ Who Is the Owner of a Schema in MS SQL Server?

When you create a schema in a database, SQL Server will assign a owner (a database user) to this schema. If your login name is mapped to the owner of a schema at the database level, you have the full permission on all objects in this schema.

The following tutorial exercise shows you how to see who is the owner of a schema:

-- Login with "sa"

USE GlobalGuideLineDatabase;

GO

SELECT s.name, u.name AS owner

FROM sys.schemas s, sys.database_principals u

WHERE s.principal_id = u.principal_id;

GO

name   owner

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

dbo dbo

ggl dbo

guest guest

...

The last query shows that schame "ggl" is owned by "dbo".

 206 views

351⟩ 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.

 210 views

352⟩ 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.

 212 views

353⟩ 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);

?>

 209 views

354⟩ 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

 224 views

355⟩ 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

 205 views

356⟩ 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

 202 views

357⟩ 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.

 244 views

358⟩ 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:

 220 views

359⟩ 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.

 213 views

360⟩ 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

 212 views