I am confused about a few points: What is the difference between a stored procedure and a view? When should I use stored procedures, and when should I use views, in SQL Server? Do views allow the creation of dynamic queries where we can pass parameters? Which one is the fastest, and on what basis … Read more
I have two t-sql queries using SqlServer 2005. How can I measure how long it takes for each one to run? Using my stopwatch doesn’t cut it. 6 Answers 6
What’s the difference between a Database and a Schema in SQL Server? Both are the containers of tables and data. If a Schema is deleted, then are all the tables contained in that schema also deleted automatically or are they deleted when the Database is deleted? 5 Answers 5
I’m no beginner to using SQL databases, and in particular SQL Server. However, I’ve been primarily a SQL 2000 guy and I’ve always been confused by schemas in 2005+. Yes, I know the basic definition of a schema, but what are they really used for in a typical SQL Server deployment? I’ve always just used … Read more
I am using SQL Server 2005. I have a table with a text column and I have many rows in the table where the value of this column is not null, but it is empty. Trying to compare against ” yields this response: The data types text and varchar are incompatible in the not equal … Read more
What are the advantages and disadvantages of using the nvarchar(max) vs. NText data types in SQL Server? I don’t need backward compatibility, so it is fine that nvarchar(max) isn’t supported in older SQL Server releases. Edit: Apparently the question also applies to TEXT and IMAGE vs. varchar(max) and varbinary(max), for those searching for those data-types … Read more
How can we check which database locks are applied on which rows against a query batch? Any tool that highlights table row level locking in real time? DB: SQL Server 2005 7 Answers 7
I created a database on my local machine and then did a backup called tables.bak of table DataLabTables. I moved that backup to a remote machine without that table and tried to do a restore but get the following error: System.Data.SqlClient.SqlError: The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘c:\Program … Read more
I want to convert a string like this: ’10/15/2008 10:06:32 PM’ into the equivalent DATETIME value in Sql Server. In Oracle, I would say this: TO_DATE(’10/15/2008 10:06:32 PM’,’MM/DD/YYYY HH:MI:SS AM’) This question implies that I must parse the string into one of the standard formats, and then convert using one of those codes. That seems … Read more
I’ve heard that you should put columns that will be the most selective at the beginning of the index declaration. Example: CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, SecondMost, Least ) First off, is what I’m saying correct? If so, am i likely to see large differences in performance by rearranging the order of … Read more
