There is quite often situation when you need to execute INSERT, UPDATE or DELETE statement based on some condition. And my question is whether the affect on the performance of the query add IF EXISTS before the command. Example IF EXISTS(SELECT 1 FROM Contacs WHERE [Type] = 1) UPDATE Contacs SET [Deleted] = 1 WHERE [Type] = 1 Wha...
I have a view which if i try to edit, MS SQL Server Management Studio crashes (the view contains a pivot function). Is there a way i can view the sql that is inside the view without crashing MS SQL Server Management Studio? ...
I have a SSRS report which displays daily status report for the team members. The dataset of the report depends on User!UserID as a parameter which is passed to the SQL Query to fetch the data. I want to create a data driven subscription on this report so that every user gets his/her daily status report each morning. But when I click on ...
Hi. I have a question regarding performance of Sql Server. Suppose I have a table persons with the following columns: (id, name, surname). Now, I want to insert a new row in this table. The rule is the following: If id is not present in the table, then insert the row If d is present, then update. I have two solutions here: First: ...
Say I have the following tables: |RefNumber| Charge| IssueDate| ------------------------------ | 00001| 40.0|2009-01-01| | 00002| 40.0|2009-06-21| |ID|RefNumber|Forename| Surname| --------------------------------- 1| 00001| Joe| Blogs| 2| 00001| David| Jones| 3| 00002| John| Smith| 4...
Hello All, here goes my first question: I need to replace the tag {URL}: DECLARE @PageUrl varchar(200) DECLARE @Body varchar(MAX) SET @PageUrl = 'http://www.website.com/site1/site2/pageName.asxp?rid=1232' SET @Body = 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed luctus, {URL} enim nec posuere volutpat, neque dui volut...
I need a SQL query that will identify seasonal sales items. My table has the following structure - ProdId WeekEnd Sales 234 23/04/09 543.23 234 30/04/09 12.43 432 23/04/09 0.00 etc I need a SQL query that will return all ProdId's that have 26 weeks consecutive 0 sales. I am running SQL server 2...
T-sql Is it possible to check if ip-address and dns name is the same ? ...
Hi Suppose i have the following data in a table: ID Type Preis 1 1 10 1 2 20 1 3 30 and now i want it reorganized like this: ID Preis_A Preis_B Preis_C 1 10 20 30 How can i do this in t-sql? ...
I have a table with two linked columns, a compulsory boolean and an optional date. There can only be a date when the boolean is FALSE. So I have this structure: CREATE TABLE FOO ( FOO_ID INT IDENTITY(1, 1) NOT NULL, MY_DATE DATETIME, MY_BOOLEAN BIT DEFAULT 0 NOT NULL, CONSTRAINT FOO_PK PRIMARY KEY (FOO_ID) ); And I've...
I have a query as follows select * from table where col1 = @param1 and col2 = @parm2 And another select * from table where col1 = @param1 Is it possible do both operations in same query based on parameter passed, if it is null query all or when parameter has value select them. My queries are very big and i have to create 2 version...
Hi, I'm writing sql query to get post and only last comment of this post(if exists). But I can't find a way to limit only 1 row for right column in left join. Here is sample of this query. SELECT post.id, post.title,comment.id,comment.message from post left outer join comment on post.id=comment.post_id If post has 3 comments I get 3...
I have an SP in SQL Server which runs hundreds of times a minute, and needs to check incoming traffic against a database. At the moment it does the following INSERT INTO table SELECT @value1,@value2 WHERE NOT EXISTS (SELECT * FROM table WHERE value1 = @value1 AND value2 = @value2); However, I could also go with IF NOT EXISTS(SELECT...
Hi I think If a trigger run On Insert, and the insert statement was in transaction, an error in the trigger will return to the transaction and I can catch it and ROLLBACK. Is that correct? Is there problems or concerns about that? Thanks ...
I'd like to insert a sql script into a table. I'm pretty sure this is more complicated than just wrapping the script in quotes and throwing it in an insert statement (scripts with quotes and more complicated escaping seem problematic for example) So, how can I safely store arbitrary tsql in a SqlServer table? I can use either sql or c#...
SQL Server books online states the following about visibility (scope?) of temporary tables: Temporary Tables You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions. I want to understand how the scope of the global...
Pointedly what I'm asking below is: What is the actual data type of the @cleartext parameter of this SQL function? >> ENCRYPTBYKEY (..) - http://msdn.microsoft.com/en-us/library/ms174361.aspx (If you read below this line you can follow the history and reasoning. I think it's trickier than it first appears.) The SQL Server documentati...
Where do I look in Microsoft SQL Server system tables to find info about the parameters a builtin stored procedure or function takes? ...
I have a following table, Client. create table Client ( ClientID int identity primary key, TaxID varchar(12), SSN varchar(12) ) GO Client can have either TaxID or SSN or both. But either one should exist. Currently, I am enforcing the rule thru following trigger. create trigger trgClient_UniqueTaxIDSSN ...
From a CLR UDF (C# syntax) is it possible to acquire a more or less direct handle to a built-in TSql function, instead of trying to kludge it into an ADO.NET SQL text like so? // Programming .inside NET CLR UDF: // Is there a more efficient way than this command? SqlCommand cmd... = new SqlCommand( ...'SELECT EncryptByKey(@Param, @Para...