Back to RyanCooper.com - Resources


Retrieve Random Records
The following SQL DML Code allows you to retrieve 
records in a random order from a table in your database. 
This is especially useful in conjunction with the TOP clause, 
if you just need to get a few random records matching a certain criteria.

SELECT TOP 3 * FROM Demo_Company ORDER BY NEWID()

Insert record and retrieve identity in one inline query
The following SQL DML Code allows you to retrieve 
the Identity Field / Primary Key from a newly inserted record. 
This is quite easy to do using a stored procedure, 
but it's a little trickier using inline SQL 
statements that are executed at run-time. 
Note the SET NOCOUNT ON declaration. This suppresses 
the typical return value from an INSERT statement 
(Which generally would say "1 Record(s) Affected"), 
thereby allowing you to return a specific value.

SET NOCOUNT ON; INSERT INTO Companies (Name) VALUES ('Cooper Auto Repair'); SELECT @@IDENTITY AS CompanyID FROM Companies
		

Multiple Aggregates in a Recordset Without Joins or Group Bys
The following SQL DML Code allows you to retrieve 
aggregated results on several related tables, without 
using any JOINS or GROUP BY clauses. 
Assuming you had a users table, a transactions 
table, and a pageviews table, with the second 
two tables having the Users ID field as their foreign 
key fields, you could in one statement grab a list of 
users with a count of page view records and a sum of 
transactions for each user in the list.

SELECT U.ID, U.Name, (SELECT COUNT(*) FROM PageViews P WHERE P.UserID = U.ID) AS PageViews, (SELECT SUM(Transactions) FROM UserTransactions T WHERE T.UserID = U.ID) AS TotalTransactions FROM Users U ORDER BY U.Name
		

Case Sensitive LIKE operator in SQL 2000
By Default, SQL 2000 fields are collated with case insensitive collation. The following SQL DML Code allows you to retrieve records using the LIKE operator with a Case-Sensitive compare.

SELECT ImagePath FROM Authors WHERE     ImagePath LIKE '%.JPG' COLLATE Latin1_General_BIN

To see a list of valid collation values, you can run this system function: 

SELECT * FROM ::fn_helpcollations()