What Is Joint Indices??
May 2, 2008 hi all
Can somebody please explain to me what does it mean when someone saids use joint
indices to join tables?
thanks in advance
ADVERTISEMENT
Query Problem - To Many Joint
Jun 10, 2008CREATE TABLE [dbo].[SEL](
[MAIN_ID] [int] NULL,
[DATE_TAKEN] [smalldatetime] NULL,
[TIME] [int] NULL,
[DAILY_RAINFALL] [int] NULL
) ON [PRIMARY]
insert into SEL
values(194,'6/1/2006 12:00:00 AM',730,11)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',930,4)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',1830,10)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',1930,20)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',2130,14)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',2230,0)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',730,22)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',930,43)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',1830,0)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',1930,54)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',2130,21)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',2230,6)
CREATE TABLE [dbo].[station_info](
[STATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATION_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MAIN_ID] [int] NOT NULL
) ON [PRIMARY]
insert into station_info
values('SEL','PUCHONG',196)
insert into station_info
values('JHR','BulohKasap',5)
Above script showing table as follow
SEL
MAIN_ID | DATE_TAKEN | TIME | DAILY_RAINFALL
--------------------------------------------------------
194 | 6/1/2006 12:00:00 AM | 730 | 11
194 | 6/1/2006 12:00:00 AM | 930 | 4
..........
..........
202 | 6/1/2006 12:00:00 AM | 450 | 23
..........
..........
*This table storing DAILY_RAINFALL everyday from time to time for each MAIN_ID.
station_info
STATE | STATION_NAME | MAIN_ID
---------------------------------------
SEL | Puchong | 194
JHR | BulohKasap | 5
.........
.........
*This table storing MAIN_ID description. Main_ID is a primary key.
1. 1 day having many TIME. So, we only take which time having MAX(DAILY_RAINFALL) per day per MAIN_ID to do the SUM(DAILY_RAINFALL) for that month.
So far, i've this query
SELECT CAST(CAST(m.Month AS varchar(2)) + '/1/' + CAST(m.Year AS varchar(4)) AS datetime) AS DATE_TAKEN,m.MAIN_ID,m.DAILY_RAINFALL
FROM
(
SELECT MONTH(t.Date_Taken) AS Month,
YEAR(t.Date_Taken) AS Year,
t.Main_ID,
SUM(t.Daily_Rainfall) As Daily_Rainfall
FROM
(
SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND
t1.RowNo=1
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-12,GETDATE()),101)
AND t1.DATE_TAKEN<=CONVERT(VARCHAR(10), GETDATE(), 101)
WHERE t2.STATE='SEL')t
GROUP BY MONTH(t.Date_Taken),YEAR(t.Date_Taken),t.Main_ID)m
ORDER BY Main_ID,Date_Taken
*Assume GETDATE()=6/10/2007
After run above SQL, I got below resultset,
DATE_TAKEN |MAIN_ID|DAILY_RAINFALL
---------------------------------------------------
2006-06-01 00:00:00.000 |194 |329
2006-07-01 00:00:00.000 |194 |160
2006-08-01 00:00:00.000 |194 |388
2007-04-01 00:00:00.000 |194 |394
...........
...........
2006-06-01 00:00:00.000 |195 |145
2006-07-01 00:00:00.000 |195 |82
2006-08-01 00:00:00.000 |195 |225
...........
...........
I'm stuck when joint table below. The purpose of joint is to pickup [cumrf1year] column
(if DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-12,GETDATE()),101)
AND t1.DATE_TAKEN<=CONVERT(VARCHAR(10), GETDATE(), 101))
and pickup [month_year] value depend on DATE_TAKEN (if DATE_TAKEN=6/1/2006, [month_year]=6/30/2006, if DATE_TAKEN=7/1/2006, [month_year]=7/31/2006).
MAIN_ID is foreign key to relate [longterm_rf_temp] table below.
CREATE TABLE [dbo].[longterm_rf_temp](
[main_id] [int] NOT NULL,
[month_year] [datetime] NULL,
[cumrf1mth] [float] NULL,
[cumrf3mth] [float] NULL,
[cumrf6mth] [float] NULL,
[cumrf9mth] [float] NULL,
[cumrf1year] [float] NULL
) ON [PRIMARY]
insert into longterm_rf_temp
values(194,'6/30/2006',207.94,550.7,850.7,1150.7,1450.7)
insert into longterm_rf_temp
values(194,'7/31/2006',200.64,590.4,858.7,1260.7,1550.7)
insert into longterm_rf_temp
values(194,'8/30/2006',222.64,390.4,958.7,1460.7,1750.7)
insert into longterm_rf_temp
values(195,'6/30/2006',217.94,550.7,840.7,1150.7,1324.7)
insert into longterm_rf_temp
values(195,'7/31/2006',202.64,590.4,858.7,1260.7,1659.7)
insert into longterm_rf_temp
values(195,'8/30/2006',222.64,490.4,958.7,1460.7,1733.7)
After joint, I should get below resultset,
DATE_TAKEN |MAIN_ID|DAILY_RAINFALL | [cumrf1year]| DiFF | DEV
----------------------------------------------------------------------------------
2006-06-01 00:00:00.000 |194 |329 | 1450.7 | -1121.7 | -0.773
2006-07-01 00:00:00.000 |194 |160 | 1550.7 | -1390.7 | -0.896
2006-08-01 00:00:00.000 |194 |388 | 1750.7
2007-04-01 00:00:00.000 |194 |394 | ......
...........
...........
2006-06-01 00:00:00.000 |195 |145 | 1324.7
2006-07-01 00:00:00.000 |195 |82 | 1659.7
2006-08-01 00:00:00.000 |195 |225 | 1733.7
...........
...........
This is the formula,
DIFF = DAILY_RAINFALL - [cumrf1year]
DEV = DIFF/[cumrf1year]
I almost give up to adjust the above SQL but still fail. Please help me, i'm really-really stuck.
Combine 2 Queries In To One (joint Query)
May 3, 2007I have these 2 queries that I need to combine into one. What is the best way of doing it? This website is made up of 293 tables so it gets confusing.
(Query 1)
SELECT category_products.category, category_products.product, category_products.position, data.base_price, data.custom_description, models.manufacturer_id, models.custom_search_text, models.name, models.image_location
FROM category_products, data, models
WHERE category_products.category = '36'
AND category_products.product = data.model_id
AND data.model_id = models.id
AND data.active = 'y'
$manufacturer_id=$data["manufacturer_id"];
(Query 2)
SELECT inventory_types.manufacturer_id, inventory_types.default_vendor_id, vendors.id, vendors.name
FROM inventory_types, vendors
WHERE inventory_types.manufacturer_id = '$manufacturer_id'
AND inventory_types.default_vendor_id = vendors.id
SQL 2012 :: Joint Two Tables On A Non-key Field?
Jul 4, 2015I have two tables tbl1 and tbl2, which I do a full outer join between tbl1 and tbl2 on recordId field. The recordId field is not a key in either of the tables.
If there is one row each for a recordId 123 in both tables, the select query would return one combined row.
If tble1 had two rows for recordId 123, and tbl2 had one row for the same, it would return to rows repeating the data in tbl2.
If tbl2 had two rows and bl1 had one row, it would return two rows in output repeating the data in tbl1.
Is the above correct? Would the result be different if it was an inner join instead of full outer join?
Is it ever possible that one of the two records with recordId 123 will be dropped from the result?
Nested Select, And Table Joint
Sep 14, 2006Hi Guys
Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:
OPBal| Receipt| IssTrns| Transfer| ClBal
SELECT dbo.inventory.location, dbo.inventory.itemnum,
(select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt,
( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer,
( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal,
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,
( sum(matusetrans.linecost) ) As IssTrnf
FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item
ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )
WHERE dbo.inventory.location = dbo.matusetrans.storeloc
AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid
OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc
OR
dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum
GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost),
dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost
T-SQL (SS2K8) :: How To Divide Amount In Joint Account
Mar 6, 2014I need to divide amount in joint account. So if joint account has 2 account holders & amount is 35622.15 then one person should have 17811.08 and other person should have 17811.07
If I used below query it just give me 17811.08 for both account holders so when we sum it it's one penny extra.
select cast((35622.15/2) as decimal(15,2))
Is there any way i can achieve this.
Dynamically Drop Indices
Nov 22, 1999Hi,
I thought I'd post this question again to see if I get a response this time. I am trying to figure out how to write a sp to drop all of a given tables indices. I have looked at the sp_helpindex text and see where the index name, index ID, table ID etc. is determined...no problem there. What I am having difficulty with is the actual DROP INDEX statement 'cause somehow I need to concatenate the table name to the index name and have the statement work...something like DROP INDEX @tablename + '.' + @indexname (which I can't get to work...sorry about my lack of SQL Srvr knowledge!). I was hoping someone might know if this can be done and how to do it.
Any help, even if it is to tell me this can't be done, would be greatly appreciated!!!
Thanks in Advance....
Kevin A
Moving Indices To New Filegroup
Mar 26, 2002I have created a new filegroup and would like to move my indices there. Do I need to create a file for each index or can I just specify the filegroup in Enterprise Manager?
Thanks!
Ellen
New Sql Server Application Without Indices?
Nov 22, 1998Recently, I assumed responsibility for a SQL Server 6.5 application( I am an Oracle DBA).
This application is being purchased from a vendor. It is replacing an Oracle application.
I did not make the decision for accepting this vendor's application
Yesterday, I had to call the vendor as I had a question. It turns out that the application
--with about 70 tables -- will not have any indices, only primary keys that will be utilized
through Powerbuilder. There will be no foreign keys as all such constraints are
handled through PB code.
I have dealt with many different applications over the years and under different platforms
but I have never encountered an application where there are no indices. I have seen
application with way too many and even some with too few. Never an application
without any.
Has any one ever worked with, or dealt with, an application with no indicies? Is it
practical to attempt to use native PB code to replace indicies? If yes, was the application
running efficiently? Was everything through table scans? Any advise on how to deal with this vendor?
Right now there are no procedures or function -- just native sql code on the theory everything is faster
and more efficient? May be?
Thanks for any information.
David Spaisman
How Would You Find Out Whether A SQL Query Is Using The Indices You Expect?
May 16, 2004Hello, everyone:
This is testing question from a tesing seb site. Any reply will be appreciated.
ZYT
Size Of SQLServer Databases With Indices
Jul 20, 2005Hello,I am starting to examine using SQLServer instead of Oracle. I wentthrough the SQLServer import utility to copy tables from Oracle intoSQLServer. I then checked the size of the database.I then started to specify indices for the tables. I did not see thedatabase file size grow all that much. I kept on using the shrinkcommand to make sure that wasted space was removed. I even made surethat minimal free space was allowed.I was rather expecting (from experience with Oracle) that there wouldbe a large growth in the database when addind indices. So what did Imiss? Are the indices stored elsewhere and I missed them? DoesSQLServer handle indices differently so they dont' bloat the databasesize?The size of our data in SQLServer is a key factor in whether we movefrom Oracle, so I need to under how indices effect storage size.Thanks for any help.Bart TorbertJoin Bytes!
View 7 Replies View RelatedCorrect Way To Create Indices? SQLServer Express 2005
Feb 22, 2006Hi,
Apologies if this has been asked before, i've done a search but can't find a definitive answer.
I've created a table in an SQLExpress 2005 db using Server Managment Studio Express.
My intention is to use GUID fields as surrogate PK's. I therefore wanted to add a additional index to prevent duplicate records being added to the table. Not having used SQLServer before could someone confirm or deny that this is the correct way to do this. The PK field [EPISODEID{unique identifier}] is set as a non-clustered index. And i've created a second clustered index using the two fields that create a unique record. I've added a screen shot if that is any help.
Thanks
Chris
SQL Server 2008 :: Identify Which Indices Are Replicated - Transactional Replication
Feb 20, 2015Is there a script to find which non-clustered indices are replicated? I know i can do this easily through GUI , having a script will make my life much easier ....
View 0 Replies View RelatedReplication :: Drop One Of Larger Table Indices On Subscriber Database
Jul 31, 2015We have a database we are replicating to about 8 SQL Express subscribers from a SQL 2012 SP2 publisher. The size of the database grew too large for the 10GB license limit for SQL Express and now replication refuses to replicate any of our deletions on the publisher to reduce the size of the database. I've come up with a few options below.
1) Drop one of the larger table indices on the subscriber database to get below the size restriction. Permit the replication to replicate the deleted records and then rebuild the index. (I'm not sure how important an index is to this table. Is it merely performance related?)
2) "Upsize" SQL Express to SQL Standard on the affected boxes. Allow the deletes to replicate. Backup the database, downgrade to SQL Express and restore the database back to SQL a new SQL express instance. This would involve a lot of work on each box. I'd like to avoid it if possible.
SQL Server Admin 2014 :: Updating Statistics On Specific Indices Script
Aug 8, 2015I'm looking for a quick script that someone has already written to update statistics (not to rebuild or re-organise) on specific indices in specific databases - I guess loop though a table comprising of a list of databases and the indices.
I know Ola has one but I'm not look for something that is that complicated. If I cannot find one I'm going to have to write one myself - I want to try and avoid re-inventing the wheel as tomorrow I have to do this work and it's about 7K plus indices in about 10+ databases.