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 into an 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