SQL - Wildcard Operators

SQL Wildcard Operators are used with SQL LIKE operator, which is used to compare a value to alike values using wildcard operators.

SQL supports two wildcard operators in combination with the LIKE operator:

       ·The percent sign (%): Matches one or more characters. Note that MS   Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.

• The underscore (_): Matches one character. Note that MS Access uses a question mark (?) instead of the underscore (_) to match any one character.

Syntax:The basic syntax of '%' and '_' is as follows:

·         SELECT FROM table_name  WHERE column LIKE 'XXXX%'

·         SELECT FROM table_name WHERE column LIKE '%XXXX%'

                    AND

·         SELECT FROM table_name WHERE column LIKE 'XXXX_'

·         SELECT FROM table_name WHERE column LIKE '_XXXX'

Example: Here are number of examples showing WHERE part having different LIKE clause with '%' and '_' operators:

·         SELECT * FROM EMP_DETSILS WHERE SALARY LIKE '90%'  Select values that start with 90

·         SELECT * FROM EMP_DETSILS WHERE SALARY LIKE '%90%' Select values that have 90 in it

·         SELECT * FROM EMP_DETSILS  WHERE SALARY LIKE '_90%' Select values that have 90 in the second and third positions

·         SELECT * FROM EMP_DETSILS WHERE SALARY LIKE '_1%5'  Select values that have a 1 in the second position and end with a 5

·         SELECT * FROM EMP_DETSILS WHERE SALARY LIKE '1___5' Select  values in a five-digit number that start with 1 and end with 5

List Empty Tables in SQL Server

List Empty Tables in SQL Server

Sometime we create tables in the DB but never use. If we need to find the list of all that tables in complete DB it will be an issue for a developer to check each table

here’s a simple query to list all empty tables in your SQL Server database that uses a Dynamic Management View called dm_db_partition_stats which returns empty table current database.


;WITH EmptyRows AS

(

   SELECT SUM(row_count) AS [TotalRows],

          OBJECT_NAME(OBJECT_ID) AS TableName

   FROM sys.dm_db_partition_stats

   WHERE index_id = 0 OR index_id = 1

   GROUP BY OBJECT_ID

)

SELECT * FROM EmptyRows

WHERE [TotalRows] = 0

What is Connection Pooling in SQL SERVER?

What is Connection Pooling in SQL SERVER?

Connection Pooling is a technique to allow multiple clients to make use of a cached set of reusable connection objects providing access to a database.

Opening and Closing database connections is an expensive process and hence connection pools improve the performance of execution of commands on a database for which we maintain connection objects in the pool. It empowers reuse of the same connection object to serve a number of client requests. Every time a client request is received, the pool is searched for an available connection object and it's highly likely that it gets a free connection object. Otherwise, either the incoming requests are queued or a new connection object is created and added to the pool. As soon as a request finishes using a connection object, the object is given back to the pool from where it's assigned to one of the queued requests. Since most of the requests are served using existing connection objects only so the connection pooling approach brings down the average time required for the users to wait for establishing the connection to the database.

normally Connection Pooling used in a web-based application where the application server handles the accountability of creating connection objects, adding them to the pool, assigning them to the incoming requests, taking the used connection objects back and returning them back to the pool. When a dynamic web page of the web-based application explicitly creates a connection to the database and closes it after use then the application server internally gives a connection object from the pool itself on the execution of the statement which tries to create a connection and on execution of the statement which tries to close the connection, the application server simply returns the connection back to pool.

Connection Pool are configurable means the maximum/minimum connections and maximum idle connections can be configured by the server administrator. On start up the server creates a fixed number of connection objects and adds them to the pool. Once all of these connection objects are exhausted by serving those many client requests then any extra request causes a new connection object to be created, added to the pool, and then to be assigned to server that extra request. This continues till the number of connection objects doesn't reach the configured maximum number of connection objects in the pool. The server keep on checking the number of idle connection objects as well and if it finds that there are more number of idle connection objects than the configured value of that parameter then the server simply closes the extra number of idle connections, which are subsequently garbage collected.

What is Sub-Queries in SQL Server?

Properties of Sub-Query

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

Type of Subqueries

• Single row subquery: Returns zero or one row.
• Multiple row subquery: Returns one or more rows.
• Multiple column subquery: Returns one or more columns.
• Correlated subqueries: Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement.
• Nested subqueries: Subqueries are placed within another subqueries.

Subqueries: Guidelines

• There are some guidelines to consider when using subqueries:
• A subquery must be enclosed in parentheses.
• A subquery must be placed on the right side of the comparison operator.
• Subqueries cannot manipulate their results internally, therefore ORDER BY clause cannot be added in to a subquery. You can use an ORDER BY clause in the main SELECT statement (outer query) which will be last clause.
• Use single-row operators with single-row subqueries.
• If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.

What are the Advantages of Using Stored Procedures?

What are the Advantages of Using Stored Procedures?

Stored procedures provide performance benefits through local storage, precompiling the code, and caching. It offers security features that includes encryption and privilege limits that restrict users from modifying structure of stored procedure.

Advantages of using stored procedures are:

• Execution plan retention and reuse
• Query auto-parameterization
• Encapsulation of business rules and policies
• Application modularization
• Sharing of application logic between applications
• Access to database objects that is both secure and uniform
• Consistent, safe data modification
• Network bandwidth conservation
• Support for automatic execution at system start-up
• Enhanced hardware and software capabilities
• Improved security
• Reduced development cost and increased reliability
• Centralized security, administration, and maintenance for common routines