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
MS SQL Server: "Concatenate" tables
I don't really want to concatenate (i.e., I don't want to merge strings), but if I say
JOINit will confuse people.
I have 2 tables that I know are properly aligned by row. I simply want to take the 2 tables and join them, but not on anything, and I don't want to
CROSS JOINor anything similar. Let me explain by example:
name | city --------|----------- Phillip | Chicago Sarah | London
phone | email -------------|------- 312-241-7754 | email@example.com +011-11-1111 | firstname.lastname@example.org
I know that these rows match. That is, the first row of each table refers to
Phillipand the second row of each table refers to
Sarah. But I don't have any shared thing to join on.
I simply want to say something like:
INSERT INTO table3 (SELECT * FROM table1 ??CONCATENATE?? table2)
where that funny
??CONCATENATE??is my magical function that simply says "align row 1 of each table together, then row 2, then row 3, etc.". I'm sure there must be a function to do this, but after searching for a while still couldn't find anything.
Querying 4 MySQL tables in one query
I'm building a forums site and I would like to display all the threads to users in the following format:
_________________________________________________________________________ Thread Title Last Post: Username at Date, Started By: Username at Date _________________________________________________________________________
This is what I've come up with:
SELECT TD.id thread_id, TD.sub_cat thread_cat, TD.title thread_title, TD.date thread_date, TD.status thread_status, TD.stick thread_stick, US.username user, GR.color group_color FROM fr_thread AS TD INNER JOIN user AS US ON TD.user_id = US.id INNER JOIN user_group AS GR ON US.user_group = GR.id ORDER BY TD.stick
While I'm displaying all the threads, I would also like the username's be to coloured according to what user group they are in. Therefore, I also have to query for that.
Although, I've come up with most of query, I'm stuck as how to query for Username, group color, date of the last poster.
Here is roughly what my DB design looks like:
user: id user_group user_group: id color fr_thread: id sub_cat user_id date fr_reply: id thread_id user_id date
How to preserve logins after server rebuild
I had one server with instance . But we had some OS issue because of which we had to rebuild the server . I had all the backups of the databases . Now the server is rebuild and databases are restored,I see security are missing . Is there any way I can get those logins . Meaning, I want to have the same security as it was before the server rebuild.
SQL Server Grant Execute to different database
Is there any syntax available in the grant command that would allow something equivalent to
Use [SomeOtherDatabase] grant execute to [domain\UserLogin]
What I'm hoping to do might look something like this:
grant execute On [SomeOtherDatabase] to [domain\UserLogin]
Which of course does not work...
SQL CODE required to add a column
I have a query created to obtain columns-TODAY,TOMORROW,TWO AND 5,FIVEPLUS by using the below query:
SELECT COL1,a.COL2,DATE COL3,COL4, IIF(CONVERT(date, DATE COL3) > (CONVERT(date, GETDATE() - 1)), 1, 0) As Today, IIF((CONVERT(date, DATE COL3) < CONVERT(date, GETDATE()) AND CONVERT(date, DATE COL3) > (CONVERT(date, GETDATE() - 2))), 1, 0) As Yesterday, IIF((CONVERT(date, DATE COL3) < (CONVERT(date, GETDATE() - 1)) AND CONVERT(date, DATE COL3) >= (CONVERT(date, GETDATE() - 5))), 1, 0) As TwoAndFive, IIF(CONVERT(date, DATE COL3) < (CONVERT(date, GETDATE() - 5)), 1, 0) As FivePlus, FROM [TBLBOOK] a LEFT JOIN [TBLUSER] b ON a.COL 1 = b.COL 1
I get the necessary columns as today, yesterday, two and five, five plus separate columns however, in addition to the 4 columns I want to incorporate additional column in the query as "Day group" with column data to populate the today,yesterday etc as the data for the new column using IF condition using the data in "date col3"as the trigger . I need this to insert one slicer with today,yesterday etc as compared to 4 different slicers. Hope my question is clear and if someone can please assist.
With above mentioned code I get the following sample table:
col 1,col2,date col3,col4,today,tomorrow,two and five,five plus
I have created a pivot table and slicers using the above table data created through the sql code. I wanted to add another column name "day group" to have today,tomorrow,two and five,five plus as data for the new column. I was able to add a column using" if statement" in the excel sheet example:
=IF([@Days]<0,"Today",(IF([@Days]<1,"Yesterday",(IF([@Days]<6,"2-5 Days","> 5 Days")))))but i wanted to use the above code to return the new column.
SQL Server: select TOP2 element by group
I have a table with following columns: item (varchar) date (date) revision (datetime) value (numeric)
I have this SQL statement that finds the last revision for each item:
select item, max(revision) as 'last_revision' from [mytable] group by item ORDER BY last_revision ASC
However I am looking to get the last 2 revisions for each item:
I have tried this which fails miserably
select TOP 2 item, revision as 'last_revision' from [myTable] group by item ORDER BY last_revision ASC
Column 'myTable.revision' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Reduce Ingest Time for SumoLogic Azure Web App Collector
Currently rolling out a POC of using SumoLogic to monitor some web apps that I am I look after.
However, currently I am seeing around a 15-45 minute delay time in the actual ingestion of logs into the SumoLogic dashboard.
The CPU Limit on the collector is set to 80% but the machine doesn't ever break 50% at this stage.
Any help would be great.
P.S Please advise where i should put this question if not StackOverflow!
Azure App Service 502 - Web server received an invalid response while acting as a gateway or proxy server
I have simple azure service app that talks to azure storage tables . This works just fine when I run it through visual studio and gives the below error when I run it after deployment .
Server error 502 - Web server received an invalid response while acting as a gateway or proxy server. There is a problem with the page you are looking for, and it cannot be displayed. When the Web server (while acting as a gateway or proxy) contacted the upstream content server, it received an invalid response from the content server.
• This use to work just fine a few months before . • I have no Azure application gateway or proxy , I believe its mandatory to have one right ? • The problem is there in both http://myservice.azurewebsites.net/Add/Create and http://www.myservice.com/Add/Create • There is some data base interaction that happens before the problem page comes. I also see that that data interaction on the problem page also completes fine , as I can see from backend that the data has been added. • Using application insights , I see that all the said actions returned https error 200 , none have 502 .
I see from some web search , this is some front end error , what logs in azure will tell me the details ?
Querying Views in Azure Data Factory Copy
My input data source is an ODBC connection. I only have read-only access to this source, with views as the main front facing piece. The goal is to query a few of these views using the copy utility built in to Azure Data Factory and output the results of the queries as dimensions for Power BI reporting purposes, a reporting hub essentially. The issue is, I haven't had luck validating any view based queries, they all time out, and I don't see any of the views in the pre-populated "select tables from" model. So I assume they aren't available. Is there any way I can get to these views?
TSQL , Weekday output
This function should accept date and output the day of the week. I tried the code before , but there was no result
alter procedure udfWeek(@days datetime) AS begin declare @day nvarchar(max)= DATEPART(weekday, @days) print @day end go select dbo.udfWeek('2011-11-11')
Common substring of two string in SQL
I need to find common substring (without space) of two strings in SQL.
select * from tbl as a, tbl as b where a.str <> b.str
str1 | str2 | max substring without spaces ----------+-----------+----------------------------- aabcdfbas | rikcdfva | cdf aaab akuc | aaabir a | aaab ab akuc | ab atr | ab
SQL Columns to rows with last 12 months of data
I have a sql table with monthly consumption amounts. I would like to convert the columns to rows by cust_no and show only the last 12 months of consumption. What's the best way to do this?
cust_no cons read_dt 1 1 2/28/2017 0:00 1 2 3/31/2017 0:00 1 2 5/1/2017 0:00 1 2 6/1/2017 0:00 1 2 6/30/2017 0:00 1 2 7/31/2017 0:00 1 2 8/29/2017 0:00 1 3 9/29/2017 0:00 1 2 10/31/2017 0:00 1 2 12/1/2017 0:00 1 2 1/2/2018 0:00 1 3 2/1/2018 0:00 1 2 3/2/2018 0:00