Comparing some differences of SQL Server to SQLite


By:   |   Updated: 2017-03-23   |   Comments   |   Related: More > Other Database Platforms

Problem

What are some of the differences in the way that Microsoft SQL Server and SQLite implement the SQL Language that a SQL Server DBA should be aware of?

Solution

Microsoft SQL Server is a powerful, full featured SQL system that supports entire ecosystems. While SQLite is a light-weight database meant to be embedded into end programs. SQLite is often useful even to SQL Server professionals when they need to create smaller databases without a full server setup to support. It can also be useful for rapid prototyping before moving to SQL Server.

The differences between their capabilities and the way they work is enormous. One way they differ is in the way they implement certain aspects of the SQL language. Without trying to be comprehensive, this article will look at some of the differences in the way they each implement the SQL language.

SQL Server uses static typing and SQLite uses dynamic typing

In SQL Server, every column is assigned a datatype, and every value in that column must be of that datatype, though the server may do implicit conversions to simplify inserts and updates under some circumstances. The datatype of the column can have significant implications for size and performance.

SQLite, on the other hand, uses a more dynamic type system and tracks the datatype with the value, rather than with the column. It is not necessary to assign a datatype at all when creating a table in SQLite, so this would be perfectly valid:

CREATE TABLE NoDatatypes (
    col1,
    col2)

SQLite does allow datatypes to be specified if desired, but (except for integer primary key columns), these are mere type affinities and SQLite will still allow any type of data to be inserted. SQLite recognizes Text, Numeric, Integer, Real, and Blob affinities. Blob is similar to the varbinary type in SQL Server. When an affinity is specified, SQLite will attempt to convert to the preferred datatype, but will not prevent other datatypes which cannot be converted from being entered. For example:

CREATE TABLE withdatatypes 
  ( 
     textcol    TEXT, 
     integercol INTEGER, 
     realcol    REAL 
  ); 

INSERT INTO withdatatypes 
VALUES      (1.0,--Will be converted to Text  
             '1.0',-- Will be converted to Int 
             '1.1'); -- Will be converted to Real 

INSERT INTO withdatatypes 
VALUES      (2.2,--Will be converted to text  
             'Into IntegerCol',--accepted in IntegerCol 
             1.0 / 2.0); --Calculation performed and added as real

INSERT INTO withdatatypes 
VALUES      (3.0, --Converted to text
             3.1,--Accepted in IntegerCol  
             'Text in Real Col'); --Accepted in RealCol

SELECT * 
FROM   withdatatypes; 

Produces:

SQLite Query Results with Datatypes

We can directly query the datatypes of the cells with:

SELECT 
    typeof(textcol) as textcol, typeof(integercol) as intergercol, typeof(realcol) as realcol
FROM 
    withdatatypes;

Which gives us:

SQLite Datatypes Query

SQLite allows for "if exists" in drop statements and "if not exists" in drop statements

SQL Server 2016 adds the ability to drop certain objects if they exists. Earlier versions of SQL Server required the developer to check for the existence of the object before dropping it. Like SQL Server 2016, SQLite allows for objects to be dropped if they exist.

SQLite also makes it simple to create an object if it does not exist without explicitly checking for existence first. This is useful in programs where you may want to append to a table if it exists, but create it and begin adding new data if it does not already exist. The syntax looks like:

CREATE TABLE IF NOT EXISTS withdatatypes 
  ( 
     textcol    TEXT, 
     integercol INTEGER, 
     realcol    REAL 
  ); 

SQLITE does not support TOP, but does support Limit

In SQL Server, TOP is used to limit the number of rows returned by a query. SQLite does not support top, but it has a similar LIMIT which is placed at the end of the query.

SELECT
    textcol, integercol, realcol
FROM
    withdatatypes
LIMIT 2

Joins and Using

SQLite, much like SQL Server, has robust support for joining tables in a query. It supports cross joins, inner joins, and left out joins. SQLite does not support right or full outer joins.

SQLite allows a simplified format for some inner joins with USING and NATURAL JOIN. USING looks for exact matches between the specified columns in the two tables. So, if we use our withdatatypes table from above and create a new table with a matching column, it could look like:

DROP TABLE IF EXISTS datatypes2; 

CREATE TABLE datatypes2 
  ( 
     textcol    TEXT, 
     numericcol NUMERIC, 
     blobcol    BLOB 
  ); 

INSERT INTO datatypes2 
VALUES      ('1.0', 
             1.11, 
             1.12); 

INSERT INTO datatypes2 
VALUES      ('2.2', 
             '2.2222', 
             '3.14'); 

SELECT wd.*, 
       dt2.* 
FROM   withdatatypes wd 
       JOIN datatypes2 dt2 USING (textcol); 

SQLite Join Results

NATURAL JOIN will compare every column in the two tables that has a matching column name.

SELECT wd.*, 
       dt2.* 
FROM   withdatatypes wd 
       NATURAL JOIN datatypes2 dt2 

This is logically equivalent to the select statement using JOIN ... USING and gives the same results.

Next Steps


Last Updated: 2017-03-23


get scripts

next tip button



About the author
MSSQLTips author Tim Wiseman Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools