Should I split my SQL Server table?
I have a table where the majority of columns are very, very often read (SELECTed) and are almost never updated.
I now need to add a set of columns to the same table (they are properties of the same entity), only these will be less often read, and also very often updated
If I add the new columns into the same table, will the UPDATEs interfere with the SELECTs?
Should I instead create a new table with a 1-to-1 relationship to the previous table?
If it matters I am using Azure SQL Server.
See also questions close to this topic
Magento Update Price Per Store view SQL
I try to update the prices of product within a store view.
What i want to achieve is a query that takes that price of a product in store view A, multiply that by a number (lets say exchange rate). And apply that new price to the product of storeview B.
For now i only have figured out a query for selecting products.. (Using Magento 1.9)
SELECT e.entity_id AS product_id, var.value AS product_name, price FROM catalog_product_entity e INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id, eav_attribute eav, catalog_product_entity_varchar var WHERE e.entity_type_id = eav.entity_type_id AND eav.attribute_code = 'name' AND eav.attribute_id = var.attribute_id AND var.entity_id = e.entity_id AND var.store_id = 2
Oracle-Error ORA-01400 Insert into
I'm trying to insert some data in a new tab!
Here is the new tab: the tab
Here is my request:
INSERT INTO resultat (nbgagnes) SELECT Count(nomclub) FROM match INNER JOIN resultat ON match.clubdomicile = resultat.nomclub and match.idsaison = resultat.idsaison WHERE nbbutsdom>nbbutsvis GROUP BY (nomclub, match.idsaison) ORDER BY match.idsaison;
I get the error 01400 who say than I can't insert a null in my new table
I know that this error came from the fact than resulat (the tab) has 12 lignes and the result of my request give me this 10:
So i want to have 0 or null where there is no results!
SQL Server - Message 145, but item is in the select List
The following Query
select DISTINCT ID, field1, field2 from MyTable where field1 = 'myValue' OR field2 = 'myValue' order by case when field1 = 'myValue' THEN 1 else 2 end
Server: Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
which seems weird, since field1 obviously IS in the select List.
What is the reason and how can I change that?
(I am sure there is a workaround, but for the time being I want to keep at least the
linq to sql, select by dates that match to month and year from dates array
I need to select rows according to month and year from dates array.
from sh in context.sh where (sh.Report_Date.Month == dates.Month && sh.Report_Date.Year == dates.Year || sh.Report_Date.Month == dates.Month && sh.Report_Date.Year == dates.Year || sh.Report_Date.Month == dates.Month && sh.Report_Date.Year == dates.Year
this don't work, because
"LINQ to Entities does not recognize the method 'System.DateTime get_Item(Int32)' method"
So i have to create 6 different variable for that? what if i don't know the array count?
- SSIS Package + Custom DLL + Package fails
SSRS : Input the Output of one Result set to Another
I have an SSRS Report In which I'm using a Query with some complex Business Logic to retrieve the Data.
The Query Takes around 30-45 Seconds In Normal Scenario to retrieve the Data.
Now, I have another requirement that I need to add a summary tab to the SSRS report where I need to SUM the Amount Filed of the Same result set to Another Table
Suppose I have the First Result set as Below
I want to Have the 2nd One like this without the need of re-writing the Entire code of the 1st dataset
Is there any way that I can achieve this?
Thanks in Advance
Docker Microservice Architecture - Communication between different containers
I've just started working with docker and I'm currently trying to work out how to setup a project using microservice architecture.
My goal is to move out different services from the api and instead have each one in their own container.
- How does the API gateway communicate with the internal services? Should all microservices have their own API which only accept communication from the API gateway? Any other means of communications?
- What would be the ideal authentication between the gateway and the microservices? JWT token? Basic Auth?
- Do you see any problems with this architecture if hosted in Azure?
- Is integration testing even possible in the desired architecture? For example, I use EF SQlite inmemory for integration testing and its easily accessible within the api, but I don't see this working if the database is located in it's own container.
- Anything important here that i've missed?
Azure Functions: Configuration file for referenced assembly
We have a referenced project in
azure function appproject. The referenced assembly is a
data serviceproject which is referred to by
web apiproject too.
When referenced in
data service projectautomatically refers to
web.configfile for connection strings and app settings. While in
azure functions appthe
data serviceproject is not able to locate the connection strings stored in
- How to address this issue locally?
- How to address the issue in production?
NOTE: Would like to have DRY approach here.
Time triggered Azure Function and queue processing
I have a Function called once a day processing all messages in a queue. But I would like to also have the retries and poison messages logic as with the Queue Trigger. Is this somehow possible?
SSRS - producing csv file with mixed format header & footer
I have a query,
I have 3 CTE's produced by a TSQL query, which are used as a list of union-ed demographics, we have a requirement to submit this file in a particular format with a header and footer row in each file saved as csv. Can someone suggest the best method to produce the following format from SSRS, ultimately the header and footer will be generated from a SQL table which will hold the available submission ID's, I am wondering if this format can be automated from SSRS, or if there is a better approach, as you can see the header and footer appear to be tab separated, without the additional columns, then the comma separated data block, followed by another tab separated footer.
Any suggestions would be appreciated - thanks very much
format required below (dummy data):
0001011RNL DBS 473520180116 10,6374635,19540714,,,7253647987,p1,,john,,2,3 MADE UP ST,,local,CUMBRIA,,ca12 0T1,,,,,,,,A82635,, 10,9283746,19650325,,,7536482965,p2,,peter,,2,38 MADE UP ST,,WIGTON,,,ca12 0T3,,,,,,,,A82045,, 9901011RNL DBS 473520180116
How to copy a table from database A on a linked server to database B?
I am using
SQL Server 2012and I need to write a T-SQL query (that I will use as a SQL job) to copy a table (say,
T1) located in a database (say,
db1) on a linked Server to another database (say,
T1does not currently exist in database
Assuming the table schema of
T1on the linked server is
[xxx.xx.x.xx].db2.dbo.T1, how do I write this T-SQL query?
Using PIVOT to aggregate monthly counts of products purchased and revenue generated
I've searched this site and have done Google searches for how to pivot multiple columns (sales counts and revenue by month), but haven't quite found what I'm looking for. I admit that I haven't completely wrapped my head around PIVOT-ing yet, so it's possible I just wasn't fully understanding a solution that has already been posted.
What I'm trying to do is construct a query using PIVOT that will show monthly counts, as well as monthly revenue for products purchased.
Here is what the data looks like:
Product | MonthPurchased | InvoiceNmber | PaymentAmount ==================================================== Pencil 1 10001 1.00 Pencil 2 10005 1.00 Pen 1 10002 2.00 Paper 2 10006 1.00 Pen 1 10003 2.00 Paper 1 10004 1.00
Here is the query I've put together:
INSERT INTO #temp_nbcc_products ( [Product], [Month], t_invoice_num, t_payment_amount ) SELECT prd_name, DATEPART(mm, pyd_add_date), invoice_num, payment_amount FROM product_table ... SELECT [Product] ,  AS Jan ,  AS Feb ,  AS Mar ,  AS Apr ,  AS May ,  AS June ,  AS July ,  AS Aug ,  AS Sept ,  AS Oct ,  AS Nov ,  AS Dec ,  +  +  +  +  +  +  +  +  +  +  +  AS [Total Count] FROM ( SELECT t_payment_amount, t_invoice_num, [Month], [Product] FROM #temp_nbcc_products ) src PIVOT ( COUNT(t_invoice_num) FOR [Month] IN (, , , , , , , , , , , ) ) piv1 ORDER BY [Product] DESC;
Here is the output of the query:
Product | Jan | Feb | March .... Total Counts ============================================= Pencil 1 1 0 2 Pen 2 0 0 2 Paper 1 1 0 2
What I'd like the result set to look like is this:
Product | Jan | Revenue-Jan | Feb | Revenue-Feb .... TotalCounts | TotalRev ======================================================================== Pencil 1 1.00 1 1.00 2 2.00 Pen 2 4.00 0 0.00 2 4.00 Paper 1 1.00 1 1.00 2 2.00
Any help would be greatly appreciated.