Creating SQL Server Tables

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Download OLE DB driver

The OLE DB Driver for SQL Server exposes the ITableDefinition::CreateTable function, allowing consumers to create SQL Server tables. Consumers use CreateTable to create consumer-named permanent tables, and permanent or temporary tables with unique names generated by the OLE DB Driver for SQL Server.

When the consumer calls ITableDefinition::CreateTable, if the value of the DBPROP_TBL_TEMPTABLE property is VARIANT_TRUE, the OLE DB Driver for SQL Server generates a temporary table name for the consumer. The consumer sets the pTableID parameter of the CreateTable method to NULL. The temporary tables with names generated by the OLE DB Driver for SQL Server do not appear in the TABLES rowset, but are accessible through the IOpenRowset interface.

When consumers specify the table name in the pwszName member of the uName union in the pTableID parameter, the OLE DB Driver for SQL Server creates a SQL Server table with that name. SQL Server table naming constraints apply, and the table name can indicate a permanent table, or either a local or global temporary table. For more information, see CREATE TABLE. The ppTableID parameter can be NULL.

The OLE DB Driver for SQL Server can generate the names of permanent or temporary tables. When the consumer sets the pTableID parameter to NULL and sets ppTableID to point to a valid DBID*, the OLE DB Driver for SQL Server returns the generated name of the table in the pwszName member of the uName union of the DBID pointed to by the value of ppTableID. To create a temporary, OLE DB Driver for SQL Server-named table, the consumer includes the OLE DB table property DBPROP_TBL_TEMPTABLE in a table property set referenced in the rgPropertySets parameter. OLE DB Driver for SQL Server-named temporary tables are local.

CreateTable returns DB_E_BADTABLEID if the eKind member of the pTableID parameter does not indicate DBKIND_NAME.

DBCOLUMNDESC Usage

The consumer can indicate a column data type by using either the pwszTypeName member or the wType member. If the consumer specifies the data type in pwszTypeName, the OLE DB Driver for SQL Server ignores the value of wType.

If using the pwszTypeName member, the consumer specifies the data type by using SQL Server data type names. Valid data type names are those returned in the TYPE_NAME column of the PROVIDER_TYPES schema rowset.

The OLE DB Driver for SQL Server recognizes a subset of OLE DB-enumerated DBTYPE values in the wType member. For more information, see Data Type Mapping in ITableDefinition.

Note

CreateTable returns DB_E_BADTYPE if consumer sets either the pTypeInfo or pclsid member to specify the column data type.

The consumer specifies the column name in the pwszName member of the uName union of the DBCOLUMNDESC dbcid member. The column name is specified as a Unicode character string. The eKind member of dbcid must be DBKIND_NAME. CreateTable returns DB_E_BADCOLUMNID if eKind is invalid, pwszName is NULL, or if the value of pwszName is not a valid SQL Server identifier.

All column properties are available on all columns defined for the table. CreateTable can return DB_S_ERRORSOCCURRED or DB_E_ERRORSOCCURRED if property values are set in conflict. CreateTable returns an error when invalid column property settings cause SQL Server table-creation failure.

Column properties in a DBCOLUMNDESC are interpreted as follows.

Property ID	Description
DBPROP_COL_AUTOINCREMENT	R/W: Read/write Default: VARIANT_FALSE Description: Sets the identity property on the column created. For SQL Server, the identity property is valid for a single column within a table. Setting the property to VARIANT_TRUE for more than a single column generates an error when the OLE DB Driver for SQL Server attempts to create the table on the server. The SQL Server identity property is only valid for the integer, numeric, and decimal types when the scale is 0. Setting the property to VARIANT_TRUE on a column of any other data type generates an error when the OLE DB Driver for SQL Server attempts to create the table on the server. The OLE DB Driver for SQL Server returns DB_S_ERRORSOCCURRED when DBPROP_COL_AUTOINCREMENT and DBPROP_COL_NULLABLE are both VARIANT_TRUE and the dwOption of DBPROP_COL_NULLABLE is not DBPROPOPTIONS_REQUIRED. DB_E_ERRORSOCCURRED is returned when DBPROP_COL_AUTOINCREMENT and DBPROP_COL_NULLABLE are both VARIANT_TRUE and the dwOption of DBPROP_COL_NULLABLE equals DBPROPOPTIONS_REQUIRED. The column is defined with the SQL Server identity property and the DBPROP_COL_NULLABLE dwStatus member is set to DBPROPSTATUS_CONFLICTING.
DBPROP_COL_DEFAULT	R/W: Read/write Default: None Description: Creates a SQL Server DEFAULT constraint for the column. The vValue DBPROP member can be any of a number of types. The vValue.vt member should specify a type compatible with the data type of the column. For example, defining BSTR N/A as the default value for a column defined as DBTYPE_WSTR is a compatible match. Defining the same default on a column defined as DBTYPE_R8 generates an error when the OLE DB Driver for SQL Server attempts to create the table on the server.
DBPROP_COL_DESCRIPTION	R/W: Read/write Default: None Description: The DBPROP_COL_DESCRIPTION column property is not implemented by the OLE DB Driver for SQL Server. The dwStatus member of the DBPROP structure returns DBPROPSTATUS_NOTSUPPORTED when the consumer attempts to write the property value. Setting the property does not constitute a fatal error for the OLE DB Driver for SQL Server. If all other parameter values are valid, the SQL Server table is created.
DBPROP_COL_FIXEDLENGTH	R/W: Read/write Default: VARIANT_FALSE Description: The OLE DB Driver for SQL Server uses DBPROP_COL_FIXEDLENGTH to determine data type-mapping when the consumer defines a column's data type by using the wType member of the DBCOLUMNDESC. For more information, see Data Type Mapping in ITableDefinition.
DBPROP_COL_NULLABLE	R/W: Read/write Default: None Description: When creating the table, the OLE DB Driver for SQL Server indicates whether the column should accept null values if the property is set. When the property is not set, the ability of the column to accept NULL as a value is determined by the SQL Server ANSI_NULLS default database option. The OLE DB Driver for SQL Server is an ISO-compliant provider. Connected sessions exhibit ISO behaviors. If the consumer does not set DBPROP_COL_NULLABLE, columns accept null values.
DBPROP_COL_PRIMARYKEY	R/W: Read/write Default: VARIANT_FALSE Description: When VARIANT_TRUE, the OLE DB Driver for SQL Server creates the column with a PRIMARY KEY constraint. When defined as a column property, only a single column can determine the constraint. Setting the property VARIANT_TRUE for more than a single column returns an error when the OLE DB Driver for SQL Server attempts to create the SQL Server table. Note: The consumer can use IIndexDefinition::CreateIndex to create a PRIMARY KEY constraint on two or more columns. The OLE DB Driver for SQL Server returns DB_S_ERRORSOCCURRED when DBPROP_COL_PRIMARYKEY and DBPROP_COL_UNIQUE are both VARIANT_TRUE and the dwOption of DBPROP_COL_UNIQUE is not DBPROPOPTIONS_REQUIRED. DB_E_ERRORSOCCURRED is returned when DBPROP_COL_PRIMARYKEY and DBPROP_COL_UNIQUE are both VARIANT_TRUE and the dwOption of DBPROP_COL_UNIQUE equals DBPROPOPTIONS_REQUIRED. The column is defined with the SQL Server identity property and the DBPROP_COL_PRIMARYKEY dwStatus member is set to DBPROPSTATUS_CONFLICTING. The OLE DB Driver for SQL Server returns an error when DBPROP_COL_PRIMARYKEY and DBPROP_COL_NULLABLE are both VARIANT_TRUE. The OLE DB Driver for SQL Server returns an error from SQL Server when the consumer attempts to create a PRIMARY KEY constraint on a column of invalid SQL Server data type. PRIMARY KEY constraints cannot be defined on columns created with the SQL Server data types bit, text, ntext, and image.
DBPROP_COL_UNIQUE	R/W: Read/write Default: VARIANT_FALSE Description: Applies a SQL Server UNIQUE constraint to the column. When defined as a column property, the constraint is applied on a single column only. The consumer can use IIndexDefinition::CreateIndex to apply a UNIQUE constraint on the combined values of two or more columns. The OLE DB Driver for SQL Server returns DB_S_ERRORSOCCURRED when DBPROP_COL_PRIMARYKEY and DBPROP_COL_UNIQUE are both VARIANT_TRUE and dwOption is not DBPROPOPTIONS_REQUIRED. DB_E_ERRORSOCCURRED is returned when DBPROP_COL_PRIMARYKEY and DBPROP_COL_UNIQUE are both VARIANT_TRUE and dwOption equals DBPROPOPTIONS_REQUIRED. The column is defined with the SQL Server identity property and the DBPROP_COL_PRIMARYKEY dwStatus member is set to DBPROPSTATUS_CONFLICTING. The OLE DB Driver for SQL Server returns DB_S_ERRORSOCCURRED when DBPROP_COL_NULLABLE and DBPROP_COL_UNIQUE are both VARIANT_TRUE and dwOption is not DBPROPOPTIONS_REQUIRED. DB_E_ERRORSOCCURRED is returned when DBPROP_COL_NULLABLE and DBPROP_COL_UNIQUE are both VARIANT_TRUE and dwOption equals DBPROPOPTIONS_REQUIRED. The column is defined with the SQL Server identity property and the DBPROP_COL_NULLABLE dwStatus member is set to DBPROPSTATUS_CONFLICTING. The OLE DB Driver for SQL Server returns an error from SQL Server when the consumer attempts to create a UNIQUE constraint on a column of invalid SQL Server data type. UNIQUE constraints cannot be defined on columns created with the SQL Server bit data type.

When the consumer calls ITableDefinition::CreateTable, the OLE DB Driver for SQL Server interprets table properties as follows.

Property ID	Description
DBPROP_TBL_TEMPTABLE	R/W: Read/write Default: VARIANT_FALSE Description: By default, the OLE DB Driver for SQL Server creates tables named by the consumer. When VARIANT_TRUE, The OLE DB Driver for SQL Server generates a temporary table name for the consumer. The consumer sets the pTableID parameter of CreateTable to NULL. The ppTableID parameter must contain a valid pointer.

If the consumer requests that a rowset be opened on a successfully created table, the OLE DB Driver for SQL Server opens a cursor-supported rowset. Any rowset properties can be indicated in the property sets passed.

This example creates a SQL Server table.

// This CREATE TABLE statement shows the details of the table created by   
// the following example code.  
//  
// CREATE TABLE OrderDetails  
// (  
//    OrderID      int      NOT NULL  
//    ProductID   int      NOT NULL  
//    CONSTRAINT PK_OrderDetails  
//         PRIMARY KEY CLUSTERED (OrderID, ProductID),  
//    UnitPrice   money      NOT NULL,  
//    Quantity   int      NOT NULL,  
//    Discount   decimal(2,2)   NOT NULL  
//        DEFAULT 0  
// )  
//  
// The PRIMARY KEY constraint is created in an additional example.  
HRESULT CreateTable  
    (  
    ITableDefinition* pITableDefinition  
    )  
    {  
    DBID            dbidTable;  
    const ULONG     nCols = 5;  
    ULONG           nCol;  
    ULONG           nProp;  
    DBCOLUMNDESC    dbcoldesc[nCols];  
  
    HRESULT         hr;  
  
    // Set up column descriptions. First, set default property values for  
    //  the columns.  
    for (nCol = 0; nCol < nCols; nCol++)  
        {  
        dbcoldesc[nCol].pwszTypeName = NULL;  
        dbcoldesc[nCol].pTypeInfo = NULL;  
        dbcoldesc[nCol].rgPropertySets = new DBPROPSET;  
        dbcoldesc[nCol].pclsid = NULL;  
        dbcoldesc[nCol].cPropertySets = 1;  
        dbcoldesc[nCol].ulColumnSize = 0;  
        dbcoldesc[nCol].dbcid.eKind = DBKIND_NAME;  
        dbcoldesc[nCol].wType = DBTYPE_I4;  
        dbcoldesc[nCol].bPrecision = 0;  
        dbcoldesc[nCol].bScale = 0;  
  
        dbcoldesc[nCol].rgPropertySets[0].rgProperties =   
            new DBPROP[NCOLPROPS_MAX];  
        dbcoldesc[nCol].rgPropertySets[0].cProperties = NCOLPROPS_MAX;  
        dbcoldesc[nCol].rgPropertySets[0].guidPropertySet =  
            DBPROPSET_COLUMN;  
  
        for (nProp = 0; nProp < NCOLPROPS_MAX; nProp++)  
            {  
            dbcoldesc[nCol].rgPropertySets[0].rgProperties[nProp].  
                dwOptions = DBPROPOPTIONS_REQUIRED;  
            dbcoldesc[nCol].rgPropertySets[0].rgProperties[nProp].colid  
                 = DB_NULLID;  
  
            VariantInit(  
                &(dbcoldesc[nCol].rgPropertySets[0].rgProperties[nProp].  
                    vValue));  
  
            dbcoldesc[nCol].rgPropertySets[0].rgProperties[nProp].  
                vValue.vt = VT_BOOL;  
            }  
        }  
  
    // Set the column-specific information.  
    dbcoldesc[0].dbcid.uName.pwszName = L"OrderID";  
    dbcoldesc[0].rgPropertySets[0].rgProperties[0].dwPropertyID =   
        DBPROP_COL_NULLABLE;  
    dbcoldesc[0].rgPropertySets[0].rgProperties[0].vValue.boolVal =   
        VARIANT_FALSE;  
    dbcoldesc[0].rgPropertySets[0].cProperties = 1;  
  
    dbcoldesc[1].dbcid.uName.pwszName = L"ProductID";  
    dbcoldesc[1].rgPropertySets[0].rgProperties[0].dwPropertyID =   
        DBPROP_COL_NULLABLE;  
    dbcoldesc[1].rgPropertySets[0].rgProperties[0].vValue.boolVal =   
        VARIANT_FALSE;  
    dbcoldesc[1].rgPropertySets[0].cProperties = 1;  
  
    dbcoldesc[2].dbcid.uName.pwszName = L"UnitPrice";  
    dbcoldesc[2].wType = DBTYPE_CY;  
    dbcoldesc[2].rgPropertySets[0].rgProperties[0].dwPropertyID =   
        DBPROP_COL_NULLABLE;  
    dbcoldesc[2].rgPropertySets[0].rgProperties[0].vValue.boolVal =   
        VARIANT_FALSE;  
    dbcoldesc[2].rgPropertySets[0].cProperties = 1;  
  
    dbcoldesc[3].dbcid.uName.pwszName = L"Quantity";  
    dbcoldesc[3].rgPropertySets[0].rgProperties[0].dwPropertyID =   
        DBPROP_COL_NULLABLE;  
    dbcoldesc[3].rgPropertySets[0].rgProperties[0].vValue.boolVal =   
        VARIANT_FALSE;  
    dbcoldesc[3].rgPropertySets[0].cProperties = 1;  
  
    dbcoldesc[4].dbcid.uName.pwszName = L"Discount";  
    dbcoldesc[4].wType = DBTYPE_NUMERIC;  
    dbcoldesc[4].bPrecision = 2;  
    dbcoldesc[4].bScale = 2;  
    dbcoldesc[4].rgPropertySets[0].rgProperties[0].dwPropertyID =   
        DBPROP_COL_NULLABLE;  
    dbcoldesc[4].rgPropertySets[0].rgProperties[0].vValue.boolVal =   
        VARIANT_FALSE;  
    dbcoldesc[4].rgPropertySets[0].rgProperties[1].dwPropertyID =   
        DBPROP_COL_DEFAULT;  
    dbcoldesc[4].rgPropertySets[0].rgProperties[1].vValue.vt = VT_BSTR;  
    dbcoldesc[4].rgPropertySets[0].rgProperties[1].vValue.bstrVal =  
        SysAllocString(L"0");  
    dbcoldesc[4].rgPropertySets[0].cProperties = 2;  
  
    // Set up the dbid for OrderDetails.  
    dbidTable.eKind = DBKIND_NAME;  
    dbidTable.uName.pwszName = L"OrderDetails";  
  
    if (FAILED(hr = pITableDefinition->CreateTable(NULL, &dbidTable,  
        nCols, dbcoldesc, NULL, 0, NULL, NULL, NULL)))  
        {  
        DumpError(pITableDefinition, IID_ITableDefinition);  
        goto SAFE_EXIT;  
        }  
  
SAFE_EXIT:  
    // Clean up dynamic allocation in the property sets.  
    for (nCol = 0; nCol < nCols; nCol++)  
        {  
        for (nProp = 0; nProp < NCOLPROPS_MAX; nProp++)  
            {  
            if (dbcoldesc[nCol].rgPropertySets[0].rgProperties[nProp].  
                vValue.vt == VT_BSTR)  
                {  
                SysFreeString(dbcoldesc[nCol].rgPropertySets[0].  
                    rgProperties[nProp].vValue.bstrVal);  
                }  
            }  
  
        delete [] dbcoldesc[nCol].rgPropertySets[0].rgProperties;  
        delete [] dbcoldesc[nCol].rgPropertySets;  
        }  
  
    return (hr);  
    }  

See Also

Tables and Indexes