Blind Spot - T-SQL $Identity

I've been working with SQL Server for 12 years, but this short cut escaped me.

In Transact-SQL, the $IDENTITY keyword allows us to refer to the identity column without knowing its name. Since identity columns are often primary keys, we could use this as a short cut for always referring to the PK. This would be awesome to use in a code generator or an ORM.

This is not to be confused with @@IDENTITY or SCOPE_IDENTITY(), which tells us the actual number auto generated by the IDENTITY column (though each behaves in different ways.)

Test it out with this script:

use tempdb
go

create table test
(
id int identity(1,1)
, descr varchar(100)
)
go

insert into test
(descr)
values
('first record')
, ('second record')
, ('third record')
go

select $identity, descr from test

TIG Notes

Yesterday at TIG I mentioned that the Visual Studio tips from Sara Ford had helped me to be a faster developer and worker. Many of you asked where you could find her blog, so here it is:

Sara Ford's BLOG

San Diego TIG - 2nd Meeting

The Tech Immersion Group met last night, and we had a great time. Next month, we'll be reading Head First C#, Chapters 6 - 8, and Lab 2. We'll be meeting on October 27th.

We also setup a new Google Group. DM me for an invitation.

San Diego Tech Immersion Group

TIG - Tech Immersion Group

WHAT

The Tech Immersion Group is founded on the belief that learning new technology can be fun. Our first topic will be C# and .NET. Future topics include Silverlight, SharePoint, WCF, Entity Framework, LINQ, and Azure. The idea is as follows:

1) We will meet monthly to discuss about 200 – 300 pages of a book we’ve chosen.

2) We will stay with the same topic for several months (probably until we’re finished with the book.)

3) We will study for certification exams together.

4) We will write sample code and software projects.

5) This will be a safe place where any question can be asked (Ugh, I hope you get a good answer).



WHO

All are welcome to join the group. We do need to know who they are ahead of time so we can ensure a book is provided. We’ve been able to get our first book donated by O’Reilly Press. Hopefully, we can keep this sweet deal going.

I’m Ike Ellis, and I’ve been writing software for 12 years. Software I have written is currently in use by several thousand people. I love to teach and I love to learn. I can’t wait to meet you and get started on this together!

THE COMMITMENT

You will get a free book, free time from a mentor, and really valuable knowledge. In return, we ask:

1) That you show up on time and ready to learn.

2) That you commit to being there every month.

3) That you consider becoming a member of the San Diego .NET User Group ($50 annually.)

4) That you write a review on Amazon regarding the book you got for free (This will keep the free books coming.)

That’s it! Let’s get ready to learn a ton!

WHERE

Robert Half Technology
4365 Executive Drive
Suite 450
San Diego, CA 92121

WHEN

The 4th Wednesday of every month. Please be there at 5:45pm, as the doors to our building will lock by 6pm. Don’t be late!

Ike Ellis Contact Info
Twitter: @EllisTeam1
FaceBook: http://www.facebook.com/ellisike
Linked In
http://ellisteam.blogspot.com
http://www.ellisteam.net



A BIG THANK YOU TO O'RIELLY FOR DONATING OUR BOOKS!!!
Visit them at http://www.oreilly.com.

T-SQL Session, Promised Script

Several people asked that I post this script on T-SQL fundementals:

USE Northwind

SELECT *
FROM Customers;

SELECT CompanyName
, ContactName
, ContactTitle
--, Address
--, City
--, Region
--, PostalCode
FROM Customers;


SELECT CompanyName
, ContactName
, ContactTitle
, Address
, City
, Region
, PostalCode
FROM Customers
ORDER BY ContactName;




SELECT CompanyName
, ContactName
, ContactTitle
, Address
, City
, Region
, PostalCode
FROM Customers
ORDER BY CompanyName, ContactName DESC; --ASC is the default.

SELECT CompanyName
, ContactName
, ContactTitle
, Address
, City
, Region
, PostalCode
FROM Customers
WHERE CompanyName = 'Alfreds Futterkiste'
ORDER BY ContactName DESC; --ASC is the default.

--Comparison Operators
--=
--<>
--!=
--<
--<=
--!<
-->
-->=
--!>
--BETWEEN
--IS NULL

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight > 100;

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight !> '100';

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight <= '100' ;

SELECT CustomerID
, Region
FROM Customers
WHERE Region <> NULL;

SELECT CustomerID
, Region
FROM Customers
WHERE Region <> 'CA';

SELECT CustomerID
, Region
FROM Customers
WHERE Region <> 'CA' OR Region IS NOT NULL;

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight BETWEEN '10.14' AND '43.90'
ORDER BY Freight ;

--Logical Operators
--AND
--OR
--NOT

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight BETWEEN '10.14' AND '43.90'
OR ShippedDate BETWEEN '1/1/1997' AND '12/1/1997'
ORDER BY ShippedDate, Freight ;

SELECT CustomerID
, OrderID
, Freight
, ShippedDate
FROM Orders
WHERE Freight BETWEEN '10.14' AND '43.90'
OR ShippedDate BETWEEN '1/1/1997' AND '12/1/1997'
ORDER BY ShippedDate, Freight ;


--I want all orders from CustomerID = VINET with a freight over $10 and all orders from CustomerID TOMSP with a freight over $10.
SELECT CustomerID
, OrderID
, Freight
FROM Orders
WHERE Freight > '10'
AND CustomerID = 'VINET'
OR CustomerID = 'TOMSP'
ORDER BY CustomerID;

--SQL is reading this from left to right. So technically we can do this:
SELECT CustomerID
, OrderID
, Freight
FROM Orders
WHERE CustomerID = 'VINET'
AND Freight > '10'
OR CustomerID = 'TOMSP'
AND Freight > '10'
ORDER BY CustomerID;

SELECT CustomerID
, OrderID
, Freight
FROM Orders
WHERE (CustomerID = 'VINET'
OR CustomerID = 'TOMSP')
AND Freight > '10'
ORDER BY CustomerID;

--IN Operator
SELECT CustomerID
, OrderID
FROM Orders
WHERE (CustomerID = 'VINET'
OR CustomerID = 'TOMSP'
OR CustomerID = 'HANAR'
OR CustomerID = 'SUPRD');


SELECT CustomerID
, OrderID
FROM Orders
WHERE CustomerID IN ('VINET', 'TOMSP', 'HANAR', 'SUPRD');

SELECT CustomerID
, OrderID
FROM Orders
WHERE CustomerID NOT IN ('VINET', 'TOMSP', 'HANAR', 'SUPRD');


--WILDCARDS
SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE 'A%';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE '%A';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE '%A%';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE 'W%A';


SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE 'F%A%';

SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE '[FM]%';


SELECT CompanyName + ' - ' + ContactName
+ ' - ' + ContactTitle AS HeaderLine1
FROM Customers
WHERE CompanyName + ' - ' + ContactName
+ ' - ' + ContactTitle LIKE 'A%'
ORDER BY HeaderLine1;

--Order of Operation
--1.FROM clause
--2.WHERE clause
--3.GROUP BY clause
--4.HAVING clause
--5.SELECT clause
--6.ORDER BY clause

--Calculated fields - Math
SELECT
OrderID
, ProductID
, UnitPrice
, Quantity
, UnitPrice * Quantity AS TotalPrice
FROM [Order Details];

--Math operators
-- +, -, *, /


--FUN WITH FUNCTIONS!

--Figuring out envelope size for a mailing
SELECT CompanyName
, LEN(CompanyName) AS NumberOfChars
FROM Customers;

--Uppercase CompanyName
SELECT UPPER(CompanyName) AS UpperCompany
FROM Customers;

--STRING FUNCTIONS
--LTRIM, RTRIM
--SUBSTRING
--RIGHT
--LEFT
--SOUNDEX

SELECT ContactName
FROM Customers
WHERE SOUNDEX(ContactName) = SOUNDEX('paul');

--Date Functions
--DATEDIFF
--DATEADD
--MONTH
--YEAR
--DATEPART

--The difference between order date and ship date in days - T-SQL help is great with Functions - SHIFT-F1
SELECT
OrderDate
, ShippedDate
, DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
FROM Orders;

--The orders that took longer than 15 days to ship
SELECT
OrderDate
, ShippedDate
, DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
FROM Orders
WHERE DATEDIFF(d, OrderDate, ShippedDate) > 15;


--Aggregates
--MAX
--MIN
--SUM
--AVG
--COUNT
--COUNT DISTINCT

--Most recent Order Date
SELECT MAX(OrderDate) AS MaxOrderDate
FROM Orders;

--First Order Date
SELECT MIN(OrderDate) AS MaxOrderDate
FROM Orders;

--Let's combine this into a subquery
SELECT OrderID
FROM Orders
WHERE OrderDate =
(
SELECT MAX(OrderDate) AS MaxOrderDate
FROM Orders
);

--Max Days to Ship --Null values out of aggregation
SELECT MAX(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders;

--Average Days to Ship
SELECT AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders;

--Orders above my average days to ship
SELECT OrderID
, CustomerID
, OrderDate
, ShippedDate
, DATEDIFF(d, OrderDate, ShippedDate) AS DaysToShip
FROM Orders
WHERE DATEDIFF(d, OrderDate, ShippedDate) >
(
SELECT AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders
) ;


SELECT CustomerID
, AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders
GROUP BY CustomerID;

--Customers with an average greater than the average
SELECT CustomerID
, AVG(DATEDIFF(d, OrderDate, ShippedDate)) AS MaxDaysToShip
FROM Orders
GROUP BY CustomerID
HAVING AVG(DATEDIFF(d, OrderDate, ShippedDate)) >= 15;

--JOINS
--INNER
--RIGHT OUTER
--LEFT OUTER

--Inner Join
SELECT c.CompanyName
, c.CustomerID
, o.OrderDate
, o.ShippedDate
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
ORDER BY c.CompanyName

--LEFT OUTER JOIN
SELECT c.CompanyName
, c.CustomerID
, o.OrderDate
, o.ShippedDate
FROM Customers c
LEFT OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL
ORDER BY c.CompanyName


SELECT *
FROM Customers

--CRUD
INSERT INTO Customers
(CustomerID, CompanyName)
VALUES
('ELLIS', 'EllisTeam')

DELETE FROM Customers
WHERE CustomerID = 'ELLIS'

UPDATE Customers
SET ContactName = 'Ike Ellis'

SELECT * FROM Customers
WHERE CustomerID = 'Ellis'

DELETE FROM Customers
WHERE CustomerID = 'ELLIS'


UPDATE o
SET Freight = Freight + 1
FROM Customers AS c
JOIN Orders AS o
ON c.CustomerID = o.CustomerID
WHERE Region = 'CA'


--Tips
--================
--Sometimes T-SQL doesn't read as intuitively as we like. Use white space and comments.

--Limit record results to as little as possible. (Good for network, disk, memory, users, and performance.)

--Nulls

--Connection Tips - Coloring and highlighting

--Always use Linenumbers

--Copy and paste results into EXCEL for adhoc stuff

--Show an error message (RAISEERROR) with severity
RAISERROR (N'This is message.', 18, 1);

--Learn about Query Plans
SELECT *
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
JOIN [Order Details] od
ON o.OrderID = od.OrderID
JOIN Products p
ON od.ProductID = p.ProductID
ORDER BY c.CompanyName

--Object Explorer Drag & Drop

--Avoid Cursors

--Be careful updating and deleting (WHERE clauses, backup, SELECT using the WHERE clause, then Delete with the same one.)

--Never change production without changing dev and beta first

--Don't bother coding for portability or language independence.

--Consider using synonyms for long table names
CREATE SYNONYM Cust FOR dbo.Customers
SELECT * FROM Cust
DROP SYNONYM Cust

--TRUNCATE VS DELETE
--Logged and WHERE
DELETE FROM Temp1

--Non-logged and no WHERE, but fast
TRUNCATE TABLE Temp1


--Common question - How to delete duplicates
CREATE TABLE dbo.duplicateTest
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White')
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

SELECT *
FROM dbo.duplicateTest

SELECT *
FROM dbo.duplicateTest
WHERE ID = 1 AND FirstName = 'Bob' AND LastName = 'Smith'

DELETE TOP(1) FROM dbo.duplicateTest WHERE ID = 1

DROP TABLE duplicateTest

UPDATE Customers
SET ContactName = 'Ike Ellis'


WHERE CustomerID = 'ELLIS'


DELETE FROM Customers
WHERE CustomerID = 'ELLIS'

Microsoft Lightswitch = Enterprise Microsoft Access

Lynn Langit blogged on Microsoft Lightswitch and that made me go check it out. It's a beta tool for Visual Studio that allows screens and database tables to be created with minimal software development or training.

This reminds me of Microsoft Access in the 1990s. Access made it so easy to create a database, data entry screens, and reports, that tons of lay people were writing custom apps for their own purposes. Because Access would collapse after it had 12 concurrent users, professional developers would need to be hired to port the app to SQL Server.

Lightswitch finally provides all of those great Access features for SQL Server, SQL Azure, and cloud development. I'll post more about it after I've played with it awhile.

Here are some links:

Lynn's Original Post

MSDN Lightswitch Site

Lightswich: 15 Reasons Non-Programmers Should Try It Out

UPDATE: Carl Franklin on .NET Rocks released a podcast on LightSwitch last month.
.NET Rocks Podcast on LightSwitch