FAQ: Creating and altering SQL Server tables

Whether you want to select rows, move records, track activity or perform various other table-related tasks, this collection of frequently asked questions offers expert insights to help.

Whether you're selecting a row, moving records, tracking activity or performing any number of SQL Server table-related tasks, you may need some basic information to get you started or simplify your work. This collection of frequently asked questions, submitted by SearchSQLServer.com readers, offers expert technical advice on creating and working easily with SQL Server tables.

 

Frequently Asked Questions:

Working with SQL Server tables

  1. What are common table and column name restrictions?
  2. How do I pass column and table names as parameters?
  3. What script will select a specific row from a table?
  4. How do I retrieve the first two rows in a table?
  5. What script will select a specific record from a table?
  6. How do I list tables without records?
  7. What's a generic audit trail trigger to track table activity?
  8. How do I move records to new tables without a counter?
  9. How do I restore tables without a backup?
  10. How do I create a table-like structure in Visual Basic?

1. What are common table and column name restrictions?

DBMS table and column names under SQL Server 2000 have a 1 through 128-byte limit and follow the rules established for identifiers.

The first character must be one of the following:

 

  • A letter as defined by the Unicode Standard 2.0 The Unicode definition of letters includes Latin characters from A through Z in addition to letter characters from other languages.

     

  • The underscore (_), "at" sign (@) or number sign (#) Certain symbols at the beginning of an identifier have special meaning in SQL Server. An identifier beginning with the "at" sign denotes a local variable or parameter. An identifier beginning with a number sign denotes a temporary table or procedure. An identifier beginning with double number signs (##) denotes a global temporary object.

Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, it is recommended that you do not use names that start with @@.

Subsequent characters can be any of the following:

 

  • Letters as defined in the Unicode Standard 2.0
  • Decimal numbers from either Basic Latin or other national scripts
  • The "at" sign, dollar sign ($), number sign, or underscore

The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words. Embedded spaces or special characters are not allowed although you will see these objects in good old Northwind with names that include embedded spaces. You'll have to access them by enclosing them in brackets.
—Joe Toscano, Integration Services/Development Expert

Return to SQL Server tables FAQs

2. How do I pass column and table names as parameters?

Try looking into using dynamic SQL. The simplest would be to pass the table/column(s) as parameters and using the exec command to execute a Transact-SQL command you construct. Below is an example where I use local variables to construct a statement that retrieves data from a table in the Northwind database. You will notice that by using exec (SQL command), you can dynamically construct the SQL command. For complex commands I've used another local variable such as @sqlcmd varchar(2048) to hold the actual command. Below is a simple example:

declare @tablename varchar(64), @columnname varchar(64)
select @tablename = 'Categories', @columnname = 'CategoryName'
exec ('select ' + @columnname + ' from ' + @tablename)

You may consider using sp_executesql which accomplishes the same, but the Query Optimizer is more likely to re-use the execution plan if only the parameters change. Please see Books Online for some detailed examples. Be aware that using dynamic SQL while very flexible may not be very efficient in some cases. Look the query plans the optimizer generates and compare the exec statement and sp_executesql's performance.
—Joe Toscano, Integration Services/Development Expert

Return to SQL Server tables FAQs

3. What script will select a specific row from a table?

Does the table have a primary key? That would make the query easier. Otherwise, I think you're after something like this:

 

SELECT prod_key,item_key,pack_key,last_sale
FROM (SELECT item_key,pack_key,MAX(last_sale) AS last_sale FROM tablex 
GROUP BY item_key,pack_key) AS MaxDateTable
WHERE tablex.item_key = MaxDateTable.item_key
AND tablex.pack_key = MaxDateTable.pack_key
AND tablex.last_sale = MaxDateTable.last_sale


—Greg Low, Development Expert

Return to SQL Server tables FAQs

4. How do I retrieve the first two rows in a table?

In SQL Server 2000 or later you use the TOP statement. For example:

 

SELECT TOP 2 * FROM authors ORDER BY au_lname,au_fname


—Greg Low, Development Expert

Return to SQL Server tables FAQs

5. What script will select a specific record from a table?

 

CREATE TABLE TestTable ( ID int, TestDate datetime )

GO 
INSERT TestTable VALUES(1,'20051231') 
INSERT TestTable VALUES(1,'20051230') 
INSERT TestTable VALUES(1,'20051230') 
INSERT TestTable VALUES(1,'20051229') 
INSERT TestTable VALUES(2,'20051230') 
INSERT TestTable VALUES(3,'20051229') 
INSERT TestTable VALUES(3,'20050101') 
GO

The query might be:

 

SELECT ID,TestDate 
FROM TestTable AS tt 
WHERE (SELECT COUNT(*) 
FROM TestTable AS tc 
WHERE tt.ID = tc.ID 
AND tt.TestDate < tc.TestDate) = 1 
UNION 
SELECT ID,MIN(TestDate) 
FROM TestTable 
GROUP BY ID 
HAVING COUNT(ID) = 1 
ORDER BY ID


—Greg Low, Development Expert

Return to SQL Server tables FAQs

6. How do I list tables without records?

I created one dynamic SQL Server query to count the number of rows in a table. My aim is to list out the tables which have no records in a database. The query is as follows:

declare @strsql varchar(100)
declare @tablename varchar(50)
@tablename='table123'

@strsql='select count(*) from ' + @tablename

exec(@strsql)

I am getting the output but I can't store this value in a variable for checking. I want to check like this:

/* @countvariable=0
print(@tablename)*/

If there is any other solution?

You can make your solution more flexible by grabbing the table names from the sysobjects table:

 

declare @strsql varchar(256)
create table #emptytables  (tablename varchar(128), table_rowcount int)

select @strsql='select distinct o.name as TableName, x.rowcnt as Table_RowCount
 from  sysobjects o
  inner join sysindexes x 
 on o.id = x.id 
 where x.rowcnt =  0 and
 o.type = ''U'''

insert #emptytables (TableName, Table_rowcount) exec (@strsql)
select * from #emptytables
drop table #emptytables


—Joe Toscano, Integration Services/Development Expert

Return to SQL Server tables FAQs

7. What's a generic audit trail trigger to track table activity?

Writing a generic audit trail trigger in SQL Server can be a bit tricky, as it requires manipulation of a bitmask exposed by the COLUMNS_UPDATED function. You'll also need to figure out how to store disparate data types in some generic fashion, which might be difficult if your database uses TEXT, NTEXT, or IMAGE columns.

Luckily, SQL Server MVP Nigel Rivett has already done most of the work. You can find his audit trigger at Nigel's Web site.
—Adam Machanic, SQL Server 2005 Expert

Return to SQL Server tables FAQs

8. How do I move records to new tables without a counter?

There's a couple different ways you can move records to new tables without creating a counter. The main issue is whether you need to do this only once or to implement this as a repeatable process.

Assume that you need to do this only once, you break apart your table using either the TOP or SET ROWCOUNT statements. (Refer to SQL Server Books On-Line topic "Limiting Result Sets Using TOP and PERCENT" for more information.) The following statements are essentially equivalent:

 

SELET TOP 1000000 *
FROM foo
INTO temp_foo1
ORDER BY col1, col2

INSERT INTO temp_foo1
SELECT TOP 1000000 * 
FROM foo
ORDER BY col1, col2

SET ROWCOUNT 1000000
   INSERT INTO temp_foo1
   SELECT * 
   FROM foo
   ORDER BY col1, col2
SET ROWCOUNT 0

Once the records are moved, you should remove the records from the source table so that you don't put the same records into more than one of your ten target tables. Since I'm a stickler for being careful with my data. So I would create a copy of the source table and copy the records to the target tables from the copied source table like this:

 

SELET TOP 1000000 *
FROM copy_of_foo
INTO temp_foo1
ORDER BY col1, col2

DELETE copy_of_foo 
WHERE col1 IN
   (SELECT col1 FROM temp_foo1)


—Kevin Kline, SQL Server Expert

Return to SQL Server tables FAQs

9. How do I restore tables without a backup?

The only way to restore the deleted tables is by using a third party transaction log tool such as Log Explorer, SQL Log Rescue, Apex SQL Log or Log PI. The only caveat is that the database must have been in the full or bulk logged recovery mode, so the transactions are logged and still recoverable.
—Greg Robidoux, Backup and Recovery Expert

Return to SQL Server tables FAQs

10. How do I create a table-like structure in Visual Basic?

You can do this with the Dataset object (presuming we're talking about a VB.NET application, not a VB6 application). It can contain DataTables which are made up of DataColumns. You can even specify relations between the DataTables. These can be built in memory (ie. on the fly) and do not have to be associated with "real" database objects at all.
—Greg Low, Development Expert

Return to SQL Server tables FAQs

 

 
 
  Didn't find what you were looking for?

Pose a question to anyone of our SQL Server experts.

You can also browse our SQL Server Topics section for more advice.

 
 
This was first published in June 2007

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close