SQL Server Data Types Vs SSIS Data Types

SQL Server Data Types and SSIS Data Types

Thanks, TAMATAM

How to use GROUP BY Clause in SQL Server

SQL Server - GROUP BY Clause

GROUP BY clause is used to divide a table into logical groups and calculate aggregate statistics for each group.
Important properties of GROUP BY clause:
GROUP BY clause appears after the WHERE clause and before the ORDER BY clause. 

• We can group columns or derived columns. 
• Columns from the input table can appear in an aggregate query's SELECT clause only when they are also included in the GROUP BY clause. 
• Group BY expression must match the SELECT expression exactly. 
• If you specify multiple grouping columns in the GROUP BY clause to nest groups, data is summarized at the final specified group. 
• If WHERE clause is used in the query containing a GROUP BY clause, Rows are eliminate 
• First which does not satisfy where condition and then grouping occurs. 
• You cannot use column alias in the GROUP BY clause but table aliases are allowed.

Syntax  :

SELECT Columns

FROM Table

[WHERE Search_condition]

GROUP BY Grouping_columns

[HAVING Search_condition]

[ORDER BY Sort_columns]

Here…

Columns and grouping columns are one or more comma separated column names.
Table is a name of table that contains columns and Grouping_columns.

Search_condition is a valid sql expression.

Sort_columns are one or more column name of specified table.

Consider the following Table  EMP :

Example-I :

A Query to find the count of Employees of various Designations (JOB) in each Department (DEPT_ID) from the above table EMP.

SELECT DEPT_ID,JOB, COUNT(*) AS COUNT_OF_EMPS 
FROM EMP 
GROUP BY DEPT_ID  ,JOB 
ORDER BY DEPT_ID

OR we can write as follows

SELECT DEPT_ID,JOB, COUNT(EMP_ID) AS COUNT_OF_EMPS 
FROM EMP 
GROUP BY DEPT_ID  ,JOB 
ORDER BY DEPT_ID

Output :

Example-II :

A Query to find the count of Employees of Designation (JOB)='Analyst" in each Department (DEPT_ID) from the above table EMP.

SELECT  JOB,DEPT_ID,COUNT(EMP_ID) AS COUNT_of_Analysts 
FROM EMP 
WHERE JOB='Analyst' 
GROUP BY JOB,DEPT_ID 
ORDER BY DEPT_ID

Output :

Notes :

Where clause can be use only on Existing columns of a table.

Where clause cannot be use on Alias columns of a table.

Where clause should use only after From clause and before Group By clause of a table.

Thanks,TAMATAM

What is the difference between Len and DataLength Functions in SQL

SQL Server Len() Function Vs. DataLength() Function
The LEN system function returns the number of characters of the specified string expression.
On the other hand, the DATALENGTH function returns the number of bytes used to represent any expression. Here’s a summary of the similarities and differences between the LEN() and DATALENGTH() system functions:

Similarities:
The LEN and DATALENGTH of NULL is NULL.
The return data type is BIGINT if the input expressions is of the VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX) data types; otherwise the return data type is INT.

Differences:                                LEN                      DATALENGTH Returns the number of characters of the specified string expression. Returns the number of bytes used to represent any expression. Excludes trailing blanks in determining the number of characters. Includes trailing blanks in determining the number of bytes used. Input parameter is of character or binary data type. Input parameter is of any data type. LEN is a string function. DATALENGTH is a data type function. LEN() returns the number of characters in the string or variable while DATALENGTH() returns the number of bytes used to represent a string or expression. We have 4 different cases in which we can study these two functions. Case 1:  With  "Varchar" Data types:  When a Varchar type is passed to both the DATALENGTH() and LEN() functions, they return the same value. Example:                 DECLARE @value varchar(20)                         SET @value = ‘Reddyz’                 SELECT  DATALENGTH(@value)                 SELECT  LEN(@value) These statements both return 6 as the Varchar type uses 6 single bytes characters to store the 6-character value. So in this case, both the functions are returning the same. Case 2:   With "Nvarchar" Data types:   If an nvarchar type is used, DATALENGTH() takes twice as many bytes to manage a value of the same length whereas LEN() returns the number of characters. Example:                 DECLARE @value nvarchar(20)                        SET @value = ‘Reddyz’                 SELECT  DATALENGTH(@value)                 SELECT LEN(@value) The DATALENGTH() returns 12 because 2 bytes are used to store each character using a Unicode character set. The LEN() returns 6 as this returns the number of characters, not the number of bytes.

Case 3:   With Integer Data types:  If integer Datatype is used, DATALENGTH() returns 4 and LEN() returns the number of digits. Example:                 DECLARE  @value1 int, @value2 int                         SET @value1=2                         SET @value2 =20000                 SELECT  DATALENGTH(@value1)                 SELECT  LEN(@value1)                 SELECT  DATALENGTH(@value2)                 SELECT  LEN(@value2) The DATALENGTH() returns 4 in both the cases because int always takes 4 bytes, whatever the value would be. But the LEN() treats the integer value as if it were converted to a character type and returns the number of digits. So results are 1 and 5 respectively. Case 4:  With Extra Spaces: If string or variable value is NULL then both functions returns null. If the string is having trailing blanks then LEN() function returns the length only up to the last non null character and ignores the spaces. On the other hand, DATALENGTH() behaves differently returning all along with spaces. Example:                   DECLARE @value1 VARCHAR(25)                   SELECT @value1 = 'Reddyz     '                   SELECT @value1 AS COL_TEXT, LEN(@value1) AS   LENGTH,                                          DATALENGTH (@value1) AS   DATA_LENGTH The result would be 6 and 11. As DATALENTH() takes into account the spaces so that it returns 11 and LEN() returns only the non-null characters so it returns 6.

#--------------------------------------------------------------Thanks--------------------------------------------------------------#