SQL Useful Functions

SQL Useful Functions

SQL has many built-in functions for performing processing on string or numeric data. Following is the list of all useful SQL built-in functions

• SQL COUNT Function - The SQL COUNT aggregate function is used to count the number of rows in a database table.
• SQL MAX Function - The SQL MAX aggregate function allows us to select the maximum value for a certain column.
• SQL MIN Function - The SQL MIN aggregate function allows us to select the minimum value for a certain column.
• SQL AVG Function - The SQL AVG aggregate function selects the average value for certain table column.
• SQL SUM Function - The SQL SUM aggregate function allows selecting the total for a numeric column.
• SQL SQRT Functions - This is used to generate a square root of a given number.
• SQL RAND Function - This is used to generate a random number using SQL command.
• SQL CONCAT Function - This is used to concatenate any string inside any SQL command.
• SQL Numeric Functions - Complete list of SQL functions required to manipulate numbers in SQL.
• SQL String Functions - Complete list of SQL functions required to manipulate strings in SQL.

Rules to create SQL Sub Queries

Rules to create SQL Sub Queries

A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, >=, <=, IN, BETWEEN etc.

There are a few rules that subqueries must follow:

• Subqueries must be enclosed within parentheses.
• A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
• The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used within the subquery.
• Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.
• The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
• A subquery cannot be immediately enclosed in a set function.
• An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery. 

To Know More On Subquery Please check 

http://mysqlfacts.blogspot.in/2016/01/what-is-sub-queries-in-sql-server.html

SQL HAVING CLAUSE

The HAVING clause enables you to specify conditions that filter which group results appear in the final results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax: 

SELECT ...

FROM ...

WHERE ...

GROUP BY ...

HAVING ...

ORDER BY ...

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause:

SELECT column1, column2

FROM table1, table2

WHERE [ conditions ]

GROUP BY column1, column2

HAVING [ conditions ]

ORDER BY column1, column2

Configuring session state on SQL server

There are 2 steps for setting up the ASPState database and the formation setting in your web.config.

you need to use ASP_RegSQl.exe from a command prompt. The script file and the aspregsql EXE are found at

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727.

Formation Steps:

1     Open a command prompt and locate the following path:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 based on your OS version and .NET version

      Use the following statement:

a.       Using default ASPState database and SQL security

aspnet_regsql -S serverName -U UserName -P Password -ssadd -sstype p

b.       Using default ASPState database and windows security

aspnet_regsql -S serverName -E -ssadd -sstype p

c.       Using custom database and SQL security

aspnet_regsql -d TableName -S serverName -U UserName -P Password -ssadd -sstype c

         1) t - Stores session data in the SQL Server tempdb database. This is the default. If you store session data in the tempdb database, the session data is lost if SQL Server is restarted.

         2)   p - Stores session data in the ASPState database instead of in the tempdb database.

         3)  c - Stores session data in a custom database. If you specify the c option, you must also include the name of the custom database using the -d option.

     In your configuration file [web.config]:

       A) Using default SQL security:

<sessionstate mode="SQLServer" timeout="60" allowcustomsqldatabase="true"

sqlconnectionstring="Data Source=Server;User ID=sa;Password=sa;"

cookieless="false">

      B) Using default windows security:

<sessionstate mode="SQLServer" timeout="60" allowcustomsqldatabase="true"

sqlconnectionstring=" Data Source=Server;Integrated-Security=SSPI;”

cookieless="false">

      C) Custom database name:

<sessionstate mode="SQLServer" timeout="60" allowcustomsqldatabase="true"

sqlconnectionstring=" Data Source=Server; Initial Catalog = tblname; User ID=sa;Password=sa;”

cookieless="false">

NOTE: Things to lookout for when using SQL to store the session info in a web form situation:

    a)  The machine key among the servers' needs to be the same as AspState Session info is encrypted using the machine key.

    b) The application path to your websites on all machines needs to be constant as well.

10 things you CAN NOT do in SQL Server.

There are few things you cannot do in SQL server. Surprised?? Try some of following points. Please add your comments to the blog after trying.

1. You cannot add data and log files in master and model database.
2. You cannot add User Defined File Groups in master, model and tempdb database.
3. You cannot change the recovery model of tempdb database.
4. You cannot take transaction log backups of master database even if the recovery model of master database is changed to Full.
5. You cannot take any backup of tempdb database.
6. You cannot rename any system database.
7. You cannot allocate Transaction log files under File Groups
8. You cannot change the file group of a file once it is set.
9. You cannot create a new database if model database is in use
10. You cannot change the owner of master, model, tempdb or distribution databas