Michael J. Swart

Every day I get tons of questions in my inbox asking about all things SQL Server. Just kidding. I usually get zilch. So I thought that I’d answer some questions suggested by Google. Using Google’s toolbar, I typed “sql server how to”. And then I took the top ten suggestions that Google gave me:

So here they are in the order that they were suggested:

1. sql server how to buy

Well Microsoft explains this better than I could: The first Google hit sends you to a web page (not surprisingly) called Microsoft SQL Server 2005: How to Buy.

There’s also a FAQ on Pricing and Licensing. The FAQ was written for SQL Server 2005, but the information applies to SQL Server 2008 as well.

2. sql server how to create a table

This is an important question for people starting out. I usually execute a script. Using Management Studio, open a new query window and then use the syntax described in the help for the CREATE TABLE command.

Here’s a simple example of what that would look like.

CREATE TABLE mytable (
column1 int not null primary key,
column2 nvarchar(max) null
)

You can also use Management Studio to design your table. In Object Explorer, click New Table as shown here and work with that:

Another technique involves storing the results of a select command and giving it a name. This is the INTO clause of the SELECT command. For example:

SELECT firstname, lastname
INTO newTableName
FROM CUSTOMERS

3. sql server how to backup table

Well, there is no BACKUP command that applies to tables alone (Don’t you want to back up the whole database?), but if you want to export the data contained in a table to an external location then I think the easiest thing to do is to go through the Export Data wizard in Management Studio and choose a flat file destination:

But my personal preference is to use the BCP utility. It’s a command line tool that is used something like:

>bcp dbname.dbo.USERS out c:\temp\users.dat -T -S SERVERNAME -n

4. sql server how to allow remote connections

This is a question where we should mentally step back a bit. Chances are you’ve received an error message like the following:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. Provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

In my experience, the problem is almost always that the server is not accessible. Try pinging the server. Make sure the server’s SQL Server Browser service is on. Checking spelling etc…

If you really want to ensure that remote connections are enabled use the following:

EXEC sp_configure 'remote access'

to view the setting and

EXEC sp_configure 'remote access', 1

to turn it on.

Another thing I sometimes forget is to check that the DB supports the TCP protocol. I do this using the SQL Server Configuration Manager. It’s important to remember because that protocol is not enabled by default.

5. sql server how to install

I don’t know how to answer this one really. Assuming that you’ve got a DVD or some other media containing the software, pop it in and follow the wizard. There’s some tricky stuff in there like choosing credentials for services. But when you get to a point you’re unsure of, hit F1.

Here are the official docs:

• How to: Install SQL Server 2008 (Setup)
• Installing SQL Server 2005

6. sql server how to lock a table

Again, take another mental step back. We should ask why you need to lock a table. Presumably you want to prevent access to the table to avoid:

• Prying eyes?
• Inconsistencies etc…?

If it’s prying eyes you want to avoid. Then SQL Server security is the thing you want to study. You may eventually find yourself issuing something like

DENY CONTROL ON [tablename] TO [public]

If it’s data inconsistencies, then you want to study transactions. Once you do, you have a better idea of what you want to do. Maybe start here.

7. sql server how to backup database

People have
written textbooks and given week long lecture series on this topic. But as a start look at the BACKUP Database syntax

A short example for a full backup looks like:

BACKUP DATABASE mydatabase TO  DISK = N'C:\temp\mydatabase.bak'

Consider the above a kind of “Hello World” example.

8. sql server how to execute stored procedure

Say you’ve got the name of a stored procedure and hypothetically it’s called s_UpdateCalculations. It takes one integer parameter called @options and a bit parameter called @flag.

In a query window, the best way to execute that stored procedure is to type:

EXEC s_UpdateCalculations @options=1, @flag = 0

Or more simply

EXEC s_UpdateCalculations 1, 0

Then hit the Execute button.

s_UpdateCalculations 1, 0

will also work.

9. sql server how to index

Again, this is a huge topic. It’s also as much as an art as it is a science. But assuming that you’ve done the work and determined that you need an index on column A on table B. You would use:

CREATE INDEX ix_B on B(A)

Here’s the on-line help for CREATE INDEX.

10. sql server how to update identity column

Values in identity columns cannot be updated. This is by design. But if you really really must change the values in that column table, then one way is to use Management Studio to:

• open the table designer
• turn off the identity,
• save
• make your updates,
• open the table designer again
• turn on the identity again
• and save

This can be time consuming, especially if there’s a lot of data in that table. This is because to change the table, Management Studio copies the entire table to a temporary one, drops the old one and renames the temporary table to the original name. It then repeats the whole process when adding the identity column back.