Join a Microsoft SQL Server and SQLite table
is there any way to join Microsoft SQL server table with SQLite table , i am using SQLite database for storing data locally in android device and after that i would like to join the SQLite table data with SQL Server table data using Inner join but i didn't find any way to Join
See also questions close to this topic
SQL Server SP error Msg 4145, Level 15, State 1
When I try to run individual query for select and/or insert, it works. When I put it in SP and run it for all DB, it errors out.
Msg 4145, Level 15, State 1, Line 45 An expression of non-boolean type specified in a context where a condition is expected, near 'Addr'.
Declare @command varchar(MAX) Select @command = ' USE [?] IF DB_NAME() not like ''%_VER'' BEGIN RETURN END ElSE BEGIN Insert Into [TEST_VER].[dbo].[TestTable] (ClientName,ID,Type,Filled,FilledDate,Cancelled,CancelledDate,CancellationReason,Deleted,NumberOfPosition,Address,City,State,Country,Annual,AnnualMax,FeePercentage,FeeTotal,GrossProfit,NetProfit,Rate,OTRate,CRate,COTRate,GrossProfit,GrossMargin,ProfitMargin,RegularMarkup,OTMarkup) select DB_NAME() as ClientName, ID, Type.Description as Type, Filled, FilledDate, Cancelled, CancelledDate, CancellationReason.Description as CancellationReason, Item.Deleted, NumberOfPosition, Address.Description as Address, City.Description as City, ProvinceState.Description as State, Country.Description as Country, PayP.Annual, PayP.AnnualMaximum, PayP.FeePercentage, PayP.FeeTotal, PayP.GrossProfit, PayP.NetProfit, PayT.Rate, PayT.OTRate, PayT.CRate, PayT.COTRate, PayT.GrossProfit, PayT.GrossMargin, PayT.ProfitMargin, PayT.RegularMarkup, PayT.OTMarkup from [Item] left join [Type] on Item.TypeID = Type.TypeID AND Type.LanguageId = 1 left join [CancellationReason] on Item.CancellationReasonID = CancellationReason.CancellationReasonID AND CancellationReason.LanguageID = 1 left join [Address] on Item.LocationID = Address.AddressID left join [City] on Address.CityId = City.CityID left join [ProvinceState] on Address.ProvinceStateId = ProvinceState.ProvinceStateID left join [Country] on Address.CountryId = Country.CountryID left join [PayP] on (Item.PaymentID=PayP.ID and Item.TypeID = 1) left join [PayT] on (Item.PaymentID=PayT.ID and Item.TypeID > 1) END' EXEC sp_MSforeachdb @command
Slow Query Execution In SQL Server Due to Partitions
I have the following query in SQL Server 2012:
SELECT Code , ReferenceKey , CodeKey FROM dbo.Codes WHERE PartitionKey = 'PART1' AND CurrentPosition = 1 AND Deleted = 0 AND Code = 'CODE1' AND ReferenceKey = 0x0000000000000000000000000000000000000000
This gives the following statistics output:
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) affected) Table 'Codes'. Scan count 239, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) SQL Server Execution Times: CPU time = 31 ms, elapsed time = 35 ms.
The actual execution plan shows a single index seek against an index defined as:
CREATE NONCLUSTERED INDEX [idx_Codes] ON [dbo].[Codes] ( [PartitionKey] ASC, [Code] ASC, [ReferenceKey] ASC, [CurrentPosition] ASC, [Deleted] ASC ) INCLUDE ([CodeKey]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
If I rephrase the query to:
SELECT Code , ReferenceKey , CodeKey FROM ( SELECT Code , ReferenceKey , CodeKey FROM dbo.Codes WHERE PartitionKey = 'PART1' AND CurrentPosition = 1 AND Deleted = 0 AND Code = 'CODE1' AND ReferenceKey = 0x0000000000000000000000000000000000000000 ) Q
I get the following statistics output, which shows a significant reduction in both CPU time and elapsed time:
(1 row(s) affected) Table 'Codes'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
The execution plan is identical, other than the latter includes a more elaborate Seek Predicate which appears to mean that the query is targeting only 1 of 239 partitions.
Actual execution plan XML extract for the first query:
<RunTimePartitionSummary> <PartitionsAccessed PartitionCount="239"> <PartitionRange Start="1" End="239" /> </PartitionsAccessed> </RunTimePartitionSummary>
And for the second query:
<RunTimePartitionSummary> <PartitionsAccessed PartitionCount="1"> <PartitionRange Start="14" End="14" /> </PartitionsAccessed> </RunTimePartitionSummary>
The partition key is based only on the column
Why are these queries interpreted so differently (particularly in terms of how they perform) by the query optimizer?
Also, currently, only 2 or 3 of the 239 partitions have any data in them, so I'm wondering what might account for the significant difference in performance between the two queries?
One further thing to add is that after restarting the SQL Server service, this problem appears to resolve itself, with the execution times both going down to 0-2 milliseconds, however, the variation in execution plans/Seek Predicates is still the same.
Paste The Plan Links
First query: https://www.brentozar.com/pastetheplan/?id=HyfFHXeJF
First query after SQL Server service restart: https://www.brentozar.com/pastetheplan/?id=BkWk-EeyY
(The first query is much quicker after a restart, and the only difference in the actual execution plan is that the
StmtSimplenode's value for the
StatementCompIdattribute has changed from 2 to 0.)
Second query: https://www.brentozar.com/pastetheplan/?id=HkIeEQlyK
Using Max() with other field with GROUP BY
I have the following table in SQL SERVER:
CREATE TABLE cats ( name VARCHAR(10), breed VARCHAR(25), weight decimal(8,2), color VARCHAR(25), age int ) INSERT INTO @cats VALUES('Ashes','Persian',4.5,'Black',5) INSERT INTO @cats VALUES('Molly','Persian',4.2,'Black',1) INSERT INTO @cats VALUES('Felix','Persian',5.0,'Tortoiseshell',2) INSERT INTO @cats VALUES('Smudge','British Shorthair',4.9,'Black',4) INSERT INTO @cats VALUES('Tigger','British Shorthair',3.8,'Tortoiseshell',2) INSERT INTO @cats VALUES('Alfie','Siamese',5.5,'Brown',5) INSERT INTO @cats VALUES('Oscar','Siamese',6.1,'Black',1) INSERT INTO @cats VALUES('Millie','Maine Coon',5.4,'Tortoiseshell',5) INSERT INTO @cats VALUES('Misty','Maine Coon',5.7,'Brown',2) INSERT INTO @cats VALUES('Puss','Maine Coon',5.1,'Tortoiseshell',2) INSERT INTO @cats VALUES('Smokey','Maine Coon',6.1,'Brown',4) INSERT INTO @cats VALUES('Charlie','British Shorthair',4.8,'Black',4)
I need for each (color) to select\view the oldest cat, and it's name. It's very easy to use
GROUP BY colorbut the problem is when you add the name everything is missed up.
I think I should use
DENSE_RANK... I tried them all but I got stuck. please help me with this.
Android SQLite database is editable outside app?
I am using sqlite database as localdatabase in android app.My confusion is,can anyone change the values of database outside my app and use that modified database? If yes,then how can I prevent this? I have backend server too.
How to save List<KeyValuePairs<int,object>> as a SQLite table?
I have a List of key value pairs, but I'm not sure how I can save it in SQLite. I've downloaded the library, and created a database. I know how to save objects, but how do I save the List<KeyValuePair<int,object>> into a table?
How to retrieve/print proper Hindi meaning text in Console from a english-hindi.sqlite database
I have downloaded a word meaning Android sqlite.db dictionary file. By fetching SQL query I got
By using Hindi Typeface in TextView I got proper Hindi meaning. If I try to print by
I am confused about the problem and looking for a solution. Please, check the raw Android sqlite.db Github link in comment sections.