Sql Code Masterz

Reduce memory footprint of a .NET application   try { Process process = Process . GetCurrentProcess (); process . MaxWorkingSet = loProcess . MaxWorkingSet ; process . Dispose (); } catch { }  

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

When SET ANSI NULLS ON it means ISO Standard is being followed. = and <> should not be used for null comparison.Instead use is null and is not null

As all of us know it is very typical to choose column for non cluster index. We should consider creating non-cluster index on any column that are frequently referenced in the where clauses of Sql statements.we may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as covered queries and experience excellent performance gains. Index Tuning is the process of finding appropriate column for non-clustered indexes. SQL Server provides a wonderful facility known as the Index Tuning Wizard which greatly enhances the index selection process.

NULLIF function returns null value if the two specified expressions are equivalent. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL. NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a null value of the type of the first expression. Syntax of NULLIF Function is given below:- NULLIF (expression1 , expression2)

Security functions are basically used to get information about users and there roles. All the security functions are nondeterministic means they return different value every time they are called with a specific set of values or parameters. Below are the list of some security functions available in sql server. (1)fn_trace_geteventinfo (2)fn_trace_getfilterinfo (3)fn_trace_getinfo (4)fn_trace_gettable (5)HAS_DBACCESS (6)IS_MEMBER  (7)IS_SRVROLEMEMBER (8)SUSER_SID (9)SUSER_SNAME (10)USER_ID (11)USER

Below are the advantages of using temporary table in SQLServer (1)Temporary table improves performance. (2)Temporary table makes the complex logic simple. (3)Temporary table cache Intermediate results.

A subquery in sql sever is a SELECT statement within another SQL statement. The SQL statement can be SELECT, WHERE clause, FROM clause, JOIN, INSERT, UPDATE, DELETE, SET, DO, or another subquery.The query that contains the subquery is normally called outer query and the subquery itself is called inner query. Why to use subquery:- (1)Subqueries structure a complex query into isolated parts so that a complex query can be broken down into a series of logical steps for easy understanding and code maintenance. (2)Subqueries allow you to use the results of another query in the outer query. (3)In some cases, subqueries can replace complex joins and unions and subqueries are easier to understand. Why not to use subquery:- (1)When subquery is used, the database server (actually the query optimizer) may need to perform additional steps, such as sorting, before the results from the subquery are used. If a query that contains subqueries can be rewritten as ...

There are many options available to move data or databases in between servers and databases in sql server so we have to choose good option depending upon our requirements. Some of the options we have are:- (1)BACKUP/RESTORE (2)detaching and attaching databases (3)Replication (4)DTS (5)BCP (6)logshipping (7)INSERT...SELECT (8)SELECT...INTO (9)creating INSERT scripts to generate data.

In pessimistic locking whenever user wants to update any data first it locks the record and till then no one can update data. Other users can only view the data when there is pessimistic locking.

Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables. Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables.

Composite index is an index on two or more columns. Both clustered and nonclustered indexes can be composite indexes. Composite indexes are especially useful in two different circumstances. First, you can use a composite index to cover a query. Secondly we can use a composite index to help match the search criteria of specific queries.

A subquery must be enclosed in the parenthesis. A subquery must be put in the right hand of the comparison operator, and A subquery cannot contain a ORDER-BY clause. A query can contain more than one sub-queries.

Below are the different functions use inside CATCH block:- (1)ERROR_NUMBER:- The number of the error that occurred. This is similar to @@ERROR except that it will return the same number for the duration of the CATCH block. (2)ERROR_MESSAGE:- The complete text of the error message including any substitute parameters such as object names. (3)ERROR_LINE:- This is the line number of the batch or stored procedure where the error occurred. (4)ERROR_SEVERITY:- This is the severity of the error. The CATCH block only fires for errors with severity 11 or higher. Error severity from 11 to 16 are typically user or code errors. Severity levels from 17 to 25 are usually software or hardware errors where processing may not be able to continue. (5)ERROR_STATE:- This is sometimes used by the system to return more information about the error. (6)ERROR_PROCEDURE:- If the error was generated inside a stored procedure this will hold the name of the proce...

Workload governor sits between the client and the database engine and counts the number of connections per database instance. If Workload governor finds that the number of connections exceeds eight connections, it starts stalling the connections and slowing down the database engine. One important thing is that it does not limit the number of connections but makes the connection request go slow. By default 32,767 connections are allowed both for SQL SERVER and MSDE. But it just makes the database engine go slow above eight connections.

As all of us know pages are contained in extent. Every extent will have around eight data pages. But all the eight data pages are not created at once; they are created depending on data demand. So when a page becomes full it creates a new page, this process is called as Page Split.