COGNOS = CRAP

I cant wait for Microsoft to put these idiots out of business
so i can stop working on the crapIt'll be a while, won't it? In the recent "Ready Set Go" webcast, I asked
if they were going to have more functionality in Reporting Services than
what I get with OWC and the accompanying field list, and they
private-messaged me saying that for now, at least, there would be less.
I know we can do cool stuff with Excel hooked up to cubes, but what my
customers want is a web-based report that shows a few charts together on one
page, and the ability to slice the cube from any of those charts. Right now
Office Web Components won't show just achart (a flattened one can be shown,
without interactivity, via ADO), and I can't show multiple slices of a cube
(unless I'm willing to do clunky frames), and even if I'm willing to show
the table with the chart, the chart formatting issues are untenable.
I agree with you that I would much rather use MS tools for data warehousing,
but on the presentation end, even with the new Reporting Services, there
seems to be room for improvement. That is my impression. I certainly don't
know everything, so if I've got my facts wrong, I hope someone here will
straighten 'em out for me.
"Reg Besseling" <reg.usenet.nospam@.epic-sc.com> wrote in message
news:aLGdnZ5fWrlBhiLdRVn-ig@.is.co.za...
> I cant wait for Microsoft to put these idiots out of business
> so i can stop working on the crap
>|||Try checking out a XLCubed!
"J O Holloway" <respond.to.group@.your.convenience> wrote in message
news:e0$aanXSEHA.1172@.TK2MSFTNGP11.phx.gbl...
> It'll be a while, won't it? In the recent "Ready Set Go" webcast, I
asked
> if they were going to have more functionality in Reporting Services than
> what I get with OWC and the accompanying field list, and they
> private-messaged me saying that for now, at least, there would be less.
> I know we can do cool stuff with Excel hooked up to cubes, but what my
> customers want is a web-based report that shows a few charts together on
one
> page, and the ability to slice the cube from any of those charts. Right
now
> Office Web Components won't show just achart (a flattened one can be
shown,
> without interactivity, via ADO), and I can't show multiple slices of a
cube
> (unless I'm willing to do clunky frames), and even if I'm willing to
show
> the table with the chart, the chart formatting issues are untenable.
> I agree with you that I would much rather use MS tools for data
warehousing,
> but on the presentation end, even with the new Reporting Services, there
> seems to be room for improvement. That is my impression. I certainly
don't
> know everything, so if I've got my facts wrong, I hope someone here will
> straighten 'em out for me.
>
> "Reg Besseling" <reg.usenet.nospam@.epic-sc.com> wrote in message
> news:aLGdnZ5fWrlBhiLdRVn-ig@.is.co.za...
>

COGNOS = CRAP

I cant wait for Microsoft to put these idiots out of business
so i can stop working on the crap
It'll be a while, won't it? In the recent "Ready Set Go" webcast, I asked
if they were going to have more functionality in Reporting Services than
what I get with OWC and the accompanying field list, and they
private-messaged me saying that for now, at least, there would be less.
I know we can do cool stuff with Excel hooked up to cubes, but what my
customers want is a web-based report that shows a few charts together on one
page, and the ability to slice the cube from any of those charts. Right now
Office Web Components won't show just achart (a flattened one can be shown,
without interactivity, via ADO), and I can't show multiple slices of a cube
(unless I'm willing to do clunky frames), and even if I'm willing to show
the table with the chart, the chart formatting issues are untenable.
I agree with you that I would much rather use MS tools for data warehousing,
but on the presentation end, even with the new Reporting Services, there
seems to be room for improvement. That is my impression. I certainly don't
know everything, so if I've got my facts wrong, I hope someone here will
straighten 'em out for me.
"Reg Besseling" <reg.usenet.nospam@.epic-sc.com> wrote in message
news:aLGdnZ5fWrlBhiLdRVn-ig@.is.co.za...
> I cant wait for Microsoft to put these idiots out of business
> so i can stop working on the crap
>
|||Try checking out a XLCubed!
"J O Holloway" <respond.to.group@.your.convenience> wrote in message
news:e0$aanXSEHA.1172@.TK2MSFTNGP11.phx.gbl...
> It'll be a while, won't it? In the recent "Ready Set Go" webcast, I
asked
> if they were going to have more functionality in Reporting Services than
> what I get with OWC and the accompanying field list, and they
> private-messaged me saying that for now, at least, there would be less.
> I know we can do cool stuff with Excel hooked up to cubes, but what my
> customers want is a web-based report that shows a few charts together on
one
> page, and the ability to slice the cube from any of those charts. Right
now
> Office Web Components won't show just achart (a flattened one can be
shown,
> without interactivity, via ADO), and I can't show multiple slices of a
cube
> (unless I'm willing to do clunky frames), and even if I'm willing to
show
> the table with the chart, the chart formatting issues are untenable.
> I agree with you that I would much rather use MS tools for data
warehousing,
> but on the presentation end, even with the new Reporting Services, there
> seems to be room for improvement. That is my impression. I certainly
don't
> know everything, so if I've got my facts wrong, I hope someone here will
> straighten 'em out for me.
>
> "Reg Besseling" <reg.usenet.nospam@.epic-sc.com> wrote in message
> news:aLGdnZ5fWrlBhiLdRVn-ig@.is.co.za...
>

CodeGen.net

hi, i cant use codeGen.net with SQL SERVER 6.5 this cause SQl SERVER 6.5 dose not support sqlclient is there is way to connect SQL SERVER 6.5. thanxYou can still use OleDb (System.Data.OleDb) to connect SQL 6.5

Code error please help

my code was working fine but i changed a few things and now i cant figure it out.

the problem is that when i click my edit button none of the items go into the listbox. i added a label to track th error and i narrowed it done to a smaller ssection. Please help

//------------------------ /////////////////////////////////////////////////////////////////////////////////protected void EditButton1_Click(object sender, EventArgs e) {int counter = 0;//counter for inserting into arraybool Validate = checknumberValidation(TextBox1.Text.ToString());if (Validate ==false)throw new Exception(InvalidCheckNumber + TextBox1.Text); Label4.Text = GridView1.Rows.Count.ToString();//---RETURNS 0--SHOULD BE NUMBER OF ITEMS--// //get # of checkboxes and set array size;int x = 0; x = SetStringArraySize();//set x to number of checkboxes that are checked String[] updateString =new String[x];//create string array of size x Label4.Text = GridView1.Rows.Count.ToString();//---RETURNS 0--SHOULD BE NUMBER OF ITEMS--// //for loop that will insert update statements into arrayfor (int i = 0; i < GridView1.Rows.Count; i++) { GridViewRow row = GridView1.Rows[i];bool isChecked = ((CheckBox)row.FindControl("UpdateCheckBox1")).Checked;if (isChecked ==true) {if (counter == 0) updateString[counter] ="[batchid] = '" + GridView1.Rows[i].Cells[3].Text +"' AND [loanid] = '" + GridView1.Rows[i].Cells[5].Text +"' AND [historycounter]='" + GridView1.Rows[i].Cells[8].Text +"'";if (counter > 0) updateString[counter] ="[batchid] = '" + GridView1.Rows[i].Cells[3].Text +"' AND [loanid] = '" + GridView1.Rows[i].Cells[5].Text +"' AND [historycounter]='" + GridView1.Rows[i].Cells[8].Text +"'"; counter++; } }//Add items to listbox and set listbox to visible. Panel1.Visible =true; PanelGridView.Visible =false; Panel2.Visible =false; updateCount.Text = updateString.Length.ToString(); updateCheckNumber.Text = TextBox1.Text;int q = 0;while (q < updateString.Length) { ListBox1.Items.Add(updateString[q]); q++; }//Label1.Text = temp.Length.ToString(); //Test print to label }//------------------------ //COMPLETEprotected int SetStringArraySize() { Label4.Text = GridView1.Rows.Count.ToString();//---RETURNS 0--SHOULD BE NUMBER OF ITEMS--//int count = 0;for (int i = 0; i < GridView1.Rows.Count; i++) { GridViewRow row = GridView1.Rows[i];bool isChecked = ((CheckBox)row.FindControl("UpdateCheckBox1")).Checked;if (isChecked ==true) { count++; } } Label4.Text = GridView1.Rows.Count.ToString();//---RETURNS 0--SHOULD BE NUMBER OF ITEMS--//return count;//returns number of checkboxes that are checked }///////////////////////////////////////////////////////////////////////////////// //------------------------

Are you saying GridView1 has no rows at time of clicking button? Have you checked it's Databound? Try adding a Gridview1.DataBind() statement before accessing the Rows.Count property.|||

ill give that a try.

I added a e.rowsaffected on the pageload for a count and it returns the correct number in the gridview on the page load. Then when i click my edit button it still says the number of rows but the other label still says 0. Ill try what you said, hopefully that works

|||

ok the label now returns the correct count but i have narrowed down the problem to my function that checks for the check boxes.

protected int SetStringArraySize() { Label4.Text = GridView1.Rows.Count.ToString();int count = 0;for (int i = 0; i < GridView1.Rows.Count; i++) { GridViewRow row = GridView1.Rows[i];bool isChecked = ((CheckBox)row.FindControl("UpdateCheckBox1")).Checked;if (isChecked ==true) { count++; } }return count;//returns number of checkboxes that are checked }

This always returns 0. my guess is it is not finding the control "UpdateCheckBox1" so here is the html code to go with it where i specify the check box and it shows up and is functional.

<%@. Page Language="C#" MasterPageFile="~/MasterPage2.master" AutoEventWireup="true" CodeFile="checknum.aspx.cs" Inherits="checknum" Title="Intranet - Utilities - Check Number Assignment" %><%@. Register Assembly="eWorld.UI, Version=2.0.0.2148, Culture=neutral, PublicKeyToken=24d65337282035f2" Namespace="eWorld.UI" TagPrefix="ew" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server"><!-- Auto Refresh page after 16 Minutes (1000 seconds) <meta http-equiv="refresh" content="1000"; url="checknum.aspx" />--> <script type="text/javascript" language="javascript"> function SelectAllCheckboxes(spanChk) { // Added as ASPX uses SPAN for checkbox var oItem = spanChk.children; var theBox=(spanChk.type=="checkbox")?spanChk:spanChk.children.item[0]; xState=theBox.checked; elm=theBox.form.elements; for(i=0;i<elm.length;i++) { if(elm[i].type=="checkbox" && elm[i].id!=theBox.id) { //elm[i].click(); if(elm[i].checked!=xState) elm[i].click(); //elm[i].checked=xState; } } }//end function function fnCheckTextBox1(sender, args) { var TextBox1 = document.getElementById("SearchTextBox1"); var TextBox2 = document.getElementById("SearchTextBox2"); if (TextBox1.value == '') { TextBox2.value = ''; TextBox2.disabled = true } else { TextBox2.disabled = false; } args.IsValid = true; } div><table style="width: 100%; font-size: 11pt"><tr style="width: 100%"><td style="width: 100%; text-align: center; background-color:#5D7B9D"> <strong><span style="color: white">Check Number Assignment</span></strong></td></tr></table></div> <div> <table style="font-size: 10pt; width: 100%"> <tr> <td align="right" style="width: 118px" valign="middle"> <span style="font-size: 10pt">PayeeID:</span></td> <td style="width: 20%" valign="middle"> <ew:MaskedTextBox ID="SearchTextBox1" runat="server" Font-Size="10pt"> </ew:MaskedTextBox></td> <td style="width: 20%" valign="bottom"> <asp:Button ID="SearchButton1" runat="server" Font-Size="10pt" Text="Search" OnClick="SearchButton1_Click" /> <asp:Button ID="resetButton" runat="server" Font-Size="10pt" OnClick="resetButton_Click" Text="Reset" /></td> <td rowspan="3" style="width: 48%"> <span style="font-size: 9pt">When searching by PayeeID use % as a wildcard. <br /> Example to search for all payees that have 'abc' in their PayeeID you would type %abc%<br /> <br /> Enter TransactionDate as mmddyy<br /> <br /> <asp:Label ID="Label2" runat="server" Text="Label" ForeColor="#FF0000"></asp:Label> <br /> <asp:Label ID="Label3" runat="server" ForeColor="#FF0000"></asp:Label> <br /> <asp:Label ID="itemCount" runat="server" Text="Label" ForeColor="Green"></asp:Label></span></td> </tr> <tr> <td align="right" style="width: 118px; height: 32px;" valign="middle"> <span style="font-size: 10pt">BatchID:</span></td> <td style="width: 20%; height: 32px;" valign="middle"> <ew:MaskedTextBox ID="SearchTextBox2" runat="server" Font-Size="10pt"> </ew:MaskedTextBox></td> <td style="width: 20%; height: 32px;"> </td> </tr> <tr> <td align="right" style="width: 118px; height: 34px;" valign="middle"> <span style="font-size: 10pt">TransactionDate:</span></td> <td style="width: 20%; height: 34px;" valign="middle"> <ew:MaskedTextBox ID="searchTransDate" Mask="99/99/99" ValidationExpression="^\d{2}/\d{2}/\d{2}$" runat="server"></ew:MaskedTextBox></td> <td style="width: 20%; height: 34px; text-align: center;"> <asp:Label ID="Label4" runat="server" Text="Label"></asp:Label></td> </tr> </table> <asp:Panel ID="Panel3" runat="server" Height="52px" Visible="False" Width="100%"> <hr style="height: 1px" />  <br /> <asp:Label ID="Label1" runat="server" Text="Label" Font-Size="10pt"></asp:Label><br /> <span style="font-size: 10pt"><strong>To change more check #s please click reset!<br /> </strong></span> </asp:Panel> <asp:Panel ID="Panel1" runat="server" Height="50px" Visible="False" Width="100%"> <span style="font-size: 10pt">You are about to change the check number on the following <asp:Label ID="updateCount" runat="server" Text="Label"></asp:Label> items to Check# <asp:Label ID="updateCheckNumber" runat="server" Text="Label"></asp:Label>.<br /> </span> <asp:Button ID="updateConfirm" runat="server" Font-Size="10pt" OnClick="updateConfirm_Click" Text="Confirm" />       <asp:Button ID="updateCancel" runat="server" Font-Size="10pt" OnClick="updateCancel_Click" Text="Cancel" /><br /> <br /> <br /> <asp:ListBox ID="ListBox1" runat="server" Font-Size="8pt" Height="172px" Width="100%"> </asp:ListBox></asp:Panel> <br /> <asp:Panel ID="Panel2" runat="server" Height="34px" Width="100%"> <asp:Button ID="EditButton1" runat="server" Text="Update" Width="72px" OnClick="EditButton1_Click" Font-Size="10pt" Height="24px" /> <span style="font-size: 10pt">Check #: <asp:TextBox ID="TextBox1" runat="server" Width="160px" Font-Size="10pt"></asp:TextBox></span></asp:Panel> <asp:Panel ID="PanelGridView" runat="server" Height="500px" ScrollBars="Auto" Width="100%"> <br /> <asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" Font-Names="Verdana" Font-Size="10pt" PageSize="20" EnableViewState="False"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <Columns> <asp:TemplateField> <ItemTemplate> <asp:CheckBox ID="Update_CheckBox1" runat="server" /> </ItemTemplate> <HeaderTemplate> <input id="Update_CheckBox1" onclick="javascript:SelectAllCheckboxes(this);" runat="server" type="checkbox" /> </HeaderTemplate> </asp:TemplateField> <asp:BoundField DataField="TransactionDate" HeaderText="Posting Date" SortExpression="TransactionDate" DataFormatString="{0:MM/dd/yyyy}" HtmlEncode="False" ReadOnly="True" /> <asp:BoundField DataField="CheckNumber" HeaderText="Check #" SortExpression="CheckNumber" ReadOnly="True"> <ItemStyle HorizontalAlign="Center" /> </asp:BoundField> <asp:BoundField DataField="BatchID" HeaderText="BatchID" SortExpression="BatchID" ReadOnly="True" /> <asp:BoundField DataField="PayeeID" HeaderText="PayeeID" SortExpression="PayeeID" ReadOnly="True" /> <asp:BoundField DataField="LoanID" HeaderText="LoanID" SortExpression="LoanID" ReadOnly="True" /> <asp:BoundField DataField="TransactionAmt" HeaderText="Transaction Amount" SortExpression="TransactionAmt" ReadOnly="True" DataFormatString="{0:f2}" HtmlEncode="False"> <ItemStyle HorizontalAlign="Right" /> </asp:BoundField> <asp:BoundField DataField="FeeDesc" HeaderText="Fee Description" SortExpression="FeeDesc" ReadOnly="True" /> <asp:BoundField DataField="HistoryCounter" HeaderText=" " SortExpression="HistoryCounter" ReadOnly="True"> <ItemStyle CssClass="hiddencol" /> </asp:BoundField> </Columns> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <EditRowStyle BackColor="#999999" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="

|||

<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="Update_CheckBox1" runat="server" />
</ItemTemplate>
<HeaderTemplate>
<input id="Update_CheckBox1" onclick="javascript:SelectAllCheckboxes(this);" runat="server"
type="checkbox" />
</HeaderTemplate>
</asp:TemplateField>
==========================================================

is actually correct below. I just tried changing the name to see if that was it.

==========================================================

<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="UpdateCheckBox1" runat="server" />
</ItemTemplate>
<HeaderTemplate>
<input id="UpdateCheckBox1" onclick="javascript:SelectAllCheckboxes(this);" runat="server"
type="checkbox" />
</HeaderTemplate>
</asp:TemplateField>
==========================================================

CMDEXEC Can't Find File, Yet Runs In a "Run" Dialog

Good morning, I hope someone here has had this problem and can tell me what
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me wha
t
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if th
e
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in t
he
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

CMDEXEC Can't Find File, Yet Runs In a "Run" Dialog

Good morning, I hope someone here has had this problem and can tell me what
I'm doing wrong.
I have created a CMDEXEC job to run a program. I know my path is correct
because it works in a Run dialog. Yet SQL Server Agent reports "Process
could not be created... the system cannot find the file specified" in the
error log.
The job step is currently set to the path and name of the exe. I.e.:
\\server\folder\app.exe
Agent reports "Success" for this step after just a second, but the
application runs for an hour or so when it's started manually. Even if the
application was failing and crashing out, which it does not do when run
manually, it should take it a few seconds. Is Agent starting the app in the
background, asynchronously?
Any suggestions appreciated!JonOfAllTrades wrote:
> Good morning, I hope someone here has had this problem and can tell me what
> I'm doing wrong.
> I have created a CMDEXEC job to run a program. I know my path is correct
> because it works in a Run dialog. Yet SQL Server Agent reports "Process
> could not be created... the system cannot find the file specified" in the
> error log.
> The job step is currently set to the path and name of the exe. I.e.:
> \\server\folder\app.exe
> Agent reports "Success" for this step after just a second, but the
> application runs for an hour or so when it's started manually. Even if the
> application was failing and crashing out, which it does not do when run
> manually, it should take it a few seconds. Is Agent starting the app in the
> background, asynchronously?
> Any suggestions appreciated!
It's a permissions problem. SQL Agent is running as a user (or maybe
Local System) that doesn't have permission to access
\\server\folder\app.exe. Fix that, and all should be well.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Clustering on SQL Standard Edition?

I guess it is not possible. We can't afford the cost and at the same time downtime, I want to know if it is possible to simulate clustering using SQL Standard edition with minimal downtime. E.g., if I am running Windows 2000 Advanced Server in a cluster a
nd install SQL Server 1 on box1 and point to the data on the disk array and keep it running. When SQL server 1 crashes or stops functioning, can I start SQL Server 2 (on a diff box) and attach the databases on the disk array will it function smoothly. I g
uess it is a stupid question (it doesn't makes sense to me either), but if you have some insights on this please post them.
If you can count the cost of downtime then, depending on these costs to business, you may be able to justify to management the cost of moving to W2K Advanced Server or W2K3 Enterprise Edition and SQL2K Enterprise Edition. Introducing manual processes to
simulate High Availaible is a mistake. But you already knew that ;-)
|||Clustering is an Enterprise Edition feature only.
However, what you outline for a failover scenario will work exactly as you
expect it will. As long as the second SQL Server can gain access to the
data files, the SQL Server will start up cleanly with full access to your
data. The only thing you would have to manage is that your applications
have to be repointed to the new server.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||I can completely understand the cost factor of implementing the cluster.
But the bigger question that needs to be asked is - does the downtime and
loss of productivity justify the savings or a workaround for NOT
implementing high availability clustering ? I guess, the mere fact that
your company is looking at clustering is that they value the high
availability aspect of running a database application and they value the
money lost in terms of lost productivity. From now onwards, it would be a
question of justifying the cost-benefit scenario to your BDMs.
If you try to manually point the database to the shared disk, its possible,
but why would you like to do that? There will be a lot of contingencies to
consider and plan for if we go the manual way.
I would sugges that you have a look at this link
MS SQL Server 2000 High Availability Series
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
This is written for both Business and Technical folks and could give you
possible pointers on how you can achieve the functionality by justifying
the costs.
Hope this helps.
Sanchan [MSFT]
sanchans@.online.microsoft.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Clustered Index on Non-Unique Column

I've done a little research but I'd like to get some opinion from the group.
I'll also preface this by saying that we can't change how it was done in the
first place, only investigate how to fix it if possible.
Let's say you've got a table that has 4,333,776 rows. Currently that table
has a PK that is functioning as a clustered index(it's the identity). You've
then got two more indexes that were placed on this table that are not
clustered.
The problem is that the clustered index is never used, and it appears that
if we moved the clustered to one of the other keys we may gain some
performance. The problem is that the column that is preferrable isn't unique
so SQL Server will add the "uniqueifer" to it. I've checked on the
selectivity ratio of the column and it's currently at .81. Not sure if
that's .81% or 81%. If it's the latter I wonder if it's close enough to the
sometimes suggested 95% unique.
Any thoughts? Any more details I can give to make this more helpful?
Is the clustered index unique? If not, why not? Also, what is the PK?
Have you got the complete build script for the table, including indexes and
constraints?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:036D72E7-F282-4316-AA6B-53F555B9DF19@.microsoft.com...
I've done a little research but I'd like to get some opinion from the group.
I'll also preface this by saying that we can't change how it was done in the
first place, only investigate how to fix it if possible.
Let's say you've got a table that has 4,333,776 rows. Currently that table
has a PK that is functioning as a clustered index(it's the identity).
You've
then got two more indexes that were placed on this table that are not
clustered.
The problem is that the clustered index is never used, and it appears that
if we moved the clustered to one of the other keys we may gain some
performance. The problem is that the column that is preferrable isn't unique
so SQL Server will add the "uniqueifer" to it. I've checked on the
selectivity ratio of the column and it's currently at .81. Not sure if
that's .81% or 81%. If it's the latter I wonder if it's close enough to the
sometimes suggested 95% unique.
Any thoughts? Any more details I can give to make this more helpful?
|||It does sound like the clustered index is being wasted on field that doesn't
usualy get searched.
Clustered indexes are great for range queries. Say you wanted to get a list
of records from 07/010/2007 to 07/31/2007, having a clueterd index on the
date field would be great.
Nonclustered indexes are better for queries that return one or a few records.
MG
"jason7655" wrote:

> I've done a little research but I'd like to get some opinion from the group.
> I'll also preface this by saying that we can't change how it was done in the
> first place, only investigate how to fix it if possible.
> Let's say you've got a table that has 4,333,776 rows. Currently that table
> has a PK that is functioning as a clustered index(it's the identity). You've
> then got two more indexes that were placed on this table that are not
> clustered.
> The problem is that the clustered index is never used, and it appears that
> if we moved the clustered to one of the other keys we may gain some
> performance. The problem is that the column that is preferrable isn't unique
> so SQL Server will add the "uniqueifer" to it. I've checked on the
> selectivity ratio of the column and it's currently at .81. Not sure if
> that's .81% or 81%. If it's the latter I wonder if it's close enough to the
> sometimes suggested 95% unique.
> Any thoughts? Any more details I can give to make this more helpful?
|||jason7655 wrote:
> I've done a little research but I'd like to get some opinion from the group.
> I'll also preface this by saying that we can't change how it was done in the
> first place, only investigate how to fix it if possible.
> Let's say you've got a table that has 4,333,776 rows. Currently that table
> has a PK that is functioning as a clustered index(it's the identity). You've
> then got two more indexes that were placed on this table that are not
> clustered.
> The problem is that the clustered index is never used, and it appears that
> if we moved the clustered to one of the other keys we may gain some
> performance. The problem is that the column that is preferrable isn't unique
> so SQL Server will add the "uniqueifer" to it. I've checked on the
> selectivity ratio of the column and it's currently at .81. Not sure if
> that's .81% or 81%. If it's the latter I wonder if it's close enough to the
> sometimes suggested 95% unique.
> Any thoughts? Any more details I can give to make this more helpful?
Why is it a problem that the clustered index is never used?
Anyway, if the clustered index is not unique, then a uniqueifier is only
added to "duplicate" rows. For those rows, the key requires more
storage. Depending on your table structure it will require anywhere
between 6 and 8 bytes more per row. This extra space might not be a
problem for you, but that is something for you to decide.
Another thing to remember is that the clustered index keys are also
stored in all nonclustered indexes. So a narrow clustered index key
(such as int) is preferred over a wide index key. If you choose a very
wide clustered index key, then you other nonclustered index will grow a
lot (and slow down just as much).
HTH,
Gert-Jan
|||PK = Primary Key
Let me try to explain a little better.
This table has 3 indexes.
Index 1
Primary key, clustered. 1,2,3,4,5,etc.
Index 2
non-clustered. Use 2nd most.
Index 3
non-clustered, but the main column used by most queries. Used the most.
From my limited reading of sites (including
http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
various other documents) it would seem that we could gain performance by
changing the Index 3 to clustered.
It's not unique and that raises another question. I would hope that the SQL
Server "uniquefier" that gets added would not change the makeup of the
current data, only create a new column.
Does that make things more clear? Is this a good idea?
"Gert-Jan Strik" wrote:

> jason7655 wrote:
> Why is it a problem that the clustered index is never used?
> Anyway, if the clustered index is not unique, then a uniqueifier is only
> added to "duplicate" rows. For those rows, the key requires more
> storage. Depending on your table structure it will require anywhere
> between 6 and 8 bytes more per row. This extra space might not be a
> problem for you, but that is something for you to decide.
> Another thing to remember is that the clustered index keys are also
> stored in all nonclustered indexes. So a narrow clustered index key
> (such as int) is preferred over a wide index key. If you choose a very
> wide clustered index key, then you other nonclustered index will grow a
> lot (and slow down just as much).
> HTH,
> Gert-Jan
>
|||I've also found the following snippet that has gotten me a little confused
and seems to go counter to other things I've read:
"Think of a clustered index as the foundation of the table. Keep it simple
and clean. The point is that if you add non-clustered indexes to the table,
they repeat the clustered index and add the indexed column(s). So ideally
your clustered index should be on a single column.
Ideally, a clustered index should be on a column where new rows have a
steadily increasing value. Otherwise the clustered index will get fragmented,
and so will the non-clustered indexes based on it.
In short, for a table that sees lots of inserts, use only the identity
column for the clustered index."
(http://sql-server-performance.com/Community/forums/p/9800/53961.aspx#53961)
I could use some clarification on this one.
"jason7655" wrote:
[vbcol=seagreen]
> PK = Primary Key
> Let me try to explain a little better.
> This table has 3 indexes.
> Index 1
> Primary key, clustered. 1,2,3,4,5,etc.
> Index 2
> non-clustered. Use 2nd most.
> Index 3
> non-clustered, but the main column used by most queries. Used the most.
> From my limited reading of sites (including
> http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
> http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
> various other documents) it would seem that we could gain performance by
> changing the Index 3 to clustered.
> It's not unique and that raises another question. I would hope that the SQL
> Server "uniquefier" that gets added would not change the makeup of the
> current data, only create a new column.
> Does that make things more clear? Is this a good idea?
> "Gert-Jan Strik" wrote:
|||Jason,
Finding rows by seeking the clustered index is less expensive than
seeking a nonclustered index followed by a bookmark lookup. However, for
exact match selects, the difference usually isn't that big, for example
WHERE my_id = 15.
Where the clustered index really comes in handy is when you select a
range that potentially returns a lot of rows (or causes a lot of rows to
be processed). For example WHERE order_date >= '20070701' AND order_date
< '20070801'. In that case, the column order_date would really benefit
from a clustered index.
Another situation is a table that is joined very often to the table that
it is referrencing, and the Primary Key contains the Foreign Key of the
referenced table. For example a "Order Details" table with a Primary Key
on (order_id, line_no) that references an Orders table on column
(order_id). In such a case, creating the clustered index on the Primary
Key/Foreign Key can be a good choice. Basically it is the same rule
here. When joining, there are potentially many rows that need to be
processed.
So changing your index 3 to be the clustered index could be a good
choice, but there is not enough information for me to decide. If you
join this table a lot on the columns of index 1, then join performance
could drop. You should analyse your workload, and decide which queries
are most important (with respect to performance). The, if possible, you
should test the different scenario's and see what works best for you.
BTW: if the clustered index is defined as not unique, the uniqueifier is
added under the covers. Its existance of columns values will never be
exposed when you select from the table.
As for your other post, see my opinion inline:

> I've also found the following snippet that has gotten me a little confused
> and seems to go counter to other things I've read:
> "Think of a clustered index as the foundation of the table. Keep it simple
> and clean. The point is that if you add non-clustered indexes to the table,
> they repeat the clustered index and add the indexed column(s).
Correct.

> So ideally your clustered index should be on a single column.
The point is not how many columns it concerns. The point is how many
bytes the key uses. From that point of view, two int columns are better
than one varchar(50) column with an average fill of 25 characters. Even
6 int columns are better than a varchar(50) column with average string
size of 25 characters.

> Ideally, a clustered index should be on a column where new rows have a
> steadily increasing value. Otherwise the clustered index will get fragmented,
True. So from that point of view, a clustered index on a GUID is not a
good idea, unless you defragment regularly.

> and so will the non-clustered indexes based on it.
I think this is false. Nonclustered indexes will fragment if new values
of the (nonclustered) indexed key are not "steadily increasing".
However, since nonclustered indexes are much smaller than the clustered
index (which includes the table data), the cost of fragmentation of a
nonclustered index is typically a lot smaller. Of course, regular
defragmenting is the solution for this too.
There is one exception. If your nonclustered index has a very low
selectivy (just a few distinct values), then the nonclustered index'
fragmentation will be very similar to the clustered index'
fragmentation.

> In short, for a table that sees lots of inserts, use only the identity
> column for the clustered index."
> (http://sql-server-performance.com/Community/forums/p/9800/53961.aspx#53961)
I definitely disagree with this statement as a general rule, especially
because of the compulsory tone.
For starters, if you don't use Identity as a (surrogate) key, then IMO
you should not add one.
Next, in many situations, the primary key's index is used a lot in
joins, but in other situations (such as filtering on "Lookup Tables")
the optimizer's access path is different and the primary key's index is
used very little.
And finally: it is very unusual to have range selects on an Identity
column. Such range selects are much more common for datetime columns.
So putting a clustered index on an Identity Primary Key could be a good
choice, but it really depends on your situation, and IMO you should not
have policy to always add an Identity and/or to always create the
clustered index on the Identity.
HTH,
Gert-Jan
jason7655 wrote:
> PK = Primary Key
> Let me try to explain a little better.
> This table has 3 indexes.
> Index 1
> Primary key, clustered. 1,2,3,4,5,etc.
> Index 2
> non-clustered. Use 2nd most.
> Index 3
> non-clustered, but the main column used by most queries. Used the most.
> From my limited reading of sites (including
> http://www.sql-server-performance.com/tips/clustered_indexes_p1.aspx,
> http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx, and
> various other documents) it would seem that we could gain performance by
> changing the Index 3 to clustered.
> It's not unique and that raises another question. I would hope that the SQL
> Server "uniquefier" that gets added would not change the makeup of the
> current data, only create a new column.
> Does that make things more clear? Is this a good idea?
>
[snip]