SQL Server & Access Tips

by Arthur Fuller

Installation and maintenance	Optimization
Minimize tempdb size	Optimize queries with temporary tables
SQL Server on Linux	Set file locations to optimize performance
What can go wrong, will	Use hash keys instead of string indexes
Connect	Queries
Connect to server databases with SQL-DMO	Comma-delimited lists
Database design	Common table expressions (CTEs)
Add columns to all tables in a database	Compound JOINs
Build a data values dictionary with SQL Server	Creativity with COALESCE()
Fix what Access export wizards miss	Crosstab queries for SQL Server 2000 with Access
Improve performance with horizontal table partitions	Displaying currency
MSDE or SQL Server?	Improve query performance with multi-column indexes
OLAP dimensions	Loose associations
Partition tables with hardware	Many uses of CASE
Point-in-time architecture	Modeling columns as rows
Speed up development by re-using databases	Nested subqueries
Ten questions to ask before optimizing a database	Reporting Services in SQL Server 2005
The sysobjects table	Running totals
Data	Select random rows
A hold-release window for online customers	Simple histograms in SQL Server
Benchmarking tables with millions of rows	Statistical functions
Bill of materials	Unusual ordering
Comma-delimited lists	Security
Compare two tables	Block unauthorized access to encrypted database
FIFO and LIFO stacks	Checklist for hardening your database server
Insert specific values in an identity column	SQL Injection
Override default values in INSERTs	Using logins, users and roles more efficiently
Retrofitting date values in SQL Server 2005	Stored routines
Storing URLs	Build stored procedures with templates
Update and insert in one query	Debugging stored procedures in Query Analyzer
XML and SQL Server	Emulate MySQL's Group_Concat()
Maintenance	Recursion
Change authentication mode	Scope @@identity in stored procedures
Recover from hardware upgrade problems	User-defined functions
Microsoft Access	WITH RECOMPILE
OneNote collection of code tips

Do you have an item or correction to contribute?

Back to the home of Get It Done With MySQL 5&Up;