Patterns for table design

I am looking for something similar to patterns for table design, but I have been unable to find anything on that, or good rule of thumb performance guides for table design.

Most of the situations we face look like perfect candidates for patterns, and some good rules of thumb -- problems like scalable OLAP requirements PLUS real time reporting.

I am looking for several differnet approaches and a good summary of the trade-offs for each one.

Thanks!

You're in luck...the newest book in the Martin Fowler Signature Series is on Database Refactoring

http://martinfowler.com/books.html#refactoringDatabases

I haven't read it yet but looking at the website (that has a catalog of the patterns), it seems to be pretty thorough. It goes hand in hand with Martin's Patterns of Enterprise Application Architecture and the other book in the series on Enterprise Integration Patterns.

Patterns & Practices

I have looked through the Patterns & Practices information and cannot find
anything on naming conventions for SQL Server stored procedures, tables, and
user-defined functions. Where might this information be found?
Thanks,
MikeHere's one:
http://vyaskn.tripod.com/object_naming.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"memead" <memead@.discussions.microsoft.com> wrote in message
news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
>I have looked through the Patterns & Practices information and cannot find
> anything on naming conventions for SQL Server stored procedures, tables, and
> user-defined functions. Where might this information be found?
> Thanks,
> Mike|||Thank you so much for your timely response.
Mike
"Tibor Karaszi" wrote:
> Here's one:
> http://vyaskn.tripod.com/object_naming.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "memead" <memead@.discussions.microsoft.com> wrote in message
> news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
> >I have looked through the Patterns & Practices information and cannot find
> > anything on naming conventions for SQL Server stored procedures, tables, and
> > user-defined functions. Where might this information be found?
> >
> > Thanks,
> >
> > Mike
>|||Vyas has some GREAT scripts here too: http://vyaskn.tripod.com/code.htm
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:BF3CF558-1162-4057-ABD8-841A9CF541EB@.microsoft.com...
> Here's one:
> http://vyaskn.tripod.com/object_naming.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "memead" <memead@.discussions.microsoft.com> wrote in message
> news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
>>I have looked through the Patterns & Practices information and cannot find
>> anything on naming conventions for SQL Server stored procedures, tables,
>> and
>> user-defined functions. Where might this information be found?
>> Thanks,
>> Mike
>|||Thank you for the information.
"TheSQLGuru" wrote:
> Vyas has some GREAT scripts here too: http://vyaskn.tripod.com/code.htm
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:BF3CF558-1162-4057-ABD8-841A9CF541EB@.microsoft.com...
> > Here's one:
> >
> > http://vyaskn.tripod.com/object_naming.htm
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "memead" <memead@.discussions.microsoft.com> wrote in message
> > news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
> >>I have looked through the Patterns & Practices information and cannot find
> >> anything on naming conventions for SQL Server stored procedures, tables,
> >> and
> >> user-defined functions. Where might this information be found?
> >>
> >> Thanks,
> >>
> >> Mike
> >
>
>sql

Pattern matching with multiple values

I want to check for multiple patterns in a particular column.
For one pattern I can write e.g.
SELECT * FROM <TablName> WHERE ColumnName LIKE '%abcd%'.

My requirment is to select all rows for which column value matches with many patterns.I will fetch the patterns in a subquery
e.g. (SELECT '%'+name+'%' FROM <TableName>)

Any thoughts?

Hi
It might work if you move your like into Exists , like

SELECT * FROM <TableName> T1
WHERE EXISTS (SELECT * FROM T2
WHERE T1.column LIKE '%' + T2.NAME + '%'
)

NB.|||

Thanks for your solution !

|||

Thanks for your solution !

However my problem won't get solved this way. Actually I am building anSSRS report and the subquery was meant to be the 'Available Values ' of a multi-valued Input parameter.
When the user selects one or more params SRS will form a string of the values like " 'val1','val2','val3'...."
In my case SRS is forming a WHERE clause like
.........
.........
WHERE colname in ('val1','val2','val3'). This would search for the exact string, whereas I would like it to only match with patterns like '%val1%','%val2%','%val3%'.

|||

i think you need to build it as

WHERE colname like '%val1%' or colname like '%val2%' or colname like '%val3%'.

|||

Thnx. Shallu.....That is correct syntax, but you need to see that the number of values is not static and all of them are stored as one parameter by ssrs.

To simplify it for you, say SSRS is providing me with a string series like " '%val2%', '%val2%' ,'%val2%' ...". Now I need to use this to do my comparison.

|||Hi

There might be a better solution from SSRS comunity , you should try your question there as well. From T_SQL point of view you could try following solution:
Create 2 store procedures : one - master that recived your string of parameters , parses a string into a list of single parameters and collects whatever children return , the other one - child , that can process one parameter at a time

CREATE PROCEDURE MultipleParameterSearch_procedure
@.intString varchar ()

as
CREATE TABLE #reultset (<your columns>)

DECLARE @.PARAMETER
-- parse your string into separate parameters , one at a time
...
...

WHILE @.PARAMETER is not nul
BEGIN
-- accumulate your single parameter procedure results
INSERT INTO #reultset (<your columns>)
EXEC SingleParameterSeach_Procedure @.PARAMETER
-- get next parameter
END

SELECT * FROM #reultset
RETURN
GO

CREATE PROCEDURE SingleParameterSeach_Procedure @.Parameter VARCHAR()
AS
SELECT <your columns>FROM <Table> WHERE Column LIKE '%' + @.Parameter + '%'
RETURN
GO|||Hi
Just one last suggestion.
LIKE %val%' is a very expensive operation, as no indexes can be used to help QueryOptimiser to make a quick search. Adding multiple LIKE parameters are going to decrease performance of your query. If you are doing your Report for production , explain to the user implications and pesweid them to use a single-parameter select .

:-).NB|||Thanks for taking the pains .I'll follow this up |||

Please take a look at the link below:

http://www.sommarskog.se/arrays-in-sql.html

It discusses various techniques to process lists using SQL. You can use one of those methods to generate a table that contains the individual values and then use it as source in the EXISTS sub-query.