Saving Pictures Inside the DB?

Hi,
I have a database that stores a lot of searchable data (I mean INT and
VARCHAR and ...) but also huge number of pictures are added regularly. My
estimation is that after a few months (when all clients become active), at
least 4GB of pictures will be inserted daily. So after 3 years the database
reaches to 4TB!
I'd like to know that whether this is a right way that we are going or not?!
I mean what will happen to size of our backups?
Some people suggest to store pictures outside and keep the path in rows.
I would be grateful if someone could evaluate these two methods.
Thanks in advance,
LeilaGOOGLE 'storing files database'.
Here's are 2 sites that discuss both options:
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=414
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>|||When SQL Server 2008 is released it will offer a better alternative using
the FILESTREAM data type:
http://blogs.msdn.com/manisblog/archive/2007/10/21/filestream-data-type-sql-server-2008.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||You may also want to check out this paper;
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45
Linchi
"Leila" wrote:
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the database
> reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or not?!
> I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>
>|||Leila we use the database only to store the Thumbs and show them in the grid
when the client do a search, ours thumbs are under 2kb and our database
start becaming very big 17millions of records so far, another issue will be
your transacion log, think about people updating or deleting the images,
that will create a HUGE transaction log file but you can solve that using
"writetext".
Remember this: if you are going to show only 1 image at the time you can
save the image path on the row and always load the image and show in the
client.
Last thing to consider is your network traffic, you don't want huge images
going forward and backward in your network
hope this will help you
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>|||I appreciate everybody!
We won't have too much of manipulations over pictures. What if we store
pictures in another database within the same SQL Server instance? This way
we can have: transactional consistency, replication, smaller size for
backing up the main database.
Any ideas?
Thanks again.
"Jorge" <nospam@.austin.rr.com> wrote in message
news:480638d2$0$31736$4c368faf@.roadrunner.com...
> Leila we use the database only to store the Thumbs and show them in the
> grid when the client do a search, ours thumbs are under 2kb and our
> database start becaming very big 17millions of records so far, another
> issue will be your transacion log, think about people updating or deleting
> the images, that will create a HUGE transaction log file but you can solve
> that using "writetext".
> Remember this: if you are going to show only 1 image at the time you can
> save the image path on the row and always load the image and show in the
> client.
> Last thing to consider is your network traffic, you don't want huge images
> going forward and backward in your network
> hope this will help you
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a database that stores a lot of searchable data (I mean INT and
>> VARCHAR and ...) but also huge number of pictures are added regularly. My
>> estimation is that after a few months (when all clients become active),
>> at least 4GB of pictures will be inserted daily. So after 3 years the
>> database reaches to 4TB!
>> I'd like to know that whether this is a right way that we are going or
>> not?! I mean what will happen to size of our backups?
>> Some people suggest to store pictures outside and keep the path in rows.
>> I would be grateful if someone could evaluate these two methods.
>> Thanks in advance,
>> Leila
>|||Just curious, are you EVER going to fix your system clock? I have asked you
multiple times to correct this, and the fact that you are ignoring my
requests demonstrates that you are doing it intentionally. This is very
inconsiderate.
On 4/16/08 8:19 AM, in article uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl,
"Leila" <Leilas@.hotpop.com> wrote:
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the database
> reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or not?!
> I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>|||I also have a problem with the system date\time. It just does not remember
the date\time after a short while. I guess it's because of the BIOS battery
(even if this laptop is just 2,5 years old?) of my laptop. However I keep it
up to date using Clock Synchronization of Windows.
Maybe Leila also has a problem like this and does not know how to work
around it.
--
Ekrem Önsoy
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:C42D4D61.DFC%ten.xoc@.dnartreb.noraa...
> Just curious, are you EVER going to fix your system clock? I have asked
> you
> multiple times to correct this, and the fact that you are ignoring my
> requests demonstrates that you are doing it intentionally. This is very
> inconsiderate.
>
>
> On 4/16/08 8:19 AM, in article uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl,
> "Leila" <Leilas@.hotpop.com> wrote:
>> Hi,
>> I have a database that stores a lot of searchable data (I mean INT and
>> VARCHAR and ...) but also huge number of pictures are added regularly. My
>> estimation is that after a few months (when all clients become active),
>> at
>> least 4GB of pictures will be inserted daily. So after 3 years the
>> database
>> reaches to 4TB!
>> I'd like to know that whether this is a right way that we are going or
>> not?!
>> I mean what will happen to size of our backups?
>> Some people suggest to store pictures outside and keep the path in rows.
>> I would be grateful if someone could evaluate these two methods.
>> Thanks in advance,
>> Leila
>>
>|||If dangling relationships concerns you:
http://sql-server-performance.com/Community/forums/p/26369/144268.aspx#144268
Be sure to put images in a separate filegroup though.
/k
"Leila" <Leilas@.hotpop.com> wrote in message
news:uwrAXUwnIHA.5944@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a database that stores a lot of searchable data (I mean INT and
> VARCHAR and ...) but also huge number of pictures are added regularly. My
> estimation is that after a few months (when all clients become active), at
> least 4GB of pictures will be inserted daily. So after 3 years the
> database reaches to 4TB!
> I'd like to know that whether this is a right way that we are going or
> not?! I mean what will happen to size of our backups?
> Some people suggest to store pictures outside and keep the path in rows.
> I would be grateful if someone could evaluate these two methods.
> Thanks in advance,
> Leila
>|||> I also have a problem with the system date\time. It just does not remember
> the date\time after a short while. I guess it's because of the BIOS
> battery (even if this laptop is just 2,5 years old?) of my laptop. However
> I keep it up to date using Clock Synchronization of Windows.
> Maybe Leila also has a problem like this and does not know how to work
> around it.
No, Leila has been told about this multiple times, and her system clock is
always magically 24 hours ahead. If it were a BIOS battery issue, the clock
should lose time, so she should be posting in the past in that case, not the
future. Future posting is a slimy way to keep your post at the top of the
message list when you sort by sent descending, since the server obeys the
date/time in the headers of your message, as opposed to the date/time they
actually received the post.|||Hi Aaron,
Thank you for mentioning this issue. Actually I had checked this multiple
times before and my clock was fixed. It was a long time that I didn't come
to this newsgroup and I wasn't aware that this problem has turned up again.
I have the same problem with my Pocket PC, sometimes my reminder displays my
tasks of tomorrow and I realize that my PPC's clock is wrong. Probably this
happens when I sync my PPC with notebook.
I guess some software which is resident in the memory is doing this due to
some wrong setting, but really have no idea what can this be.
I apologize again!
Leila
"Aaron Bertrand [SQL Server]" <ten.xoc@.dnartreb.noraa> wrote in message
news:7971D90C-DBB1-43FB-92AA-95B8959A9B04@.microsoft.com...
>> I also have a problem with the system date\time. It just does not
>> remember the date\time after a short while. I guess it's because of the
>> BIOS battery (even if this laptop is just 2,5 years old?) of my laptop.
>> However I keep it up to date using Clock Synchronization of Windows.
>> Maybe Leila also has a problem like this and does not know how to work
>> around it.
> No, Leila has been told about this multiple times, and her system clock is
> always magically 24 hours ahead. If it were a BIOS battery issue, the
> clock should lose time, so she should be posting in the past in that case,
> not the future. Future posting is a slimy way to keep your post at the
> top of the message list when you sort by sent descending, since the server
> obeys the date/time in the headers of your message, as opposed to the
> date/time they actually received the post.

Saving large text file in table

Have created an SQL Server 2000 table which includes two columns each data type varchar(8000) in order to accomodate a semi-large amount of text (want to avoid using text data type). I enter the max in the first column but I keep getting an error of "Cannot create a row of size 12066 which is greater than the allowable maximum of 8060" when I try to save any more in the second column of the table. What am I doing wrong?

SQL Server stores rows (and indexes) in pages. Pages are 8K in size. There is some required header information (i.e. next block in chain, etc.), which drops the usable space for rows to 8060. So a single row cannot exceed 8060 bytes and rows cannot span multiple pages. You have two choices:

Split the table into two tables:
Table1
Id int (PK)
Text1 varchar(8000)

Table2
Id int (PK and FK to Table1)
Text2 varchar(8000)

Use the text data type:
The text data type stores the data in separate data pages which can span multiple pages. In the row, you simply store a pointer to the first data page. You can optimize the table so that the text type is stored inline with the row so long as it doesn't exceed a certain size.

|||Only had a couple of pages that were over the 8060 limit and using two tables works perfect. Thanks for the advice.|||

Glad I could help. You can always throw an updatable view overtop of the two tables so that they appear as one table to queries. Just write a view that INNER JOINs the two tables and then write INSTEAD OF INSERT, UPDATE, and DELETE triggers.

Saving EXEC Result to a Variable ?

How can I save the EXEC Result to a Variable.
For example:
declare @.myString as varchar(50)
declare @.myValue as decimal(12,2)
set @.myString='Select ' + '10-5'
EXEC (@.myString)
Print @.myalue <-- Should print 5you can use sp_ExecuteSql. Check BOL for more info on this. or google it.
--
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:uP38IDcDFHA.3340@.TK2MSFTNGP10.phx.gbl...
> How can I save the EXEC Result to a Variable.
> For example:
> declare @.myString as varchar(50)
> declare @.myValue as decimal(12,2)
> set @.myString='Select ' + '10-5'
> EXEC (@.myString)
> Print @.myalue <-- Should print 5
>
>
>|||You can also do this using either temp tables or a user-defined function:
CREATE FUNCTION udf_test( @.value1 DECIMAL( 12, 2 ), @.value2 DECIMAL( 12, 2 )
)
RETURNS DECIMAL( 12, 2 )
AS
BEGIN
DECLARE @.result DECIMAL( 12, 2 )
SET @.result = @.value1 - @.value2
RETURN @.result
END
GO
DECLARE @.myString VARCHAR(50)
DECLARE @.myValue DECIMAL( 12, 2 )
-- Temp table way
CREATE TABLE #result ( result DECIMAL( 12, 2 ) )
SET @.myString = 'INSERT INTO #result SELECT ' + '10-5'
EXEC( @.myString )
SELECT * FROM #result
DROP TABLE #result
-- User defined function way (Make sure you set the database owner dbo to
whatever you need)
SET @.myValue = dbo.udf_test( 10, 5 )
PRINT @.myValue
DROP FUNCTION udf_test
GO
"Luqman" wrote:

> How can I save the EXEC Result to a Variable.
> For example:
> declare @.myString as varchar(50)
> declare @.myValue as decimal(12,2)
> set @.myString='Select ' + '10-5'
> EXEC (@.myString)
> Print @.myalue <-- Should print 5
>
>
>
>sql

Saving Data into SQLServer

Hi,

Here is a structure of my table

create table events
(
event_id integer primary key identity(1,1),
event_name varchar(200),
event_date datetime,
)

Note: The event_id is a automacally and we don't need to insert record in this field.

for example

i have inserted 10 records in a table

then i deleted all records manually from sql server

but when again I save the record the event_id field shows started from 11 even the table was empty,

although it should be start from 1?

11 was saved on the table for the next Id to be used.

|||

So how I manage this records becuase I want to start from 1 if I have deleted from database.

|||

That is proper. The only way to start back at 1 would be to reindex your DB. The Identity prperty will always give a "unique" number that hasnt been assigned before.

Hope this helps

Tim

|||

Well, Tim Could you please explain me that how I can reindex automatically this table I am using asp.net

|||

Actually now that i think about it, an even easy solution would be to just drop and recreate the table after you have deleted the records:

Just right click on the table on SQL server Object explorer and go to Script Table as --> Create to --> New Query Editor Window. This will give you the script to create the table exactly as you haven ow if you dont already ahve it then simply in a script do the following:

DROP TABLE 'TableName'

Paste CREATE TABLE script here

GO

May I ask why you need th eindex to start over after you delete records? Are you using the ID's somewhere else?

Tim

|||

Actually I am doing a Project thats why I am asking anyway I solved my Problem Thanks a lot

Cheers!

save boolean values in a sql2000 table

I try to insert VB.NET boolean true values into a SQL2000 table thru a stored procedure. While varchar fields of the table are correctly filled, I'm surprised to find the bit fields all stay 0 after every single insert. This makes me wonder if VB.NET boolean variables can be passed to sp bit parameters and inserted into a SQL2000 table. Or did I miss something? Please advise. Thanks.Oop! I found a bug in the GUI that captures the input. It's fixed and now works fine. Thanks.

SARG

Is the below Where clause is sargable?
Is using the function ISNULL makes it non-sargable?
Create proc SearcCustomer
@.FirstName varchar(30) = null,
@.LastName varchar(30) = null,
@.Address varchar(30) = null
as
SELECT * from customers
WHERE
((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
AND
((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
AND
((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
I have non-clustured index on all the columns. Please advise if I can
write this in a better way. The Input can be any of the three
parameters or multiple.
Thanks
ShijuHi
"shiju" wrote:
> Is the below Where clause is sargable?
No
> Is using the function ISNULL makes it non-sargable?
Yes columns involved in an expression are not SARGable and also the '%' +
@.variable + '%' isn't (BTW you code does not parse because of +'@.address!! )
> Create proc SearcCustomer
> @.FirstName varchar(30) = null,
> @.LastName varchar(30) = null,
> @.Address varchar(30) = null
> as
> SELECT * from customers
> WHERE
> ((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
> AND
> ((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
> AND
> ((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
If you SET SHOWPLAN_TEXT ON and then execute the procedure you will know if
it is using any indexes, this procedure will do a clustered index scan or a
table scan if there is no clustered index
Ken Handerson's book The Gurus Guide to Transact-SQL ISBN 0201615762 has a
good section on SARGs
Also look at
http://www.sommarskog.se/dyn-search.html
> Thanks
> Shiju
>
John|||> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
To add to John's response, applying a function to a column will render that
expression non-sargable. However, the ISNULL function doesn't matter here
because a variable is used. The real culprit is the LIKE expression.
If you eliminate the leading '%' from the LIKE expression, the indexes can
be used more efficiently. Also, I would use IS NULL instead of ISNULL here
for readability. For example:
WHERE
(@.FirstName IS NULL OR FirstName LIKE '@.FirstName+'%')
AND
(@.LastName IS NULL LastName LIKE '@.LastName+'%')
AND
(@.Address IS NULL OR Address like '@.Address'+'%')
You might also read Erland's dynamic search condition article
(http://www.sommarskog.se/dyn-search.html).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1177835585.469292.300480@.l77g2000hsb.googlegroups.com...
> Is the below Where clause is sargable?
> Is using the function ISNULL makes it non-sargable?
> Create proc SearcCustomer
> @.FirstName varchar(30) = null,
> @.LastName varchar(30) = null,
> @.Address varchar(30) = null
> as
> SELECT * from customers
> WHERE
> ((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
> AND
> ((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
> AND
> ((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
> Thanks
> Shiju
>

SARG

Is the below Where clause is sargable?
Is using the function ISNULL makes it non-sargable?
Create proc SearcCustomer
@.FirstName varchar(30) = null,
@.LastName varchar(30) = null,
@.Address varchar(30) = null
as
SELECT * from customers
WHERE
((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
AND
((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
AND
((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
I have non-clustured index on all the columns. Please advise if I can
write this in a better way. The Input can be any of the three
parameters or multiple.
Thanks
ShijuHi
"shiju" wrote:

> Is the below Where clause is sargable?
No

> Is using the function ISNULL makes it non-sargable?
Yes columns involved in an expression are not SARGable and also the '%' +
@.variable + '%' isn't (BTW you code does not parse because of +'@.address!! )

> Create proc SearcCustomer
> @.FirstName varchar(30) = null,
> @.LastName varchar(30) = null,
> @.Address varchar(30) = null
> as
> SELECT * from customers
> WHERE
> ((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
> AND
> ((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
> AND
> ((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
If you SET SHOWPLAN_TEXT ON and then execute the procedure you will know if
it is using any indexes, this procedure will do a clustered index scan or a
table scan if there is no clustered index
Ken Handerson's book The Gurus Guide to Transact-SQL ISBN 0201615762 has a
good section on SARGs
Also look at
http://www.sommarskog.se/dyn-search.html

> Thanks
> Shiju
>
John|||> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
To add to John's response, applying a function to a column will render that
expression non-sargable. However, the ISNULL function doesn't matter here
because a variable is used. The real culprit is the LIKE expression.
If you eliminate the leading '%' from the LIKE expression, the indexes can
be used more efficiently. Also, I would use IS NULL instead of ISNULL here
for readability. For example:
WHERE
(@.FirstName IS NULL OR FirstName LIKE '@.FirstName+'%')
AND
(@.LastName IS NULL LastName LIKE '@.LastName+'%')
AND
(@.Address IS NULL OR Address like '@.Address'+'%')
You might also read Erland's dynamic search condition article
(http://www.sommarskog.se/dyn-search.html).
Hope this helps.
Dan Guzman
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1177835585.469292.300480@.l77g2000hsb.googlegroups.com...
> Is the below Where clause is sargable?
> Is using the function ISNULL makes it non-sargable?
> Create proc SearcCustomer
> @.FirstName varchar(30) = null,
> @.LastName varchar(30) = null,
> @.Address varchar(30) = null
> as
> SELECT * from customers
> WHERE
> ((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
> AND
> ((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
> AND
> ((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
> Thanks
> Shiju
>

SARG

Is the below Where clause is sargable?
Is using the function ISNULL makes it non-sargable?
Create proc SearcCustomer
@.FirstName varchar(30) = null,
@.LastName varchar(30) = null,
@.Address varchar(30) = null
as
SELECT * from customers
WHERE
((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
AND
((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
AND
((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
I have non-clustured index on all the columns. Please advise if I can
write this in a better way. The Input can be any of the three
parameters or multiple.
Thanks
Shiju
Hi
"shiju" wrote:

> Is the below Where clause is sargable?
No

> Is using the function ISNULL makes it non-sargable?
Yes columns involved in an expression are not SARGable and also the '%' +
@.variable + '%' isn't (BTW you code does not parse because of +'@.address!! )

> Create proc SearcCustomer
> @.FirstName varchar(30) = null,
> @.LastName varchar(30) = null,
> @.Address varchar(30) = null
> as
> SELECT * from customers
> WHERE
> ((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
> AND
> ((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
> AND
> ((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
If you SET SHOWPLAN_TEXT ON and then execute the procedure you will know if
it is using any indexes, this procedure will do a clustered index scan or a
table scan if there is no clustered index
Ken Handerson's book The Gurus Guide to Transact-SQL ISBN 0201615762 has a
good section on SARGs
Also look at
http://www.sommarskog.se/dyn-search.html

> Thanks
> Shiju
>
John
|||> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
To add to John's response, applying a function to a column will render that
expression non-sargable. However, the ISNULL function doesn't matter here
because a variable is used. The real culprit is the LIKE expression.
If you eliminate the leading '%' from the LIKE expression, the indexes can
be used more efficiently. Also, I would use IS NULL instead of ISNULL here
for readability. For example:
WHERE
(@.FirstName IS NULL OR FirstName LIKE '@.FirstName+'%')
AND
(@.LastName IS NULL LastName LIKE '@.LastName+'%')
AND
(@.Address IS NULL OR Address like '@.Address'+'%')
You might also read Erland's dynamic search condition article
(http://www.sommarskog.se/dyn-search.html).
Hope this helps.
Dan Guzman
SQL Server MVP
"shiju" <shiju.samuel@.gmail.com> wrote in message
news:1177835585.469292.300480@.l77g2000hsb.googlegr oups.com...
> Is the below Where clause is sargable?
> Is using the function ISNULL makes it non-sargable?
> Create proc SearcCustomer
> @.FirstName varchar(30) = null,
> @.LastName varchar(30) = null,
> @.Address varchar(30) = null
> as
> SELECT * from customers
> WHERE
> ((ISNULL(@.FirstName,'')='' or FirstName like '%'+@.FirstName+'%')
> AND
> ((ISNULL(@.LastName,'')='' or LastName like '%'+@.LastName+'%')
> AND
> ((ISNULL(@.Address,'')='' or Address like '%+'@.Address'+%')
> I have non-clustured index on all the columns. Please advise if I can
> write this in a better way. The Input can be any of the three
> parameters or multiple.
> Thanks
> Shiju
>