SQL Edition

Friends, Today i will let you know the all SQL edition which Microsoft makes. 

Microsoft makes SQL server available in multiple editions, including free edition , this is called express edition. Any user can access this database from Microsoft website and download locally and use. This database also distributed with applications or used to learn abount SQL server.

Full featured editions (Standard, Business Intelligence and Enterprise) that are used to store terabytes of data or more. This database is used enterprises wide. 

We have compact edition which can be used for mobile or smart phones.

Developer edition , this is fully featured but used only for development purposes.

Edition	usage	Expense
Compact	Occasionally connected systems including mobile devices	Free
Express	Great for learning SQL Server and can be distributed with applications	Free
Web	Used for small web sites	Inexpensive
Workgroup	Used for workgroups or small database applications	Inexpensive
Developer	Full featured but used for development only	Inexpensive
Standard	Full featured but used for development only	Expensive
Enterprise	All available features	Very Expensive
Business Intelligence	Used in both large and small companies to deploy comprehensive Business Intelligence solutions	Expensive

How do we configure SQL Server Express to allow remote tcp/ip connections on port 1433?

How do we configure SQL Server Express to allow remote tcp/ip connections on port 1433?

• Run SQL Server Configuration Manager.
• Go to SQL Server Network Configuration > Protocols for SQLEXPRESS.
• Make sure TCP/IP is enabled.

1.       Right-click on TCP/IP and select Properties.

2.       Verify that, under IP2, the IP Address is set to the computer's IP address on the local subnet.

3.       Scroll down to IP All.

4.       Make sure that TCP Dynamic Ports is blank. (This is possible there can be  5-digit port number.)

5.       Make sure that TCP Port is set to 1433.

SQL Release

convert function (Date conversion)

We have used convert function for conversion of data type.

Numeric or a date to a string, the number or date must first be cast to a string. The CONVERT function has an optional parameter called style that can be used to format a date.

We have many time used cast to format the date in various style.

SELECT CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '/' +

CAST(DATEPART(MM,GETDATE()) AS VARCHAR) +

'/' + CAST(DATEPART(DD,GETDATE()) AS VARCHAR) AS fnDateCast;

We can achive this by accomplishing convert in our sql statements.

Syntax:-

CONVERT(<data type, usually varchar>,<date>,<style>)

DECLARE @TODAY DATETIME=GETDATE();

SELECT CONVERT(VARCHAR,@TODAY,1) AS "1",

CONVERT(VARCHAR,@TODAY,101) AS "101",

 CONVERT(VARCHAR,@TODAY,2) AS "2",

CONVERT(VARCHAR,@TODAY,102) AS "102",

CONVERT(VARCHAR(10),GETDATE(),10) AS "10",

CONVERT(VARCHAR(10),GETDATE(),110) AS "110" ,

CONVERT(VARCHAR(11),GETDATE(),6) AS "6"   ,

CONVERT(VARCHAR(11),GETDATE(),106)AS "106"       ,

CONVERT(VARCHAR(24),GETDATE(),113) AS "113";

Standard Date conversion in  different Formats
Date Format	Standard	SQL Statement	Sample Output
Mon DD YYYY 1 HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)	Jun 8 2011 1:30PM 1
MM/DD/YY	USA	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]	06/08/11
MM/DD/YYYY	USA	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]	06/08/2011
YY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]	11.06.08
YYYY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]	2011.06.08
DD/MM/YY	British/French	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]	08/06/11
DD/MM/YYYY	British/French	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]	08/06/2011
DD.MM.YY	German	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]	08.06.11
DD.MM.YYYY	German	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]	08.06.2011
DD-MM-YY	Italian	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]	08-06-11
DD-MM-YYYY	Italian	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY]	08-06-2011
DD Mon YY 1	-	SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]	08 Jun 11 1
DD Mon YYYY 1	-	SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]	08 Jun 2011 1
Mon DD, YY 1	-	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]	Jun 08, 11 1
Mon DD, YYYY 1	-	SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]	Jun 08, 2011 1
HH:MM:SS	-	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8) SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108)	13:30:45
Mon D YYYY H:MI:SS.NNNNNNNAM (or PM) 1	Default + nanoseconds	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9) SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)	Jun 8 2011 1:30:45.9428675PM 1
MM-DD-YY	USA	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]	06-08-11
MM-DD-YYYY	USA	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY]	06-08-2011
YY/MM/DD	-	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD]	11/06/08
YYYY/MM/DD	-	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD]	2011/06/08
YYMMDD	ISO	SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]	110608
YYYYMMDD	ISO	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]	20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1	Europe default + nanoseconds	SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13) SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113)	08 Jun 2011 13:30:45.94286751
HH:MI:SS.NNNNNNN(24H)	-	SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 14) AS [HH:MI:SS:MMM(24H)] SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 114) AS [HH:MI:SS:MMM(24H)]	13:30:45.9428675
YYYY-MM-DD HH:MI:SS(24h)	ODBC Canonical	SELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120)	2011-06-08 13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)	ODBC Canonical (with nanoseconds)	SELECT CONVERT(VARCHAR(23), SYSDATETIME(), 121)	2011-06-08 13:30:45.9428675
MM/DD/YY HH:MI:SS AM	-	SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22)	06/08/11 1:30:45 PM
YYYY-MM-DD	-	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 23)	2011-06-091
HH:MI:SS (24h)	-	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 24)	13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN	-	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25)	2011-06-08 13:30:45.94286751
YYYY-MM-DDTHH:MM:SS:NNNNNNN	ISO8601	SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126)	2011-06-08T13:30:45.9428675
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1	Kuwaiti	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)	08 Jun 2011 1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131)	08/06/2011 1:30:45.9428675PM

combining non string datatype

COMBINING NON STRING DATATYPE WITH STRING

When we are combining string we have CONCAT function in SQL 2012 but in earlier version we are using operator (+).

Now in case we have to contact Date, Numeric values with string how we will do this? If we use operator (+) then this will give error.

Conversion failed when converting the varchar value 'dada' to data type int.

What we can do here, we have to make data compatible, for concatenating date or numeric values we need to convert their type to string then we can CONCAT both.

How we will do this?

We have another method (CAST, CONVERT), both can be used to convert the data type.

To concatenate non string values to strings, the non-string value must be converted to a string. If the string value can be implicitly converted to a number, the values will be added together instead.

Example:-

select 1+'1'

If we are getting result as 11 instead of 2, then numeric value must be converted to a string.

This can be achieved using CAST or CONVERT function.

If you attempt to concatenate a non-numeric string and a number without converting, you will receive an error message. Run this example to see the error:

Example 1

SELECT 1 + 'abc';

You will get this error.

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value 'abc' to data type int.

Example 2

select 'Todays date : '+GETDATE();

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

Function CAST or CONVERT can be used to convert a numeric or temporal value to a string.

Syntax :

CAST(<value> AS <new data type>)

CONVERT(<new data type>,<value>)

Using convert function we can use as:-

select 'Todays date : '+convert(varchar(20),GETDATE());

Result: - Todays date : Jul 24 2015  1:46PM

select 'Shyam age is : ' + CAST(32 as varchar(10))+' Years';

Result:-

Shyam age is : 32 Years