|
John Gathogo wrote: Is there any benefit in setting a primary key in a table variable?
Yes, mainly to ensure referential integrity. That's probably an unsatisfying answer.
Imagine that your table only has "name" defined in it. We could add "John" as a record, and have a list of things that John should do in some other table. Might look like this;
TABLE Users (Name NVARCHAR(36))
"John"
"Peter"
TABLE Tasks (Name NVARCHAR(36), ThingToDo NVARCHAR(50))
"John", "Water the plants"
"John", "Cook dinner"
"Peter", "Write an article for CodeProject"
We're in trouble as soon as someone adds another user with the firstname "John"; we would no longer be able to differentiate between the tasks of the first "John" and the second "John". Now, it's a "best practice" to define a set of column-names that make a record unique. We could add the [Birthdate] of the user to the "Users" table, that way we could distingiush between the first and the second Johnny. It's easier[^] to just add a generated identity and to use that as a primary key.
A primary key is often accompanied by a clustered index. There's a heap of information on MSDN[^], if that is your kind of thing 
I are Troll 
|
|
|
|
|
Hi Guys,
I've just finished a .NET application that uses MSSQL EXPRESS as its local temp DB. I just wanna ask, for convenience sake, is there a way i can put SQLEXPRESS into the installation such that it installs "silently", with the other app? I mean, without the user having to run the .exe by himself.
He who goes for revenge must first dig two graves.
|
|
|
|
|
Look Here[^]
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Thanx. Was quite helpful.
He who goes for revenge must first dig two graves.
|
|
|
|
|
I want to connect to the sql server database and do some query command via ado. I use this connection string to do that work:"Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DaDian2008;Data Source=localhost;UID=sa;PWD=123456;Network Library=dbmssocn", it works well. But how about with a remote server(LAN). For example, my ip address is 192.168.1.100, i just replace localhost to 192.168.1.100, it would get an exception says:
Code = 80004005
Code meaning = Unspecified error
Source = Microsoft OLE DB Provider for SQL Server
Description = [DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.
Why? And how can i connect to a remote sql server(MS SQL Server within LAN)?
btw, if I use "Driver={SQL Server};Server=192.168.1.100;Address=192.168.1.100,1433;Network=DBMSSOCN;User ID=sa;PWD=123456;Database=DaDian2008; Then, I can connect to the database on my locale computer, but it would fail again on another computer.
|
|
|
|
|
You need the name/IP address of the server you want to connection to.
localhost - 192.168.1.100 and your computer name are all pointing the the instance of SQL server installed on your machine.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your reply. But my real problem is that, how to connect to a remote ms sql server(within LAN)?
|
|
|
|
|
And the answer is to find out the name/IP Address of the server you wish to connect to and use that in the connection string.
I assume you have the database set up with credentials on the other server!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Oh, I use my computer as sql server, the code which useing "Driver={SQL Server};Server=192.168.1.100;Address=192.168.1.100,1433;Network=DBMSSOCN;User ID=sa;PWD=123456;Database=DaDian2008" works well on my own computer, but doesn't on another computer.
|
|
|
|
|
kcynic wrote: 192.168.1.100
This is a local IP, the other machine is looking for SQL on their machine, not yours. You might try replacing that with a machine name or find out your lan IP. I am presuming you want to use your machine as the SQL server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
But, 192.168.1.100 is the real local IP of mine and i do want to use my machine as the SQL server. You meant, I should replace Server=192.168.1.100 with Server=MyPcName? But, if so, to a client witch knew its server ip only, how should it configure this argument? Unfortunely, if it's not a practice but a C/S product, one of its user is a orgnization with a plenty of members,they all need to install the client program, so they configure the Server argument with their server's correct name. And for another group, they have to do the similar work! In another words, the installation can't be done automately! right?
|
|
|
|
|
You need to define your requirements
Are you on a LAN with all the clients
How many clients
Is you machine the dev box
Do you have a UAT server or even a database
Do you have a production server/ddatabase
Do you have an IT department, b/c if you are it you are in deep do do.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Oh, there are the answers:
1. All clients within a LAN.
2. My pc is a dev box
3. I have MS SQL Server 2005 installed.
4. Of course no product. I just took that as a example
5. Im not a web developer, so b/s has no help for me.
No matter how many clients would be, i just only want to know how. Thanks
|
|
|
|
|
Depending on whether you use C++ or VB, the connection string is slightly different. Look at connectionstrings.com . Also, you should first make sure that you can connect with C:\WINDOWS\system32\odbcad32.exe before playing with your code.
|
|
|
|
|
Oh, thanks for you reply, i think i got the answer from that page.
|
|
|
|
|
Identify what is your problem. Is that Network problem or SQL Server problem or both?
You are using LAN so test these:
1. Try ping command to/from server & clients’ PCs. (y/n?)
2. Or try file or printer sharing among PCs.
If you failed all above tests, you have Network problem. Otherwise you are facing the problem to connect with SQL Server.
All Mycroft Holmes’s discussions are about Network problem without being sure what the problem is.
I’m waiting to know what your problem is.
|
|
|
|
|
Hi... i dont have any network problem but still i'm not able to connect to MS SQL Server which is in another system of my LAN.... Please provide me what to chect wit SQl server and Connection string... pLZZZZZZZZ.......
|
|
|
|
|
really gives us novices a great place to post our beginner questions:
SELECT COUNT(BookTitle) FROM Book WHERE IsFiction = 1;
I am Indexing the IsFiction field, but not sure if I should be Indexing the BookTitle field too? Any expert thoughts?
Thanks again!
|
|
|
|
|
Not based on that query. If the booktitle field is in a join or a where clause then it may need an index. Take a look at the execution plan and see where the main cost is, if it says table scan then an index will help.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am not joining on the BookTitle field...I just wasn't sure if Count needed BookTitle to be Indexed when it aggregates the count command. I will leave BookTitle un-indexed, thanks!
|
|
|
|
|
You could in theory get a faster query if you make a composite index on (IsFiction, BookTitle) and booktitle are having many duplicates.
But this is improbable in this case as booktitles are fairly unique, and you will have extra overhead on the inserts and updates.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
It is normally good practice to have indexes on fields used in where clauses. However, if IsFiction is a bit field, it will have low selectivity (a measurement of how unique each value is) which will mean that it is highly unlikely that the query optimizer would consider using the index. You are much more likely to find that the optimizer is using a clustered index scan or a table scan.
|
|
|
|
|
maybe in need 2 go back to the basics...can anyone give me a link or two of a basic tutorial that talks about optimization...i always put indexes on Where or Order By fields if I am doing joins on the Where, and if I think the tables are going to have a huge number of records.
also, a simple tutorial link about the Query Optimizer and how to use it would be great too.
Thanks!
|
|
|
|
|
|
I am using VB 2008 & MS Access database
I have a table WebRateList with 2 columns ResFrom and ResTo defined as DateTime, another column WebID defined as Text (allow Null)
When I search:
strSQL = "SELECT * FROM WebRateList WHERE ResFrom < #" & ChkOut & "# AND ResTo > #" & ChkIn & "#"
Then it showed "It is in this range!"
But if I scan the same row with column WebID must be emptied, it didn't get in (infact the WebID is empty)
strSQL = "SELECT * FROM WebRateList WHERE ResFrom < #" & ChkOut & "# AND ResTo > #" & ChkIn & "# AND WebID = NULL"
da = New OleDbDataAdapter(strSQL, dbConn)
dt = New DataTable
da.Fill(dt)
If (dt.Rows.Count() > 0) Then
MsgBox("It is in this range!")
End If
Even I defined the following the result is the same
strSQL = "SELECT * FROM WebRateList WHERE ResFrom < #" & ChkOut & "# AND ResTo > #" & ChkIn & "# AND WebID = '' "
Anyone can help? 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
