Creating a table in SQL Server Express

Question

I'm trying to setup a SQL database for a local program management and install system. I think i've created a basic serviceable schema that should work, but i'm unsure how to translate it to a CREATE TABLE command. Can someone help me with the translation and/or point me to resources that will?

Schema:

Programs(
    progID[key] (integer/index field?),
    name (text),
    desc (text), 
    iconFile (path to file),
    installScript (path to file))

Answer 1

Questions regarding SQL syntax should be directed over at Stack Overflow.

That said, if you have SQL Studio Management Express (grab it from here), when you create your table structure, but before saving it, you can click on the "Generate Script" icon (it has a scroll with a disk on it), and it will show you the code it's about to execute.

Answer 2

Use the following syntax:

create table programs (
    progid int primary key identity(1,1),
    name nvarchar(255),
    description nvarchar(500),
    iconFile nvarchar(255),
    installScript nvarchar(255)
)

The primary key sets the progid column to be the index column of the table. The identity(1,1) clause sets the progid to be an auto-incrementing field, starting at 1 and incrementing by 1 each time. Therefore, the following SQL enters the corresponding rows into programs:

insert into (name, description, iconfile, installscript)
values ('Name1', 'Test test', 'C:\file\path', 'C:\script\path')
insert into (name, description, iconfile, installscript)
values ('Name2', 'Test 123', 'C:\file\path1', 'C:\script\path2')

------------------------------------------------------------------
progid    name     description    iconfile        installscript
------------------------------------------------------------------
1         Name1    Test test      C:\file\path    C:\script\path
2         Name2    Test 123       C:\file\path1   C:\script\path2

Notice, also, that I used nvarchar instead of varchar. This is because nvarchar uses the Unicode character set, while varchar uses the ASCII character set. nvarchar is the recommended usage, since ASCII characters take no additional space in Unicode, but Unicode does allow for internationalization and obscure characters.