Comparing some differences of SQL Server to SQLite

By: Tim Wiseman | Comments | Related: > 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:

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 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);

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

Graham Okley describes how to Create a SQL Server Linked Server to SQLite to Import Data.
Tibor Nagy has a description of some of the differences between SQL Server and MySQL.
Python's Regex capabilities can be used for ETL with either SQL Server or SQLite and converting between the two in Python code can be relatively simple.

About the author

Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips