Dynamic SQL Command: key

Showing posts with label key. Show all posts

Thursday, March 29, 2012

Easy way to index a column with URLs

We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?If you need to enforce uniqueness, or you are frequently searching the table
by the URL column, then an index is appropriate. Bear in mind that an index
key can be no more than 900 bytes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Kevin" <none@.none.com> wrote in message
news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
We have a column that stores URL (varchar), which ultimately are unique
within the table (though an identity is the actual primary key). Is it good
practice to put an index on such a wide column? Are there are other
alternatives, possibly different data type to use?|||I'm afraid that some of the urls possibly could get over that size after
encoding (the URL column is nvarchar).
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
> If you need to enforce uniqueness, or you are frequently searching the
> table
> by the URL column, then an index is appropriate. Bear in mind that an
> index
> key can be no more than 900 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Kevin" <none@.none.com> wrote in message
> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
> We have a column that stores URL (varchar), which ultimately are unique
> within the table (though an identity is the actual primary key). Is it
> good
> practice to put an index on such a wide column? Are there are other
> alternatives, possibly different data type to use?
>|||"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>> If you need to enforce uniqueness, or you are frequently searching the
>> table
>> by the URL column, then an index is appropriate. Bear in mind that an
>> index
>> key can be no more than 900 bytes.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Kevin" <none@.none.com> wrote in message
>> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
>> We have a column that stores URL (varchar), which ultimately are unique
>> within the table (though an identity is the actual primary key). Is it
>> good
>> practice to put an index on such a wide column? Are there are other
>> alternatives, possibly different data type to use?
>|||Kevin,
if you badly want this column be indexed (of those values that do not excede
the 900 bites limit), you can do some more work:
create an indexed view that have a pk column and the URL column, with the
URL records no longer than 900 bites, index on the URL column;
create an indexed view that have a pk column and the URL column, with the
URL records longer than 900 bites, index on the pk column;
chances are that you may have a handful of records in the second view so
that you do not care to index scan it. And you have index seek on the
shorter URL values. Then join the views with your base table.
HTH
Quentin
"Kevin" <none@.none.com> wrote in message
news:0DCDCCBA-8397-46D2-A860-8432E352277B@.microsoft.com...
> I'm afraid that some of the urls possibly could get over that size after
> encoding (the URL column is nvarchar).
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:O0MdobtnHHA.1476@.TK2MSFTNGP03.phx.gbl...
>> If you need to enforce uniqueness, or you are frequently searching the
>> table
>> by the URL column, then an index is appropriate. Bear in mind that an
>> index
>> key can be no more than 900 bytes.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "Kevin" <none@.none.com> wrote in message
>> news:4671898C-0CAC-4396-85F1-BA45E527D8E5@.microsoft.com...
>> We have a column that stores URL (varchar), which ultimately are unique
>> within the table (though an identity is the actual primary key). Is it
>> good
>> practice to put an index on such a wide column? Are there are other
>> alternatives, possibly different data type to use?
>sql

Easy way to index a column with URLs

Thursday, March 22, 2012

Easiest way to get a value in af file into a variable ?

I get a file with some key information delivered to an ftp destination each day along with some files containing rawdata.

The file is a csv file containing some short description of what is being delivered.

Numrows;pulltime;sourceinfo

25302524;25-01-2006;dssrv34

So the file has columndescription and 1 row with some information.

My question is, what is the easiest way to get those 3 informations into 3 variables ?

How about a Flat File Adapter into a script component in which you can load the values into the variables.

-Jamie

|||

Or you could do this: http://blogs.conchango.com/jamiethomson/archive/2005/06/15/1693.aspx

(I forgot I'd done this before)

-Jamie

|||Personally, I would create a script task that takes in a filespec variable and writes to three variables. Like so.

Dim strFileSpec as String = cstr(Dts.Variables("MyFileSpec").Value)

Dim sr As System.IO.StreamReader

Dim strVals() As String

' Check if file exists

If System.IO.File.Exists(strFileSpec) Then

' Open the stream reader

sr = New System.IO.StreamReader(strFileSpec)

' Check if not at end of stream

' Split the first line at the semicolons and put in string array

If Not sr.EndOfStream Then

strVals = sr.ReadLine.Split(";")

End If

' Close the stream reader

sr.Close

' Check if there are three variables,

' If so right to output variables

If strVals.GetLength = 3 Then

Dts.Variables("MyVar1").Value = strVals(0)

Dts.Variables("MyVar2").Value = strVals(1)

Dts.Variables("MyVar3").Value = strVals(2)

End If

Larry Pope

Sunday, March 11, 2012

Dynamically Drop FK Restraints?

I was wondering if anyone had anything in their bag of tricks that
would detect any foreign key contraints on a table and break them. I
am constantly dropping/adding tables as part of a MSSQL 2000 utlity
function and I keep hitting an error if I drop a parent table that has
FKs.
Thanks.See if this helps you get on the right track:
http://sqljunkies.com/WebLog/amacha...onstraints.aspx
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"xenophon" <xenophon@.online.nospam> wrote in message
news:ulrn51pb732k09237hfk9u8iri2h7vl9pq@.
4ax.com...
> I was wondering if anyone had anything in their bag of tricks that
> would detect any foreign key contraints on a table and break them. I
> am constantly dropping/adding tables as part of a MSSQL 2000 utlity
> function and I keep hitting an error if I drop a parent table that has
> FKs.
> Thanks.
>

Wednesday, March 7, 2012

Dynamic WHERE operator based on user input

Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements:

DropDownList1: This drop down list contains 3 choices- "=", ">", and "<".

Age: This text box is where someone would enter a number.

Button1: This is the form's submit button.

I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.

The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error:

"The variable name'@.Age' has already been declared. Variable names must be unique within a query batch or stored procedure."

Any help would be appreciated.

Here is what I'm using in my code behind:

protected void Button1_Click(object sender, EventArgs e)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT * FROM People WHERE Age ");
switch (DropDownList1.SelectedValue)
{
case "=":
sb.Append("= ");
break;
case ">":
sb.Append("> ");
break;
case "<":
sb.Append("< ");
break;
}
sb.Append("@.Age");
SqlDataSource1.SelectCommand = sb.ToString();
SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text);
}

i would create 3 stored procedures first with "<" second with "=" and third with ">"

then, in code behind

if (DropDownList1.SelectedValue.ToString() == "<") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("LessThan_toate", conn);//cnn = your conn string command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader;// your dataReader DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }if (DropDownList1.SelectedValue.ToString() =="=") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("EqualsWith", conn); command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader; DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }if (DropDownList1.SelectedValue.ToString() ==">") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("GreatherThen", conn); command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader; DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }

sure, my stored procedures doesn't have input parameters but you can change easly that

|||

adammckee:
I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.

So basically you want everything in the table? what are you excluding?

|||

If you have the Age value when you click the Button, why don't you just add the value directly into your select statement instead of worrying about a parameter.

protected void Button1_Click(object sender, EventArgs e){SqlDataSource1.SelectCommand = String.Format("SELECT * FROM People WHERE Age {0} {1}",DropDownList1.SelectedValue,Age.Text);}

|||Either I don't understand what you're doing or you're making it more complicated than it should be. Wouldn't this work?

Private Sub cmd_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cmd.ClickDim sQueryAs String sQuery ="SELECT * FROM People WHERE Age " &Me.DropDownList1.SelectedValue &" " &Me.txtAge.Text SqlDataSource1.SelectCommand = sQueryEnd Sub

|||

This worked nicely, but doesn't this leave me open to SQL injection? I was attempting to do this with the SelectParameters method to avoid that.

To clarify what I'm trying to do for the other person that asked, I want to give the user the ability to search for people in the database based on their age. If the user wants to search for anyone that's over 50, they pull down the drop down list, select the ">" operator then type "50" in the text field and perform the search.

ecbruck:
If you have the Age value when you click the Button, why don't you just add the value directly into your select statement instead of worrying about a parameter.
protected void Button1_Click(object sender, EventArgs e){SqlDataSource1.SelectCommand = String.Format("SELECT * FROM People WHERE Age {0} {1}",DropDownList1.SelectedValue,Age.Text);}

|||

If you're worried about it, then go ahead and use a Parameter instead. However, I would at least recommend declaratively placing the Parameter within your SqlDataSource on your ASPX page and then simply setting the DeefaultValue of the property within your Click event handler or the SqlDataSource.Selecting event handler.

|||

For anyone interested, this is what works for me. Thanks again for the advice.

1protected void Button1_Click(object sender, EventArgs e)2 {3 System.Text.StringBuilder sb =new System.Text.StringBuilder();4 sb.Append("SELECT * FROM People WHERE Age ");5switch (DropDownList1.SelectedValue)6 {7case"=":8 sb.Append("= ");9break;10case">":11 sb.Append("> ");12break;13case"<":14 sb.Append("< ");15break;16 }17 sb.Append("@.Age");18 SqlDataSource1.SelectCommand = sb.ToString();19if (SqlDataSource1.SelectParameters["Age"] ==null)20 SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, TextBox1.Text);21else22 SqlDataSource1.SelectParameters["Age"].DefaultValue = TextBox1.Text;23 }

Thursday, March 29, 2012

Easy way to index a column with URLs

Easy way to index a column with URLs

Easy way to index a column with URLs

Thursday, March 22, 2012

Easiest way to get a value in af file into a variable ?

Sunday, March 11, 2012

Dynamically Drop FK Restraints?

Wednesday, March 7, 2012

Dynamic WHERE operator based on user input

Dynamic SQL Command

Blog Archive

About Me