SQL Server Frequently Asked Questions FAQ

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server Frequently Asked Questions FAQ

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-09-14 : 08:20:16

If you have followed a link to this page (i.e. the URL contains "SearchTerms=xxx") please scroll down to see items of interested highlighted

This list of SQL Server Frequently Asked Questions (FAQ) was originally compiled in August 2005 based on the most frequently "read" posts on SQL Team. Since then new links have been added as topics become popular - so it might be better referred to as FGA - Frequently Given Answers!

NC=Indicates that the Article has NOT been checked for relevance. As time goes on these are getting fewer and fewer
The latest change is in Red
Recent changes are in Teal

URL to link to this post: 17Nov2005
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=
Followed by either "Keyword1,Keyword2" or "PhraseWord1+PhraseWord2"

The

icon allows you to RightClick-CopyShortcut to provide a link to this topic with appropriate keywords - I got bored of having to make them manually! 07Mar2006 Stats

Homework

I put this one at the top, in case you don't read any further. To find out if your homework will be answered on this forum you should see if it qualifies:
Requirements for homework questions 07Mar2006
Example of Failure to meet the minimum requirements 07Mar2006
Learning SQL www.sql-tutorial.net 07May2007
www.firstsql.com/tutor.htm 07May2007
www.w3schools.com/sql/default.asp 07May2007

Why?
Why is my LDF Log File so big / getting bigger / bigger than my MDF 14Oct2005

How to

How to ask a question on SQL Team and get a quick answer - i.e. the information you need to provide 06Nov2006

How to format your question or a reply (in particular see the [code] option 18Sep2007

how to encrypt and decrypt my password 07May2007
Rounding up/down to nearest multiple (e.g. order in dozens, buy at least 30) 28Sep2006
Replace multiple spaces, or other character, with single space 28Sep2006
Generate a Random String 13Sep2006
String comparison 24Jul2006
Article: Images and SQL Server

How to get Images or blobs into SQL table (NC)
Inserting Images as BLOB in SQL server table (NC)
How to use DeBUG window in QUERY Analyzer (NC)
Use a Parameter in the Top clause

Dynamic SQL or How do I SELECT TOP @var records?
Can't insert NULL into table with unique key (NC)
Efficient paging of recordsets with T-SQL
Article: Paging in SQL 2005 07May2007
Execute dynamic SQL that is longer than varchar(8000) 05Oct2005
Convert multiple spaces to single spaces 14Oct2005

Making Upper/Lower Case Sensitive comparison 27Oct2005

Convert to Proper Case (each word starts with capital) 19Feb2006

Deleting a massive number of rows 04Mar2006

Combine values from rows into a column 05Oct2005

i.e.
FOO 1
FOO 2
becomes:
FOO 1,2

CSV / Splitting delimited lists

WHERE IN @MyCSV
Article: Passing a CSV or Array to a Stored Procedure (NC)
Best split functions
and Concatenating data from rows with same GroupBy column
Triggers

Triggers to audit changes
trigger to keep tables matching
Set Update/Modified date column using a trigger 02Nov2005
Deactivating a trigger 07Feb2006

SQL Team Articles
Article: Identity and Primary Keys - and Surogate Keys See also
Article: Sending SMTP Mail using a Stored Procedure (NC)
Article: Dynamic ORDER BY (NC)
Article: More Trees & Hierarchies in SQL (NC)
Article: Using SELECT to INSERT records (NC)
Article: Deleting Duplicate Records
Article: Returning a Row Number in a Query (NC)
Article: Calculating Running Totals (NC)

Datatypes / Collation

GUIDs (NC)
IsGUID check GUID is valid 01Aug2007
DATALENGTH() and LEN() 21Sep2005
Compare BIT columns and a BitMask column 05Jan2006
Collation conflict in Assignment or WHERE clause 05Feb2006
(See "Developer / Techie stuff" below for some other Collation links)

Datatypes - Dates

Time Only Function: F_TIME_FROM_DATETIME 29Apr2006
Date/Time Info and Script Links (including UNIX time) 16Apr2006
Date formatting (

Format Date/Time (NC)
last date of the month (NC)
Getting the date portion of a datetime value

or
Round a date to Year, Quarter, Month, Day, Hour or Minute
How to get date in t-sql (NC)
compare date with current date (NC)
convert varchar to datetime (NC)
Compare dates regardless of time? (NC)
Convert varchar to date (NC)
Time in Analog (NC)
Start of Week Function (NC)
Earlier date than 1/1/1753

Techniques

Prevent Administrators accessing a database 14Sep2006
SELECT vs SET 26Sep2005
What is a cursor, Why is it bad? 23Sep2006
Cursor or Not (NC)
TSQL/ SP Best Practices (NC)
case-sensitive (NC)
User-Defined string Functions Transact-SQL (NC)
Passing an array into a stored procedure (NC)
Comparing two tables or views easily (NC)
All Tables in All Databases (NC)
Better Phonetic Matching Algorithm (A better Soundex)
Levenshtein Edit Distance Algorithm (A better Soundex)
Fuzzy matching 01Aug2007
Find tables with no PK (NC)
Function: HTMLDecode (NC)
How to protect errorlogs to tables from rollbacks (NC)
Distance of Zip/Postcodes
Great Circle Formula 07May2007
Haversine Formula 07May2007
Accuracy v. Speed 07May2007 See also here, here, here, here and here and here
Upsert - combined Update/Insert Procedure 01Aug2007 and here
Display all columns but only for rows with the most recent date 01Aug2007

Developer / Techie stuff

Changing column names on referencing objects 07May2007
When was DB object last updated 07May2007
How to clear the whole database 07May2007
Move / Script Development Database Changes to Production Database / Ship Application updates 04Nov2006
Script to analyze table space usage 11Oct2006
What version, and Service Pack, of SQL Server am I using? 23May2006
SQL Beautifier 13Apr2006
Performance / System Monitor / PerfMon - preferred values for counters 04Apr2006
SQL Server 2000 Knowledge Base Articles - updated every 3 days 21Jan2006
Concerns about SQL 2000 SP4 08Jan2006
Support stops for SP3a AND installing sp4 on cluster 26May2006

How to read Mssql transaction log file
Compare data in two tables / two databases 31Oct2005
Recommended books 07Oct2005

Good tools for DBAs 06Oct2005
Best SQL Security Method (NC)
Some query optimalisation (NC)
Requirements for Horizontal Partitions

Multiple SQL Server 2000 Instances (NC)
Primary Key v. Unique Index (NC)
HOWTO Run SQL Profiler without sysadmin rights (NC)
upgrade from SQL7 to SQL2000 (NC)
xp_cmdshell execute right (NC)
Monitor object usage over time (NC)
size of user tables in a database (NC)
Changing Column Length (NC)
Add, Sub, Mul, Div With Really! Big Numbers (NC)
So you're a DBA eh?!
Why I'm not a DBA (NC)
Do you S-Q-L or Sequel (NC)
Interview questions (NC)
More Interview questions 11Sep2006
Best steps to becoming a DBA (NC)

Developer / Techie stuff - Collation

Bulk Delete of Records 07May2007 and locking issues 01Aug2007
Deleting lots of Records from a large table 01Aug2007
Synchronising data using staging tables 07May2007
Version Control 07May2007
how to change Collate on the server 20Sep2005
How to change default collate in SQL 20Sep2005
Cannot resolve collation conflict for equal to OP 20Sep2005
different Collation Settings between Databases 20Sep2005

Database Design and Structures

Codes - Common Lookup Tables CLT 04Jun2006
Good table naming convention 09May2006
Naming Store Procedures 09May2006
Recursion / Hierarchy 29Apr2006
Hierarchies (NC)
Normalisation (NC)
NULL opinion? (NC)
Finding evidence of sql injection attack
Security Testing: SQL Injection 09May2006

Locking

Using the NOLOCK hint 07Jun2006
how does row locking work (NC)

EMail

sp sending mail (NC)
CDOSYS Send Email (NC)

XML

SQL Server 2000 XML (NC)
Adding an XML Root Tag
Handling tricky namespaces in OpenXML

Backup / Restore / Replication / Jobs / Houskeeping

Backup

How Backup works
What type of backup should I use? - Full, Differential, Transaction? Should I shrink the log? 22Jan2006
Automating Backups - Scripting v. Maintenance Plan / Wizard 08Mar2006
Backup to Disk, or direct to Tape? 05Dec2006

Disaster Procedure Checklist I think I have a database corruption, how do I mitigate any further damage and get upright again? 07May2007
How I Rescue Data from Corrupted Databases 07May2007
Disaster Recovery Plan 07May2007
Quiesce the SQL Service to enable low-level copy 01Aug2007

Restore

RESTORE syntax / example
Restore SQL 2005 to SQL 2000 via Downgrade 07May2007

Restore Full and all TLogs backups based on files in disk folder 07Dec2006
Move database to a new server (with minimal downtime) 16Feb2006

Moving to a new sql server 25Jun2006
Fix Orphaned Users (after a Restore or moving DB to a different server)
Script User Logins after a Restore 01Aug2007
and
Scripting users & logins from one server to another (e.g. after moving DB to a different server) 25Jul2006
how long will take to restore a 10 gb?
All I have is the .bak file
Restoring .BAK file to a different server?
Unable to restore/attach a MDF file (single file attach / sp_attach_single_file_db)
See also: This and this and this

Shrink Database / Files

Shrinking TEMPDB 19Apr2006
Database Shrinking 09Jan2006
Log Shipping -Shrinking Log and Data files (NC)
MSDB Massive msdbdata.mdf

Housekeeping / Maintenance

Tara Blog Houskeeping Routines 06Oct2006
Index Tuning Wizard 07May2007
User Rights for SQL Service 07May2007
When was SQL Server last restarted? 07May2007
Optimising your indexes with DBCC INDEXDEFRAG 06Oct2006
Detach Move Db & Log File Reattach 17Mar2006
Rebuilding Indexes
DBCC CHECDB, CHECKTABLE, NEWALLOC (old)
Suspect Databases after Power Cut?
Jobs failing (NC)
Reindex All Tables in All Databases (old)
deleting replication (NC)

Export / Import / DTS

Copying/backing up DTS Packages (NC)
Export to Excel (NC)
~~Generate INSERT statements for N rows of data~~
Tool to script database records? (i.e. generate INSERT statement script)
How to convert DATE Format from DD/MM/YY to MM/DD/YY in DTS (NC)
Importing an Excel File with DTS (NC)
Is DTS Secure for credit card backup to Access? (NC)
Winzip in scheduled DTS Package (NC)
Article: Using BULK INSERT to Load a Text File (NC)

Output / Reporting / Formatting

Pivot table (NC)
Article: Dynamic Cross-Tabs/Pivot Tables (NC)
which led to:
More Dynamic CrossTabs using T-SQL 04Aug2006 (Jeffs Blog)

Error Messages

SQL Server does not exist or access denied (NC)

Connections

Linked Server Newbie Question (NC)

Configuration / Hardware

/3GB in boot.ini on Win2003 standard 19Oct2005
SQL memory /3gb /PAE AWE 19Sep2005
SQL Server Standard & 4GB memory
Using AWE option in SQL
Increasing SQL Server Memory

MSDE

MSDE Setup fails (NC)
MSDE Install Issue (NC)
Installing MSDE on XP Pro (NC)
Backing up MSDE database (NC)

SQL 2005 / Yukon

Migrating to SQL 2005 Hints and Tips 01Aug2007
SELECT not allowed in Yukon/SQL2005? (NC)
Download Yukon/SQL2005 (NC)
SQL Server Management Studio (NC)

MySQL, Oracle, and the others

MySQL not as good as MS SQL?
Migrating from Oracle to SQL Server

Other

SQL Reporting Service SP2? (NC)

Got an hour .. or ten ... to spare?

Would you work at Elwoo's office?

Simple Quiz
What is a Yak? 07May2007

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-26 : 18:04:55

This is great work Kristen !!!
I bookmarked it. Thank You.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 06:01:05

Nice to know this thread has become Sticky topic

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-12 : 16:35:40

Wouldn't that be

FGA

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-11-02 : 07:11:47

Thanks a lot Kristen. These links are going to help us lot.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-17 : 09:22:09

quote:

URL to link to this post: 17Nov2005
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Keyword1,Keyword2
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=PhraseWord1+PhraseWord2

Very nice Kristen

Madhivanan

Failing to plan is Planning to fail

Auric
Yak Posting Veteran

70 Posts

Posted - 2005-11-24 : 14:33:43

Thanks Kristen you'll help this noob out a lot! :)

Select * from users where clue > 0

itsmani1
Starting Member

2 Posts

Posted - 2005-11-25 : 05:21:15

very informative thread.

Mannan.

http://mannan.zabvision.edu.pk
M. Abdul Mannan

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-12-01 : 00:16:03

well. this helps me alot every day..

Thanks Kristen.. great work..

Sucess Comes to those who Believe in Beauty of their Dream..

sponguru_dba
Yak Posting Veteran

93 Posts

Posted - 2005-12-01 : 00:42:26

Oh Greate Kristen..Thanks for Greate Help

By the way
Hi for all ... Iam Sreenivas New to this forum

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-12-02 : 09:40:53

oops I made a bad post Sorry

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-08 : 09:30:49

Brilliant, brilliant article(s)

ashwinreddy.c
Starting Member

13 Posts

Posted - 2006-01-22 : 04:48:14

thats really really gr8 kristen

Regards
Ashwin Reddy
SQLServer DBA

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-28 : 17:15:56

I saw these articles, while reading. I think its very usefull.

Managing ntext, text, and image Data
http://msdn2.microsoft.com/en-us/library/ms191262.aspx
http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=309

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-30 : 00:49:30

Well. Kristen is referring to the Topics/Articles of Sqlteam only

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-30 : 03:08:59

Handy to have in this thread though, I reckon.

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-30 : 04:24:08

quote:
Originally posted by Kristen

Handy to have in this thread though, I reckon.

Kristen

Yes !
Yes !!
Yes !!!

Brilliant compilation

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-30 : 09:35:32

I thought this was a sticky? What happened?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2006-01-30 : 14:29:57

"I thought this was a sticky?"

Yeah, it is. But I think the consideration was whether the "top post" should be edited to include external references. I'd prefer to keep it to SQL Team internal items, but for other stuff folk are welcome to post additional material - perhaps by editing an existing post, early in the thread, to keep their preferred links up-to-date.

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 22:46:47

The Topic ID link for "Unable to restore/attach a MDF file" under the Restore section is empty ?
Should be this ? http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37161

----------------------------------
'KH'
Time is always against us

Kristen
Test

22859 Posts

Posted - 2006-02-17 : 00:04:57

Cheers, now fixed.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources