Difference Between Correlatedquery and NonCorrelatedquery in Sql Server

A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly.

Example:
----Example of Correlated Subqueries
USE AdventureWorks;
GO
SELECT e.EmployeeID
FROM HumanResources.Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
FROM Person.Contact c
WHERE MONTH(c.ModifiedDate) = MONTH(e.ModifiedDate)
)
GO

A noncorrelated subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query.

Example:
----Example of Noncorrelated Subqueries
USE AdventureWorks;
GO
SELECT e.EmployeeID
FROM HumanResources.Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
FROM Person.Contact c
WHERE c.Title = 'Mr.'
)
GO

Both of above subqueries can be written using Joins, Exists, In clauses. However, I have tried to demonstrate two different kind of subquery examples. Let me know if you have any questions or wants me to elaborate on this subject.

DataTypes in Sqlserver 2008

STRING AND BINARY TYPES
Differences between BINARY AND VARBINARY
BINARY1
Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.
 
VARBINARY
Variable-length binary data. n can be a value from 1 through 8,000.
 
Max indicates that the maximum storage size is 2^31-1 bytes.
 
The storage size is the actual length of the data entered + 2 bytes.
 
The data that is entered can be 0 bytes in length.
 
The ANSI SQL synonym for varbinary is binary varying.

Conclusion:
Use binary when the sizes of the column data entries are consistent.
 
Use varbinary when the sizes of the column data entries vary considerably.
 
Use varbinary(max) when the column data entries exceed 8,000 bytes.
 
Differences between CHAR AND VARCHAR
CHAR
Fixed-length. so any remaining space in the field is padded with blanks.
non-Unicode character data with a length of n bytes, n must be a value from 1 through 8,000. The storage size is n bytes
The SQL-2003 synonym for char is character.
 
VARCHAR
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes.
The storage size is the actual length of data entered + 2 bytes.
The data entered can be 0 characters in length.
The SQL-2003 synonyms for varchar are char varying or character varying.
For example, if you set a VARCHAR(100) datatype = 'Jen', then it would take up 3 bytes (for J, E, and N) plus 2 bytes, or 5 bytes in all.
 
Conclusion:
Use Char, When Using the fixed length data in column & data entries are consistent, like phone number
Use Varchar When using the variable length data in column & data entries vary considerably, like address
 
Differences between VARCHAR/VARCHAR(MAX) and NVARCHAR/NVARCHAR(MAX)
Varchar/Varchar(MAX)
Varchar(8000) stores a maximum of 8000 characters. Varchar(max) stores a maximum of 2 147 483 647 characters or 2GB.
Variable length Non-Unicode Characters data.
The storage size is the actual length of data entered + 2 bytes
The SQL-2003 synonyms for varchar are char varying or character varying.
 
NVarchar/NVarchar(MAX)
NVarchar(4000) stores a maximum of 4000 characters. Varchar(max) stores a maximum of 1 073 741 823 characters or 1GB.
Variable length Unicode Characters data.
The storage size, in bytes, is two times the number of characters entered + 2 bytes
The SQL-2003 synonyms for nvarchar are national char varying and national character varying.
 
Differences between NCHAR AND NVARCHAR
NCHAR
Fixed-length. so any remaining space in the field is padded with blanks.
Unicode character data with a length of n bytes, n must be a value from 1 through 4,000. The storage size is n bytes
The ISO synonyms for nchar are national char and national character.
 
NVARCHAR
Variable-length, non-Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2GB).
The storage size is in bytes, is two times the actual length of data entered+ 2 bytes
The ISO synonyms for nvarchar are national char varying and national character varying.
 
Conclusion:
Use nchar when the sizes of the column data entries are probably going to be similar.
Use nvarchar when the sizes of the column data entries are probably going to vary considerably.

Differences between NTEXT, TEXT AND IMAGE
NTEXT
Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823).
Storage size, in bytes, is two times the string length entered.
The ISO synonym for ntext is national text.
 
TEXT
Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647).
When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes.
Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

IMAGE
Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
 
NUMERIC DATATYPES
 
Difference between Decimal[ (p[ ,s] )] and Numeric[(p[ ,s] )]
 
Fixed precision and scale numbers.
When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1.
The ISO synonyms for decimal are dec and dec(p, s).
Numeric is functionally equivalent to decimal.

p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point.
The precision must be a value from 1 through the maximum precision of 38.
The default precision is 18.
 
s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point.
Scale must be a value from 0 through p.
Scale can be specified only if precision is specified.
The default scale is 0; therefore, 0 <= s <= p.
Maximum storage sizes vary, based on the precision.
 
Precision Storage bytes
 
1 - 9 5
10-19 9
20-28 13
29-38 17
 
Difference between Float and Real
 
Data type: float
Range: - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
Storage: Depends on the value of n
 
Data type: real
Range: - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38
Storage: 4 Bytes
 
nvalue: 1-24
Precision: 7 digits
Storage size: 4 bytes
 
nvalue: 25-53
Precision: 15 digits
Storage size: 8 bytes
 

Differences between INT, BIGINT, SMALLINT and TINYINT
 
Data type: int
Range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
Storage: 4 Bytes
 
Data type: bigint
Range: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
Storage: 8 Bytes
 
Data type: smallint
Range: -2^15 (-32,768) to 2^15-1 (32,767)
Storage: 2 Bytes
 
Data type: tinyint
Range: 0 to 255
Storage: 1 Bytes
 
Conclusion:
 
The int data type is the primary integer data type in SQL Server.
 
The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.
 
bigint fits between smallmoney and int in the data type precedence chart.
 
Functions return bigint only if the parameter expression is a bigint data type.
 
SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.
 

Differences between MONEY and SMALLMONEY
 
Data type: money
Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Storage: 8 Bytes
 
Data type: money
Range: - 214,748.3648 to 214,748.3647
Storage: 4 Bytes
 

Conclusion:
The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.

Editions of Sql server 2008

Editions of Sql server 2008

• Enterprise: this is the high-end edition with the full feature set. It supports systems up to 8 CPU and 2 TB RAM. The maximum size of the database is 524 PB.
• Standard it has a bit less features than Enterprise, but it is a good choice when advanced functions (such as data compression, compressed backups, indexed views, etc.) are not required for the business. It supports systems up to 4 CPU and 64 GB RAM.
• Workgroup: it is suitable for remote offices of a larger company. It supports systems up to 2 CPU and 4 GB RAM.
• Web: it is designed for web applications. It supports 4 CPU without memory limitations.
• Developer: similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation.
• Express: free entry level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB.
• Compact: free embedded database for mobile application development. The maximum size of the database is 4 GB.

Difference Between Sqlserver2008 and Sqlserver2008R2

SQL 2008 R2 Express is the latest version, it could has been called
something like SQL-2008 Express Service Pack 2 or SQL 2010 Express but they
have chosen to call it SQL 2008 R2 Express. Beside some bugs that have been
corrected, the main new feature of this release is the maximum size of a
database that has been increased from 4Gigs to 10Gigs. All the other
limitations have remained the same.

Difference Between Delete and Truncate

Delete	Truncate
Delete is an DML (Data Manipulation Language) command	Truncate is DDL (Data Definition Language) command
Delete is a row level operation. Only a specific row is effected	Truncate is Table level operation, whole table data is effected.
Delete statements gets executed using Row Lock meaning, each row on a table is locked for deletion	Truncate Table command locks the complete table and page but not row.
We can filter the row and delete using WHERE clause	Truncate cannot use any filters, Whole table is Truncated.
If a table is having Identity Column, then executing Delete statement does not reset identity value	If a table is having identity column, then executing Truncate reset identity value.
Delete statement deletes the specific rows which are mentioned in where condition	Truncate removes all the data of the table.
Delete activates a trigger as it maintains log for each file	Truncate table cannot activate trigger as the operation doesn’t log individual row deletions.
Slower in execution compared to Truncate as it maintains the logs for every record.	Faster than Delete statement as it is logged minimum is transaction log.
Delete statements keeps object’s statistics and all allocated space. After a DELETE statement is executed, the table can still contain empty pages.	Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table
Delete statement removes rows one at a tme and records an entry in the Transaction log for each deleted row.	TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log
Delete works at row level thus, row level restrictions apply.	Restrictions on Using Truncate are: Column Values should not be referenced by Foreign Key Participate in Indexed view  Are Published by using transactional replication or merge replication.

Difference Between sqlserver 2008 and sqlserver 2012

S.No	SQL Server 2008	SQL Server 2012
1	Maximum number of  concurrent connections: The Maximum number of concurrent connections to SQL Server 2008 is 32767.	Maximum number of  concurrent connections:  SQL server 2012 has unlimited concurrent connections.
2	Precision used for spatial calculations: The SQL Server 2008 uses 27 bit bit precision for spatial calculations.	Precision used for spatial calculations: The SQL Server 2012 uses 48 bit precision for spatial calculations
3	TRY_CONVERT() and FORMAT() functions:  TRY_CONVERT() and FORMAT() functions are not available in SQL Server 2008	TRY_CONVERT() and FORMAT() functions:   TRY_CONVERT() and FORMAT() functions are newly included in SQL Server 2012
4	ORDER BY Clause with  OFFSET / FETCH options: ORDER BY Clause does not have OFFSET / FETCH options as in SQL Server 2012	ORDER BY Clause with OFFSET / FETCH options: ORDER BY Clause now have OFFSET / FETCH options to use paging to show required rows per page in applications and allow the user to scroll through each page of results rather than download the entire set In the sample query below, SQL Server would return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time. SELECT BusinessEntityID, FirstName, LastName FROM Person.Person ORDER BY BusinessEntityID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
5	Code Name: SQL Server 2008 is code named as Katmai.	Code Name: SQL Server 2012 is code named as Denali
	In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition.	In SQL Server 2012,support for server auditing is expanded to include all editions of SQL Server.
7	Sequence Object: Sequence is not available in SQL Server 2008	Sequence Object: Sequence is included in SQL Server 2012.Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence. /****** Create Sequence Object ******/ CREATE SEQUENCE MySequence START WITH 1 INCREMENT BY 1; /****** Create Temp Table ******/ DECLARE @Person TABLE ( ID int NOT NULL PRIMARY KEY, FullName nvarchar(100) NOT NULL ); /****** Insert Some Data ******/ INSERT @Person (ID, FullName) VALUES (NEXT VALUE FOR MySequence, 'Umar Ali'), (NEXT VALUE FOR MySequence, 'John Peter'), (NEXT VALUE FOR MySequence, 'Mohamed Iqbal'); /****** Show the Data ******/ SELECT * FROM @Person; The results would look like this: ID FullName 1 Umar Ali 2 John Peter 3 Mohamed Iqbal
8	Full Text Search Capability: The Full Text Search in SQL Server 2008 does not allow us to search and index data stored in extended properties or metadata.	Full Text Search Capability: The Full Text Search in SQL Server 2012 has been enhanced by allowing us to search and index data stored in extended properties or metadata. Consider a PDF document that has "properties" filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest release of SQL Server, this data could be indexes and searched along with the data in the document itself. The data does have to be exposed to work, but it's possible now.
9	BISM Model: Analysis Services in SQL Server does not have BI Semantic Model (BISM) concept.	BISM Model:  Analysis Services will include a new BI Semantic Model (BISM). BISM is a 3-layer model that includes: Data Model Business Logic Data Access BISM will enhance Microsoft's front end analysis experiencing including Excel, Reporting Services and SharePoint Insights. Microsoft has said that BISM is not a replacement for the current BI Models but more of an alternative model. In simple terms, BISM is a relation model that includes BI artifact such as KPIs and hierarchies.

Sr No	SQL Server 2005	SQL Server 2008
1	XML datatype is introduced.	XML datatype is used.
2	Can not encrypt the entire database.	Can encrypt the entire database introduced in 2008.
3	Datetime is used for both date and time.	Date and time are seperately used for date and time
4	No table datatype is included.	Table datatype introduced.
5	SSIS is started using.	SSIS avails in this version.
6	CMS is not available.	Central Management Server(CMS) is Introduced.
7	PBM is not available	Policy based management(PBM) server is Introduced.

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

Difference Between Table variable and temporary Table

Feature	Table Variables	Temporary Tables
Scope	Current batch	Current session, nested stored procedures. Global: all sessions.
Usage	UDFs, Stored Procedures, Triggers, Batches.	Stored Procedures, Triggers, Batches.
Creation	DECLARE statement only.	CREATE TABLE statement. SELECT INTO statement.
Table name	Maximum 128 characters.	Maximum 116 characters.
Column data types	Can use user-defined data types. Can use XML collections.	User-defined data types and XML collections must be in tempdb to use.
Collation	String columns inherit collation from current database.	String columns inherit collation from tempdb database.
Indexes	Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.	Indexes can be added after the table has been created.
Constraints	PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.	PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
Post-creation DDL (indexes, columns)	Statements are not allowed.	Statements are allowed.
Data insertion	INSERT statement (SQL 2000: cannot use INSERT/EXEC).	INSERT statement, including INSERT/EXEC. SELECT INTO statement.
Insert explicit values into identity columns (SET IDENTITY_INSERT).	The SET IDENTITY_INSERT statement is not supported.	The SET IDENTITY_INSERT statement is supported.
Truncate table	Not allowed.	Allowed.
Destruction	Automatically at the end of the batch.	Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
Transactions	Last only for length of update against the table variable. Uses less than temporary tables.	Last for the length of the transaction. Uses more than table variables.
Stored procedure recompilations	Not applicable.	Creating temp table and data inserts cause procedure recompilations.
Rollbacks	Not affected (Data not rolled back).	Affected (Data is rolled back).
Statistics	Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.	Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
Pass to stored procedures	SQL 2008 only, with predefined user-defined table type.	Not allowed to pass, but they are still in scope to nested procedures.
Explicitly named objects (indexes, constraints).	Not allowed.	Allowed, but be aware of multi-user issues.
Dynamic SQL	Must declare table variable inside the dynamic SQL.	Can use temporary tables created prior to calling the dynamic sql.

Complex sql query -group by

What is the average total amount of penalties for players who live in Stratford and Inglewood? 



SELECT AVG(TOTAL) FROM (SELECT PLAYERNO, SUM(AMOUNT) AS TOTAL FROM PENALTIES GROUP BY PLAYERNO) AS TOTALS WHERE PLAYERNO IN (SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford' OR TOWN = 'Inglewood') 


------------------------------------------------------------------------------------------------------------