, ,

Learning Topic: 1:

1. What is Sql Agent?

Sql Agent run as Windows Service in background and can run specific scheduled job. Sql Agent uses SQL Server to store Job information such as specific step e.g. database back up etc.

Component of SQL Agent can be defined as follows:

Component Description
Job Job contains of one or more step. Step could be one or more SQL statement to execute on a predefined time.
Alert Alert to be sent when a specific event occurs. Such as sending mail to some one when database reaches a specific size or alert for creating backup.
Operators These are usually people to whom alert to be sent by Email

we can set or modify an Agent using SQL Server Enterprise Manager.

2. What is Job?

Definition of job can be found in the previous question. I will focus on example here.

  • Creating Backup for database etc.

3. What is Schema?


Schema is an object that conceptually holds database table, view, store procedure that attached to it. Object belonged to a schema said to be schema bound. e.g. When creating an Indexed view, the view has to be schema bound.

The concept of Schema comes form separation of ‘User’ and ‘Object Owner’. Previously if an user creates a table that user can’t be deleted unless the table ownership shifted to another user or deleting the table. But now an Table could be created and attached it to a schema without having any user created.

Create Table MySchema.TableName( col1 int, col2 varchar)

This means that object and Schemas could be created before even user added to the database. Extendedly means, a user can be dropped without dropping the object owned by the user. [Some parts collected form : blog.sqlauthority.com]

  • Ownership of schemas and schema-owned objects is transferable by ALTER AUTHORIZATION command.
  • Objects can be moved between schemas by ALTER SCHEMA command.
  • A single schema can contain objects owned by multiple database users.
  • Multiple database users can share a single default schema

    An advanced use may be: Sensitive data can be protected in more managed way.

    The SQL :

    CREATE LOGIN altaf WITH PASSWORD=’altafPass’
    USE AdventureWorks
    CREATE USER altaf FROM LOGIN altaf
    GRANT SELECT ON SCHEMA::[Accounts] TO [altaf]

    4. What is Exists Clause?

    Exists Function takes one parameter which is a SQL statement.

    Return :
    True: if any record match the criteria

    False: if no record match the criteria


    if EXISTS ( Select * from CITY where cityName = ‘syl’)

    ….do somthing


    there is no city named syl

    Important thing is that the EXISTS function stop processing once it found any record. So doesn’t have a huge impact if checking on a primary key.

    >> It have a 4 time faster processing time than the following SQL:

    if( Select count(*) from CITY where cityName =’sy’) > 0

    5. What do you mean by Arithmetic abort? 

    Terminates a query when an overflow or divide-by zero error occurs during query execution.

    If ON and any error occurs in a transaction then the transaction is rolled back. If OFF, an error message is displaced and null is assigned to the result of the arithmetic operation.


    Note: SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views.

    6. What is Set NOCOUNT?

    Set NOCOUNT stops the message indicating the number of rows affected by a T-SQL or store procedure.

    Getting affected rows count might be useful for T-SQL but when stored procedures run there is no need to send this information to the client. So, Setting it OFF on top of each SP we can get performance gain.

    However, Setting NOCOUNT still update @@ROWCOUNT, so we can still able to use this in SP.

    7. What is global variable in SQL server?

    Variable that are read-only, global to a specific session and changes form one session to another session. They can be examined form anywhere anytime. The declaration starts with ‘@@’ and return scalar value(single value). 

    @@ROWCONT: is a global variable that store information of affected row by the last executed T-SQL.

    @@Identity: Give the last identity value generated an SP or T-SQL. So, for next T-SQL or SP it will change the value.

    So, If we want to use it we have to assign it to a local variable starts with single ‘@’.

    @identityCount = @@identity


  • 8. What is System defined stored procedure?

    Various informational and administrative work can be done using system defined stored procedure.

    like: sp_help_fulltext_tables>> return the list of table that are registered indexing.

    sp_helptext: displays the definition of the user-defined rule, sp, column etc.

    use AdventureWorks

    sp_helptext @objname = N’AdventureWorks.sales.salesOrder’,

    @columname = Totaldue;


    9. What is Maintenance Plan? why it is used?

    Typically an wizard that create an workflow of the task required to maintain a database like: creating regular backup, free inconsistencies. These task defined by an integration of agent job.


    • create workflow of typical maintenance task.
    • Support for logging plan history to remote servers.