Click here to Skip to main content
15,851,242 members
Home / Discussions / Database
   

Database

 
QuestionMerge Replication rights Pin
Sgg24528-Jun-07 21:15
Sgg24528-Jun-07 21:15 
QuestionCritical probs to display records..., Pin
Member 387988128-Jun-07 19:02
Member 387988128-Jun-07 19:02 
AnswerRe: Critical probs to display records..., Pin
Devkranth Kishore Vanja28-Jun-07 21:24
Devkranth Kishore Vanja28-Jun-07 21:24 
GeneralRe: Critical probs to display records..., Pin
Member 387988128-Jun-07 21:57
Member 387988128-Jun-07 21:57 
AnswerRe: Critical probs to display records..., Pin
Navneet Hegde29-Jun-07 4:34
Navneet Hegde29-Jun-07 4:34 
QuestionParamaterized Queries vs. Stored Procedures Pin
martin_hughes28-Jun-07 9:17
martin_hughes28-Jun-07 9:17 
AnswerRe: Paramaterized Queries vs. Stored Procedures Pin
kubben28-Jun-07 10:09
kubben28-Jun-07 10:09 
AnswerRe: Paramaterized Queries vs. Stored Procedures [modified] Pin
Mike Dimmick28-Jun-07 10:22
Mike Dimmick28-Jun-07 10:22 
The compiled query plan for a stored procedure has a slightly higher weighting than that for a parameterized ad-hoc query, so it will tend to stay around in memory for a little longer.

In security terms, you can GRANT access to EXEC a stored procedure without having to grant access to the tables that the stored procedure accesses. This allows you to control the entry points to your database. This is a defence-in-depth measure in case you happen to accidentally allow a SQL injection or disclose the credentials used to connect to the database, or they're compromised. Best practice is to only permit each user and/or application the rights they actually need to get their job done.

Don't go overboard, though. Some people are seduced by the idea of building a single stored procedure to handle querying a table or view with optional parameters (e.g. looking for books by title or by author). This often looks something like:
CREATE PROCEDURE SelectBooks
(
   @title varchar(50),
   @author varchar(50)
)
SELECT *
FROM books
WHERE
  (title = @title OR @title IS NULL) AND
  (author = @author OR @author IS NULL)
They've found a syntactically valid solution, but it makes harder work for the optimizer. Further, the query plan gets compiled the first time the procedure is used, using the parameters supplied. If you specify @title the first time round, leaving @author set to NULL, the optimizer might choose to seek through an index on title. If you then call it with @author rather than @title, it may still try to use that index and fall back on a table scan (i.e. reading every row in the table), even if there was an index on title that it could have used. In this case, I think parameterized queries are better.

[EDIT:]

I've just tried an equivalent query on SQL Server 2000 SP4, with no primary key on the table but with a separate single-column index on each of the columns in the query. The table has 240,000 rows. SQL Server elected to do an Index Scan (that is, reading the index from start to finish) on the title column, whether you specified title or author. If instead you run the parameterized query specifying only the columns you actually want to filter on, it correctly picks an Index Seek to the value you're after. I'm not yet sure about SQL Server 2005.

In fact if you specify EXEC ... WITH RECOMPILE it's using the opposite index from the value you specify! Flushing the data from the cache using DBCC DROPCLEANBUFFERS causes the query using the above stored procedure to take 46 seconds! The database is 160MB but my laptop, a Core 2 Duo T7200 [2GHz], has 2GB of RAM so if you don't run from cold, it's sub-second. The index scan is so fast, even from cold, that the Client Statistics view in Query Analyzer shows a time of 0.

-- modified at 16:17 Thursday 28th June, 2007

Stability. What an interesting concept. -- Chris Maunder

GeneralRe: Paramaterized Queries vs. Stored Procedures Pin
martin_hughes29-Jun-07 2:34
martin_hughes29-Jun-07 2:34 
AnswerRe: Paramaterized Queries vs. Stored Procedures Pin
originSH29-Jun-07 1:11
originSH29-Jun-07 1:11 
GeneralRe: Paramaterized Queries vs. Stored Procedures Pin
martin_hughes29-Jun-07 2:32
martin_hughes29-Jun-07 2:32 
QuestionView more than one page with CrystalReportViewer vb.net Pin
kindman_nb28-Jun-07 6:09
kindman_nb28-Jun-07 6:09 
QuestionAlternative of CONVERT function in MS Access Pin
FishiFishi28-Jun-07 4:12
FishiFishi28-Jun-07 4:12 
AnswerRe: Alternative of CONVERT function in MS Access Pin
leckey28-Jun-07 5:20
leckey28-Jun-07 5:20 
Questiondatetime code Pin
niki_nilu28-Jun-07 2:56
niki_nilu28-Jun-07 2:56 
AnswerRe: datetime code Pin
Sathesh Sakthivel28-Jun-07 3:22
Sathesh Sakthivel28-Jun-07 3:22 
Questiondatetime problem Pin
niki_nilu28-Jun-07 1:54
niki_nilu28-Jun-07 1:54 
AnswerRe: datetime problem Pin
Krish - KP28-Jun-07 2:30
Krish - KP28-Jun-07 2:30 
GeneralRe: datetime problem Pin
FishiFishi28-Jun-07 4:16
FishiFishi28-Jun-07 4:16 
AnswerRe: datetime problem Pin
Pratik Vasant Shah28-Jun-07 21:19
Pratik Vasant Shah28-Jun-07 21:19 
Questionhow to pass null values to a query in the design time Pin
pashitech28-Jun-07 1:12
pashitech28-Jun-07 1:12 
AnswerRe: how to pass null values to a query in the design time Pin
Xandip28-Jun-07 21:13
Xandip28-Jun-07 21:13 
QuestionDate Time String Transformation sample mssql Pin
cutequencher28-Jun-07 0:18
cutequencher28-Jun-07 0:18 
AnswerRe: Date Time String Transformation sample mssql Pin
andyharman28-Jun-07 1:18
professionalandyharman28-Jun-07 1:18 
GeneralRe: Date Time String Transformation sample mssql Pin
cutequencher28-Jun-07 1:39
cutequencher28-Jun-07 1:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.