|
Kindly let me know how to make updateble views on a table and how to apply Indexes on them??? i will be very thankful if anyone can give me the exact syntax for whole..
Thanks in Advance..
|
|
|
|
|
Creating an index on a view uses the normal CREATE INDEX syntax. For considerations, you should look at: Creating an Indexed View[^]
Also note that this feature is in Enterprise Edition only.
|
|
|
|
|
advantage and disadvantage of it??
thanks in advance.
|
|
|
|
|
In short, worker thread is the actual thread serving a call to the database.
Aman786Singh wrote: advantage and disadvantage of it
There's no choice in this. This is how SQL Server works.
|
|
|
|
|
How to get which process is deadlocking the system??
how to know current connections establish??
Thanks in Advance.
|
|
|
|
|
What is it and benefits of it???
Thanks in Advance.
|
|
|
|
|
It's designed to decrease context switches in multi CPU environment, thus giving a little bit better performance in certain, very specific situations. Normally there's no need to use it (or at least lots of optimizations should be done before this is considered).
For more info: The Perils of Fiber Mode[^]
|
|
|
|
|
Goodness, am I just thick or why can't I seem to get my head around GROUP BY!?
Let's use the Northwind DB as an example and let's say I want to join the Orders and Customers tables and return the first row for each customer. In other words, what I want is the first order that every customer placed, but inner joined to the customer table so that the customer's details is also returned.
This sounds simple enough but I just can't seem to make it work. The fact that I want the first order for each customer suggested to me that I should use something like SELECT TOP 1 * and have the GROUP BY CustomerID clause in there somewhere but then the result set would be rife with columns that are not contained in aggregate functions or the GROUP BY clause.
Would anyone care to offer advice here? I'm just using the Northwind DB as an example because I'm hoping that most people would be familiar with it but what I'm actually trying to achieve is to extract the first observation plus some details about the bird from a birdwatching database in which, roughly speaking, the Observations table would be equivalent to the Orders table and BirdList table would be equivalent to the Customers table.
|
|
|
|
|
GROUP BY isn't necessarily the easiest way to do this (although it's possible). Since you want to join two tables and when joining retrieve only the first row from the other table, I would use something else than GROUP BY (basically because I'm not calclulating anything, sums , averages, counts etc, but just restricting rows)
First version could be something like:
SELECT ...
FROM BirdList bl,
INNER JOIN
Observations o
ON bl.BirdId = o.BirdId
WHERE NOT EXISTS (SELECT 1
FROM Observations o2
WHERE o2.ObservationDate < o.ObservationDate
AND 02.BirdId = o.BirdId)
So fetch rows using normal join but then use correlated subquery test to check that there are no earlier records.
Didn't know the actual names of your columns so they are propably nonsense.
|
|
|
|
|
Excellent suggestions, thanks! I really should have thought of this before I even started to think along the lines of GROUP BY.
|
|
|
|
|
You're welcome 
|
|
|
|
|
Hi, I am planning to host my owns websites. and all are in .net with ms SQL, I want to by MS SQL 2005 but all about license in microsoft is so confusin, Question
If I want to Use the ms SQL only for websites, what version I should get?
Standard?
thanks
|
|
|
|
|
The licensing is a bit complicated. Why not contact microsoft sales or local dealer directly?
|
|
|
|
|
|
how to search amharic unicode data from sql server?
|
|
|
|
|
Have you tried using N character before strings? For example:
WHERE Column = N'text to search'
|
|
|
|
|
Hello EveryOne!!!
I am using Sql server 2005.
I have one table containing one of the column of type "DateTime".
Can i insert data like "Dec-08" in it ?.
and how ?
Thanks in advance.
DaTtA. 
|
|
|
|
|
Datetime data type always contains both date and time portion.
Depending how you want to use it, you can make a decision to use for example only month and year and always set day to 1. For example you can use CONVERT function to convert literal month with year to datetime. One example is:
SELECT CONVERT( datetime, '01-DEC-08', 6)
For more info on usage see: CAST and CONVERT[^]
|
|
|
|
|
Thanks a lot
Nice answer but suppose i want it in the form
if i want to insert 'DEC-08-2009' into DateTime then what should i do ?
|
|
|
|
|
Did you have a look at CONVERT link? The last parameter defines the date format that's used in the string literal. There are only few formats SQL Server recognizes as listed in the covert description.
If you want to use custom format, you could use substring to break the string to different parts and then reformat the parts to a format recognized by convert.
|
|
|
|
|
Hi all,
I am being struglling with a query from past 2-3 days and i m unable to find a solution to it.
I am using sql 2000 and i am having the foll probs.
I have a table "FG_STK" which stores the stock of all items branch wise. The schema is
FG_ID,BranchID,Item_Code,Item_Batch_No,Item_Lot_No,Recon_Code,CLD_Qty,Sku_Qty
and other table called Recon_Codes which has foll schema..
Recon_Code,Recon_Type,Recon_Description
which has predefined values such as
100 CR Opening Stock
200 DR Stock Removed
8000 DR Damage Material Transfer
9000 DR Expired Material Transfer
and in FG_STK the data stored as
1, 2001,350154,Mar809,Mar1208,100,100,0
(item 350154 at branch 2001 with batch Mar809 and Lot Mar1208 with Opening stock of 100 Clds)
2,2002,350154,Feb0809,Feb1208,8000,0,200
(item 350154 with loose qty 200 at branch 2002 with batch Feb0809 and Lot Feb1208 is removed for the purpose of transfer because its damaged)
The rows in FG_STk with recon code not starting with 8 and 9 are considered as good stock, while rows with recon code starting with 8 are considered as damaged and with 9 are considered as expired.
Now to fetch good clds i did the following
Select item.Brand,item.Category,item.Item_Desc as Item,stock_data.batchno,stock_data.lotno,isnull((SUM(CR) - SUM(DR)),0) as Total_Clds,isnull((SUM(CRSKU)-SUM(DRSKU)),0) AS Tot_Sku
From(
SELECT Case Recon_Type
When 'CR' Then SUM(CLD_Qty)
When 'DR' Then 0 END CR,
Case Recon_Type
When 'CR' Then 0
When 'DR' Then SUM(CLD_Qty) END DR,
Case Recon_Type
When 'CR' Then SUM(Sku_Qty)
When 'DR' Then 0 END CRSKU,
Case Recon_Type
When 'CR' Then 0
When 'DR' Then SUM(Sku_Qty) END DRSKU,Item_Code,Item_Batch_No as batchno,Item_Lot_No as lotno
FROM FG_STK INNER JOIN Recon_Codes ON FG_STK.Recon_Code = Recon_Type.Recon_Code
where BranchID=@branch_ID
GROUP BY Recon_Type,Item_Code ,Item_Batch_No,Item_Lot_No
)stock_data
inner join
view_Full_Item_Details item
on
stock_data.Item_Code=item.code
group by stock_data.Item_Code,Item_Desc,stock_data.batchno,stock_data.lotno,item.Brand,item.Category
having ((SUM(CR) - SUM(DR))<>0 or (SUM(CRSKU)-SUM(DRSKU))<>0)
Now the problem is i want damaged and expired along with good stock in diff columns which has almost the same query with an additional where clause as
and Recon_Codes.Recon_Code like '8%' for damaged
and
and Recon_Codes.Recon_Code like '9%' for expired
and i want to show data as
Brand, Category, Item,Batch_no,Lot_no,Good_Cld,Good_Loose,Damaged_Cld,Damaged_Loose,Expired_CLD,Expired_Loose
also if the batch and lot of good cld, expired cld and damaged cld are same they should appear in same row else in diff row.
Any help would be kindly appreciated.
When you fail to plan, you are planning to fail.
|
|
|
|
|
It's quite hard to say the exact solution with such small amount of data, but could you simply add the conditions to the inline view and use case to differentiate good, expired and damaged amounts. If I understood the idea correctly you would use double condition in case. Something like:
WHEN RECON_TYPE = 'CR' THEN ...
...
WHEN RECON_TYPE = 'DR' AND RECON_CODE NOT IN (8000, 9000) THEN ...
...
WHEN RECON_TYPE = 'DR' AND RECON_CODE = 8000 THEN ...
...
WHEN RECON_TYPE = 'DR' AND RECON_CODE = 9000 THEN ...
...
Also if you have the possibility to re-check the E/R-model, I think there would be easier ways to handle the data than how it's currently done.
|
|
|
|
|
Hello Friends,
I've a table in which there are two columns Name and Salary as described below.
Name Salary
A 100
B 200
C 300
D null
I want to increment the salary by 100 of all employees. For it i'm using the command given below
Update Rough Set Salary=Salary+100 Where Salary in (Select Salary From Rough)
But this query is just updating the Salary of A,B,C not of D because of null So can anybody let me know how to update the value of null as well
|
|
|
|
|
You could use COALESCE function. Something like:
Update Rough
Set Salary=COALESCE(Salary, 0) + 100
|
|
|
|
|
Well, firstly that where clause is bonkers. If you want to update all records you want to do away with that.
try this:
update Rough set Salary = isnull(Salary, 0) + 100
It's a good idea to avoid nullable columns where they're not appropriate or you end up having to do stuff like this all over the place.
Regards,
Rob Philpott.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
