Showing posts with label linking. Show all posts
Showing posts with label linking. Show all posts

Tuesday, March 20, 2012

Composite vs Single Primary Key

Our DBA has chosen to use an autonumber column as the primary key for a linking table consisting of 2 other IDs, I'll call them x and y. Another developer is concerned that since our application only keeps track of IDs x and y, not the autonumber ID, that searches will be less efficient because if x and y were a composite key they would be indexed. Is this correct? If so, couldn't you just create an index for these columns to make it equally efficient?

I believe that to eliminate all composite keys is a requirement for second normal form (correct me if I'm wrong), but does assigning an autonumber primary key to a linking table made up of solely 2 IDs have any benefits?

First of all, it's entirely possible to have the primary key (in the relational database sense, the one you can link tables with) different from the CLUSTERING key (the key for the main index of the table), e.g.:

Create Table T(p int primary key nonclustered, x int, y int);

Create Clustered Index T_i on T(x);

Also, you can always create secondary nonclustered indexes to improve efficiency, please look up indexing strategies articles (e.g. Books On-Line article http://msdn2.microsoft.com/en-us/library/ms189271.aspx), although I think for a linking table a single clustered index should be fine.

Now, for your particular issue I can't think of any benefit to an autonumber primary key except that if a lot of INSERT operations occur, and if the two other ID's are not naturally increasing (on average), then the autonumbering clustering key would keep the index from being fragmented and INSERT's would be quicker since you always insert at the end. Having said that, my gut feel is you shouldn't do it and you should keep the composite key. The composite key (if it's a primary key not just a clustereing key) also would enforce that no duplicate combinations would be inserted into the linking table.

|||

2nf just states that all data in a table must rely on the entire key.

that doesnt mean the key cant be composite.

if this table resolves a many to many relationship (Which sounds to be the case) then it is correctly designed for 2nd (even 3nf)

Friday, February 17, 2012

compile [really link] question

Hi,
I'm having trouble linking a simple example using the bcp functions...
The error:
************************************************
test.obj : error LNK2001: unresolved external symbol _bcp_init@.20
************************************************
Environment: Windows 2000, SQL Server 2000, VS C++ 6.0 Enterprise
I know my environment and connection handles are working...but my bcp_init()
call is driving me crazy...
Headers I'm including:
////////////////////////////////
//C HEADERS
#include <stdio.h>
#include <stdlib.h>
//WINDOWS
#include <windows.h>
//ODBC HEADERS
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//BCP HEADER
#include <Odbcss.h>
////////////////////////////////
Library I'm including
Odbcbcp.lib
I use both
a) /libpath:"C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib"
and
b) LIB environmental variable points there too
I've checked the folder and the file is def. there!
Thinking I might not be using the ODBC 3.0 headers and lib...
I've check my LIB path and "Odbcbcp.lib" only exists once!
If I comment out the bcp_init() call, my program compiles and links fine
The call to bcp_exec() is FINE...
Anyone have a clue as to what I'm doing wrong?
Included below is my c file (test.c) pasted just in case I'm doing something
else wrong.
// START test.c
#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
#include <Odbcss.h>
#define MAXBUFLEN 255
#define SQLSERVER1
#defineDSN2
int createHandles();
void destroyHandles();
SQLHENV henv = SQL_NULL_HENV;//Environment Handle
SQLHDBC hcn = SQL_NULL_HDBC;//Connection Handle
main()
{
intret;//internal return codes
RETCODEretcode;//ODBC return codes
UCHARszDSN[SQL_MAX_DSN_LENGTH+1] = "cnABL",//DSN info
szDSN_UID[MAXNAME] = "sa",
szDSN_PSWD[MAXNAME] = "";
SQLCHARconnectionStringIn[MAXBUFLEN] = "";//SQL SERVER driver
connection string
SQLCHAR connectionStringOut[MAXBUFLEN];//SQL SERVER driver
connection string out
SQLSMALLINT lengthOfConnectionStringOut = 0;
// Bulk copy variables.
SDWORD cRows;
intmyType = DSN;
//Create Handles
if ( ( ret = createHandles() ) )
{
destroyHandles();
returnEXIT_FAILURE;
}
switch (myType)
{
case SQLSERVER:
//Build up the ConnStrIn...
sprintf(connectionStringIn, "DRIVER={SQL
Server};SERVER=%s;UID=%s;PWD=%s;DATABASE=%s;", "", "sa", "", "abldb");
printf("\nCONNECTION STRING->%s<-", connectionStringIn);
//connect
retcode = SQLDriverConnect(hcn,// Connection handle
NULL,// Window handle
connectionStringIn,// Input connect string
SQL_NTS,// Null-terminated string
connectionStringOut,// Address of output buffer
MAXBUFLEN,// Size of output buffer
&lengthOfConnectionStringOut,// Address of output length
SQL_DRIVER_NOPROMPT);
//check it
if (retcode == SQL_ERROR)
//if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nSQL SERVER CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nSQL SERVER CONNECTION SUCCESS!");
}
break;
case DSN:
//connect
retcode = SQLConnect(hcn, szDSN, (SWORD)strlen(szDSN), szDSN_UID,
(SWORD)strlen(szDSN_UID), szDSN_PSWD, (SWORD)strlen(szDSN_PSWD));
//check it
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nDSN CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nDSN CONNECTION SUCCESS!");
}
break;
}
// Initialize the bulk copy.
//retcode = bcp_init(hcn, "abldb..trendtable", "c:\\ben\\BCPODBC.bcp",
"c:\\ben\\BCPERROR.out", DB_OUT);
// Note that the test is for the bulk copy return of SUCCEED,
// not the ODBC return of SQL_SUCCESS.
if ( (retcode != SUCCEED) )
{
printf("bcp_init(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
// Execute the bulk copy.
retcode = bcp_exec(hcn, &cRows);
if ( (retcode != SUCCEED) )
{
printf("bcp_exec(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
printf("Number of rows bulk copied out = %d.\n", cRows);
//clean up
destroyHandles();
return EXIT_SUCCESS;
}
int createHandles()
{
RETCODE retcode;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Allocate an ODBC connection handle
retcode = SQLAllocHandle(SQL_HANDLE_DBC,
henv,
&hcn);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
return 1;
}
void destroyHandles()
{
SQLDisconnect(hcn);
SQLFreeHandle(SQL_HANDLE_DBC, hcn);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// END test.c
thanks in advance,
georgejetson
Nevermind...
Although I had my environmental variables pointing to the more recent dirs
first, it was not working...I'm still learning ms vc dev environment...
FYI: To compile ODBC using BCP in VC6 Enterprise...
My Solution:
a) Downloaded latest and greatest mdac sdk
b) Added mdac lib & include dirs to LIB & INCLUDE environmental variable
list at front of list
c) For all configurations (Release/Active...) in VC6
1) Made sure all required libs existed
Odbc32.lib Odbccp32.lib Odbcbcp.lib
(probably don't need Odbccp32.lib)
2) Set Additional Library Path to FIRST use new mdac/lib folder, then
sqlserver/lib folder
ie:C:\mdacSdk28\Libs\x86,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Lib
d)Did the same thing for additional include directories
ie:C:\mdacSdk28\Inc,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Include
In my source code, I included the following...
//ALL ODBC CALLS NEED
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//ODBC UNICODE
#include <Sqlucode.h>
//ODBC BCP
#include <Odbcss.h>
e) Finally, I took OUT the /nologo option so I could see where the compiler
and linker were really looking
the result below
Compiling...
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
cl /MLd /W3 /Gm /GX /ZI /Od /I "C:\mdacSdk28\Inc" /I "C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include" /D "WIN32" /D "_DEBUG"
/D "_CONSOLE" /D "_MBCS" /FR"Debug/" /Fp"Debug/prjOdbcTest.pch" /YX
/Fo"Debug/" /Fd"Debug/" /FD /GZ /c
"C:\sqlutil\odbcTest\test.c"
test.c
Note: Using precompiled header
Linking...
Creating browse info file...
prjOdbcTest.exe - 0 error(s), 0 warning(s)
thanks in advance,
georgejetson

compile [really link] question

Hi,
I'm having trouble linking a simple example using the bcp functions...
The error:
****************************************
********
test.obj : error LNK2001: unresolved external symbol _bcp_init@.20
****************************************
********
Environment: Windows 2000, SQL Server 2000, VS C++ 6.0 Enterprise
I know my environment and connection handles are working...but my bcp_init()
call is driving me crazy...
Headers I'm including:
////////////////////////////////
//C HEADERS
#include <stdio.h>
#include <stdlib.h>
//WINDOWS
#include <windows.h>
//ODBC HEADERS
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//BCP HEADER
#include <Odbcss.h>
////////////////////////////////
Library I'm including
Odbcbcp.lib
I use both
a) /libpath:"C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib"
and
b) LIB environmental variable points there too
I've checked the folder and the file is def. there!
Thinking I might not be using the ODBC 3.0 headers and lib...
I've check my LIB path and "Odbcbcp.lib" only exists once!
If I comment out the bcp_init() call, my program compiles and links fine
The call to bcp_exec() is FINE...
Anyone have a clue as to what I'm doing wrong?
Included below is my c file (test.c) pasted just in case I'm doing something
else wrong.
// START test.c
#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
#include <Odbcss.h>
#define MAXBUFLEN 255
#define SQLSERVER 1
#define DSN 2
int createHandles();
void destroyHandles();
SQLHENV henv = SQL_NULL_HENV; //Environment Handle
SQLHDBC hcn = SQL_NULL_HDBC; //Connection Handle
main()
{
int ret; // internal return codes
RETCODE retcode; // ODBC return codes
UCHAR szDSN[SQL_MAX_DSN_LENGTH+1] = "cnABL", //DSN info
szDSN_UID[MAXNAME] = "sa",
szDSN_PSWD[MAXNAME] = "";
SQLCHAR connectionStringIn[MAXBUFLEN] = ""; //SQL SERVER driver
connection string
SQLCHAR connectionStringOut[MAXBUFLEN]; //SQL SERVER driver
connection string out
SQLSMALLINT lengthOfConnectionStringOut = 0;
// Bulk copy variables.
SDWORD cRows;
int myType = DSN;
//Create Handles
if ( ( ret = createHandles() ) )
{
destroyHandles();
return EXIT_FAILURE;
}
switch (myType)
{
case SQLSERVER:
//Build up the ConnStrIn...
sprintf(connectionStringIn, "DRIVER={SQL
Server};SERVER=%s;UID=%s;PWD=%s;DATABASE
=%s;", "", "sa", "", "abldb");
printf("\nCONNECTION STRING->%s<-", connectionStringIn);
//connect
retcode = SQLDriverConnect( hcn, // Connection handle
NULL, // Window handle
connectionStringIn, // Input connect string
SQL_NTS, // Null-terminated string
connectionStringOut, // Address of output buffer
MAXBUFLEN, // Size of output buffer
&lengthOfConnectionStringOut, // Address of output length
SQL_DRIVER_NOPROMPT);
//check it
if (retcode == SQL_ERROR)
//if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nSQL SERVER CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nSQL SERVER CONNECTION SUCCESS!");
}
break;
case DSN:
//connect
retcode = SQLConnect(hcn, szDSN, (SWORD)strlen(szDSN), szDSN_UID,
(SWORD)strlen(szDSN_UID), szDSN_PSWD, (SWORD)strlen(szDSN_PSWD));
//check it
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nDSN CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nDSN CONNECTION SUCCESS!");
}
break;
}
// Initialize the bulk copy.
//retcode = bcp_init(hcn, "abldb..trendtable", "c:\\ben\\BCPODBC.bcp",
"c:\\ben\\BCPERROR.out", DB_OUT);
// Note that the test is for the bulk copy return of SUCCEED,
// not the ODBC return of SQL_SUCCESS.
if ( (retcode != SUCCEED) )
{
printf("bcp_init(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
// Execute the bulk copy.
retcode = bcp_exec(hcn, &cRows);
if ( (retcode != SUCCEED) )
{
printf("bcp_exec(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
printf("Number of rows bulk copied out = %d.\n", cRows);
//clean up
destroyHandles();
return EXIT_SUCCESS;
}
int createHandles()
{
RETCODE retcode;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle ( SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr( henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Allocate an ODBC connection handle
retcode = SQLAllocHandle( SQL_HANDLE_DBC,
henv,
&hcn);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
return 1;
}
void destroyHandles()
{
SQLDisconnect(hcn);
SQLFreeHandle(SQL_HANDLE_DBC, hcn);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// END test.c
thanks in advance,
georgejetsonNevermind...
Although I had my environmental variables pointing to the more recent dirs
first, it was not working...I'm still learning ms vc dev environment...
FYI: To compile ODBC using BCP in VC6 Enterprise...
My Solution:
a) Downloaded latest and greatest mdac sdk
b) Added mdac lib & include dirs to LIB & INCLUDE environmental variable
list at front of list
c) For all configurations (Release/Active...) in VC6
1) Made sure all required libs existed
Odbc32.lib Odbccp32.lib Odbcbcp.lib
(probably don't need Odbccp32.lib)
2) Set Additional Library Path to FIRST use new mdac/lib folder, then
sqlserver/lib folder
ie:C:\mdacSdk28\Libs\x86,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Lib
d)Did the same thing for additional include directories
ie:C:\mdacSdk28\Inc,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Include
In my source code, I included the following...
//ALL ODBC CALLS NEED
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//ODBC UNICODE
#include <Sqlucode.h>
//ODBC BCP
#include <Odbcss.h>
e) Finally, I took OUT the /nologo option so I could see where the compiler
and linker were really looking
the result below
--
Compiling...
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
cl /MLd /W3 /Gm /GX /ZI /Od /I "C:\mdacSdk28\Inc" /I "C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include" /D "WIN32" /D "_DEBUG"
/D "_CONSOLE" /D "_MBCS" /FR"Debug/" /Fp"Debug/prjOdbcTest.pch" /YX
/Fo"Debug/" /Fd"Debug/" /FD /GZ /c
"C:\sqlutil\odbcTest\test.c"
test.c
Note: Using precompiled header
Linking...
Creating browse info file...
prjOdbcTest.exe - 0 error(s), 0 warning(s)
--
thanks in advance,
georgejetson