What is an Operators in SQL

What is an Operators in SQL?

An operator is a reserved word or a character used mainly in an SQL statement's WHERE clause to perform operation, like comparisons and arithmetic. Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

•         Arithmetic operators
•          Comparison operators
•          Logical operators

SQL Arithmetic Operators:

•          +  Addition - Adds values on either side of the operator
•          - Subtraction - Subtracts right hand operand from left hand operand
•          * Multiplication - Multiplies values on either side of the operator             
•          / Division - Divides left hand operand by right hand operand      
•          % Modulus - Divides left hand operand by right hand operand and returns remainder   

SQL Comparison Operators:

•          = Checks if the values of two operands are equal or not, if yes then condition becomes true.
•          != Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.     
•          <> Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
•          > Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.
•          < Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.
•          >= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.
•           <= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.
•          !< Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.
•          !> Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.

SQL Logical Operators:

•          ALL: The ALL operator is used to compare a value to all values in another value set.
•          AND: The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
•          ANY: The ANY operator is used to compare a value to any applicable value in the list according to the condition.
•          BETWEEN: The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
•          EXISTS: The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
•          IN: The IN operator is used to compare a value to a list of literal values that have been specified.
•          LIKE: The LIKE operator is used to compare a value to similar values using wildcard operators.
•          NOT: The NOT operator reverses the meaning of the logical operator with which it is used.
•          OR: The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
•         IS NULL: The NULL operator is used to compare a value with a NULL value.
•          UNIQUE: The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

View in Sql Server

View in Sql Server

A view is nothing but a SQL statement that is stored in the database with an allied name. A view is truly an arrangement of a table in the form of a SQL query. A view can be created from one or many tables which depends on the written SQL query to create a view.

Views, which are kind of virtual tables, allow users to do the following:

•        Structure data in a way that users or classes of users find natural or intuitive.

•        Limit access to the data such that a user can see and modify exactly what they need.

•        Summarize data from various tables which can be used to produce reports.

How to create view in DB:

Database views are created using the CREATE VIEW statement.

The basic CREATE VIEW syntax is as follows:

CREATE VIEW view_name AS

SELECT columns .....

FROM table_name

WHERE [condition];

You can have multiple tables in SELECT statement in very like you use them in normal SQL SELECT query.

WITH CHECK OPTION:

The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition.

If they do not satisfy the condition, the UPDATE or INSERT returns an error.

The following is an example of creating same view CUSTOMERS_VIEW with the WITH CHECK OPTION:

CREATE VIEW Emp_Details_View AS

SELECT name, Address, PhoneNo, Salary, age

FROM  Emp_Details

WHERE age IS NOT NULL WITH CHECK OPTION;

The WITH CHECK OPTION in this case should deny the entry of any NULL values in the view's AGE column, because the view is defined by data that does not have a NULL value in the AGE column.

Update a View:

A view can be updated under certain conditions:

•        SELECT clause may not contain the keyword DISTINCT.

•        SELECT clause may not contain summary functions.

•        SELECT clause may not contain set operators.

•        SELECT clause may not contain an ORDER BY clause.

•        FROM clause may not contain multiple tables.

•        WHERE clause may not contain subqueries.

•        SELECT clause may not contain set functions.

•        Query may not contain GROUP BY or HAVING.

•        Calculated columns may not be updated.

•        All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.

Inserting Rows into a View:

Rows of data can be inserted into a view. The same rules that apply to the UPDATE command also apply to the INSERT command.

Here we cannot insert rows in Emp_Details_VIEW because we have not included all the NOT NULL columns in this view, otherwise you can insert rows in a view in similar way as you insert them in a table.

Deleting Rows into a View:

Rows of data can be deleted from a view. The same rules that apply to the UPDATE and INSERT commands apply to the DELETE command.

Following is an example to delete a record having AGE= 22.

DELETE FROM Emp_Details_VIEW  WHERE age = 22;

Dropping Views:

 Where you have a view, you need a way to drop the view if it is no longer needed. The syntax is very simple as given below:

DROP VIEW view_name;

Database Tuning.

Database Tuning

While working with the database we should keep in mind few facts and concepts to make database operations fast.

Following list may be helpful to increase database performance and reduce the server overhead

• Use 3BNF database design.

• Avoid number-to-character conversions because numbers and characters compare differently and lead to performance downgrade.

• Don’t use * in your SELECT queries because it would load the system unnecessarily. Select only necessary fields.

• Create your indexes carefully on all the tables where you have frequent search operations

• Avoid index on the tables where you have less number of search operations and more number of insert and update operations.

• Avoid a full-table scan by creating an index on columns that are used as conditions in the WHERE clause of an SQL statement.

• Be very careful of equality operators with real numbers and date/time values.

• Use pattern matching carefully. LIKE INDI% is a valid WHERE condition, reducing the returned set to only those records with data starting with the string COL. However, INDI%A does not further reduce the returned results set since %A cannot be effectively evaluated.

• Fine-tune your SQL queries examining the structure of the queries, the SQL syntax, to discover whether you have planned your tables to support fast data operation and written the query in an optimal manner, allowing your DATABASE to operate the data efficiently.

• Use stored procedures in SQL rather than queries.

• Avoid using the logical operator OR in a query if possible.

• You can enhance bulk data loads by dropping indexes

• When performing batch transactions, perform COMMIT at after a fair number of records creation instead of creating them after every record creation.

• Plan to defragment the database on a regular basis, even if doing so means developing a weekly routine.

Built-In Tuning Tools

• Explain plan − tool classifies the access path that will be taken when the SQL statement is executed.

• tkprof − measures the performance by time elapsed during each phase of SQL statement processing.

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