|
You got MCSD? Master!! Thats what I'm working at right now. Anyway, why did you say
Jeff Varszegi wrote:
index can't be fragmented.
I mean, after a bunch of inserts/deletes, any index can get fragmented.
|
|
|
|
|
Well, I never actually got down into actually testing for myself the physical file's structural changes when inserting and deleting, but that's not my understanding. Here's what I found in Books Online when I searched for 'clustered index key':
After the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. For example, if an application frequently executes a query to retrieve records between a range of dates, a clustered index can quickly locate the row containing the beginning date, and then retrieve all adjacent rows in the table until the last date is reached.
Also, this:
Clustered indexes are not a good choice for:
- Columns that undergo frequent changes
This results in the entire row moving (because SQL Server must keep the data values of a row in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile.
Like I said, I freely admit that I am not a complete expert on SQL Server. Anything I say should be taken with a grain of salt.  I'm sure you'll do really well on all your tests, because I don't think that the average schmuck who takes these things is so concerned with getting everything really straight. It's nice to see.
Regards,
Jeff Varszegi
|
|
|
|
|
Oh, one more thing: you know about Transcender, right? I don't view their test-prep software so much as a cramming aid as an extra source of valuable information. They write paragraphs-long explanations for every right and wrong choice on their practice tests, and it's happened more than once that their explanations sparked extra explorations on my part. I think their stuff is really worth the money.
Regards,
Jeff Varszegi
|
|
|
|
|
Yes, I'm taking the exams now. Find it pretty useful in filling the loopholes. The most annoying part is when you bought the wrong prep package that gives wrong answers or lame explanations - lame vendors. MeasuXXXXX for instance. They offer online tutoriung along with prep packages. But you can only ask questions regarding specific question on their prep. Depending on the person you talk to, if you ask anything slightly deviate from the question itself, they'll tell you it's outside their jurisdiction. There're some good tutors who'd take you all the way through. But for the most part, their online help does not better than forum like this.
|
|
|
|
|
Going through the options:
a) has no effect, since that's purely syntactic convenience: SQL Server translates that to PolicyDate >= '2/1/2001' AND PolicyDate <= '3/4/2002' anyway.
b) A hash might work better, but it's clear that the index isn't being used, as you say. A hash join constructs a hash table of one side of the join, which the other looks up in, whereas a loop join simply loops over each side of the result set looking for matches.
c) According to http://www.sql-server-performance.com/hints_table.asp[^], WITH(INDEX(0)) forces a table scan - precisely what we're trying to avoid. That same page recommends using index names rather than index IDs, because the IDs can change if the indexes are dropped and recreated - in other words, they're fragile.
d) Updates any index statistics that are out of date. SQL Server uses the statistics to determine how good an index might be for finding the requested information.
e) Rebuilding the index will incidentally update the statistics, but it's a lot of work that might not be necessary (and locks everyone out of the table while it does it).
Assuming that appropriate indexes already exist (and I don't know which table contains which fields), then updating statistics might work. If there are no appropriate indexes, it won't help - at least one of the tables will have to be scanned to find the rows matching the WHERE clause, then those rows joined onto the other table. The problem description doesn't say whether the index scan was caused by the join operation or by the original lookup.
Answering your question 3, I looked up 'hash join' in my copy of Inside SQL Server 2000 (which I thoroughly recommend). If there isn't a good index for the join (in this case, no index on AgentPolicy.PolicyNumber), a hash join might be cheaper. However, it can only be done for an equijoin (i.e. the join condition uses '=') and can take a lot of memory. The query optimiser won't use it if it thinks it will need to do more I/O to store and retrieve the temporary hash table than it will to repeatedly retrieve the original data.
This is one reason to keep hints out of your queries - they tend to be very system specific. What helps one system might hurt another.
|
|
|
|
|
In C#, since SqlException class has SqlErrorCollection member which is a colleciton of SqlErrors, why
do I always get only one error even though I do raise two errors(both at severity level 16) within a sql statement such as a stored procedure before "return" is called? Is there any special property I need to set or any other trick for me to get back both errors? Thanks.
|
|
|
|
|
Hmm. Is it true that only the LAST one is returned? I don't know of any trick to return more than one error. If necessary, try a nesting approach (where the text of one message contains the text of the other). Cheesy, but it works.
Thank you.
Jeff Varszegi
|
|
|
|
|
Not even the last one. Actually only the first one is returned. I thought it should work since they have such a concept of collection of sqlerrors there.
|
|
|
|
|
Geez, that sounds like an awful bug in the API. I would report it at the first opportunity. I can think of lots of cases where I'd want to return multiple error statements from a stored procedure, although I haven't had to do this yet. I tested just to verify on my own SQL Server installation, and it's definitely not a SQL Server issue. Good luck.
Regards,
Jeff Varszegi
|
|
|
|
|
If you are doing a RAISERROR in T-SQL, then it seems right that it breaks out and returns *that* error to your calling code.
Not sure how you would return 2 errors to the calling code...
Cheers,
Simon
sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.
article :: animation mechanics in SVG blog:: brokenkeyboards
|
|
|
|
|
I Need to know if it possible to run an sql SELECT commands on a DataSet that already contains the infromation in it's tables because i want to maintain the structure of the data base in the data-set but i want to be able to get Joins from the DataSet.
Example : Lets say i have a customer table & an accounts table where the customers have their id as the key & the accounts have the customer id & the account id as the key.
now i would like to get the accounts of a certain customer.
My questions are :
1. Is this possible with a DataSet ?
2. If it is possible, how can it be done ?
|
|
|
|
|
If everything is in one dataset, DataTable.Select("customer_id=x")
If you have two related datasets go throught the releationship to get the children.
|
|
|
|
|
Hi everyone,
Using access, I try to run the following SQL:
SELECT (Select Values from AnotherTable) FROM MyQuery
MyQuery returns a set of data and I want to filter it further by selecting only the records that are returned by the sub-select. However, this does not work. Are such select queries illegal. Is it not possible to tell it the column names for select through another select query? Is there any workaround for it, rather than creating the string in memory and manually concatenating each column name. An SQL solution would be nice.
I would really appreciate some help on this one.
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Is this what you are trying to
SELECT values FROM Table
WHERE somefield IN (SELECT values FROM AnotherTable)
Search MSDN for subquery
|
|
|
|
|
Hi there,
Actually here is a more clear description of what I am trying to do. Sorry to be vague earlier.
In a Select statement you can specify the columns that you want to display.
Example,
Select FirstName, LastName from PeopleTable.
What I am trying to do is this: I have a table (say PeopleTable) that has many columns...say LastName, FirstName, Data Of Birth, Occupation etc.
Now, I have another table that is filled by another process that tells me which columns to display: say, example, LastName, FirstName...
So what I wanted to do was:
SELECT (Select Values from AnotherTable) FROM PeopleTable
Select Values from AnotherTable would return LastName and FirstName and my query would be:
SELECT(LastName, FirstName) from PeopleTable. However, I guess SQL does not do such syntactic substitution.
Does anyone know a way to achieve this. I do not want to use a string and concatenate each of the choices, if there is a way to achieve this with SQL.
Thanks a lot 
Sincerely,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Without using a string and concating your query together, No, there is no way to do what you want.
Sorry.
|
|
|
|
|
Yeah, that is what I figured after hours of experimenting here. Damn SQL, we need macros here 
Thanks for replying and making me finally give up so that I can go to bed
Sincerely,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
hi,there
I have a Datagrid bind to Dataview (Columns : Name,type,body,haveread,system......)
show as follow :
<asp:datagrid id="myDataGrid" runat="server" onitemdatabound="myDataGrid_ItemDataBound" border="0"
="" width="100%" allowsorting="True" backcolor="#EEEEEE">
<footerstyle backcolor="#CCCC99">
<columns>
<asp:boundcolumn datafield="Name" sortexpression="MailType" headertext=" ">
<headerstyle width="3%">
<asp:boundcolumn datafield="Name" sortexpression="Name" headertext="Name">
<headerstyle font-bold="True" width="20%">
<asp:boundcolumn datafield="Type" sortexpression="Type" headertext="Type">
<headerstyle font-bold="True" width="57%">
Code behind have Event as follow:
public void myDataGrid_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if ((e.Item.ItemType == ListItemType.Item) || (e.Item.ItemType == ListItemType.AlternatingItem))
{
e.Item.Attributes.Add("onmouseover", "this.style.backgroundColor='#FFFFCC'");
e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor='#FFFFFF'");
}
}
Please help me how to do :
1.If I click on one row, I can in the textBox1---to TextBox6 show detail information such name,type,body,haveread....(though Datagrid just show 2 columns),
2.If I check some row in dataview the item"haveread" is "true", I want Bold Datagrid row related .
3.If I check some row in dataview the item"Syetem " is "true", I want have more column in the Datagrid show a picture "s1.gif" ,otherwise "s2.gif"
I am a new programmer in Datagrid,please show me in detail as possible.
Thank a lot
|
|
|
|
|
I want to know the complete information of a Database...
how many tables it have what r there manes...
each table have how many attributes and what r there names......
and want to show it in a treeview...like this
TreeView1.Nodes(0).Nodes.Add(snode)
using OleDbConnection
i execute query like this
select * from emp
Table.Columns.Count
Table.Columns.Item(count).ToString
through this i came to know the column number and name
but want to know full information about a database.....
Nothing ...........................................................
|
|
|
|
|
Ineed help to know how to open mdb file in vb.net
|
|
|
|
|
use the OleDb classes. You'll find all the information in the online help or at MSDN. You should read up on database connectivity in .NET to get an idea of how it all works. However, OleDBConnection, OleDBCommand are both good search strings to get you started.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
Enumerators in .NET: See how to customise foreach loops with C#
|
|
|
|
|
Another good resource I like is http://www.connectionstrings.com[^]. I always forget what to use for access or SQL connection strings - that site has those plus plenty more.
|
|
|
|
|
I've done a couple of ASP/.NET apps that use databases so I know the basics of ADO. My problem is all my development up to this point have been in MySQL and not MsSQL(Or maybe just SQL) anyway, I have absolutly no idea what I need to do when it comes to getting started with what I need.
Here's what I fumbled through so far.
1) Got SQL installed on a dedicated SQL machine. It's already being used by other programs.
So that's it.... 
I did use the Enterprise manager to create a new database, but I noticed all this other stuff gets created with it (All those sys tables for one thing) Do I need to keep those around? Adding tables seems pretty strait forward and not too tough. I am a liitle confused on users, as I added a new database user to the database but I can't seem to remove the user dbo. Nor can I seem to have the new user take ownership of everything as I want to have this new user I created to be able to do everything(Cause I will be using this login to do all the updates and querys on the DB)
Anyone know how I should be properly creating this or if there are any quick and dirty guides on quickly setting up a new database. All my books seem to skim right through this stuff.....
Thanks!
|
|
|
|
|
Kluch wrote:
(All those sys tables for one thing) Do I need to keep those around?
Yes, you need the system tables - they contain the metadata that keeps SQL Server running. You wouldn't want to remove the table that defines all the columns in the database would you? Or the table that contains the permissions for who can access what?
Kluch wrote:
I can't seem to remove the user dbo
dbo = DataBase Owner and cannot be removed. IIRC, Any user with admin rights is automatically the dbo. All objects that everyone has access to should be created as dbo because then things get really confused. As a standard many organisations have a rule that the dbo owns everything - this improves efficiency and removes confusion when security come in to play. If you start having lots of users owning lots of different things then security and access rights are hell to manage.
Kluch wrote:
All my books seem to skim right through this stuff.....
I would suggest that only the sa (system administrator) user should be modifying the database at this level. If you take that advice then there isn't much to do - so skim right on through.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
Enumerators in .NET: See how to customise foreach loops with C#
|
|
|
|
|
In my DTS package I have (Execute SQL Task ) name test1.
I would like to execute this task and skip other taks in
the same dts. How do I do this?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
