Sure, but if he starts adding more complaints on the original blog posts (or gathers another lynch mob to help him), I won't be able to ignore him. That's work.
It seems the OP was following advice by @Mark Bannister:
Have you already decided which database management system (such as relational DBMSs like Oracle, SQLServer etc) you intend to implement this on? Also, have you considered asking this on dba.stackexchange.com (which may be a better site for this question)? — Mark Bannister59 mins ago
i am duplicating an MSSQL database, for another client to use (to be completely separate from the other).
I am doing this via Tasks -> Generate Scripts. i am copying all the tables.
Should i copy the schema too, or just the tables? (The schema has the old database user name)
thanks
I've noticed that when you set up a transactional replication, SQL Server will set identity range management to manual. What this means is that in my subscription database, when I try to insert a new record into a table whose PK is an identity column, it will give me an error and say that it tri...
Jeff has been running around crapping on people all over the place.
No! Don't do this. It uses a recursive CTE to "count" and it's generally worse than a While loop for performance and resource usage. There are a lot of ways to effeciently generate a sequnce of numbers and this isn't one of them. — Jeff ModenJun 26 at 5:41
All on old questions. I wonder what is stirring all this up.
@AndriyM well, not necessarily. Apparently he is really bored but I'm wondering what is drawing his attention to certain questions, given that other than his, there's been no activity on them in months or more...
I am advising someone on how to disable TCP Chimney Offload, which is enabled by default by Windows Server 2003 SP2. [1] We are working in an environment with multiple server machines, namely the application server machine which communicates with the database server machine.
On which machine sho...
@AaronBertrand I remember receiving a similar comment on a DBA answer in wich I used a CTE for generating 12 months (I mean, lazy, but hardly inefficient for 12 rows)
Recursive CTEs that count are absolutely horrible for performance compared to just about any other method even for a piddly 12 rows. Why would you intentionally use something slower and much more resource intensive even if it's just for 12 rows? Avoid recursive CTEs like the plague when it comes to counting. — Jeff ModenJun 4 at 6:40
In order to get the result you will have to look at pivoting and unpivoting the data in your table and since you want to do this for any two years, you will have to look at applying dynamic SQL.
First, let's start with the initial query to get the data for the 2 years. You need to join on your ...
Jesus effing christ man, can't you do anything on your own?
Hi Lamak, the microseconds have been trimmed. However I have just noticed that the time has not been adjusted 4 hours behind in the output. — omar K6 mins ago
Is it best to use sequence table for generating unique id or there is another better way of doing it ? I am writing a stored procedure to allocate unique integer ids. sqlblog.com/blogs/paul_white/default.aspx?p=4 Any suggestions ?
@Kin what version of SQL Server? SEQUENCE in SQL Server 2012 is going to be more efficient than anything you would roll yourself. If you're not on SQL Server 2012, IDENTITY is pretty close - any reason you don't want to do that?
And unless you really need to maintain a contiguous set of values, there should be absolutely no reason you should have to worry about deadlocks. Those should only come in to play if you feel you need to generate a new sequence value in a transaction, then go do other things, and return the sequence value to the pool if it goes unused.
Allocation occurs when tempdb needs to use space. Usually this occurs one extent at a time (an extent is 8 8K pages), but will be repeated until the estimated space required is made available. At least that's my working knowledge.
What causes them? All kinds of things. Spills, spools, sorting, certain cursor types, worktables, #temp tables, @table variables, row versioning, checkdb, online index operations...
@swasheck hmm, not sure, it may be possible to use free pages in an existing extent, again depending on what caused the need, who asked for it (user or system), and how much they asked for.
@AaronBertrand no worries. i'm actively googling too but just didnt know if i could save myself some googletime. thanks. you've already done more than enough
SELECT DISTINCT
SM.sales_date,
(SELECT
SUM(gross_sale)
FROM
sales_main AS INTERNAL
WHERE
INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TGROSS,
(SELECT
SUM(net_sale)
FROM
...
There are three DMVs you can use to track tempdb usage:
sys.dm_db_task_space_usage
sys.dm_db_session_space_usage
sys.dm_db_file_space_usage
The first two will allow you to track allocations at a query & session level. The third tracks allocations across version store, user and internal object...
@MarkStorey-Smith i'd throw a bounty at it but i'm trying so hard to get to 3000
I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type being PAGELATCH_EX).
The issue appears to happen sporadically when the server is under decent load...
@AaronBertrand thanks. i'm exploring tempdb and all of the useful information i can get is helpful. for right now we're not having any problems but i'd like to be prepared.
@ypercube Maybe. I just.....kinda dislike participating on meta. I visit the site almost every day, and read about new politics and decisions, but I try to avoid actively participate. They are so.....kind of "hive-minded"
If you go to a question that has a pending close vote for a custom off-topic reason whose text includes italics on a line that coincides with the pending close vote count for that reason, the italics get dragged down to the margin-bottom of the count.
This sounds pretty obscure, except that it h...
hm. well. ok. i just took it as absolute truth to enable Auto Create and Auto Update stats on my 2008 instances :) i loved her MCM sessions on stats so i just took her as authoritative and this as reminding me of her authority :)
so sharepoint can control its own statistics, thankyouverymuch
@AaronBertrand Sharepoint is an MS product, right? How can this be explained, the same company that produces SQL-Server not making a decent db design in another product?
@AaronBertrand apparently the quality declined when he left. we just installed a brand new version for some social site and MAN it's further crippled the installation.
I am very new to MS SQL Server (I have always been using SAS to pull).
I have program which identifies children which where not seen in a particular month - the criterion is in bold below. When I run the program this is a example of the result:
Children which were not seen in the month of May...
I used try and except in this program for the first time:
def ScienceSearch():
query = SearchTerm.get()
address = ("http://www.google.com/search?q=%s&num=100&hl=en&start=0" % (urllib.quote_plus(query)))
request = urllib2.Request(address, None, {'User-Agent':'Mosilla/5.0 (Macintosh; Intel Mac OS ...
@AaronBertrand i was on a conf call with their guys. he was nice but admitted the code was released too early (and clearly we adopted too early). i'm not sure how Glenn would feel about the proliferation of virtualized sql servers that we have here in production
@AaronBertrand On a totally unrelated note (and sorry if it's already been brought up, I might have missed it then), why is your reputation shows as 0 in the chat?
Interstingly, @swasheck's is also less than I would expect it to be. But at least his "main" profile shows as discuss.area51.stackexchange.com, where he does have 151 rep. Yours is DBA.SE and 0? Strange
@JNK I'm currently at home and the chat was open at my work too until early this morning. When you said "cache", I remembered that and reloaded the page and vois la!
I have 5 random names each for male and female. I need to insert random names based on the gender. But how can we insert names in random from a set of 5 names in SQL. Is it possible?
There isn't a guaranteed way. It is non deterministic how many times it will be evaluated even for SELECT N AS N1, N AS N2 FROM (SELECT NEWID())T(N) — Martin SmithJun 19 at 21:26
The preview for question in SO is right justified on my browser (My locale is Hebrew) as in this image:
Is it possible to fix that?
i am duplicating an MSSQL database, for another client to use (to be completely separate from the other).
I am doing this via Tasks -> Generate Scripts. i am copying all the tables.
Should i copy the schema too, or just the tables? (The schema has the old database user name)
thanks
In order to get the result you will have to look at pivoting and unpivoting the data in your table and since you want to do this for any two years, you will have to look at applying dynamic SQL.
First, let's start with the initial query to get the data for the 2 years. You need to join on your ...
SELECT DISTINCT
SM.sales_date,
(SELECT
SUM(gross_sale)
FROM
sales_main AS INTERNAL
WHERE
INTERNAL.sales_date = SM.sales_date AND INTERNAL.vehicle_name=SM.vehicle_name) AS TGROSS,
(SELECT
SUM(net_sale)
FROM
...
There are three DMVs you can use to track tempdb usage:
sys.dm_db_task_space_usage
sys.dm_db_session_space_usage
sys.dm_db_file_space_usage
The first two will allow you to track allocations at a query & session level. The third tracks allocations across version store, user and internal object...
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
I am very new to MS SQL Server (I have always been using SAS to pull).
I have program which identifies children which where not seen in a particular month - the criterion is in bold below. When I run the program this is a example of the result:
Children which were not seen in the month of May...
I have 5 random names each for male and female. I need to insert random names based on the gender. But how can we insert names in random from a set of 5 names in SQL. Is it possible?
