Can someone please explain to me in simple English how an index on a view works? I have a fairly simple understanding of indexes on tables; how would indexing a view work differently from just letting the indexes on the underlying tables do their thing naturally?
...
I'm making a Utility that compares 2 Databases and makes one Equal to the other by changing and adding objects (Tables, Views, UserDefinedDatatypes, Defaults, Functions etc etc etc)
I'm trying to add a Column, not null with a userdefineddatatype wich has a Default defined, the problem is this:
I'm not able to use BindDefault before the...
i have a large sql server db, and i want to get the schema (all tables/triggers/sprocs), i'm pretty sure that's easy.
but the tough part is that i want to get 100 records from each table. it's a huge db on a remote server and i can't develop locally without a mockup copy.
thanks for your help!
...
Hi, I know how to do this and moreover this has been asked by many peoples in STACK OVER as well as bunch & bunch of stuffs are available in the net.
However, while googling, I came across something interesting (at least for me)
adding IDENTITY to existing column
The solution to the above question (obtained from that site, at the bot...
I need to define a one-to-one relationship, and can't seem to find the proper way of doing it in SQL Server.
Why a one-to-one relationship you ask?
I am using WCF as a DAL (Linq) and I have a table containing a BLOB column. The BLOB hardly ever changes and it would be a waste of bandwidth to transfer it across every time a query is ma...
Hi,
I have recently discovered the tablediff utility of SQL Server 2005.
I have 2 instances of the same database each on a different server.
Is it possible to compare all tables using tablediff without having to replicate the same command while only changing the table name?
For example, compare table1 on server1 with table1 on server...
My problem is simple... or may be not. I've got a table that contains two dates:
StartDate
EndDate
And I have a constant which is a month. For example:
DECLARE @MonthCode AS INT
SELECT @MonthCode = 11 /* NOVEMBER */
I need a SINGLE QUERY to find all records whose StartDate and EndDate includes the given month. For example:
/* Ca...
Hi everyone,
I'm writing some code that updates a table. Depending on what the user wants to do, it either updates a large set of records, or a smaller one. The delineating factor is a group ID.
The user can choose whether to update the table for all records, or just those with that groupID. I'd like to use the same stored procedu...
My need is fairly basic and I don't want to rebuild the wheel. I like to script my database and have written scripts to update it from one version to the next ie 001-create-tables.sql, 002-alter-column.sql etc.
What I want is a simple tool command line or MsBuild that will look at a database see what version the database is at (usin...
I have the following issue, I have this query that select the latest 5 records created for an employee:
SELECT TOP 5
p.value,
p.record_date AS FECHA
FROM
employee_loan_movements p
WHERE
p.employee_code = '1'
AND p.record_date <= '2009-11-11'
AND p.movement_type = 1
AND p.value > 0
ORDER BY p.record...
I am working on optimizing one of the SQL Job.
Here I have few places where we have used <> operator. THe same query can be replaced using NOT EXISTS operator. I am just wondering which is better way of doing it.
Sample Query
If(@Email <> (select Email from Members WHERE MemberId = @MemberId))
--Do Something.
--Same thing can be wr...
I am using the ROW_NUMBER() function introduced in SQL SERVER 2005 to return a paged set of results. The Query Works as expected, but I have one issue. What I would like to do is return the results ordered by the count in descending order. Here is the query and below I will give a little description:
DECLARE @StartRowIndex INT
DECLAR...
hey
i have a temp table (question_desc, ans1, ans2, ans3, ans4, correct_ans, marks) with say 10 entries
from this table i have to insert values in two other tables
questions (q_id(auto-generated), que_desc, marks)
answers (ans_id(auto_generated), q_id(FK), ans_desc, istrue)
for each insert in question table there should be four inse...
I have seen sql statements using nolock and with(nolock)
e.g -
select * from table1 nolock where column1 > 10
AND
select * from table1 with(nolock) where column1 > 10
Which of the above statements is correct and why?
...
Hi,
I'm in a position of evaluating products / approaches to build Business Intelligence Dashboards on top of Sharepoint WSS (no MOSS at this stage). Does anyone have any suggestions where would be a good place to start?
The BI platform is currently built on SQL Server 2005 / SSIS / SSRS and we're currently investigating adding SSAS to...
Hello,
I am working on a stored procedure in SQL Server 2008 for resetting an integer column in a database table. This integer column stores or persists the display order of the item rows. Users are able to drag and drop items in a particular sort order and we persist that order in the database table using this "Order Rank Integer".
...
I was just pushing a website to production, which worked fine in a QA environment. The patch involved DLL dependency updates, and some database scripts.
I will cut to the chase and explain the error that took me 4 hours to figure out. One of my database scripts created a stored procedure, which was missing [dbo] before its name. So ins...
I overwrote my Stored Procedure with a old script by mistake.
I dont want to re code everything again.
Is there any way I can get the old Stored Procedure script?
...
I have a query as below:
SELECT * FROM Members (NOLOCK)
WHERE Phone= dbo.FormatPhone(@Phone)
Now here I understand that formatting has to be applied on the variable on column. But should I apply it on variable to assign to some other local variable then use it (as below).
Set @SomeVar = dbo.FormatPhone(@Phone)
SELECT *
FROM Me...
I am dealing with Sql Server and Oracle through Qt, when using QSqlDatabase::transaction() on a database connection. When another user/connection has a transaction open on the same database does the transaction() call block until the other transaction is finished or fail ?
...