One Bit per Atom

IBM has, in the laboratory, been able to store a single bit on a single atom.

http://thehackernews.com/2017/03/atom-data-storage.html

Ordering mixed Alpha and Digit Characters

Suppose you have a VARCHAR column that has a mix of alpha and digit characters. In other words, the values represent different types across rows.

Like this:

SELECT   mixedColumn
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)

Which produces:

So how does this get sorted, by default?

SELECT   mixedAlphaColumn
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedAlphaColumn)
ORDER BY mixedAlphaColumn

Which produces:

This gets ordered based on its ASCII code value. Since all ASCII digit characters come before alpha characters, digits will come before alpha when using ORDER BY.  You can see this with this code:

SELECT   mixedColumn,
         ASCII(LEFT(mixedColumn, 1)) AS [ASCII Value]
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY mixedColumn

Which produces:

But what if we want to have the digit characters come after the alpha characters?

We could do this:

SELECT   mixedColumn,
         ASCII(LEFT(mixedColumn, 1)) AS [ASCII Value]
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY mixedColumn DESC

Which produces this:

But what if we want the digits ordered ascending, and the alpha ordered ascending, but the alpha before the digits?

Here's the trick - the ISNUMERIC function.

SELECT   mixedColumn,
         ISNUMERIC(mixedColumn) AS [isNumeric]
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY ISNUMERIC(mixedColumn),
         mixedColumn

Which produces:

And of course, we don't need to display the isNumeric column:

SELECT   mixedColumn
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY ISNUMERIC(mixedColumn),
         mixedColumn

Which produces:

The ISNUMERIC function became available in SQL 2008. However, be careful, since certain characters that you might expect to be alpha are actually interpreted as digit, e.g. periods, commas, plus sign, etc.

Can I use NULL with the IN operator?

Can I use NULL with the IN operator?

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 9/8/2016
-- the IN operator and NULL
-- aka Can I use IN with NULL?



-- the short answer is no
-- but why?

-- a couple of simple examples

SELECT 1
WHERE  NULL IN (1, 2, 3)

-- notice that NULL is not IN a list that contains a NULL

SELECT 1
WHERE  NULL IN (NULL, 1, 2, 3)

-- ok, let's look at a table I have

USE ReferenceDB


-- this is very close to what I saw in a piece of code
SELECT  lastname,
        email
FROM    Person
WHERE   email IN (NULL, '')


-- so are there zero-length strings? yes
SELECT  lastname,
        email
FROM    Person
WHERE   email = ''

-- are there NULLs? no?
SELECT  lastname,
        email
FROM    Person
WHERE   email = NULL

-- ah, for test for a NULL value, you need the IS operator
SELECT  lastname,
        email
FROM    Person
WHERE   email IS NULL

-- and more proof...
SELECT 1
WHERE  NULL = NULL

SELECT 1
WHERE  NULL IS NULL

-- but is an IN *really* just a translation of a sequence of OR'ed equalities?

SELECT  lastname,
        email
FROM    Person
WHERE   email IN (NULL, '')

SELECT  lastname,
        email
FROM    Person
WHERE   email = NULL
   OR   email = ''

SELECT  lastname,
        email
FROM    Person
WHERE   email IN (NULL, '', 'fred')

-- so, the query optimizer is smart enough to
-- remove the NULL from the IN list, since it doesn't matter

Simple indexing and what to look at for database performance

Simple Indexing and what to look at for database performance

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 7/8/2016
-- simple indexing - an introduction

-- the example that's in every book
-- but more about why it works, and what to look at

USE ReferenceDB

SELECT *
FROM   Person


/****** Object:  Table [dbo].[Person]    Script Date: 7/8/2016 10:55:36 AM ******/
--CREATE TABLE [dbo].[Person](
-- [ID] [int] IDENTITY(1,1) NOT NULL,
-- [salutory] [nvarchar](15) NULL,
-- [firstName] [nvarchar](25) NULL,
-- [middleInitial] [nchar](1) NULL,
-- [lastName] [nvarchar](25) NOT NULL,
-- [nameSuffix] [nvarchar](8) NULL,
-- [email] [nvarchar](255) NULL,
-- [entryDate] [datetime] NOT NULL CONSTRAINT [DF_Person_entryDate]  DEFAULT (getdate()),
-- [lastUpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Person_lastUpdateDate]  DEFAULT (getdate()),
-- [weight] [float] NULL,
-- [gender] [char](1) NULL,
-- [dateOfBirth] [date] NULL,
-- [bogusGUID] [uniqueidentifier] ROWGUIDCOL  NULL CONSTRAINT [DF_Person_bogusGUID]  DEFAULT (newid()),
-- [bogusGUID2] [uniqueidentifier] NULL CONSTRAINT [DF_Person_bogusGUID2]  DEFAULT (newid()),
-- [bogusChar] [char](100) NOT NULL CONSTRAINT [DF_Person_bogusChar]  DEFAULT ('fred'),
-- [bogusNChar] [nchar](100) NOT NULL CONSTRAINT [DF_Person_bogusNChar]  DEFAULT (N'george'),
-- CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
--(
-- [ID] ASC
--)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 60) ON [PRIMARY]
--) ON [PRIMARY]

-- typical hard disk seek time:           6.43 ms
-- typical RAM seek time (CAS latency):  14.00 ns

-- note the ms and ns difference
-- in the same units
-- typical hard disk seek time:        6430.00 ms
-- typical RAM seek time (CAS latency):  14.00 ns

-- so RAM is roughly 500 times faster than a spinning hard drive
-- but it is also a lot more expensive, and volatile

-- Databases are built to leverage this difference
-- use both RAM and disk, each for what its best at

SET STATISTICS IO ON -- see the IO involved in each query

SELECT *
FROM   Person

-- logical reads: 138533
-- physical reads: 0

-- these are "page reads"
-- each page is 8Kb
-- they are the amount of data moved between RAM and disk

-- this means the entire table is in memory

-- but it might not be

-- in a "real" database, you might have 10 TB of data, but 16 GB of RAM
-- you can't keep all data in RAM

-- so think of logical page reads as "gifts"
-- you got lucky, the data you needed happened to be in RAM
-- and therefore came back 500 times faster than it might have

-- the best thing is to reduce the overall number of 
-- page reads, logical or physical

SELECT  firstname
FROM    Person

-- lots faster, but same number of reads
-- this is due to less data coming back over the network

SELECT *
FROM   Person
WHERE  ID = 2417634

-- almost instant
-- page IOs: 3

-- what about his?
SELECT *
FROM   Person
WHERE  lastname = 'kline'

-- page IOs: 138534

-- what is special about ID?

-- there is an index on ID

-- what if I want to make the lastname lookup faster?

-- we could create an index that would make that faster

USE [ReferenceDB]
GO

/****** a really simple create index statement
CREATE NONCLUSTERED INDEX [IX_LastName] ON [dbo].[Person]
(
 [lastName] ASC
)   
GO
 ******/

 -- try again?
SELECT *
FROM   Person
WHERE  lastname = 'kline'

-- page IOs: 393, vs. 138534 without the index

-- that's the perfect scenario for an index
-- it's what is covered in all the basic DB books

-- this is all upside
-- there are significant downsides to indexes
-- particularly regarding INSERTS, UPDATES, and DELETES
-- in future videos

-- books also tend to focus on the data structure performance
-- in other words, balanced trees and the *computational* efficiency of them

-- the real performance benefits are all in reduced disk IO

-- in the above, I'm not measuring performance by CPU
-- I'm measuring performance by disk IOs

-- Why? 

-- because CPU is 1000(?) times faster than RAM

-- for DB, generally worry about performance in this order:

-- network, which is 1000(?) slower than
-- disk,    which is 1000(?) slower than
-- RAM,     which is 1000(?) slower than
-- CPU


-- Database by Doug
-- Douglas Kline
-- 7/8/2016
-- simple indexing