Okay, so I have a query that looks like this:
Declare @Table1 Table (some columns)
Insert into @Table1 [QueryA]
Update @Table1
set Field1 = A.Value1
from ([QueryB]) A
where Field2 = A.Value2
Select * from @Table1
QueryA is a simple query that returns ~150 rows. QueryB is more complex and ...
In the comment thread under the question some of them do link the unwelcoming behaviour with frustration about deteriorating average quality of new questions – something we've been experiencing too.
We need to get better at closing/deleting hopeless questions quickly. And placing otherwise duff ones on hold quickly. And reopening improved, previously-duff questions. The system does work, mostly, eventually.
I wouldn't bother but there are 6 upvotes on the accepted answer, which was posted long enough after the last edit or comment from the OP too, so...
What do you mean by row 0 and row COUNT(*)? First and last row when sorted by val? In your example, two rows have the min val (which is 1), it's the rows id=1 and id=4. Your expected output shows that of the two only id=1 was removed. Why not id=4? And must it really be one row and not both? I'm asking this because the answer you've accepted would remove both val=1 rows from your example data. What was the actual requirement? — Andriy M3 mins ago
Watch out DBAs, I'm walking into a whole new world of writing potentially bad SQL. My next project is using Impala (Cloudera SQL on Hadoop) to write queries for Tableau reports.
This is the first time in at least 4 years that I am not using SQL Server for an entire project.
@swasheck I don't think I'm dragging and dropping, but agree that there are several tools like that. But the flavor of SQL is slightly different and I wasn't really that good at writing efficient SQL in the first place compared to you guys. So it will be a fun learning adventure.
Moving rows to another table does the job without sort but...
declare @T table
(
D datetime primary key
)
declare @T2 table
(
D date primary key
)
insert into @T2(D)
select cast(D as date)
from @T
select count(*)
from @T2
group by D
You can not use dynamic sql in views or function. One possible solution could be to alter your query to drop/create a view and use it as a stored procedure that gets called by a trigger on insert/delete in the source table (so that the view is updated whenever rows are inserted or deleted).
The ...
You also say "SELECT TOP 1000 * FROM table_with_1m_rows ORDER BY col1; ORDER BY will perform even worse, because it will have to first arrange all 1m rows in the table before it then returns 1000 rows. All of the above holds true whether the DB needs to use a sequential scan or an index scan.." Really? Will such a query always have to sort all the 1m rows of the table? — ypercube2 mins ago
Please, someone tell me I'm crazy. I can't understand this guy's assertions in the question.
@swasheck yup. When I try to add a single article to the subscription I get the very helpful message - Specify all articles when subscribing to a publication using concurrent snapshot processing.
Why does a highway back up when there's an accident on the other side of the f*ing median?
^^ happened to me this morning
@Zane people are arguing over the color of a dress in some picture, because the lightning made it look different. Because they have nothing more important to talk about.
@user965347 Totally depends on the factors I mentioned. If I were to read 13GB over a 9600 baud dial-up Internet connection, I might be dead before it completed.
@swasheck It's really weird what's happening. I dropped one table from a subscription but when I try to add it back it - it's trying to create a new subscription instead of using the existing one
I am importing data from a flat file to Sql server database. In a flat file data comes without decimal like 3000 for 30.00. I tried setting up this column to DT_Decimal with DataScale 2 or DT_Numeric with precision (12 - 18) and DataScle 2 and I also tried DT_CY Currency. All these are inserting ...
@swasheck I have a publication with 2 subscribers. I dropped an article from one of the subscriptions. That worked. But now I can't add that single article back into the subscription it was dropped from
A humble confession. Cleaned off my old Mac Pro to prepare for sale. It has a 240 GB OWC SSD as a boot drive. Copied important content to a 240 GB OWC external SSD. Wiped the SATA drives those files came from and created a concatenated disk set. Opened disk utility. Wiped wrong 240 GB SSD. Lesson learned: disconnect the f*ing external SSD before wiping anything. Moron.
Read the tip, then read the comments. I don't understand where he got the impression that I was suggesting everyone have a free-for-all on creating extended events sessions.
@AaronBertrand Obviously you don't understand XE well enough. I mean, look at the title of your article: Give anonymous users XE creation access in production
Although perhaps "Give SQL Server users read access to Extended Events" might help the SEO
> I fail to see the rationale behind the comment here. Nowhere in the article is it implied that users should be able to create XEs or be forced to plod through the XE metadata in order to enable users to do so.
> In fact, the author quite explicity states that he's moving toward a solution that would specifically address a scenario in which, "we may not want them to have all of those implied rights, and we may not even want them to see everything in a single extended events session. Let's say we want XEUser to only be able to see deadlock information, for example."
> Certainly the point is well-taken that a poorly-formed XE can "bring a server to its knees" but so can many things that occur within the day-to-day operations of a server. A few poorly-formed ad-hoc queries from a drag-and-drop reporting tool can also "bring a server to its knees" under the correct circumstances.
> Certainly we wouldn't be advocating that ad-hoc queries may only be executed by DBAs or Senior Database Developers, would we? Additionally, who's to say that DBAs or Senior Database Developers are immune from silly mistakes like filtering on the sql_text of an XE (especially in a 2008 XE)? Additionally, parsing XML is a CPU-intensive task in and of itself so why should anyone ever attempt to shred it in the first place?
> Or, we could simply let cooler heads prevail and read the actual content of an article and stop getting rankled about strawmen.
@JNK you'll never be comparing 100% accurately to production. But you can compare the different impacts of the same replay and those should be pretty accurate relative to each other if you catch my drift.
We have code to approximately copy of one database over to another
This allows a team to load data into it so they don't bone the load into the app database (~1TB)
The initial creation was done by hand but I'm too lazy so I must automate
For an identity column, would you create it with same seed and increment as the source database and then change the actual seed value to match or would you just declare it as current seed
Your code worked the first time, without edit. I didn't know about ROW_NUMBER() OVER ... PARTITION, so that's a new trick for me. You're right, it seems relatively straightforward with that in hand. But, you also pointed me to some new tools, and to a book's worth of material to read about all the related issues. I don't think there's more that could be done with a single answer. A+++. Would ask again. — David Krider2 mins ago