Q's on Differences in SQL Server

SUB-QUERY VS INNER JOIN:

SUB-QUERY:
SELECT * FROM TABLEA WHERE  ID IN ( SELECT ID FROM TABLEB)

INNER JOIN:
SELECT * FROM TABLEA  X
        INNER JOIN TABLEB  Y  ON X.ID  = Y.ID  AND X.NAME = Y.NAME

Using sub-queries we can join based only on one column where as using joins we can use more than one.

STORED PROCEDURE VS FUNCTION:

SP:

1. Pre-Complied.
2. Proceeds execution till the last statement even if any error occurs in the middle.
3. Providing an output is not a mandatory.
4. Can Provide multiple outputs with the help of OUTPUT keyword.
5. Cannot be called in a select statement.
6. Can call other SP's or Functions inside it.
7. Reduces network traffic by executing bunch of statements together.

Functions:

1. Can be called in a select statement.
2. Should mandatorily provide the output.
3. Cannot call SP's inside it.
4. User Defined Functions cannot be used to modify base table information.
5. SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions.GETDATE is an example of a non-deterministic function.
6. Can be used in joins.

UNION VS UNION ALL

Union gives the distinct common records between the entities. where are Union all brings all the records from the two entities but in common the attributes used in the select clause should hold the same data type in both the tables.

VIEW VS MATERIALIZED VIEW

Views do not store data physically where the materialized view does. The Data snapshot inside materialized view resides till the next call to it and if there is no modifications in the underlying data. If any changes made to base data gets reflected in materialized view on the next call to it.

LEN() Vs DATALENGTH()

While LEN() is used to return the number of characters in a String, DATALENGTH() can be used to return the number of bytes used by any expression.

Example:

CREATE TABLE dbo.SampleTable
(
      NameNVarchar      NVARCHAR(64),
      NameVarchar       VARCHAR(64),
      NameChar          CHAR(64)
)

INSERT INTO dbo.SampleTableVALUES
('Anitha Saradhi', 'Anitha Saradhi', 'Anitha Saradhi')
INSERT INTO dbo.SampleTableVALUES
('Nidhi', 'Nidhi', 'Nidhi')

SELECT      LEN(NameVarchar)  AS 'LEN(VARCHAR)',
            LEN(NameNVarchar) AS 'LEN(NVARCHAR)',
            LEN(NameChar)     AS 'LEN(CHAR)'
FROM        dbo.SampleTable

SELECT      DATALENGTH(NameVarchar)  AS 'DATALENGTH(VARCHAR)',
            DATALENGTH(NameNVarchar) AS 'DATALENGTH(NVARCHAR)',
            DATALENGTH(NameChar)     AS 'DATALENGTH(CHAR)'
FROM        dbo.SampleTable

Results:

LEN(VARCHAR) LEN(NVARCHAR) LEN(CHAR)
------------ ------------- -----------
14           14            14
5            5             5
(2 row(s) affected)

DATALENGTH(VARCHAR) DATALENGTH(NVARCHAR) DATALENGTH(CHAR)
------------------- -------------------- ----------------
14                  28                   64
5                   10                   64
(2 row(s) affected)