Category Archives: SqlServer

Maintaining subclass integrity in a relational database

Questions: Let’s say I have a table that represents a super class, students. And then I have N tables that represent subclasses of that object (athletes, musicians, etc). How can I express a constraint such that a student must be modeled in one (not more, not less) subclass? Clarifications regarding comments: This is being maintained… Read More »

Column-based query accelerator in SQL Server 2012

Questions: I have been researching, SQL Server 2012 (aka Denali) and Microsoft has a pre-release available. The pre-release is located here with some information on key features. As I have downloaded the pre-release and installed on a VM. I have been curious about the following key feature mentioned. But Im not sure of its ability.… Read More »

UNION ALL vs OR condition in sql server query

Questions: I have to select some rows based on a not exists condition on a table. If I use a union all as below, it gets executed in less than 1 second. SELECT 1 FROM dummyTable WHERE NOT EXISTS ( SELECT 1 FROM TABLE t WHERE Data1 = t.Col1 AND Data2=t.Col2 UNION ALL SELECT 1… Read More »

Call stored procedure with table-valued parameter from java

Questions: In my application I want to execute query like SELECT * FROM tbl WHERE col IN (@list) where,@list can have variable no of values. I am using MS SQL server database. When I google this problem then I found this link http://www.sommarskog.se/arrays-in-sql-2008.html This link says to use table-valued parameter. So I created user-defined data… Read More »

Update the same table in SQL Server

Questions: I was trying to update the same table data from same table data. My SP is as shown below : UPDATE T1 SET T1.Name = T2.Name , T1.Age = T2.Age , T1.Subject = T2.Subject FROM Student T1 , Student T2 WHERE T1.StudentID = @OldID AND T2.StudentID = @NewID When I am executing this query,… Read More »

Calculated Column in SQL Server

Questions: I have my data in table as: id Author_ID Research_Area Category_ID Paper_Count Paper_Year Rank ——————————————————————————— 1 677 feature extraction 8 1 2005 1 2 677 image annotation 11 1 2005 2 3 677 probabilistic model 12 1 2005 3 4 677 semantic 19 1 2007 1 5 677 feature extraction 8 1 2009 1… Read More »

SQL Server Table Synonyms with Indexes

Questions: I have multiple databases on a single instance of SQL Server 2005. I’ve created a synonym on one database to access a table on another database and when writing my queries, I’d like to utilize a specific index, however, when evaluating the execution plan, it doesn’t appear to use it. If I write the… Read More »