DB Design :: Optimize A Query That Uses A Varchar Column That Is Used In Order By Clause
May 5, 2015
I am querying a tableA with 1.8 million rows, it has id as its primary key and is a clustered index. I need to select all rows where I order by lastname. Its taking me 45 seconds. Is there anything i can do to optimize the query.Will creating a fulltext index on lastname If so, can you give me an example on how to create a full text index on lastname?
[Project1].[Id] AS [Id],
[Project1].[DirectoryId] AS [DirectoryId],
[Project1].[SPI] AS [SPI],
[Project1].[FirstName] AS [FirstName],
[Project1].[LastName] AS [LastName],
[Project1].[NPI] AS [NPI],
[Project1].[AddressLine1] AS [AddressLine1],
[Project1].[AddressLine2] AS [AddressLine2],
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM ... ... ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index]
Table:ColumnsUsersList:UserID, UserName, Country I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order So the query first return all the users from Pakistan and the the users from rest of the world in ascending order. Forexample, 1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan then the query returns. 3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA
I could write a query with a sub-query in order to perform an UPDATE on the most recent 60,000 records of a table based on a date field, but unfortunately I am receiving an error.
Code: SELECT * FROM DMTM SET transmit_date = '2012-05-07 00:00:00.000', transmit_status = '1223' WHERE temp_pk in
Hi all,Just wondering if anyone can tell me if an order by clause on a selectquery would have any impact on the time it takes to retrieve results?Essentially I'm selecting Top 1 out of a table via various criteriaand currently getting it back without an order by clause. The order bywould only include the column that has the clustered primary index onit.Can anyone tell me if in theory this will slow the query down?Many thanks in advance!Much warmth,Murrau
In my BM_Maps subform, there is a combo box called called Borders. I want the borders to be filtered based on product series, so if the user picks "City Guide", they only get city guide borders, so I have added a table called Product_Series_X_Border which has the product series and border id.
I have a query which joins two tables, and I've ordered it by the border name, which works fine, until I add a where clause for the product series from the other table. I have attached a screengrab which I hope works. I have tried joining other tables but it still doesn't work. Is there any other way I can order by border name with the where clause? I've tested with individual product series as the where clause with the same result.
We have 4 phases(scenarios), the no of attributes in each scenario is unknown(dynamic) ex: Scenario1 & case1 = 10,Scenario1 & case2 = 50,Scenario2 & case1 = 70,Scenario1 & case6 = 100
We want to give preference in this sequence... 1. Performace 2. More customization 3. HDD Space 1) For more customization, we need to maintain all the phases' details in same table like id,name,description,textfield. In case of more fields, copy all the values to text fields by seperating with delemeters(,) like: 10,'Name1','Description1','110,abc,xyz,nmo,pqz'. In this approach we can save n number of values. Out of 10 lakh records, 600-900 records will be filtered by id and name in average. Will it cause any performance problem while make the text and extract the individual values? What about searching?
2) For performace, we will have a big table with fixed no of tables say 300(no exendability beyond this & most of the columns will be NULL if values are less).
3) Create seperate tables dynamically for each schenario and case, save the values in curresponding tables.(can be a chance of getting 300-700 tables)
Please suggest me best among all these. Thanks in advance...
on executing the below query i am getting the following error
ERROR: Errors in the back-end database access module. Nested table keys in a SHAPE query must be sorted in the same order as the parent table. The nested query may be missing an ORDER BY clause.
even though the order by clause is presenet in the nested query
SELECT t.[ProductId], Predict ([Association].[Product Basket],3) From [Association] PREDICTION JOIN SHAPE { OPENQUERY([Adventure Works Cycle MSCRM], 'SELECT DISTINCT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')} APPEND ({OPENQUERY([Adventure Works Cycle MSCRM], 'SELECT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')} RELATE [ProductId] To [ProductId] ) AS [Product] AS t ON [Association].[Product Id] = t.[ProductId] AND [Association].[Product Basket].[Product Id] = t.[Product].[ProductId]
Hi, I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow... Can anyone help? Thanks in advance Gemma
The challenge that the spectrum of varchar lengths across the table. I have one attribute that requires varchar(max) and all other attributes (about 40) are varchar (200).
I created two fields (Old_ValueAtrLong and New_ValueAtrLong) dedicated for the one attribute that is a varchar (max). I was trying to avoid storing [Status] for example that's a varchar(200) in a field that is varchar(max). Is this the right approach? Or are there other recommendations in how to handle storing the data in the most efficient manner?
I have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons');
then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75));
select * from test_sort order by cast( description as nvarchar(75));
Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty FROM T1 t1 WHERE MaxQty>=10
But it fails as computed or inline query columns can not be added in where clause.
However following works: Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty FROM T1 t1 WHERE (Select Max(Qty) From T2 where ID=t1.ID) >=10
BUT IS IT OPTIMIZED? Please suggest an optimized way to handle such scenarios.
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error: [OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
date item_id grade in out ------------- --------------- ------- -------- ---------- 01-01-08 001 A 10 0 02-01-08 001 O 8 0 01-02-08 002 O 1 0 03-01-08 001 T 0 10 02-01-08 003 O 20 0 02-01-08 003 T 0 10 02-01-08 003 B 0 8
Result View =======
Item_id A B O T Total
001 10 0 8 -10 8 002 0 0 1 0 1 003 0 8 20 -10 18
where grade could be any Alphabet, the column of result query could varies each grade contain sum of (in-out) of all item_id of detail table.
I’m facing performance related problem while running following query on SQL Server 2000.
This query is basically used to find last location of each unit that are passed. Here I am passing data like “‘26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50'" in @Units variable. But it takes too much time and I don’t get output. Table is having around 5 Million records.
Query:
SELECT Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid FROM tblUnit INNER JOIN tblUnitHistory ON tblUnit.UnitID = tblUnitHistory.UnitID WHERE tblUnitHistory.UnitHistoryDate IN (SELECT MAX(UnitHistoryDate) FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @Units + ') GROUP BY tblUnitHistory.UnitID) AND tblUnit.UnitID in (' + @Units + ') ORDER BY tblUnit.UnitID
Table Structure:
UnitHistoryID int Primary Key UnitID int Location varchar(200) Latitude decimal 9 Longitude decimal 9 Speed decimal 5 BatteryVoltage decimal 5 ReasonCode int DistanceFromLastLocation decimal 9 UnitHistoryDate datetime
Indexes:
1. Clustered Index on Column UnitID 2. Non-clustered Index on Column UnitHistoryDate 3. Non-clustered Index on Column UnitHistoryID
Please help me to write optimize query and suggest me the steps to require making this query faster. Any help is appreciated. Thanks in advance.
I have a nightly job that aggregates data into a summary table. Each night, an additional item may or may not be added, so the table is cleared and repopulated. I've got this working fine. Now, I am being asked to track when an item first appears in the summary table. Here is the code I am using to do this. I realize it is very poor performance, but I don't know how to make it better. MyTable Columns: id INT PK ,v1 VARCHAR(4) ,v2 VARCHAR(10 ,v3 DECIMAL ,item1 BIT ,item2 BIT ,item3 BIT MyTable2 Columns: v1 VARCHAR(4) ,v2 VARCHAR(10 ,v3 DECIMAL ,item INT ,FirstKnownDate DATETIME All columns are NOT NULL.1 DECLARE @iNextRowID INT 2 ,@iCurrentRowID INT 3 ,@iLoopControl INT 4 ,@v1 VARCHAR(4) 5 ,@v2 VARCHAR(10) 6 ,@v3 DECIMAL 7 8 /* This is a loop that executes for a dynamic number of items, eg item1=1, then item2=1, etc */ 9 SET @iLoopControl=1 10 SELECT @iNextRowID=MIN(id) FROM MyTable WHERE item1=1 11 12 IF ISNULL(@iNextRowID,0) <> 0 BEGIN 13 SELECT @iCurrentRowID = id 14 ,@v1 = v1 15 ,@v2 = v2 16 ,@v3 = v3 17 FROM MyTable 18 WHERE id=@iNextRowID 19 20 WHILE @iLoopControl = 1 BEGIN 21 IF NOT EXISTS(SELECT * FROM MyTable2 WHERE v1=@v2 AND v2=@v2 AND v3=@v3 AND item=1) BEGIN 22 INSERT INTO MyTable2 23 /* COLUMN */ (v1,v2,v3,item,firstknowndate) 24 SELECT @v1, @v2, @v3, 1, GETDATE() 25 END 26 27 SELECT @iNextRowID = NULL 28 SELECT @iNextRowID = MIN(id) FROM MyTable WHERE id>@iCurrentRowID AND item1=1 29 30 IF ISNULL(@iNextRowID,0) = 0 BEGIN 31 BREAK 32 END 33 34 SELECT @iCurrentRowID = id 35 ,@v1 = v1 36 ,@v2 = v2 37 ,@v3 = v3 38 FROM MyTable 39 WHERE id=@iNextRowID 40 END 41 END42 /* This is the end of the items loop */ This query takes 5 minutes to do straight inserts without the test for exists in the loop for 3 items. MyTable has just under 96,000 rows in it. MyTable2 has 121,857 rows in it. It is not about 55 minutes and it is still running with the exists so that a row isn't inserted twice. Is there anything I can do to optimize this? EDIT: The query just finished running again with the test for exists on each row. It shouldn't have inserted any new rows, but it took 57 minutes and inserted another 114,115 rows. Not good. Anyone see what is wrong with the query?
SELECT s.konto, s.sifra_partnera, s.temeljnica, s.rbr, s.duguje, s.potrazuje, s.saldo FROM saldakonti s WHERE s.datum<'2005-01-01' AND s.uplata='R' AND EXISTIS (SELECT t.temeljnica, t.rbr FROM saldakonti t WHERE t.konto=s.konto AND t.sifra_partnera=s.sifra_partnera AND t.vezni_broj=s.vezni_broj and t.datum<'2005-01-01' AND (t.uplata='S' or t.uplata='U') GROUP BY t.temeljnica, t.rbr HAVING abs(sum(t.duguje-t.potrazuje))<>abs(s.saldo)) ORDER BY s.konto, s.sifra_partnera, s.temeljnica, s.rbr, s.duguje, s.potrazuje, s.saldo
Currently this query takes forever to execute. Any suggestions appreciated. Thanks in advance.
Can someone look at this sql statement and tell me if it can be sped up? Also I have to add to it by joining it with another table. How do I do that? Just by nesting another join?
SELECT TOP 101 Cert_WarehouseDetails.CertID, Cert_WarehouseDetails.UnderwriterID, Cert_WarehouseDetails.WarehouseEntriesID, Cert_WarehouseDetails.DivisionWarehouseLocID, Cert_WarehouseDetails.TypeID, Cert_WarehouseDetails.ReportingType, Cert_WarehouseDetails.CertWHID, Cert_WarehouseDetails.ClientPolicyDivisionID, Cert_WarehouseDetails.RecordNO, Cert_WarehouseDetails.InsuredValueX, Cert_WarehouseDetails.PremiumTotalX, Cert_WarehouseDetails.StatusX, Cert_WarehouseDetails.StatusID, Cert_WarehouseDetails.AuthorizeDateX, Cert_WarehouseDetails.CodeX, Cert_WarehouseDetails.IssuedDateX, Cert_WarehouseDetails.BillofLadingDateX, DOGX, Cert_WarehouseDetails.ConveyanceNameX, Cert_WarehouseDetails.LogonNameX , Cert_WarehouseDetails.ClientNameX, (CASE WHEN UnderwriterID = 0 THEN ' ' ELSE tblClientUsers.FirstName + ' ' + tblClientUsers.LastName END) AS OwnedBy, (CASE WHEN UnderwriterID = 0 THEN CAST(' ' AS NVARCHAR) ELSE CAST(TakeOwnershipDate as nvarchar) END) AS OwnedDate
FROM ( SELECT tblCertificates.[CertID] AS CertID, tblCertificates.[UnderwriterID] AS UnderwriterID, tblReportingType.[TypeID] AS TypeID, tblReportingType.[ReportingType] AS ReportingType, NULL AS WarehouseEntriesID, NULL AS DivisionWarehouseLocID , tblCertificates.CertID AS CertWHID, tblCertificates.ClientPolicyDivisionsID AS ClientPolicyDivisionID, tblCertificates.CertificateNo AS RecordNO, tblCertificates.TotalInsured AS InsuredValueX, (CASE WHEN tblCertificates.Status=101 or tblCertificates.Status=104 or tblCertificates.DivReferral=1 THEN 0 ELSE PremiumTotal-tblCertificates.Discount END) AS PremiumTotalX, tblStatus.Description AS StatusX, tblStatus.StatusID AS StatusID, (CASE WHEN tblCertificates.Status < '105' and tblCertificates.Status <> '103' THEN null ELSE AuthorizeDate END) AS AuthorizeDateX, tblCurrency.Code AS CodeX, tblCertificates.IssuedDate AS IssuedDateX, tblCertificates.BillofLadingDate AS BillofLadingDateX, tblCertificates.DescriptionofGoods AS DOGX, tblCertificates.ConveyanceName AS ConveyanceNameX, tblClientUsers.LogonName AS LogonNameX , tblClient.ClientName AS ClientNameX, tblCertificates.TakeOwnershipDate AS TakeOwnershipDate, tblCertificates.ClientID AS ClientID, tblCertificates.Producer AS BrokerID, tblCertificates.SBU AS SBU, tblCertificates.AssociationID AS AssociationID, tblCertificates.AssuredName AS AssuredName, tblCertificates.UserID AS UserID, tblCertificates.Demoflag AS Demoflag FROM tblCertificates, tblReportingType,tblcurrency,tblClientUsers,tblCli ent,tblStatus WHERE tblCertificates.reportType = tblReportingType.TypeID AND tblCertificates.PremiumCurrencyType = tblCurrency.ID AND tblclientUsers.UserID = tblCertificates.UserID AND tblcertificates.ClientId = tblclient.ClientID AND tblStatus.StatusID = tblCertificates.Status UNION SELECT NULL AS CertID, NULL AS UnderwriterID, tblReportingType.[TypeID] AS TypeID, tblReportingType.[ReportingType] AS ReportingType, tblWarehouseEntries.[WarehouseEntriesID] AS WarehouseEntriesID, tblWarehouseEntries.[DivisionWarehouseLocID] AS DivisionWarehouseLocID , tblWarehouseEntries.WarehouseEntriesID AS CertWHID, tblWarehouseEntries.ClientPolicyDivisionID AS ClientPolicyDivisionID, tblWarehouseEntries.WarehouseEntriesID AS RecordNO, (CASE WHEN ValueCurrencyType=0 THEN 0 ELSE UnitValue END) AS InsuredValueX, (CASE WHEN tblWarehouseEntries.StatusID=101 THEN 0 ELSE PremiumSum END) AS PremiumTotalX, tblStatus.Description AS StatusX, tblStatus.StatusID AS StatusID, (CASE WHEN tblWarehouseEntries.StatusID < '105' THEN null ELSE ApprovalDate END) AS AuthorizeDateX, tblCurrency.Code AS CodeX, tblWarehouseEntries.IssuedDate AS IssuedDateX, tblWarehouseEntries.PeriodEndDate AS BillofLadingDateX, LocName AS DOGX, '' AS ConveyanceNameX, tblClientUsers.LogonName AS LogonNameX , tblClient.ClientName AS ClientNameX, tblWarehouseEntries.TakeOwnershipDate AS TakeOwnershipDate, tblWarehouseEntries.ClientID AS ClientID, tblWarehouseEntries.BrokerID AS BrokerID, tblWarehouseEntries.SBU AS SBU, tblWarehouseEntries.AssociationID AS AssociationID, tblWarehouseEntries.AssuredName AS AssuredName, tblWarehouseEntries.UserID AS UserID, tblWarehouseEntries.Demoflag AS Demoflag FROM tblWarehouseLoc, tblWarehouseEntries, tblReportingType,tblCurrency ,tblClientUsers,tblClient, tblDivisionWarehouseLoc,tblStatus WHERE tblWarehouseEntries.reportTypeID = tblReportingType.TypeID and tblWarehouseLoc.WarehouseLocationID = tblDivisionWarehouseLoc.WarehouseLocID and tblDivisionWarehouseLoc.DivisionWarehouseLocID = tblWarehouseEntries.DivisionWarehouseLocID and tblWarehouseEntries.PremiumCurrencyType = tblCurrency.ID and tblWarehouseEntries.UserID = tblClientUsers.UserID and tblClient.ClientID = tblWarehouseEntries.ClientID AND tblStatus.StatusID = tblWarehouseEntries.StatusID
) AS Cert_WarehouseDetails LEFT JOIN tblClientUsers ON Cert_WarehouseDetails.UnderwriterID = tblClientUsers.UserID
WHERE 1 = 1 AND Cert_WarehouseDetails.Demoflag = 0 and (convert(datetime,convert(nvarchar,Cert_WarehouseD etails.IssuedDateX,101)) >= '1/1/2003') and (Cert_WarehouseDetails.IssuedDateX is not null and convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/31/2004') ORDER BY Cert_WarehouseDetails.RecordNO
In above query,as 1. Union (INNER) query returns me 200000 records.
2. If I run above query it takes 18 sec because no index on Cert_WarehouseDetails.RecordNO and i'm ordering on that.
3. When I avoid to 'ORDER BY Cert_WarehouseDetails.RecordNO' then query takes 2 sec.
4. In this case Do I have to use 'nvarchar' convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/31/2004')
5. Why do we have to use 'nvarchar' can you explain to me in above statement..
Can you provide me alternate solution so I can make it fast.
Hi,I have these two tables in a DatabaseITEMSIDnumeric (Primary key)ZDIDnvarchar 3 (not null)IDF_Familynumeric(not null)Descriptionnvarchar40 (not null)DATASIDnumeric(Primary Key)IDF_Itemnumeric(Foreign key)IDF_Referencenumeric(Foreign Key)[Date]smalldatetime(not null)Containernchar10(not null)Averagedecimal(not null)[%Compliance]decimal(not null)[%OutOfRange<MinTg]decimal(not null)[%OutOfRange>MaxTg]decimal(not null)Targetdecimal(not null)[Min]decimal(not null)[Max]decimal(not null)The table DATAS has 4000000+ recordsI'm running this query:SELECT DISTINCT I.ID, I.ZDID, I.IDF_Family, I.DescriptionFROM Items as I, Datas as DWHERE D.IDF_Item = I.ID AND I.IDF_Family = 84AND D.Date BETWEEN '5/18/2004' AND '5/18/2004'it's taking 4-5 minutes to run.The result is correct, there is no thing on that date.I've done a reindex, but still the same thing.What can I do?Thanks
Hello,I am trying to find all records that have matching detail_1 or detail_2columns. This is what I have now:select t1.id, t1.detail_1, t1.detail_2, t2.id from user_details t1,user_details t2where t1.detail_1 = t2.detail_1 or t1.detail_2 = t2.detail_2;Using smaller tables of around 1000 records, I have verified that thisindeed does the job. However, when I apply it to the real table thathas around 40,000 records, it takes so long that I have to stop thequery.The table in question is quite simple and is created as follows. Thereare no indexes on the table:create table user_details (id integer, detail_1 varchar(50), detail_2varchar(50)Is there a way to make it go faster?
Hi! I have an optimisation problem. I've currently got a query that retrieve about 10K records according the mode that user difined earlier.
At the very beginning I've wrote the same code through the user-defined functions, but I found in publications that it's a wrong way to develop with MSSQL, but it worked more and more faster (3 sec). Now I rewrite my query with inline table functions...CREATE FUNCTION ans_app_name1()
RETURNS TABLE AS RETURN (SELECT CASE WHEN OBJECT_ID('app_name') > 0 THEN (SELECT TOP(1) app_name FROM app_name WHERE spid = @@spid) ELSE NULL END AS app_name ) GO
CREATE FUNCTION ans_get_user_property1( @property_name VARCHAR(255)) RETURNS TABLE AS RETURN (SELECT u.value AS user_property FROM user_property u JOIN ans_app_name1() a ON a.app_name = u.app_name WHERE property = @property_name AND username = system_user) GO
CREATE FUNCTION ans_get_mode() RETURNS TABLE AS RETURN (SELECT CAST(user_property AS INT) AS mode FROM ans_get_user_property1('mode')) GO
CREATE FUNCTION ans_get_state_date() RETURNS TABLE AS RETURN SELECT CASE WHEN ((SELECT mode FROM ans_get_mode()) IN (3,6)) THEN CAST(user_property AS DATETIME) END AS state_date FROM ans_get_user_property1('state_date') GO
ALTER FUNCTION is_real1( @mode INT, @state_date DATETIME) RETURNS TABLE AS RETURN ( SELECT uid, CASE
WHEN (begin_date = 0) or (end_date = 0) THEN 0
WHEN ((@mode IN (1,5)) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP)))
OR ((@mode IN (2,5,6)) AND (begin_date IS NULL) AND (end_date IS NULL) AND ISNULL(is_committed, 0) = 0)
OR ((@mode IN (3,6)) AND (begin_date <= @state_date) AND ((end_date IS NULL) OR (end_date > @state_date)))
OR ((@mode = 4) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))) THEN 1 ELSE 0 END AS is_real FROM host ) GO
ALTER view v_host as SELECT h.uid, h.id_histland, h.id_group, h.numporyadok, h.numgroup, h.objidentnum, h.egrokn_reg_num, h.kad_num, h.ansname, h.objname, h.id_objprotstatus, h.id_kategory, h.id_objkind, h.id_histcultvalue, h.uid_objautor, h.createdate, h.id_scientifictype, h.obj_description, h.id_admdistrict, h.cityblock, h.id_settlement, h.id_admdistrict2, h.p_index, h.id_street, h.housenum, h.housenumpart, h.houseliteranum, h.buildingnum, h.corneradress, h.appartmentnum, h.id_urbanblock, h.id_microdistrict, h.kadblock, h.end_date, h.objlocation, h.parent_uid, h.kaddistrict, h.mundistrict, h.is_committed, h.order_uid, h.begin_date, h.cr_date, h.order_uid_end, h.uid_parent_host, h.is_host_object, h.id_funcassign, h.id_immovobjtype, h.hist_data, CASE WHEN h.obj_code is NULL THEN '<object registered>' ELSE h.obj_code END AS obj_code, s.sname AS sname_objprotstatus, k.sname AS sname_kategory, ok.sname AS sname_objkind, p.sname AS name_property, ISNULL(h.ansname + ' ', '') + h.objname as fullname, h.pre_material, h.hist_view_change, h.adr_id, h.street_id, h.cod_mun, h.hist_object_id, h.scientifictype FROM host h LEFT JOIN sl_objprotstatus s ON h.id_objprotstatus = s.id LEFT JOIN sl_kategory k ON h.id_kategory = k.id LEFT JOIN sl_objkind ok ON h.id_objkind = ok.id LEFT JOIN sl_property p ON h.id_property = p.id --JOIN is_real1((SELECT mode FROM ans_get_mode()), (SELECT state_date FROM ans_get_state_date())) r ON r.uid = h.uid WHERE (CASE
WHEN (begin_date = 0) or (end_date = 0) THEN 0
WHEN (((SELECT mode FROM ans_get_mode()) IN (1,5)) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP)))
OR (((SELECT mode FROM ans_get_mode()) IN (2,5,6)) AND (begin_date IS NULL) AND (end_date IS NULL) AND ISNULL(is_committed, 0) = 0)
OR (((SELECT mode FROM ans_get_mode()) IN (3,6)) AND (begin_date <= (SELECT state_date FROM ans_get_state_date())) AND ((end_date IS NULL) OR (end_date > (SELECT state_date FROM ans_get_state_date()))))
OR (((SELECT mode FROM ans_get_mode()) = 4) AND (begin_date <= CURRENT_TIMESTAMP) AND ((end_date IS NULL) OR (end_date > CURRENT_TIMESTAMP))) THEN 1 ELSE 0 END) = 1 GO
I've currently got a query that takes two tables with the same structure, and spits out the differences in the second table - this is done to help me see what new records have been added in the second table that don't exist in the first. The only catch is that I have to compare two columns in sort of a pseudo-composite-key. Here's the gist of the table structure:
Table A: ID (Autonumber), Firstname, Lastname, OS, PC Manufacturer
Table B (The new one): Firstname, Lastname, OS, PC Manufacturer
The two columns I have to compare are firstname and lastname, as there aren't any other unique identifiers that match between the two tables. Right now, I'm using some really convoluted WHERE statements, and I suspect that it could be done more cleanly. Does anyone have any suggestions?
The result of this query is shown below... The result shows fields such as Bid_Price_Best_Latest and Ask_Price_Best_Latest may or may not have null values. At present, I use a cursor to get the values of these two fields if any of them is null from the table tblPricesSourcesImportHistory for the latest Import_Date on each record. These two fields are referred to as Bid_Price, Ask_Price in tblPricesSourcesImportHistory.
Now I would like to find out how to do this without the use of a cursor, so that I can speed up this query.
Thanks
select fp.Security_ID, fp.Security_Name, fp.Bid_Price_Best_Latest, fp.Bid_Size_Best_Latest, fp.Bid_Source, fp.Ask_Price_Best_Latest, fp.Ask_Size_Best_Latest, fp.Ask_Source from tblTEST fp left join tblSources s on fp.Security_ID = s.Security_ID inner join tblPricesSourcesImportHistory h on h.Source_ID = s.Source_ID where (fp.Bid_Price_Best_Latest is null) or (fp.Ask_Price_Best_Latest is null) group by fp.Security_ID, fp.Security_Name, fp.Bid_Price_Best_Latest, fp.Bid_Size_Best_Latest, fp.Bid_Source, fp.Ask_Price_Best_Latest, fp.Ask_Size_Best_Latest, fp.Ask_Source order by fp.Security_ID
Not sure if it helps but here are the structures of the tables you may need...
CREATE TABLE [dbo].[tblSources]( [Source_ID] [int] IDENTITY(1,1) NOT NULL, [Security_ID] [smallint] NOT NULL, [Source_Parent] [varchar](50) NOT NULL, [Source_Code] [varchar](20) NOT NULL, [Source_Security_Name] [varchar](50) NOT NULL)
I have a varchar(900) which means that I can use 900bytes, so if I am not wrong if the character is unicode, y only can use 450 because each character need two bytes.I have a databease with a column that use the intercalation general_latin_CI_AI, but I don't know if this intercalation use 1byte per character or use 2bytes per character.How can I know how many bytes need a character of a varchar column?
