Database Links

Links that lead to off-site pages about databases.

Connection strings

• ConnectionStrings.com
• "SQL Server Connection Strings" [http://www.sqlteam.com/article/sql-server-connection-string] [20071126]
• SQLStrings.com

Database makers

• By scale
  • IBM.com. "DB2" is king of the mainframe.
  • Oracle.com. King of midrange... for now. 64 bit!
    • Oracle DB
    • PL/SQL
  • NCR.com. Their "Teradata" RDBMS runs on its own OS (a UNIX variant).
  • Microsoft.com, esp. MSDN.Microsoft.com.
    • "SQL Server" straddles the gap between enterprise and small business. 64 bit challenges Oracle!
    • "Access" is biquitous because MS Office is ubiquitous. All sorts of uses.
  • Sybase.com.
    • Sybase's "ASE" (Adaptive Server Enterprise) is for UNIX. ASE code was shared with MS to make SQL Server for OS/2. Until v4.9, ASE  and MS SQL Server were identical.
    • Sybase IQ for data warehousing.
• By sales (Ref: IDC.com 2002)
  1. Oracle 39.4% (-5%) of revenue with $5.1 billion.
  2. IBM 33.6% (+9%) with $4.3 billion.
  3. Microsoft 11.1% (+15%) with $1.4 billion.
  4. Sybase.
  5. NCR.
  6. Others.
• Open source. % of open source deployments ref: http://news.zdnet.co.uk/software/applications/0,39020384,39185042,00.htm.
  • FirebirdSQL.org. 39% of deployments.
    • Firebird (database server)  [W]
  • MySQL.com. 40% of deployments. Wikipedia and Slashdot use MySQL.
    • MySQL [W]
  • PostgreSQL.org. 11% of deployments.
    • PostgreSQL [W]

SQL injection

SQL injection is a security vulnerability that tries to abuse user input. EG:

"select * from users where name='" + UserName "';" //if 's not escaped as '', may become:
select * from users where name='' or ''='';

"select * from users where name='" + UserName "';"  //if --s not checked, may become:
select * from users where name='' or (1=1)--'; 

"select * from users where name='" + UserName "';"  //if ;s not checked, may become:
select * from users where name='';delete users 

"select * from users where id=" + UserID  //if datatype or length not checked, may become:
select * from users where id=4 or id is not null;

//Limit the permissions given to the SQL login.
//Instead of "SELECT * ...", use something like "SELECT price ..."
//Store sensitive info like passwords as hashes.

• SQL injection [W]
• "New SQL Truncation Attacks And How To Avoid Them" [http://msdn.microsoft.com/msdnmag/issues/06/11/SQLSecurity/default.aspx]
• "Protecting Yourself from SQL Injection Attacks" [4guysfromrolla.com/webtech/061902-1.shtml]
• "SQL Injection!" [sqlservercentral.com/columnists/chedgate/sqlinjection.asp]
• "SQL Injection Attacks by Example" [http://www.unixwiz.net/techtips/sql-injection.html].
• "Updated SQL Injection" [sqlservercentral.com/columnists/mcoles/updatedsqlinjection.asp]

Articles and Pieces

Not all the articles, just some I found interesting or useful. Perhaps I should do these as blog posts instead.

Database Journal

Database Journal [databasejournal.com].

• "Creative Ways to Use the TOP Clause"
• "Formatted date in SQL Server 2008"
• "Using a Subquery in a T-SQL Statement"

Erland Sommarskog

Erland Sommarskog [sommarskog.se].

• "Arrays and Lists in SQL Server 2005 and Beyond" (2007-03-03/2010-01-17). Fairly extensive.
• "How to Share Data Between Stored Procedures". By Erland Sommarskog.
• "Implementing Error Handling with Stored Procedures".
• "The Curse and Blessings of Dynamic SQL". By Erland Sommarskog.

SQL Server Central

SQL Server Central [sqlservercentral.com].

• "Calculating Moving Averages with T-SQL" (2010-03-04).
• "Common Table Expressions in SQL Server 2005" (2005-03-02/2007-03-09).
• "Conditional Statements in WHERE Clauses".
• "Denormalization Strategies".
• "Expanding The Scope of Bridge Tables".
• "Four Rules for NULLs" (2005-06-27). By Michael Coles. Also by Coles: "Sic Semper NULL" (2007-04-10); "NULL Versus NULL?" (2007-07-26). The 4 rules are:
  1. Use NULLs to indicate unknown/missing information only. Do not use NULLs in place of zeroes, zero-length strings or other "known" blank values. Update your NULLs with proper information as soon as possible.
  2. In ANSI SQL, NULL is not equal to anything, even other NULLs! Comparisons with NULL always result in UNKNOWN.
  3. Use SET ANSI_NULLS ON, and always use ANSI Standard SQL Syntax for NULLs. Straying from the standard can cause problems including portability issues, incompatibility with existing code and databases and returning incorrect results.
  4. The ANSI Standard COALESCE() and CASE syntaxes are preferred over ISNULL() or other proprietary syntax.
• "Get a Return Every Time".
• "Hidden RBAR: Triangular Joins". By Jeff Moden, 2007-12-06. "Improperly written Triangular Joins are worse than even Cursors or While Loops and can bring a CPU and Disk System right to it's knees."
• "Import Excel Spreadsheet to Database Tables"
• "New Column Updates".
• "Nesting Stored Procedures".
• "Not In v Not Equal".
• "Passing a Table to A Stored Procedure" and "Passing a Table to a Stored Procedure". Both by Jacob Sebastian.
• "Quickly Moving Databases"
• "Replacing Cursors and While Loops".
• "Row-By-Row Processing Without Cursor".
• "Server Side Paging With SQL Server 2005"
• 'Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)'
• "Some Usages for XML".
• "The HierarchyID Datatype in SQL Server 2008" (2008-03-05).
• "The Joy of Numbers".
• "The OUPUT Command".
• "The Troubleshooting List".
• "Time Bomb Coding" (2010-02-10).
• "Tips for New DBAs" (20101-02-12). By Craig Outcalt. 3 basic points in this article: CIA, No heroes, and definable expectations such as service leval agreements (SLA).
• "Using XML to Enhance the Performance of String Manipulations"
• "Writing Faster T-SQL".

SQL Team

SQL Team [sqlteam.com].

• "Alerts for when Login Failures Strike" (2008-07-14).
• "Calculating Running Totals"
• "Implementing a Dynamic WHERE Clause". Essentially construct a SQL string in a stored procedure, and then exec(sqlString). Also use the coalesce() function.
• "Stored Procedures: Returning Data"

Miscellany

• http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html. Chapter 8 Statistics in SQL from the book Transact-SQL Cookbook (2002) by Ales Spetic, Jonathan Gennick.
• "Build Date Generators and Manipulate Date and Time Data in SQL" [http://www.devx.com/dbzone/Article/33798]
• "How to avoid Dynamic SQL in Stored Procedure" [http://devpinoy.org/blogs/leonidas/archive/2007/08/15/how-to-avoid-dynamic-sql-in-stored-procedure.aspx].
• "How to import XML into SQL Server with the XML Bulk Load component" [http://support.microsoft.com/kb/316005].
• "Normalizing Name Data in SQL Server" [http://www.samspublishing.com/articles/article.asp?p=25049&rl=1]. Practically a hopeless cause.
• "Database Design: A Point in Time Architecture" [http://www.simple-talk.com/sql/database-administration/database-design-a-point-in-time-architecture/]. Especially for auditing or revision control.
• "Practical Database Design". By IBM.
• "Sample ADO Connection Strings"
• "Should I use a #temp table or a @table variable?" [http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html]. An excellent article. Basically don't bother with global temp tables (EG: ##MyTable) or temp permanent tables (EG: MyTable). There are very particular distinctions between local temp tables (EG: #MyTable) and table variables (EG: @MyTable), but here are some:
  • Both clear when they go out of scope but table variables are cleared immediately whereas local temp tables are cleared by the system who knows when. Alternatively you could drop local temp tables as soon as you're done with them.
  • You can't modify data in a local temp table within a user defined function.
  • Table variables must be referenced with an alias, except in the FROM clause.
• "Solve a many-to-many relationship problem in Microsoft Access". This discusses making a user interface for handling many-to-many tables. The article uses Access as the DB but any database could be used. The UI solution is essentially that Table 1 (EG: Candidate) has 2 list boxes (EG: Languages and Available Languages) plus 2 buttons (EG: Add Language and Delete Language)."SQL 92 Specs"
• "SQL Server I/O: Reading XML Documents" [http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=125].
• "Stored Procedures are EVIL" [http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html]. By Tony Marston. He doesn't fully address the security issue, but he does have some good points.
• "SQL Server Database Coding Conventions, Best Practices, and Programming Guidelines" [sql-server-performance.com/vk_sql_best_practices.asp]. A must read.
• "Using Oracle PL/SQL" [http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html]

Miscellany

• 4GuysFromRolla.com/WebTech/SQLGuru/
• DatabaseJournal.com
• IDC.com. "the world's leading provider of technology intelligence, industry analysis, market data, and strategic and tactical guidance to builders, providers, and users of information technology." Sort of like Gallup polls for the IT industry.
• TPC.org. The Transaction Processing Performance Council, "a non-profit corporation founded to define transaction processing and database benchmarks and to disseminate objective, verifiable TPC performance data to the industry.". Used by Intel, AMD, Oracle, MS SQL Server, etc.'
• SQLMag.com
• SQLQuiz.com. Generates quizzes with 15 random questions (from around 500) on basic SQL. Geared for people fairly new to SQL. Very clean and simple. Could be used for learning or very basic SQL screening. I'm surprised that there are no ads!
• SQLServerCentral.com. 'the largest Microsoft SQL Server community on the web with 230,636 registered members! SQLServerCentral.com offers you thousands of SQL Server articles, FAQs, scripts, and forums to help your professional development as a DBA, developer or user of SQL Server.'
• SQLTeam.com.
• SQL Tutorial [firstsql.com/tutor.htm]. For the SQL 92 standard.
• SQL-tutorial.com. Free simple SQL tutorial.

Page Modified: (Hand noted: 2008-06-09 15:32:10Z) (Auto noted: 2010-12-24 22:45:36Z)