We have a few tables each with near 100 million rows of data. A co-worker has these tables setup with partitions based on a date field.
Our disk space is starting to run low and knowing that partitions have their own file group, I wanted to see if some of the partitions were larger than they ne...
Hi @PaulWhite. Do you happen to know what this is about? "In earlier versions of SQL Server, queries against an XML document that contains strings over a certain length (more than 4020 characters) can return incorrect results. In SQL Server 2014, such queries return the correct results." from Behavior Changes to Database Engine Features in SQL Server 2014. Google is failing me or I just don't know where to look.
"The substring value specified for $arg2 has to be less than or equal to 4000 characters. If the value specified is greater than 4000 characters, a dynamic error condition occurs and the contains() function returns an empty sequence instead of a Boolean value of True or False. SQL Server does not raise dynamic errors on XQuery expressions."
Pls, i recently formated my system without exporting my database file from my phpAdmin in wamp server. How can i restore back my data from "C:\wamp\bin\mysql\mysql5.6.17\bin"?
thank you
@Lie I don't buy it. The waste in every single case can't possibly compensate for the savings you get for the (hopefully rare!) scenario where you are changing columns. The query should probably be reviewed in that case anyway because if it is joining or filtering on columns and you've added/dropped/renamed/moved those, won't the query still have potential problems? And if you've added columns, are you absolutely certain the application will actually want to use all of those columns? Perhaps not, in which case pulling them is a total waste. — Aaron Bertrand ♦4 mins ago
Used it for the first time on my last gig. We were shortening hierarchies for security purposes (A reports to B reports to C but B is unfilled so make it A reports to C)
I'm trying to fill a table parameter returned as a first table of a stored procedure.
I want this table to contain 'Parameter1', 'Parameter2', 'Parameter3' in a column named 'Parameters'. All data is not from any database table it's just hard coded strings right in the stored proc. It's a query ...
I've managed to get myself into a bit of a pickle, I did a quick redesign of the tables used in a notifications system & have managed to make the things entirely useless.
Any attempt to use them (select/update/insert/delete/alter) results in a query that runs indefinitely. I believe that I've ma...
I need to write an SQL Query for MySQL Database which gives me the data according to the necessary conditions
I have a table having structure like this
id: orderId
amount : itemAmount
rate : itemRate
time : orderTime
Now I have to select all the rows having same rates and then have to add all ...
Your XML namespace is messing things up. Either remove the xmlns="http://data.fcc.gov/api" from the Response element, or prefix your query with WITH XMLNAMESPACES ( DEFAULT 'http://data.fcc.gov/api')
;WITH XMLNAMESPACES ( DEFAULT 'http://data.fcc.gov/api')
SELECT x.i.value('@name', 'varchar(200...
@Lamak i've read the blog post 3 times and i still dont understand the metaphor
@swasheck you were explaining your problem to me (me being the rubber duck I guess). I didn't do anything to help you (just like a rubber duck wouldn't either), and you figured it out by explaining your issue
> I think its safe to summarize that retrieving unnecessary data is wasteful and should be avoided no matter what the scale of data involved. The purpose of the ORM is to map data from the table to an entity. That entity does not have to be the same entity as that generated by the ORM toolset.
Ah, well you already have Aaron in the comment thread and he's a mod so that usually works itself out. We generally just disengage from the trolls. You can try flagging comments as Too Chatty or Other and explain your situation to the mods.
I'd also favor pulling any useful comments of yours into your answer proper so that if a the comments get cleaned up, your knowledge remains in the answer instead of disappearing into the ether
Si. Lots of indexes. Lots of redundant indexes. Lots of really fat indexes.
Which is what happens when you build a table out of guids
A consultant said that we might be able to offset the tempdb contention and such that happens when we do these joins as queries vs bringing both into memory and using a merge join.
I'm willing to give it a shot but since we'll have to sort the data anyway... I don't know that it's going to buy us mutch
@PaulWhite yeah. it just seemed like the one who made the decision to partition just did it thinking it was a magic bullet. all partitions on the same disk, not switching in/out
> Let’s say you want to do something a bit more adventurous and you want to branch out into the bigger BI world that Microsoft has to offer. The AdventureWorks data sets are a great starting place, but the data set size doesn’t pose many challenges.
The Contoso Retail DW data set is several times the size of AdventureWorks and comes as a pre-built star schema. While it’s not the biggest database (my copy is around 1.6GB), ContosoRetailDW provides a large enough data set where aspiring database professionals can really start to push the limits of a local SQL Server.
I would like to remove a partition from a table SQL Server 2012. The table has primary key but the partition column is not a primary key.I would also like to keep the data structure and schema intact.
I did select * into [dbo].[new_Non_partitioned_Table] from [dbo].[partitioned_table ] and the...
lazyweb. I have an actual execution plan. My estimated rows, 93M is significantly less than actual 6M rows. Plan explorer has proposed that statistics may be off for my query. There's no way for the tool to show me where the stale values may lie, is there?
Mouseovers. Nevermind, I think this is where the dumbness here was infecting me
@Lie please don't assume that pulling all of the unnecessary columns in a table is always going to be a matter of microseconds. This can vary greatly depending on number of rows in the table, number of columns, the data types (try 50 geography columns and a bunch of MAX/XML, for example), how many columns are computed but not persisted, speed of the network, efficiency of the client app to consume, etc. Your argument about microseconds seems to be limited to the test app on your desktop working against 10 rows of BIT columns. — Aaron Bertrand ♦49 secs ago
Where did the app, computer, version and build columns come from? They are nowhere in the question. The date_entered column is already datetime, why do they need to cast it? Are you sure you answered the correct question? — bluefeet ♦2 mins ago
@AaronBertrand: if you always default to pulling all columns, then during development you wouldn't need to modify the query every single time you figured that you need a new column or when you decide later on that it turns out you don't actually need/want that column. In the rare case where selecting specific columns are measurably beneficial, the queries that specifies columns would appear highly unusual, an becomes a reminder that that particular query require extra care. — Lie Ryan53 mins ago
@Lie And then again, the developer doesn't always know what's in the table, so may not know that SELECT * pulls a bunch of crap they don't need, accesses columns they have been denied access to, etc. While I understand why developers love SELECT *, there are a ton of reasons why DBAs are against it. Development time and maintainability are a drop in the bucket compared to application response times. Maybe you're in a shop where management cares more that you can write a query in 10 minutes instead of 11 minutes; don't assume that's true for all of us. — Aaron Bertrand ♦33 secs ago
@Lie And if "performance only matters when it matters," please let everyone know where you work. Consultants, trainers and performance monitoring tools vendors like me build their entire livelihood on such statements... — Aaron Bertrand ♦6 secs ago
I can add a reasonably sober relevant comment to follow that... IN I GO
@Lie I've been called in to deal with 3 distinct and separate performance issues as a result of misapplication of ORMs in the past month. Performance matters as soon as there is a financial implication to its absence. — Mark Storey-Smith17 secs ago
@AaronBertrand: the query will fail the second the dev tried it, what time is lost? You may want to provide a view that selects the columns that the application developer had permission on, or the application developer may write a helper method that excludes those columns. — Lie Ryan40 secs ago
We have a few tables each with near 100 million rows of data. A co-worker has these tables setup with partitions based on a date field.
Our disk space is starting to run low and knowing that partitions have their own file group, I wanted to see if some of the partitions were larger than they ne...
Pls, i recently formated my system without exporting my database file from my phpAdmin in wamp server. How can i restore back my data from "C:\wamp\bin\mysql\mysql5.6.17\bin"?
thank you
Your "logic" is wrong.
> xkcd
$a = '2014-07-03';
$b = '2014-03-17';
$c = '2015-03-16';
if(($a > $b) && ($a < $c)) {
echo "1"; // it works!
}
Big-endian formats are awesome.
I need to write an SQL Query for MySQL Database which gives me the data according to the necessary conditions
I have a table having structure like this
id: orderId
amount : itemAmount
rate : itemRate
time : orderTime
Now I have to select all the rows having same rates and then have to add all ...
Your XML namespace is messing things up. Either remove the xmlns="http://data.fcc.gov/api" from the Response element, or prefix your query with WITH XMLNAMESPACES ( DEFAULT 'http://data.fcc.gov/api')
;WITH XMLNAMESPACES ( DEFAULT 'http://data.fcc.gov/api')
SELECT x.i.value('@name', 'varchar(200...
I would like to remove a partition from a table SQL Server 2012. The table has primary key but the partition column is not a primary key.I would also like to keep the data structure and schema intact.
I did select * into [dbo].[new_Non_partitioned_Table] from [dbo].[partitioned_table ] and the...
