While studying for the 70-433 exam I noticed you can create a covering index in one of the following two ways. CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3) -- OR -- CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3) The INCLUDE clause is new to me. Why would you use it and what guidelines would you suggest in determining wh...
I have to round down all incoming data with type DECIMAL(14,3) which has 3 decimal digit to the last one. I.e.: 100; 100.0; 100.00; 100.000 -> 100 100.2; 100.02; 100.20; 100.22 -> 100.xx but 100.221 -> 100.22 100.229 -> 100.22 Using which SQL operator can I check that residue of division in decimal digit is greater then zero? ...
I am importing a SQL Server 2000 database to my SQL Server 2008 server. I don't want to import any user/permission information, is there a way to ignore this? The current restoration is causing me issues as it has users tied to the db. ...
How can I retrieve the following results? user | date | login time | logoff time I wish to print out every day in a given time period with matching login and logoff time which is written is table history. If there's a multiple login for same user and for same day then SQL should select minimal date for login and maximum date for logof...
In Microsoft SQL Server Management Studio 2008 there is a "Generate scripts..." option under the Tasks menu. I'm just wondering if this tool is available from the command line somehow? It looks similar to the sqlpubwiz.exe command line tool that was available for SQL Server 2005, but I can't find this executable anywhere in the SQL 20...
Hi folks, I have three variables :- @ScoreA DECIMAL(10,7) @ScoreB DECIMAL(10,7) @ScoreC DECIMAL(10,7) @FinalScore DECIMAL(10, 7) I wish to get the average of the three scores. BUT 1, 2 or all 3 values might be zero. Eg. scenarios: A = 1.4, B=3.5, C=5.0; FinalScore = 3.3 A = 0.0, B=0.0, C=0.0; FinalScore = 0.0 A = 1.1, B=0.0, C=0.0...
Hello everyone, I am learning SQL Server deadlock, about why there is deadlock, how to solve dead lock issue, best practices, how to analyze why deadlock happens. I am working on SQL Server 2008 Enterprise. Any recommended readings for me? thank in advance, George ...
Hey there. I would really appreciate your help in creating a query that would accomplish the following. The problem is with dynamically getting upcoming what we call "namedays" in europe. Nameday is a tradition in many countries in Europe and Latin America of celebrating on a particular day of the year associated with the one's given nam...
In Sql Server 2008, they added new DATE and TIME datatypes, complimenting DATETIME. I wanted to combine a DATE and a TIME to a DATETIME, and thought maybe the obvious would work, and I could do SELECT DATEFLD + TIMEFLD FROM MYTABLE and DATE + TIME would return the corresponding DATETIME. Unfortunately, that's a little too obvious, a...
I know this isn't recommended but my development server is 2008 while my production server is 2000. What is the easiest way to copy databases back and forth? For example, I just created a database with a robust date table which I intend to use in my queries. I figured the easiest way would be to back up the database and restore it t...
SQL Server 2008 doesn't remember password inspite of checking the "Remember Password" checkbox. I was suspecting a reboot would solve the problem. But, the issue persists. Environment: Windows Vista Ultimate, SQL Server 2008 I have been hesitant to post this, as it seems so trivial and weird. ...
I am using sp_MSforeachtable to get a rowcount of specific tables in my database. I want these ordered by name. How do I add an ORDER BY clause to sp_MSforeachtable? ...
I'm joining to a table dozens of different times, and every time, I join (or filter) based on the results of a SUBSTRING of one of the columns (it's a string, but left-padded with zeros, and I don't care about the last four digits). As a result, even though this column is indexed and my query would use the index, it does a table scan bec...
Hi all, I've poached a bit of example code from a site explaining how to do a simple text search. However, the example SQL statement isn't valid for my version (SQL Server 2008), so I was wondering if someone could help clarify what it should look like. Here is the example pseudocode: SELECT t1.id FROM mytable t1, ..., mytable tN O...
Hi there, i was wondering if there is a way to automatically append to a script file all the changes i am making to my columns, tables, relationships etc... The thing is i am doing a lot of different changes on a TEST db and the idea will be to apply this change script when i move the test db to production .. hence keeping production d...
Hello, I have this simple Orders table Orders ------------- OrderID OrderDesc OrderDate OrderStatus OrderAmount In Sql server in one query How do I for each date TotalNumber of orders with orderstatus=1,TotalNumber of orders with orderstatus=2 Group by Orderdate. Thanks ...
Whenever I attempt to use a simple repository to simply update a record, I get an object reference not set to an instance of an object error. I suspect that I am doing something incorrect, can anyone see a problem with the below lines of code for saving data to subsonic? I'm using .Net 3.5, SQL Server 2008 and Subsonic 3.0. Thanks for...
SQL Server 2008. I have this very large query which has a high cost associated with it. TOAD has Query Tuning functionality in it and the only change made was the following: Before: LEFT OUTER JOIN (SELECT RIN_EXT.rejected, RIN_EXT.scar, RIN.fcreceiver, ...
Someone sent me a MDF file that was created in SQL 2008. I have SQL 2005, and the "Attach" function is rejecting the MDF file. How can I import this file? ...
Which one: datetime datetime2 is THE recommended way to store date and time in SQL Server 2008+? I'm aware of differences in precision (and storage space probably), but ignoring those for now, is there a best practice document on when to use what, or maybe we should just use datetime2 only? ...