[SQL Server] Inserting into an auto increment PK field |
- Inserting into an auto increment PK field
- getting the correct sum when join condition works.
- Automating Backup Monitoring Error
- Monitoring Lazy Writer
| Inserting into an auto increment PK field Posted: 09 Apr 2013 09:31 AM PDT i have a table as follows ColA PK autoincrementColb ncharColC ncahrColD nchar and i have to insert values into this table but ensure that the first column is always inserted with a value that is one more than the primary key that already exists. is there an inbuilt SQL function that does this on sql server.thanks for the help |
| getting the correct sum when join condition works. Posted: 09 Apr 2013 05:04 AM PDT Hello all, i am having a problem trying to get the sum of a column if a join happens. Basically, i want to sum up a quantity for all rows in table A that can join to table B. the problem is when there are two or more rows in table B, the quantity sum doubles or triples. for instance, in the following setup:create table playground.sale (email varchar(255), quantity int);insert into sale values ('email1', 1);insert into sale values ('email2', 2);insert into sale values ('email3', 3);insert into sale values ('email4', 4);create table eval (server_id varchar(255), email varchar(255));insert into eval values ('ServerID1', 'email1');insert into eval values ('ServerID2', 'email1');insert into eval values ('ServerID3', 'email1');insert into eval values ('ServerID6', 'email1');select sum(s.quantity)from sale s inner join eval eon s.email = e.emailwhere s.email = 'email1'So, the above will sum to 4. but i only want it to sum to 1. is there a way to get this desired result using a join? i know i can get the result using a 'where s.email in (select email from eval), but that is taking forever and i'm looking to optimize. Thanks in advance for your help. |
| Automating Backup Monitoring Error Posted: 09 Apr 2013 01:07 AM PDT Hi All,I have created an stored procedure which uses thebelow select statement :select getdate(),'Old or No Backup At All For the database ' + s.dbname + ' no backup for' ,s.[Days since Backup] from (SELECT t.name as [DBName], (COALESCE(Convert(varchar(10),MAX(datediff(d, getdate(), u.backup_finish_date))),101)) as [Days since Backup] FROM SYS.DATABASES t left outer JOIN msdb.dbo.BACKUPSET u ON t.name = u.database_name group by t.Name) as s where s.dbname <> 'tempdb' Now, I have a batch job which calls this procedure and the output of this select statement is sent to output.log file.When I Open this file, I get the result :as"2013-04-09 18:55:00.293 Old or No Backup At All For the database AdventureWorks2008 no backup for"And the expected result should be "2013-04-09 18:55:00.293 Old or No Backup At All For the database AdventureWorks2008 no backup for 25(This is the Days difference between getdate and last backup of DB)"So the value after for is missing in theoutput file. Although, the value is perfectly displayed in SSMS..Can someone please suggest if there is something that I am missing in this script? |
| Posted: 08 Apr 2013 11:51 PM PDT Hi AllI have set up a test scenario to monitor the effects of the Lazy Writer by limiting my SQL instance to 100MB and by querying large tables.Using the below query I can see my Clean page count fluctuate as it reaches 100MB - I am assuming the Lazy writer is doing this to maintain a decent amount of free buffers by removing [i]older[/i] pages from the buffer pool[code="sql"]SELECT (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name', SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS DirtyPageCount, SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS CleanPageCount,SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END)*8/1024 AS CleanMB FROM sys.dm_os_buffer_descriptors GROUP BY database_id ORDER BY DB_NAME(database_id) GO [/code]The problem is that while I was monitoring the buffer descriptors, I was also monitoring the instance using Performance Monitors, while monitoring I noticed that there was absolutely no increase in the LazyWrites/sec during the entire test. Why is this? Surely the Lazy Writer must have been doing something?Any thoughts?Thanks |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment