Comparison of features, capabilities, prices. Why to choose one or another.Continue reading
LocalDB is an instance of SQL Express. It is easy to use it in development from Visual Studio. I want to have localdb database files included into Visual Studio solution for easy connect to database wherever I work.
The standard software stack for a .NET developer is
- OS – desktop Windows
- IDE – Visual Studio
- Database – SQL Server
All these components are quite pricey. But there are free alternatives. And with my recent project I decided to use alternative software for development and production in .NET, all totally free.
- OS – Linux Mint 17 (based on Debian/Ubuntu)
- IDE – MonoDevelop
- Database – MySQL Community Edition
In a queries like
SELECT dividend/divisor AS data FROM table
The divisor can have a zero value, then SQL server returns an error “Division by zero encountered”.
To avoid this we have to always check the divisor for zero value
CASE WHEN divisor=0 THEN 0 ELSE dividend/divisor END AS data FROM table
Starts from version 2008 there are special types to store only date or time. These are Date and Time accordingly. But if a program should support old versions of SQL server then both date and time are stored together in DateTime field. It looks like for example ‘2012-09-02 02:07:38.430’.
Say we need to find all records for one day 2012-09-02.
The simple string comparison works in 2000, 2005 and 2008 versions if date format is correct.
SELECT * FROM SpecialDay WHERE [Date]='2012-09-02'
But this is not safe. And starts from SQL server 2012 this query will not return anything even with the correct same time as in database records.
SELECT * FROM SpecialDay WHERE [Date]='2012-09-02 00:00:00.000'
Then I learned to do it better way:
DECLARE @date DATETIME; SET @date = CONVERT(DATETIME, '2012-09-02', 120); SELECT * FROM SpecialDay WHERE [Date] >= @date AND [Date] < DATEADD(d, 1, @date)
I tried it in all versions of SQL server starts from 2000 and works everywhere.
Here the statement
CONVERT(DATETIME, '2012-09-02', 120) converts a string to a date format type 120. This is so caled ODBC-format ‘yyyy-mm-dd hh:mm:ss’ 24-hours. String can be converted to Date-type can be converted with any separator character: slash, point or whatever.