|
If you want you can write it that way too. However, I'd think that using a function like this wouldn't be as efficient (but SQL Server often surprises me with what it can do efficiently and what not) - so, if that is important to you then you should consider writing both versions and running them through the query analyser to see which generates the better execution plan.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
generates the better execution plan, what do u mean by saying it?
how to check execution plan in the query analyser.
Thank u
------------------------------------
To study, study and only to study
|
|
|
|
|
|
thanks i got it,
I have another question to u: i have stored procedure that perform select ... from ....
also this procedure return value (e.g. return 0)
if i call to the sp by EXECUTE i got the result of my sp(result of query.)
if i do:
DECLARE @res int
EXEC @res = My Stored proc
PRINT @res
i got only what the sp return.
how to get the return value and the query result(set result) both?
------------------------------------
To study, study and only to study
|
|
|
|
|
Hi Guys,
Please give advice.
I have a program that attached files. What I do is after pressing the save button it saves the entire path of the file to my table plus of course the key (attach with columns userid,fileloc,filename) regarding the physical file I saved it in a pre defined directory in our server which is related to my WEB PAGE PROJECT.
Question: What is the advantage of saving the entire file into a SQL Server rather than what i've mentined above?
/Dabuskol
|
|
|
|
|
That's kind of a hard question to answer without any other context. It seems like, if you have to ask what the advantages are, it's probably not something you really need to worry about just yet.
I guess there are obvious differences, but it's hard to say if they're advantages without knowing what it is you're trying to accomplish.
If the application may be sold to others, or there are potential file system sharing issues (networked users may need access to files), then saving the files to the database may be an advantage.
If file versioning is something you want to implement, databases offer some advantages over the file system alone.
Backups may be arguably simpler (or at least require less third-party software in many cases).
There are potential security advantages in storing files in a database - it's almost impossible to execute a malicious script if it's stored in a BLOB...
There are no doubt other, more situation-specific advantages to storing files in a database versus the file system, but there are also potential disadvantages.
Accessing a file stored in a database is seldom as fast as accessing one from the file system.
Sharing copies of a file stored in a database certainly takes more steps than sharing one on the file system.
Storing files of any size in a database can cause significant bloat, etc.
Good luck.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
CREATE PROCEDURE proname
@param int,
AS
SELECT TOP @param field1, field2, fiedl3
FROM tablename
GO
When executing the stored procedure i above, i'm reported that error happens at @param
How could i pass parameter in 'SELECT TOP' statement above
Thanks
|
|
|
|
|
You aren't allowed to use a parameter with TOP in that way.
You could, however, concatenate a sql expression using the parameter, like:
declare @param int<br />
declare @sql nvarchar(100)<br />
set @param = 10<br />
<br />
set @sql = 'select top ' + cast(@param as nvarchar(4)) + ' * from FooTable'<br />
<br />
exec (@sql)
Does that help?
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
How i can simplify this statement to better performance?
T is a table .
Column ID is not unique.
SELECT DISTINCT ID FROM T
WHERE
ID IN (SELECT ID FROM T WHERE TypeID = i)
and ID IN (SELECT ID FROM T WHERE TypeID = j)
.
.
.
and ID IN (SELECT ID FROM T WHERE TypeID = k)
and ID NOT IN (SELECT ID FROM T WHERE TypeID NOT IN (i , j , ... , k ) )
|
|
|
|
|
If you are looking for all IDs that have a record with a TypeID of i and j and k and at least one other TypeID then I think this would work:
SELECT
DISTINCT a.ID
FROM
T AS a
INNER JOIN
T AS b
ON (a.ID = b.ID)
INNER JOIN
T AS c
ON (a.ID = c.ID)
INNER JOIN
T AS d
ON (a.ID = d.ID)
WHERE
a.TypeID = i,
b.TypeID = j,
c.TypeID = k,
d.TypeId NOT IN (i,j,k)
|
|
|
|
|
There is an error in your statement.
Are u sure this statement has a better performance?
Any Idea?
|
|
|
|
|
The error might be in the engine - I use MS SQL 2000 - or it may be that there is typo since I didn't create any test tables.
Only examining the execution plan between the two queries and testing will tell you which one is faster. Indexes also help.
My experience is that IN (or NOT IN) statments tend to generate more table scans whereas JOIN makes use of indexes better. Given your set of criteria, an index on [TypeID, ID] could generate a faster query.
|
|
|
|
|
Try this:
select ID from
(Select ID, sum(case when TypeID in ('I', 'J', 'K', 'L', 'M') then 1 else 0 end) as InList,
sum(case when TypeID in ('I', 'J', 'K', 'L', 'M') then 0 else 1 end) as NotInList
from t
group by ID) as foo
where inlist > 0 and NotInList = 0
Tony Lewis
SQL "Guru"
XAKTsoft, Inc.
|
|
|
|
|
I used the following method a few years back:
SELECT ID FROM T
WHERE TypeId IN (i, j, k)
GROUP BY ID
HAVING COUNT(DISTINCT TypeId) = 3 Where "3" is the number of terms in the "IN" clause. To handle your "NOT IN" requirement, you could use the following:
SELECT ID FROM T
WHERE ID IN (SELECT ID FROM T
WHERE TypeId IN (i, j, k)
GROUP BY ID
HAVING COUNT(DISTINCT TypeId) = 3)
GROUP BY ID
HAVING COUNT(DISTINCT TypeId) = 3I don't know how fast this will run against your chosen database engine. Under SQL-Server, I would normally expect the following indexes to help:
Clustered index on T (ID, TypeId)
And possibly:
Nonclustered index on T (TypeId)
Hope this helps.
Regards
Andy
|
|
|
|
|
i just want to save time in a field got type is datetime, but every time i insert time into the field, SQL SERVER automatically add day to that field.
Any way to solve this problem? Thanks in advance
|
|
|
|
|
|
I have a document storage system written in C# that has been running fine for over a year on a SQL 2000 database. I have a table that holds three BLOB field which contain files. The problem I'm having is, that this morning the files can no longer be retrieved from the table's BLOB field(s). When I query a record in the Query Analyzer the BLOB I'm attempting to get shows a value of 0x where the other field show values of 0x123423dj3ed24aq2 etc......anyone see this before ? Any ideas why SQL is not longer storing this BLOB field correctly ?
|
|
|
|
|
My application has one web form where admin can add as many users as desired and at the end when he press SaveChanges I want to add all of them to database, There should be some way to pass arrays to stored procedure..I am looking for it or something similar to this....
Any Ideas!!!
Thanks in advance...
MaulikCE
|
|
|
|
|
No, you can't pass an array. What you should do is open your connection and loop through array of users. Set the new parameter value and execute. Why would you need to pass an array?
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
|
|
Another idea is :
To call the Update method on a SQLDataAdapter, passiing it a modified dataset containing new, changed and or deleted rows.
------------------------------------
To study, study and only to study
|
|
|
|
|
Suppose i connect to db (SQL Server) from the client (.NET) and call to some store procedure, in this sp i start transaction (BEGIN TRANSACTION) and before COMMIT or RALLBACK an error happen that imidietly stop the execution of stored procedure, In the client i cacth this error but what about an open transaction ???
HOW to rollback in the client???
------------------------------------
To study, study and only to study
|
|
|
|
|
I am not an expert in SQL by any means, and so I don't know if it is possible to do what you want. But if you could start you transaction on the client side, then you could undo everything. See SqlTransaction class.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Short answer: try SET XACT_ABORT ON...
"If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.<br />
<br />
It is the responsibility of the programmer to code the application to specify the correct action (COMMIT or ROLLBACK) if a run-time or compile error occurs."
-- SQL Server Books Online
A better answer would be to include error handling. There's a very good article
here[^] that describes one strategy for getting more predictable results from stored procedures through error handling.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
