Why we can't execute a stored procedure from a User Defined function(UDF)

Functions cannot "touch" any database but read them only. Stored procedures can do anything and everything with databases.

You cannot execute a stored procedure from a function. You can execute an extended stored procedure from a function.

There are several restrictions on what User Defined Functions (UDF's) can do.  
Primarily, they cannot have code that has side effects.  

Like, for example, they cannot make changes to tables (except to table variables defined in the function.  

These restrictions allow better optimization of function calls.  
But since those restrictions do not apply to stored procedures, 
functions cannot call stored procedure's because there would be no efficient way of enforcing the "no side effects allowed" 
rule if functions could call stored procedures.


one way is to use xp_cmdshell to call a batch file where the batch file contains the execute procedure statement. 
In the function you can call the extended proc.

e.g.

Create Function...

EXEC master.sys.xp_cmdshell 'C:\test.bat'

RETURN...

NULLIF Function in SQL

NULLIF Function

This SQL Server tutorial explains how to use the NULLIF function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the NULLIF function compares expression1 and expression2. If expression1 and expression2 are equal, the NULLIF function returns NULL. Otherwise, it returns the first expression which is expression1.

Syntax

The syntax for the NULLIF function in SQL Server (Transact-SQL) is:

NULLIF( expression1, expression2 )

Parameters or Arguments

expression1, expression2

The expressions that will be compared. Values must be of the same datatype.

What is the Default Datatype of NULL?

What is the Default Datatype of NULL?

NULL is undefined and every column can have NULL values except columns with timestamp datatype (which represent NULL values differently). There is a simple method to know the default datatype of NULL values.

First, let us create a table with SELECT INTO TABLE Syntax and then we will try to find out the datatype of the column where we have stored NULL. Please note that we are not providing any datatype for the NULL value.

SELECT NULL AS col

INTO #TempTable

Now let us look at the structure of the table and see what datatype is assigned to the column named col in the table #TempTable

EXEC tempdb..sp_columns #TempTable

Now please look at the column named Type_Name in the resultset. You will find that the default datatype is an Integer.

So, theoretically, I agree that it is difficult to come up with the datatype of the NULL value, but in SQL Server the default datatype of the NULL is an Integer.

Database normalization

Database normalization 

Database normalization is a technique to organize the contents of databases. Without normalization, database systems can be inaccurate, slow, and inefficient. The community of database professionals developed a series of guidelines for the normalization of databases. Each 'level' of normalization is referred to as a form, and there are 5 forms, total. First normal form is the lowest level of normalization, up to fifth normal form, which is the highest level of normalization.

• First Normal Form (1NF): The most basic level of data normalization, first normal form requires the elimination of all duplicate columns in a table, and also requires the creation of separate tables for related data, and identification of each table with a primary key attribute.

• Second Normal Form (2NF): Meets all the requirements of first normal form, and creates relationships between tables using foreign keys.

• Third Normal Form (3NF): Meets all the requirements of second and first normal forms, and removes all columns that are not dependent upon the primary key. Third normal form also removes all derived attributes, such as age.

• Fourth Normal Form (4NF): Fourth normal form adds one additional requirement, which is the removal of any multi-valued dependencies in relationships.

• Fifth Normal Form (5NF): Fifth normal form is a rarer form of normalization, in which case join dependencies are implied by candidate keys (possibly primary key values).

In the reality of database development, getting to 3NF is the most important jump. 4NF and 5NF are a bit more of a luxury (and sometimes a nuisance) in database development, and are rarely seen in practice. If you're struggling with the concepts, or remembering the first three forms, there is a simple relation. "The key, the whole key, and nothing but the key.", which relates to 1NF, 2NF, and 3NF.

The Benefits of Normalization

Now, without venturing too far into database theory, let's simply focus on the benefits of normalization. As the data progresses through the normalization forms, it becomes cleaner, better organized, and faster. Now, with a small database that has only 5 tables and 100 rows of data, this won't be readily apparent. However, as the database grows, the effects of normalization will become much more apparent with regards to speed and maintaining data integrity. However, there are some situations in which normalization doesn't make sense, such as when normalizing the data will create excessively complex queries required to return the data.

Understand Data Types IN SQL

Understand Data Types IN SQL

In SQL, typically each table column has an associated data type. Text, Integer, VarChar, Date, and more, are typically available types for developers to choose from.

• MySQL Data Types
• Oracle Data Types
• SQL Server Data Types

When developing, make sure you choose the proper data type for the column. Dates should be DATE variables, numbers should be a numeric type, etc. This becomes especially important when we deal with a later topic: indexing; but I'll demonstrate an example of poor knowledge of data types below:

SELECT employeeID, employeeName

FROM employee WHERE employeeID = 5142875;

Looks fine based on what we currently know, correct? However, what if employeeID is actually a string. Now we've got a problem, because the DBMS might not find a match (because string data types and integers are different types).

Therefore, if you're using indexing, you'll probably be perplexed as to why your query is taking forever, when it should be a simple index scan. This is the reason that developers need to pay special attention to data types and their applications. Non-key attributes which are IDs are often string types, as opposed to integers, because of the increased flexibility that is granted. However, this is also a trouble area for junior developers, who assume that ID fields will be integers.

Understand Joins in SQL

Understand Joins in SQL

Single table SQL select statements are rather easy to write. However, business requirements often dictate that more complex queries must be written. For example, "find all orders for each customer, and display the products for each order". Now, in this particular situation, it would be likely that there is a customer table, an order table, and an order_line table (the last would be to resolve a possible many-to-many record relationship). For those who are slightly more familiar with SQL, it is readily apparent that a table join, actually, two table joins will be required for this query. Let's look at some sample code.

SELECT customer.customerID, order.order_id, order_line.order_item FROM customer     INNER JOIN order         ON customer.customerID = order.customerID     INNER JOIN order_line         ON order.orderID = order_line.orderID;

Alright, simple enough. For those who don't know, the code above is an inner join. More specifically, the code above is an equi-join.
Let's define the various types of joins.

Inner Joins: The basic purpose of inner joins is to return matching records.

Outer Joins: Outer joins do not require each record to have a matching record.

• Left outer join: A left outer join of tables A and B will return all matching records of A and B, as well as any non-matched records from the left table, in this case, A.
• Right outer join: A right outer join of tables A and B will return all matching records of A and B, as well as any non-matched records from the right table, in this case, B.
• Full outer join: A full outer join of tables A and B will return all matching records of A and B, as well as any non-matched records from both tables.

Self Joins

·         There is one last type of join that must be considered, which is a self join. A self join is merely a join from a table to itself.

EMPLOYEE TABLE -EmployeeName -SupervisorID

·         In this situation, in order to find which employees are supervised by a given employee, a self join would be required.

·         Hopefully this clarifies the basic tenets of joins, as they are one of the primary features of SQL that makes it such a powerful database language. Make sure you use the proper join for your given situation.

what is SQL UNION clause?

The SQL UNION clause

The SQL UNION clause is used to combine the results of two or more SELECT statements without returning any duplicate rows.

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length.

Syntax:

The basic syntax of UNION is as follows:

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

UNION

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

The UNION ALL Clause:

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.

There are two other clauses (i.e., operators), which are very similar to UNION clause:

·        SQL INTERSECT Clause: is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.

·        SQL EXCEPT Clause : combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.

What is Self-Join in SQL

What is Self-Join in SQL?

A self-join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table it-self means that each row of the table is combined with itself and with every other row of the table.

The self-join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.

The syntax of the command for joining a table to itself is almost same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have same name. Table name aliases are defined in the FROM clause of the SELECT statement. See the syntax:

SELECT a.column_name, b.column_name…..

FROM table1 a, table1 b

WHERE a.common_filed = b.common_field;

Example of SQL SELF JOIN

In the following example we will use the table EMPLOYEE twice and in order to do this we will use the alias of the table.

To get the list of employees and their Manager the following sql statement has used:

SELECT a.emp_id AS "Emp_ID",a.emp_name AS "EmployeeName", 

b.emp_id AS "Manager ID",b.emp_name AS "ManagerName" 

FROM employee a, employee b 

WHERE a.emp_supv = b.emp_id;

CAST Function in SQL

CAST Function

In many cases, a database will automatically convert one data type into another when needed. On the other hand, there are instances when that is not the case, or when you want to explicitly specify what data type to change into. In these cases, you can use the CAST function. The syntax of the CAST function is as follows:

CAST (expression AS [data type])

where [data type] is a valid data type in the RDBMS you are working with.

Table Student

StudentID	First_Name	Marks
1	Anil	95.9
2	Sunil	93.5
3	Ram	80
4	sumit	70.1

Example 

SELECT First_Name, CAST(Marks AS Integer) marks FROM Student_Score;

OutPut:

First_Name	Int_Marks
Anil	96
Sunil	94
Ram	80
sumit	70

CAST in SQL

CAST Function

In many cases, a database will automatically convert one data type into another when needed. On the other hand, there are instances when that is not the case, or when you want to explicitly specify what data type to change into. In these cases, you can use the CAST function. The syntax of the CAST function is as follows:

CAST (expression AS [data type])

where [data type] is a valid data type in the RDBMS you are working with.

Table Student

StudentID	First_Name	Marks
1	Anil	95.9
2	Sunil	93.5
3	Ram	80
4	sumit	70.1

Example 

SELECT First_Name, CAST(Marks AS Integer) marks FROM Student_Score;

OutPut:

First_Name	Int_Marks
Anil	96
Sunil	94
Ram	80
sumit	70

SQL Server CONVERT() Function

SQL Server CONVERT() Function

The CONVERT() function is a general function that converts an expression of one data type to another.

The CONVERT() function can be used to display date/time data in different formats.

Syntax

CONVERT(data_type(length),expression,style)

Value	Description
data_type(length)	Specifies the target data type (with an optional length)
expression	Specifies the value to be converted
style	Specifies the output format for the date/time (see table below)

Example

The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

The result would look something like this:

Nov 04 2014 11:45 PM
11-04-14
11-04-2014
04 Nov 14
04 Nov 2014
04 Nov 2014 11:45:34:243

What is SQL Indexes? Type of SQL Indexes.

What is SQL Indexes? Type of SQL Indexes.

Indexes are lookup tables that the database search engine can use to speed up data retrieval. Put, an index is a pointer to data in a table. An index in a database is very similar to an index in a book

An index helps speed up SELECT command and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements.

Indexes can be created or dropped with no effect on the data

Creating an index need the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order

The CREATE INDEX Command:

  CREATE INDEX index_name ON table_name;

Single-Column Indexes:

CREATE INDEX index_name on table_name (column_name);

Composite Indexes:

A composite index is an index on two or more columns of a table

CREATE INDEX index_name on table_name (col1, col2);

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very often in a query's WHERE clause.

Implicit Indexes:

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints

The DROP INDEX Command:

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved

DROP INDEX index_name

We should avoid indexes in.

Though indexes are intended to enhance a database's performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered

• It should not be used on small tables.
• Tables that have frequent, large batch update or insert operations.
• It should not be used on columns that contain a high number of NULL values.
• Columns that are frequently manipulated should not be indexed. 

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.