On this page... (hide)
- Structured Query Language
- SQL Server versions
- general db notes
- Installing SQL Server database from a
- Stored Procedures
- Save not permitted
- Sample queries
- Retrieve duplicate entries:
- Delete duplicate entries
- Returning the autoincrementing column of a newly added record.
- Replace portions of fields
- Get all info on tables
- Enter a NULL value (in EnterpriseManager)
- use variables
- SqlStringManipulation (longer article)
- get date (with no time) from DateTime
- Count by field
- convert from
- swap two fields, in WHILE loop
- Set proper execute permissions on stored procedures
- creating indexes
- Notes for Not Null
- auto-create insert statements for copying data
- handling occasional deadlocks
- pseudo-boolean bit values
- Multi-row inserts
- (Temporary) Backup table
- Stupid SSMS tricks
- Using PowerShell to extract headers and data
- See Also
- Category tags
- Add Comment
Interface Language for retrieving data from, er, databases.
It should possibly be noted that just about all of these notes and links come from my experience with MS-SQL server, a glancing familiarity with MySQL and a complete unfamiliarity with Oracle. And NoSQL? uh, whut?
Joe Celko’s “SQL Programming Style” - have not read
Joe Celko’s “SQL for Smarties: Advanced SQL Programming” - I’m reading the 4th edition
SQL Weekend Crash Course - Years ago, I got it at a discount store for $5. Well worth the money. Never cracked-open the CD.
Get the version via checking the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion
If a field is set as small date-time, you cannot enter an invalid, blank or NULL value.
As of IA 5.3, setting a default of NULL is now an option in
table names have Underscore between words
fieldnames are camel case
sp's follow same rules as tables with naming convention
keep columns consistent across tables
The steps worked great. However, the code uses
C:\temp by default, and I followed it to the letter the first time I ran it, not knowing if SQL-Server would move the files to another location or not. Turned out to be not.
So, I had to move the files
Here is a semi-automated script to generate the above:
And possibly renaming them (step 2)
Always use ‘em, if possible
% = match any characters, multiple times
_ = match any single character, once
To match actual instances of “%” or “_” in a query, surround them in brackets, as follows:
WHERE AuditOperator = '<name>'
AND (FieldName LIKE 'F1[_]%' OR FieldName LIKE 'F2[_]%')
ORDER BY ID DESC;
These are not presented as beginners queries (though they may be) to teach basic SQL.
Rather, these are snippets I found useful at various times, and kept forgetting.
Maybe I remember them, maybe I don’t.
Here they are.
(SELECT barcode FROM document
GROUP BY barcode
HAVING count(barcode) > 1)
ORDER BY barcode
--WHERE ID NOT IN
--GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
WHERE ID NOT IN (
GROUP BY DCN
If you follow the logic, it deletes all records that appear more than once, that are not the MAXimum value;
CreateDate could also be used, if ID is not present.
An alternate method uses
The following Query will add a new record and then return (as per a select statement) the value assigned to the ID column (that is, the column that automatically provides a unique ID for each new record).
Test FROM TestTable WHERE ID = @@Identity;
- When you manually update table data in SQL Server Enterprise Manager press Ctrl+0 [zero] to insert a NULL value.
- This works with SQL 2005 Management Studio as well.
SET @LName = 'Florange'
DECLARE @FName varchar(15)
SET @FName = 'Bruce'
In this example, by parsing a DateTime field
GROUP BY LEFT(CreateDate,11)
ORDER BY CreateDate DESC
Do I need to point out the utter inanity of naming a varchar field with “Nbr”, or data-typing a field named “Nbr” as varchar?
Because it is utterly inane.
-- by copying each into local vars, and updating
DECLARE @DESC varchar(20)
DECLARE @SOURCE varchar(20)
DECLARE @ID int
SET @ID = 0
DECLARE @LIMIT int
SET @LIMIT = (SELECT MAX(DocTypeID) FROM CICA_EDOC_Type)
WHILE (@ID <= @LIMIT)
SET @ID = @ID+1
SET @DESC = (SELECT EDocSource FROM EDOC_Type WHERE DocTypeID = @ID)
SET @SOURCE = (SELECT EDocDescription FROM EDOC_Type WHERE DocTypeID = @ID)
UPDATE EDOC_Type SET EDocDescription = @DESC, EDocSource = @SOURCE WHERE DocTypeID = @ID
Not guaranteed to be the best way to do this, but it works...
When creating the sproc, make sure the schema is
[dbo].[<sproc-name>] - otherwise it will be created with the schema
DOMAIN/username.<sproc> and only be accessible to yourself.
GRANT VIEW definition ON [sp name] TO [<domain\group>]
You cannot add a Not Null constraint to an existing table unless you first add the column as nullable, then backfill the values. Then, once that is complete, can do an alter table/alter column in T-SQL. This is the preferred method over using SQL Mgmt Studio since it will try to recreate the entire table if you later add a not null constraint.
(there’s probably a better way to do this)
I was talking to Wilce the other day about how to handle queries that occasionally fail because of deadlocks, and I told him about this SQL I found on the web.
It looks like a stored procedure, but really it’s just regular T-SQL that can be executed just like any other query.
What you do is add your SQL to the query below, and it will try to run your query in a transaction.
If it fails due to a deadlock, it will try again, up to 3 times.
The nice thing about it is that you don’t have to add any transaction / rollback / retry code to your program - it’s all handled in SQL Server.
Caveat: I haven’t tried to use this in production yet.
Case ... WHEN ... THEN ... ELSE ... END
See also: Stackoverflow -- boolean NOT in T-SQL
SELECT 'Clark', 'Kent', 'Reporter' UNION ALL
SELECT 'Kal', 'El', 'A completely different person';
And this is for MSSQL2008+ and above, only?
VALUES ('Clark', 'Kent', 'Reporter'),
('Kal', 'El', 'A completely different person'),
('Brenda', 'Star', 'Girl Reporter'),
('Mary', 'Worth', 'Venerable Landlady and Agony Aunt');
I like to have one of these around as a small-time “audit” feature -- just when, exactly, was this data _really_ committed to the DB?
as part of your
CREATE TABLE statement:
Edit -> Intellisense -> Refresh Local Cache (CTRL+Shift+R)
Extracting data with headers using PowerShell - SQL Server Central article; may require (free) membership to view.
Code also at https://gist.github.com/MichaelPaulukonis/a75cf5d5b16627293014 - pmwiki gist-embed is failing. waaah!
MSDN SQL Server resources: SQL Server Integration Services (SSIS)
SSIS 2008 tutorial
MSDN: Lesson 1 (SSIS Tutorial): Creating the Project and Basic Package