Create a table without columns
Can I create a table without any columns in SQL Server by t-sql? ...
tsql
Where is DotNetNuke page keywords stored in database?
Which SQL Server table can I locate keywords associated with a page in DotNetNuke? ...
How to check for mutual existence of Fields in same table in Two columns
I tried using "Exist" and "IN". Not only did I not succeed, it didn't seem as an efficient solution. Here is a simplified example: TblMyTable UserName1 - Grade - UserName2 - Grade I need a query where there is a mutual relation / existence. What I mean is that the returned result from the query will only include the users wh...
TSQL: comma combination, transaction?
Given the following: select * from a; select * from b; Are these two statements run in an implicit transaction? ...
Query for retrieving top two rows from a table in SQL Server
I have a table called Employee with the following fields: EmpID Salary Name I want to get top two employees with maximum salary. How do I write this query ? ...
SQL SERVER 2008 JOIN hints
Hi all, Recently, I was trying to optimise this query UPDATE Analytics SET UserID = x.UserID FROM Analytics z INNER JOIN UserDetail x ON x.UserGUID = z.UserGUID Estimated execution plan show 57% on the Table Update and 40% on a Hash Match (Aggregate). I did some snooping around and came across the topic of JOIN hints. So I added a L...
What is the scope of TRANSACTION in Sql server
I was creating a stored procedure and i got stuck in the writing methodology of me and my collegue. I am using SQL Server 2005 I was writing Stored procedure like this BEGIN TRAN BEGIN TRY INSERT INTO Tags.tblTopic (Topic, TopicCode, Description) VALUES(@Topic, @TopicCode, @Descripti...
How to virtually delete data from multiple tables that are linked by a foreign key ?
I am using Sql Server 2005 This is a part of my database diagram. I want to perform deletion on my database which will start from tblDomain up tp tblSubTopics. Consider that each table has IsDeleted column which has to be marked true if request was made to delete data. But that data shoud remain their physically. Tables which will ...
What is the purpose of putting an 'N' in front of function parameters in TSQL?
What is the purpose of putting an 'N' in front of function parameters in TSQL? For example, what does the N mean in front of the function parameter in the following code: object_id(N'dbo.MyTable') ...
constructing dynamic In Statements with sql
Suppose we need to check three boolean conditions to perform a select query. Let the three flags be 'A', 'B' and 'C'. If all of the three flags are set to '1' then the query to be generated is SELECT * FROM Food WHERE Name In ('Apple, 'Biscuit', 'Chocolate'); If only the flags 'A' and 'B' are set to '1' with C set to '0'. Then the fol...
SQL Syntax for testing objects before creating views & functions
I'm trying to figure out the syntax for creating a view (or function) but only if a dependent CLR assembly exits. I've tried both IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'MyCLRAssembly') begin create view dbo.MyView as select GETDATE() as C1 end and IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'MyCLRAsse...
Getting average from 3 columns in SQL Server
I have table with 3 columns(smallint) in SQL Server 2005. Table Ratings ratin1 smallint, ratin2 smallint ratin3 smallint These columns can have values from 0 to 5 How to select average value of these fields, but only compare fields where value is greater then 0. So if column values are 1,3,5 - average had to be 3 if values are 0,3,...
SQLRS and Exporting to PDF Error
I have a report that displays beautifully onto the screen (print preview mode). If I try to export it to Word it works fine. If I try to do a PDF it fails. The field that causes the error is a multi-line field. If I filter our the TSQL code with REPLACE(fieldName, CHAR(10), '') AS fieldName everything works correctly but there are no ...
How to control order of assignment for new identity column in SQL Server?
I have a table with CreateDate datetime field default(getdate()) that does not have any identity column. I would like to add identity(1,1) field that would reflect same order of existing records as CreateDate field (order by would give same results). How can I do that ? I guess if I create clustered key on CreateDate field and then add...
basic sql group by with percentage
I have an issue and NO it is not homework, it's just a programmer who has been away from SQL for a long time having to solve a problem. I have the following table: create table students( studentid int identity(1,1), [name] varchar(200), [group] varchar(10), grade numeric(9,2) ) go The group is something arbitrary...
Is it possible anyhow to raise system exception on catching exception manually?
I am writing a stored procedure where i m using try catch block. Now i have a unique column in a table. When i try to insert duplicate value it throws exception with exception no 2627. I want this to be done like this if (exists(select * from tblABC where col1='value')=true) raiseError(2627)--raise system error that would have thrown...
Performance improvement to a big if clause in SQL Server function
I am maintaining a function in SQL Server 2005, that based on an integer input parameter needs to call different functions e.g. IF @rule_id = 1 -- execute function 1 ELSE IF @rule_id = 2 -- execute function 2 ELSE IF @rule_id = 3 ... etc The problem is that there are a fair few rules (about 100), and although the above is fai...
Get mutually and non mutually existening Fields in same table in Two columns
This is a question similar to another question I posted here but is a little different. I am trying to get a list of all instances of mutual and non-mutual existing Users. What I mean is that the returned result from the query will return a list of users along with their co-worker. It is similar to the question here, but the differenc...
SQL - Select all when filter value is empty
I have a SQL query in my ASP.net web app that looks like this: SELECT * FROM [Records] WHERE ([title] LIKE '%' + @title + '%') @title, of course, is the value of a text box on the page. My question is, why, when the text box is empty, does this return nothing? And how can I make it return everything, like logic tells me it ought to? ...
How to optimize simple linked server select query?
Hello, I have a table called Table with columns: ID (int, primary key, clustered, unique index) TEXT (varchar 15) on a MSSQL linked server called LS. Linked server is on the same server computer. And: When I call: SELECT ID, TEXT FROM OPENQUERY(LS, 'SELECT ID, TEXT FROM Table') It takes 400 ms. When I call: SELECT ID, TEXT F...