Process-per-client Architecture.
Multithreaded Architecture.
Hybrid Architecture.

'Count': Counts the number of non-null values.

'Count (*)': Counts the number of rows in the table, including null values and duplicates.

Views or tables participating in a view created with the SCHEMABINDING clause cannot be dropped. If the view is not created using SCHEMABINDING, then we can drop the table.

Stored procedures are nested when one stored procedure calls another. You can nest stored procedures up to 32 levels.

• The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.

• Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Use of this option is uncommon, and causes the stored procedure to execute more slowly because the stored procedure must be recompiled each time it is executed.

• You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.

You can use the sp_procoption system stored procedure in master database to mark the stored procedure to automatic execution when the SQL Server will start.

There are four different type of stored procedure given below:

1. Temporary Stored Procedures - SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends. Temporary procedures named with # and ## can be created by any user.

2. System stored procedures are created and stored in the master database and have the sp_ prefix.(or xp_) System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master. (If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.)

3. Automatically Executing Stored Procedures - One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.

4. User defined stored procedure - These stored procedure is created by user.

Microsoft® SQL Server™ 2000 uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, data within the database may become logically incorrect, and queries executed against that data may produce unexpected results.
Lock mode Description. There are six different type of locks given below:

Shared (S) Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
Exclusive (X) Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
Schema Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
Bulk Update (BU) Used when bulk-copying data into a table and the TABLOCK hint is specified.

Views can have only select statements (create, update, truncate, delete statements are not allowed) Views cannot have "select into", "Group by" "Having", "Order by"

          
            SELECT
            MIN(Salary) AS Expr1 FROM tblEmployee WHERE (empid IN (SELECTDISTINCTTOP 5
empid FROM tblSalary ORDERBY Salary)) 
        

A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form so that you can share it between a number of programs.
• They allow modular programming.
• They allow faster execution.
• They can reduce network traffic.
• They can be used as a security mechanism.

Yes, Let us consider there is employ Table ( empid, empName, empManagerid). In this table manager is also be an employ.

Difference Between DELETE & TRUNCATE Are

1. DELETE  is a DML Command & TRUNCATE is a DDL Command

2. After DELETE  can rollback the Records & After TRUNATE cannot rollback the records

3. In DELETE Command you can give the conditions in WHERE Clause & In TRUNCATE you cannot give conditions

4. After using DELETE Command The memory will be occupied till the user does not give ROLLBACK or COMMIT & After using TRUNCATE Command The memory realeased immediately

LEFT OUTER JOIN - This returns all the matching rows and the unmatched rows of the left table of the SQL code.

RIGHT OUTER JOIN - This returns all the matching rows and the unmatched rows of the right table of the SQL.

FULL OUTER JOIN - This returns all the matching and unmatched rows from both the tables.