Database

NAKRAINC28-Jun-07 22:10

28-Jun-07 22:10

QNO : 1
CREATE TABLE MEMBER_C_VIJAY(MEMBER_ID NUMBER(5) PRIMARY KEY,
ACC_OPEN_DATE DATE DEFAULT SYSDATE,
MAX_ALLOWED_BOOKS NUMBER(2) CHECK(MAX_ALLOWED_BOOKS<100),
PENALTY_AMOUNT NUMBER(7,2) CHECK(PENALTY_AMOUNT<=1000)
MEMBER_NAME CHAR(25));

QNO : 2
CREATE TABLE BOOK_C_VIJAY(BOOK_NO NUMBER(6) PRIMARY KEY,
BOOK_NAME VARCHAR2(30) NOT NULL, CATEGORY CHAR(10)
CHECK(CATEGORY='SCIENCE' OR CATEGORY='FICTION' OR CATEGORY='FICTION' OR CATEGORY='DATABASE' OR CATEGORY='RDBMS' OR CATEGORY='OTHERS'))

QNO : 3
CREATE TABLE ISSUE_C_VIJAY(LIB_ISSUE_ID NUMBER(10) PRIMARY KEY,
MEMBER_ID NUMBER(5) REFERENCES MEMBER_C_VIJAY(MEMBER_ID),
BOOK_NO NUMBER(6) REFERENCES BOOK_C_VIJAY(BOOK_NO),
ISSUE_DATE DATE,
RETURN_DATE DATE );

ALTER TABLE ISSUE_C_VIJAY ADD CONSTRAINT I CHECK(RETURN_DATE>ISSUE_DATE);

QNO : 4
ALTER TABLE BOOK_C_VIJAY ADD CONSTRAINT V CHECK(COST<2500);

QNO : 5

QNO : 6
ALTER TABLE ISSUE_C_VIJAY DISABLE CONSTRAINT I;

QNO : 7
CREATE VIEW Vijay AS( SELECT LIB_ISSUE_ID,BOOK_NAME,MEMBER_NAME,AUTHOR,ISSUE_DATE
FROM
BOOK_C_VIJAY B,MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE ((B.COST>500 AND B.COST<750) AND (B.CATEGORY='RDBMS' OR B.CATEGORY='DATABASE') AND
I.BOOK_NO=B.BOOK_NO AND I.MEMBER_ID=M.MEMBER_ID));

QNO : 8
CREATE VIEW VIJAY1 AS(SELECT BOOK_NAME FROM BOOK_C_VIJAY WHERE COST>500);

QNO : 9

QNO : 10

Re: NAKRA

Pete O'Hanlon28-Jun-07 22:20

Pete O'Hanlon

28-Jun-07 22:20

This looks suspiciously like you are posting answers to questions. Naughty, naughty.

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

Re: NAKRA

kubben29-Jun-07 1:53

kubben

29-Jun-07 1:53

Now he is doing it in the asp.net forum as well.
Ben

NAKRAINC28-Jun-07 22:09

28-Jun-07 22:09

AS : 3
QNO : 1
SELECT CATEGORY,COUNT(BOOK_NO) FROM BOOK_C_VIJAY GROUP BY CATEGORY;

QNO : 2
SELECT B.BOOK_NO,COUNT(I.BOOK_NO) FROM BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE (B.BOOK_NO=I.BOOK_NO) GROUP BY B.BOOK_NO;

QNO : 3
SELECT MAX(PENALTY_AMOUNT),MIN(PENALTY_AMOUNT),SUM(PENALTY_AMOUNT),
AVG(PENALTY_AMOUNT) FROM MEMBER_C_VIJAY;

QNO : 4
SELECT M.MEMBER_ID,COUNT(I.MEMBER_ID) FROM
MEMBER_C_VIJAY M,ISSUE_C_VIJAY I WHERE
(M.MEMBER_ID=I.MEMBER_ID) GROUP BY M.MEMBER_ID;

QNO : 5
SELECT I.MEMBER_ID,B.BOOK_NO,COUNT(I.BOOK_NO)
FROM ISSUE_C_VIJAY I,BOOK_C_VIJAY B
WHERE I.BOOK_NO IN(B.BOOK_NO)
GROUP BY I.MEMBER_ID,B.BOOK_NO;

QNO : 6
SELECT TO_CHAR(ISSUE_DATE,'MONTH'),COUNT(BOOK_NO)
FROM ISSUE_C_VIJAY
GROUP BY TO_CHAR(ISSUE_DATE,'MONTH')
ORDER BY 2 DESC;

QNO : 7
SELECT BOOK_NO FROM BOOK_C_VIJAY
WHERE BOOK_NO NOT IN(SELECT BOOK_NO FROM ISSUE_C_VIJAY);

QNO : 8
SELECT MEMBER_ID FROM MEMBER_C_VIJAY
WHERE MEMBER_ID IN(SELECT MEMBER_ID FROM ISSUE_C_VIJAY);

QNO : 9

QNO : 10
SELECT * FROM ISSUE_C_VIJAY
WHERE TO_CHAR(ISSUE_DATE,'MM') IN('12','07');

QNO : 11
SELECT B.BOOK_NO,B.BOOK_NAME,I.ISSUE_DATE FROM
BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE TO_CHAR(ISSUE_DATE,'MM') IN('12') AND B.BOOK_NO=I.BOOK_NO AND CATEGORY='DATABASE';

QNO : 12
SELECT I.MEMBER_ID,M.MEMBER_NAME,COUNT(I.BOOK_NO)
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE I.MEMBER_ID=M.MEMBER_ID
GROUP BY I.MEMBER_ID,M.MEMBER_NAME
ORDER BY 3 DESC;

QNO : 13
SELECT B.BOOK_NO,B.BOOK_NAME,I.ISSUE_DATE,I.RETURN_DATE
FROM BOOK_C_VIJAY B,MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE (M.MEMBER_NAME='VIJAY' AND I.BOOK_NO=B.BOOK_NO
AND I.MEMBER_ID=M.MEMBER_ID);

QNO : 14
SELECT M.MEMBER_ID,M.MEMBER_NAME,B.CATEGORY
FROM MEMBER_C_VIJAY M,BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE B.CATEGORY='DATABASE' AND M.MEMBER_ID=I.MEMBER_ID
AND B.BOOK_NO=I.BOOK_NO;

QNO : 15
SELECT CATEGORY, MAX(COST)
FROM BOOK_C_VIJAY
GROUP BY CATEGORY;

QNO : 16
SELECT M.ACC_OPEN_DATE,I.BOOK_NO,I.MEMBER_ID,I.ISSUE_DATE,I.RETURN_DATE
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY
I WHERE (I.ISSUE_DATE NOT BETWEEN M.ACC_OPEN_DATE AND I.RETURN_DATE)
AND I.MEMBER_ID=M.MEMBER_ID ;

QNO : 17
SELECT DISTINCT M.MEMBER_ID,M.MEMBER_NAME
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY
WHERE M.MEMBER_ID NOT IN(SELECT MEMBER_ID FROM ISSUE_C_VIJAY);

QNO : 18
SELECT MEMBER_ID,MEMBER_NAME
FROM MEMBER_C_VIJAY
WHERE
(SELECT COUNT(I.BOOK_NO) FROM ISSUE_C_VIJAY I,BOOK_C_VIJAY B,MEMBER_C_VIJAY M
WHERE I.BOOK_NO=B.BOOK_NO AND I.MEMBER_ID=M.MEMBER_ID)>MAX_ALLOWED_BOOKS
GROUP BY MEMBER_ID,MEMBER_NAME;

QNO : 19

****************************************************************************

NAKRAINC28-Jun-07 22:08

28-Jun-07 22:08

AS:2
QNO : 1
SELECT MEMBER_NAME
FROM MEMBER
WHERE ACC_OPEN_DATE>'31-DEC-2005' AND ACC_OPEN_DATE <'1-JAN-2007';

OR

SELECT MEMBER_NAME
FROM MEMBER
WHERE ACC_OPEN_DATE BETWEEN '31-DEC-2005' AND '1-JAN-2007';

QNO : 2
SELECT BOOK_NAME,AUTHOR
FROM BOOK
WHERE AUTHOR='LONI' AND COST>600;

QNO : 3
SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE COMMENTS='PENDING';

QNO : 4
UPDATE ISSUE SET RETURN_DATE='31-DEC-06'
WHERE RETURN_DATE=NULL AND (LIB_ISSUE_ID!=7005 OR LIB_ISSUE_ID!=7006);

QNO : 5
SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE (SYSDATE-ISSUE_DATE)>30 AND (ISSUE_DATE-RETURN_DATE)>30;

OR

SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE ((SYSDATE-ISSUE_DATE)>30 AND RETURN_DATE=NULL) OR (ISSUE_DATE-RETURN_DATE)>30;

QNO : 6
SELECT BOOK_NAME
FROM BOOK
WHERE (COST BETWEEN 500 AND 750) AND CATEGORY='DATABASE';

QNO : 7
SELECT BOOK_NAME
FROM BOOK
WHERE CATEGORY IN('SCIENCE','DATABASE','FICTION','MANAGEMENT');

QNO : 8
SELECT MEMBER_ID,MEMBER_NAME,PENALTY_AMOUNT
FROM MEMBER
ORDER BY PENALTY_AMOUNT DESC;

OR

SELECT MEMBER_ID,MEMBER_NAME,PENALTY_AMOUNT
FROM MEMBER
ORDER BY 3 DESC;

QNO : 9
SELECT BOOK_NAME,COST
FROM BOOK
ORDER BY BOOK_NAME,COST DESC;

OR

SELECT BOOK_NAME,COST
FROM BOOK
ORDER BY 1,2 DESC;

QN0 : 10
SELECT BOOK_NAME
FROM BOOK
WHERE BOOK_NAME LIKE '%SQL%';

QNO : 11
SELECT MEMBER_NAME
FROM MEMBER
WHERE MEMBER_NAME LIKE ('G%I%' ||'R%I%');

QNO : 12
SELECT INITCAP(BOOK_NAME),UPPER(AUTHOR)
FROM BOOK
ORDER BY 1 DESC;

QNO : 13
SELECT BOOK_NO, CONCAT('IS WRRITTEN ', 'BY') " ---------------- ", AUTHOR AS NAME
FROM BOOK;

OR

SELECT CONCAT( BOOK_NO,' IS WRITTEN BY') "BOOKNO ------------------",AUTHOR AS NAME
FROM BOOK;

QNO : 14
SELECT LIB_ISSUE_ID,BOOK_NO,TO_CHAR(TO_DATE(ISSUE_DATE,'DD-MM-YY'),'Day,Month,DD,YYYY') ,
TO_CHAR(TO_DATE(RETURN_DATE,'DD-MM-YY'),'Day,Month,DD,YYYY')
FROM ISSUE
WHERE MEMBER_ID=101;

QNO : 15 & 16
SELECT CATEGORY,
DECODE(CATEGORY,'DATABASE','D','SCIENCE','S','RDBMS','R','OTHERS','O')
FROM BOOK;

QNO : 17
SELECT CONCAT( RPAD(BOOK_NAME,20,'*'),LPAD(AUTHOR,20,'*') )
FROM BOOK;

QNO : 18
SELECT LIB_ISSUE_ID,ISSUE_DATE,RETURN_DATE,COUNT(ISSUE_DATE-RETURN_DATE) "NO OF DAYS"
FROM ISSUE;

QNO : 19
SELECT MEMBER_ID,MEMBER_NAME,ACC_OPEN_DATE,MAX_BOOKS_ALLOWED,PENALTY_AMOUNT
FROM MEMBER
ORDER BY 3 DESC;

QNO : 20
SELECT COUNT(BOOK_NO)
FROM ISSUE
WHERE MEMBER_ID=101;

QNO : 21
SELECT SUM(PENALTY_AMOUNT)
FROM MEMBER;

QNO : 22
SELECT SUM(COST)
FROM BOOK
WHERE CATEGORY='DATABASE';

QNO : 23
SELECT BOOK_NAME,COST
FROM BOOK ;

QNO : 24
SELECT BOOK_NO,MIN(ISSUE_DATE)
FROM ISSUE;

QNO : 25
SELECT BOOK_NO,MAX(ISSUE_DATE)
FROM ISSUE;

QNO : 26
SELECT AVG(COST)
FROM BOOK
WHERE CATEGORY='DATABASE';

*****************************************************************************

NAKRAINC28-Jun-07 22:06

how to encrypt whole Database ?

28-Jun-07 22:06

AS:1
QNO : 1
CREATE TABLE MEMBER_VIJAY(MEMBER_ID NUMBER(5),
MEMBER_NAME CHAR(25),
ACC_OPEN_DATE DATE,
MAX_BOOKS_ALLOWED NUMBER(2),
PENALTY_AMOUNT NUMBER(7,2)
);

CREATE TABLE BOOK_VIJAY(BOOK_NO NUMBER(6),
BOOK_NAME VARCHAR2(30),
AUTHOR CHAR(30),
COST NUMBER(7,2),
CATEGORY CHAR(10)
);

CREATE TABLE ISSUE_VIJAY(LIB_ISSUE_ID NUMBER(10),
BOOK_NO NUMBER(6),
MEMBER_ID NUMBER(5),
ISSUE_DATE DATE,
RETURN_DATE DATE
);

QNO : 2
DESC MEMBER_VIJAY;
DESC BOOK_VIJAY;
DESC ISSUE_VIJAY;

QNO : 3
ALTER TABLE ISSUE_VIJAY ADD( COMMENTS CHAR(100));

QNO : 4
ALTER TABLE MEMBER_VIJAY MODIFY(MEMBER_NAME CHAR(30));

QNO : 5
ALTER TABLE ISSUE_VIJAY ADD(REFERENCE CHAR(30));

QNO : 6
ALTER TABLE ISSUE_VIJAY DROP COLUMN REFERENCE;

QNO : 7
RENAME ISSUE_VIJAY TO LIB_ISSUE_VIJAY;

QNO : 8
INSERT INTO MEMBER_VIJAY VALUES(1,'RICHA SHARMA','10-DEC-05',5,50);
INSERT INTO MEMBER_VIJAY VALUES(2,'GARIMA SHARMA',SYSDATE,3,NULL);

QNO : 9
ALTER TABLE MEMBER_VIJAY MODIFY(MEMBER_NAME CHAR(20));
ORA-01441: cannot decrease column length because some value is too big

QNO : 10
INSERT INTO MEMBER_VIJAY VALUES(1,'VIJAY','27-JUNE-2007',110,100);
ORA-01438: value larger than specified precision allows for this column

QNO : 11
CREATE TABLE MEMBER101_VIJAY AS (SELECT *FROM MEMBER_VIJAY);

QNO : 12
INSERT INTO BOOK_VIJAY VALUES(101,'LET US C','DENIS RITCHIE',450,'SYSTEM');
INSERT INTO BOOK_VIJAY VALUES(102,'ORACLE-COMPLETE REFERENCE','LONI',550,'DATABASE');
INSERT INTO BOOK_VIJAY VALUES(103,'MASTERING SQL','LONI',250,'DATABASE');
INSERT INTO BOOK_VIJAY VALUES(104,'PL SQL-REF','SCOTT URMAN',750,'DATABASE');

QNO : 13
INSERT INTO BOOK_VIJAY VALUES(&BOOK_NO,'&BOOK_NAME','&AUTHOR',&COST,'&CATEGORY');
Enter value for book_no: 105
Enter value for book_name: C++
Enter value for author: LIPMAN
Enter value for cost: 450
Enter value for category: SYSTEM

QNO : 14
CREATE TABLE BOOK101_VIJAY AS (SELECT BOOK_NO,BOOK_NAME,AUTHOR,COST,CATEGORY FROM BOOK_VIJAY WHERE BOOK_NO NOT LIKE '1%');

QNO : 15
INSERT INTO BOOK101_VIJAY(SELECT *FROM BOOK_VIJAY);

QNO : 16
COMMIT;

QNO : 17
SELECT *FROM MEMBER_VIJAY;
SELECT *FROM BOOK_VIJAY;
SELECT *FROM ISSUE_VIJAY;
SELECT *FROM MEMBER101_VIJAY;
SELECT *FROM BOOK101_VIJAY;

QNO : 18
INSERT INTO BOOK_VIJAY VALUES(105,'NATIONAL GEOGRAPHIC','ADIS SCOTT',1000,'SCIENCE');

QNO : 19
ROLLBACK;

QNO : 20
UPDATE BOOK_VIJAY SET COST=300,CATEGORY='RDBMS' WHERE BOOK_NO=103;

QNO : 21
RENAME TABLE LIB_ISSUE_VIJAY TO ISSUE_VIJAY;

QNO : 22
INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7001
Enter value for book_no: 101
Enter value for member_id: 1
Enter value for issue_date: '10-DEC-06'

QNO : 23
COMMIT;

QNO : 24
INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7007
Enter value for book_no: 101
Enter value for member_id: 3
Enter value for issue_date: '10-JUNE-06'

INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7007
Enter value for book_no: 102
Enter value for member_id: 2
Enter value for issue_date: '15-JULY-06'

QNO : 25
UPDATE ISSUE_VIJAY SET RETURN_DATE=(ISSUE_DATE+15) WHERE LIB_ISSUE_ID=7004
OR LIB_ISSUE_ID=7005;

QNO : 26
UPDATE MEMBER_VIJAY SET PENALTY_AMOUNT=100 WHERE MEMBER_NAME='GARIMA SHARMA';

QNO : 27
SAVEPOINT X;

QNO : 28
DELETE FROM ISSUE_VIJAY WHERE MEMBER_ID=1 AND ISSUE_DATE<'10-DEC-06';

QNO : 29
DELETE FROM BOOK_VIJAY WHERE CATEGORY!='RDBMS' AND CATEGORY!='DATABASE';

QNO : 30
ROLLBACK TO X;

QNO : 31
COMMIT;

QNO : 32
DROP TABLE MEMBER101_VIJAY;

QNO : 33
DROP TABLE BOOK101_VIJAY;

QNO : 34
SELECT * FROM MEMBER_VIJAY;
SELECT * FROM BOOK_VIJAY;
SELECT *FROM ISSUE_VIJAY;
DESC MEMBER_VIJAY;
DESC BOOK_VIJAY;
DESC ISSUE_VIJAY;

QNO : 35
SELECT TNAME FROM TAB WHERE TNAME LIKE '%VIJAY';

*****************************************************************************

hdv21228-Jun-07 21:51

hdv212

28-Jun-07 21:51

hi i want to encrypt whole database, that admin or other users can't see my dataBase's core, (i don't want to encrypt fields, i want to encrypt whole database), can anyone help me ?
thanks

Re: how to encrypt whole Database ?

originSH29-Jun-07 0:06

originSH

29-Jun-07 0:06

To stop anyone else accessing your database via the management tools you'd have to deny them the rights to do so. Easy enough to do but if you don't want any server admins to be able to access your databse I assume that means your using external hosting, in which case I doubt they'd let you deny them rights :P

Merge Replication rights

Sgg24528-Jun-07 20:15

Sgg245

28-Jun-07 20:15

I have a central db as publisher on SQL Server 2005, which has SUPERVISOR user having sysadmin rights.

And created another client db with subscriber user (not having sysadmin rights on the publisher SQL Server 2005), say U1, on SQL Express 2005 instance. As subscriber is the owner of db of subscriber db, so has sysadmin rights on the SQL Express 2005.

Done all the pre-requisite for merge replication.

When I try to replicate between the publisher db & subscriber db with subscriber user, it gives error
"The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a publisher or a republishing Subscriber has run out of identity ranges to allocate to its own subscriber or when an identity column data type does not support an additional identity range allocation. If a republishing subscriber has run out of identity ranges, synchronize the republishing subscriber to obtain more identity ranges before restarting the synchronization. if a publisher runs out of identity....."

Although, if the subscriber user is created as sysadmin on the publisher i.e. SQL Server 2005 then the synchronization works perfectly and synchronizes the data.
Confused | :confused:

I would like to know why SYSADMIN right is needed to be given to the subscriber user at the publisher i.e. SQL Server 2005 instance??? Confused | :confused:

Regards
SG (sgg245@yahoo.co.in)

Critical probs to display records...,

Member 387988128-Jun-07 18:02

Member 3879881

28-Jun-07 18:02

Hi i think this is critical probs...,
But i think some solution will be there..,
If u know plz clear to me...,

I have one table like...,
Id s1 s2 s3 s4 s5
1 100 200 0 0 0
2 400 0 100 0 150
3 500 150 <null> 50 0
...................
...................

Like this i have records in my table...,
Now my question is, i want to show my record...,
what are the fields have values those only...,
Suppose if i like to show my 2nd record...,
I want to show the records like...,

Id s1 s3 s5
2 400 100 150 only...,

Suppose if i like to show my 3rd record...,
The query want to display like...,

Id s1 s2 s4
3 500 150 50

Means what are the fields have 0 or null values those records dont want to display...,
Using stored procedure or query anything if its possible plz tell me...,
Advance Thanks,

Regards,
Magi

Re: Critical probs to display records...,

Devkranth Kishore Vanja28-Jun-07 20:24

Devkranth Kishore Vanja

28-Jun-07 20:24

hi magi

acc to me there is no option to hide a column ,,u can do like this
cacth the rows which have O value and while displaying display it a NULL

Re: Critical probs to display records...,

Member 387988128-Jun-07 20:57

Member 3879881

28-Jun-07 20:57

Ok thanks for ur reply......,

Regards,
Magi

Re: Critical probs to display records...,

Navneet Hegde29-Jun-07 3:34

Navneet Hegde

29-Jun-07 3:34

Hi M.
I tried it , But only found that you can replace Null value at the most
select IsNull(fldCol,1) from tblTest where fldcol2 ='abc'
hope someone comeup with the required stuff,May be Next version provide this facility.

Regards & Wishes
Navneet Hegde
Nashik(City Of Pilgrimage)

Develop2Program & Program2Develop

Paramaterized Queries vs. Stored Procedures

martin_hughes28-Jun-07 8:17

martin_hughes

28-Jun-07 8:17

Hey all,

Are Stored Procedures always preferable to PQ's, when possible? Reason I ask is that i've used PQ's extensively during the last 3 years (or so) of development, and I'm wondering if I should "progress" (given that all my work has been, and probably will remain, with SQL Server, I'm not too worried about having to port anything to Oracle or whatever).

Some sites mention security concerns, but I had thought that PQ's were pretty good anyway, and certainly avoid SQL Injection style attacks. Are there other considerations?

Cheers,

Martin.

Re: Paramaterized Queries vs. Stored Procedures [modified]

kubben28-Jun-07 9:09

kubben

28-Jun-07 9:09

I use stored procedures only in all of my code. They are cached on the sql server so they will perform quicker. It also allows you to limit the rights of your user to only execute the stored procedures. They don't need any rights to tables at all. So that is more secure. I would suggest moving toward only using stored procedure.

Ben

Mike Dimmick28-Jun-07 9:22

Mike Dimmick

28-Jun-07 9: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

martin_hughes29-Jun-07 1:34

martin_hughes

29-Jun-07 1:34

Thanks for taking the time to investigate that Mike - I'll try a similar test in 2005 to measure the performance of SP's vs PQ's... I remember reading somewhere that 2005 can optomise and cache PQ's, so it'll be interesting to see.

originSH29-Jun-07 0:11

originSH

29-Jun-07 0:11

This subject is hotly debated and is often the cause of religious wars! Well on the web anyway Wink | ;)

http://www.google.co.uk/search?hl=en&q=stored+procs+vs+dynamic+queries&meta=[^]

Personally I like Stored Procs, they appeal to my OO side with their encapsulation of data access sql code in the database. Another advantage is not having to update your application to change a query. But as others have said dynamic sql definatly has its place and shouldn't be completely dismissed.