@AaronBertrand code failed on 2008 R2 (SP2) - 10.50.4033.0 (X64) with "Correlated parameters or sub-queries are not supported by the inline function "sys.dm_db_index_physical_stats"."
@AaronBertrand Returns an empty set on SQL Server 2014 - 12.0.2000.8 (X64). Didn't work so well on SQL 2000 either :-)
@SimonRigharts You know, bringing intelligence to the business.
Working with perhaps the most dysfunctional DW project I've ever seen.
We've estimated something like half the effort and wasted time in this project is down to troubleshooting data quality issues that are leaking through into the reporting layer.
Yes. Beginning of the financial year, so funding for projects is becoming available. It's a good time to be looking for contract work as you tend to see a spike at this time of year. You know what to do ...
I want primary keys in my table to be random and unguessable while still preserving correct order (order in which documents were inserted to database). Firebase uses this algorithm: https://gist.github.com/mikelehen/3596a30bd69384624c11. Is it a good idea to port it to psql or plv8 for example an...
I can't understand why this deadlock is occuring.
The transaction on the right affects (updates) only one row, it looks like so:
update table1
set column1 = 'value1'
where key1 = 1
I assume the IX request is for the page where the row (that is being updated) is located, right? But that canno...
Hey @JNK where did you live in Syracuse? Wife and I are looking for a place, any recommendations? I work on the east side of town so Dewitt/East Syr would be where we would start. Any recommendations on neighborhoods?
@JNK Ahhhh, ok. Landlord changed his mind and wants to move back into his house. Sucks, but at least we have a few months (3) to find a new place. When we moved here from NC, we had 2 weeks, haha
Absolutely, with only 2 months we may just need to rent for 1 more year to get 20% down. I know with 20% you're all but guaranteed a home loan. Right now, it would be a stretch for us and may cause more problems than its worth.
liverpool I would not consider just because of the overpolicing
not sure if it still happens but it was super cop-heavy when I was there
I used to have to drive through there every night at like 2am when I closed my restaurant down and got pulled over for doing 26 in a 25 a couple of times
House Hunters International episode on Sunday showed a couple looking for a house in La Sirena, Chile. It was beautiful, but the couple proved to be typical American idiots. "This kitchen doesn't look like American kitchens. There is no way I could live here."
@JamesLupolt it's the design and architecture what's different. American kitchens are mostly thought to be a place to cook and eat, whereas here the kitchen are mostly for just cooking, in a separate place of the one for eating. They aren't as open and they are commonly more spacious
(I just used a lot of words that I don't know, so forgive the spelling mistakes)
@Lamak Interesting. Lots of American homes have a kitchen table for eating informal meals, and a separate 'dining room' for eating more formal or larger meals
@Lamak My only problem when I lived in Chile was getting the hot water heater started to heat up water for showers (@Lamak your house probably doesn't have this issue as I was in a cheap apartment). I could never get the pilot light to go on and I remember my roommate drunkenly holding a lighter to it in the hopes that would get it started quicker. I was, at the time, not used to gas heating or cooking in general so that was probably part of it.
@JamesLupolt Yeah, kitchens do have those here too, but it's not where you would eat mostly, they have they own room. And houses typically have a room for informal eating and another for more formal ones too. But the kitchen is mostly for cooking
@mmarie yeah, it depends on the place where you are. A lot of places have gas heating and you know how to use them since childhood. The apartment where I live now has electrical heating
@mmarie exactly, larger houses can have those kind of big kitchens too, but as I said, their primary goal is cooking, not eating
I have gas heating in my current house, and I'm used to it now, but I don't have a separate water heater that must be started to heat water for showers.
@MarkSinkinson About 90% of them, anyway. This one astonishes me with how much melodrama they can make out of a data set with so little essential complexity.
@ConcernedOfTunbridgeWells There seems to be an inverse relationship between the quality of a publication and its ability to use technology well. The Daily Mail has a great tech stack from what I've heard, for example. In contrast, WSJ and the Economist can't even manage subscriptions online effectively.
How about this:
;WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SORT_ORDER, TYPE_ID) AS RowNumber
FROM MyTable
)
SELECT *
FROM cte
WHERE RowNumber = 1
The ORDER BY is how you determine the priority within each ID.
I'm moving a SQL Server table from SQL Server 2008 R2 to MySQL on my c:\ drive by using SSIS.
In SSIS, I have created ADO.Net source and destination connections with a 64 bit unicode driver I downloaded and have managed to successfully to transfer the data from SQL Server to a newly created My...
@JamesLupolt I suspect that many 'quality' publications tend to have management that lives in a comfort zone as they can substantially rely on brand recognition. Management here are fairly complacent about technology.
I have a little solution that I want to share with today's DBAs but I'm really uncertain that the problem it solves is still of interest to anyone. for some reason, the "answer your own question" pattern looks all wrong to me in this case.
The solution is a little Powershell script that solves ...
Hence my dive into code refactoring right now. Lots of bunk code swimming around in this pool. Previous DBA had NOLOCK as best practice. I've removed (systematically) about 80% of them. His reasoning was we have a 'volatile' system and deadlocks were common. I think it was just slapping a band-aid on because he didn't want to address the root cause
Also, with almost all of them gone, things are running smoothly, if not better now
Respect the isolation level and it shall respect you
Yeah, but throwing it in as a blanket statement for all queries is superfluous. I has it's place in certain situations, but without justification, it's just plain bad practice and, to be honest, lazy.
I'm just saying 99% of the time it's used, the ramifications aren't considered, and defensive mode starts immediately: the excuse is always that either it resolves blocking/deadlocking, or it makes queries "faster"
@AaronBertrand Absolutely right. But if it's isn't worth investigating and doing the right way, why do it all? That's our job, to be nitpicky pains in the butt. There's a reason behind our madness and it's for the greater good of our company/client.
Unfortunately, that falls on deaf ears most of the time
I think most folks don't think there's more to investigate - they assume that it just means the query won't take locks, therefore won't contribute to blocking/deadlocking and will be faster. I really wish they had named the hint something different.
@AaronBertrand Incorrect syntax near '('. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.
i downvoted you because this won't parse in sql server. you need a GROUP BY and, while you're at it, you should put an alias on your aggregate — swasheck7 secs ago
All right, off to Bits, anyone else going to be there? Anyone else in London proper between now and Saturday? I won't have much time for venturing very far away - two presentations and a couple of customer calls.
Interesting. So they posted their database schema and zoinks, that's sensitive. They attempted to edit every answer to align with new column and table names but their edit to the question itself got rolled back
> Take the Piccadilly Line of the London Underground from Terminals. Get off at Hammersmith and walk across the platform to catch an eastbound District Line train. Get off at Westminster and transfer to the Jubilee Line eastbound (down several lifts or escalators). Get off at Canning Town and transfer to the Docklands Light Railway for a train towards Beckton. Get off at Custom House (for the ExCeL west entrance) or Prince Regent (for the ExCeL east entrance and convention centre).
@ypercube I haven't joined Uber and between the data breach and how all the cab companies want to kill them I think I'd rather stay safe. Especially when it's not my dime.
how to run chain update command on dbase i'm using code below but performance is very slow. is there any way to update using sp's.
private static void UpdateRdTable(string rdTableName, List Data)
{
// Write to RD database
var oledbConnection = new OleDbConnection(Ole...
Query performs Index Seek on Primary Key. If Tool Tip shows **Bmk**… in Output List, this means that not all data was present in the index. This shows that the Index Seek is part of a query that has a Bookmark Lookup.
The query performs a RID Lookup, which is a type of Bookmark Lookup that occurs on a HEAP table (No Index) and uses a RID to find the rows to return. Since table does not have a Clustered Index, it must use a RID that links the Index to the HEAP. This adds I/O because two different operations have to be performed instead of one operation, which are then combined…
> This indicates that SQL Server will create a bookmark to be used in an Index or RID Lookup. When a bookmark is present, the index seek/scan is part of a two-step process where the optimizer will need to perform a lookup on the clustered index (or the table if no clustered index exists) after creating a data set with the bookmarks. See the section on Key/RID Lookups for more information.
there is a query I'm looking at involving the heap I mentioned, and some other non-heap tables, where the estimate of rows returned by a join is 310 trillion. OPTION (FORCE ORDER) makes it sensible.
I first ran into it while investigating why a query that runs in test takes several seconds, whereas in prod with triple the ram, it takes forever. FORCE ORDER saved the day. Query Optimizer was having fits because the database doesn't have auto-create-statistics turned on, and no one ever looks at that stuff. Don't even talk to me about indexes. I optimized the query afterwards, and it runs in the blink-of-an-eye now.
> Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
@MikeFal the question behind the question is, when are global temp tables destroyed?
Why can't you restore a SQL Server database from a backup file that is in a sql filetable?
I can't understand why this deadlock is occuring.
The transaction on the right affects (updates) only one row, it looks like so:
update table1
set column1 = 'value1'
where key1 = 1
I assume the IX request is for the page where the row (that is being updated) is located, right? But that canno...
Doesn't AND Operator work there?
How about this:
;WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SORT_ORDER, TYPE_ID) AS RowNumber
FROM MyTable
)
SELECT *
FROM cte
WHERE RowNumber = 1
The ORDER BY is how you determine the priority within each ID.
I'm moving a SQL Server table from SQL Server 2008 R2 to MySQL on my c:\ drive by using SSIS.
In SSIS, I have created ADO.Net source and destination connections with a 64 bit unicode driver I downloaded and have managed to successfully to transfer the data from SQL Server to a newly created My...
I have a little solution that I want to share with today's DBAs but I'm really uncertain that the problem it solves is still of interest to anyone. for some reason, the "answer your own question" pattern looks all wrong to me in this case.
The solution is a little Powershell script that solves ...
how to run chain update command on dbase i'm using code below but performance is very slow. is there any way to update using sp's.
private static void UpdateRdTable(string rdTableName, List Data)
{
// Write to RD database
var oledbConnection = new OleDbConnection(Ole...
