Monday, March 4, 2013

[T-SQL] MaxDOP Query Hint

[T-SQL] MaxDOP Query Hint

MaxDOP Query Hint

Posted: 04 Mar 2013 12:04 AM PST

Is it possible to pass a variable for MAXDop as a query hint? My query below works when I set a constant for MAXDop, but as soon as I use a variable, I get a syntax error.declare @MAXDoParallelism as int set @MAXDoParallelism = 1select object_id as ObjectID, index_id as IndexID, partition_number as PartitionNum, avg_fragmentation_in_percent as Frag, page_count as Pages, alloc_unit_type_desc into #Fragmentation from sys.dm_db_index_physical_stats (db_id('sysutility_mdw'), null, null , null, 'sampled') where avg_fragmentation_in_percent >= 1 and avg_fragmentation_in_percent <= 100 and index_id > 0 and Page_Count >= 0 and Page_Count <= 1000000000000000000000000 and avg_fragmentation_in_percent <> 0 and avg_fragmentation_in_percent is not null order by avg_fragmentation_in_percent desc option (maxdop @MAXDoParallelism)

Time - Adding minutes and seconds

Posted: 27 Jan 2010 03:06 AM PST

I have the 2 columns with time datatypes.select top 5 [Connect Time2],[totalTime2] from dbo.verizonDetailsConnect Time2 totalTime208:05:44.0000000 00:13:00.000000008:05:57.0000000 00:01:00.000000009:07:42.0000000 00:03:00.000000009:07:46.0000000 00:09:00.000000009:08:08.0000000 00:01:00.0000000I want to add the time from totalTime2 to column [Connect Time2]. The desired result to look like this:Connect Time2 totalTime2 endTime08:05:44.0000000 00:13:00.0000000 08:18:44.000000trying to run the following:select top 5 [Connect Time2],[totalTime2], ([Connect Time2] + [totalTime2]) as endTimefrom dbo.verizonDetailsbut getting error:Operand data type time is invalid for add operator.

Query Help

Posted: 03 Mar 2013 04:37 PM PST

below is the code [code="sql"]DROP TABLE Class,Amountcreate table Class(code int,CName varchar(10))create table Amount(code int,Currency char(3),Amount float)insert into Classselect 1 , 'ASD' UNION select 2 , 'SSS' UNION select 3 , 'BBB' UNION select 4 , 'EEE' UNION select 5 , 'MMM' insert into Amountselect 3, 'CAD',230.00 UNION select 5, 'CAD',440.00 UNION select 1, 'CAD',666.00 UNION select 5, 'USD',40.00 UNION select 2, 'USD',66.00 select * from Classselect * from Amount[/code]Desired output [quote]Code Currency AmountASD CAD 666SSS CAD NABBB CAD 230EEE CAD NAMMM CAD 440ASD USD NASSS USD 66BBB USD NAEEE USD NAMMM USD 40 [/quote]

Query optimisation

Posted: 03 Mar 2013 07:39 PM PST

HiCan anyone help improving a query i haveI have a table with a list of phone numbersTable1Telephone012122345670121223456801212234569012122345440121223451701212234527012122345370121223454701212234557Iam given 5 phone numbers and i need to check if each one exists in Table1At the moment i am doing 5 individual querysset @existsTel1 = 0select @existsTel1 = 1 from Table1 where Telephone=@telephone1I would like to do this in one query, can anybody help at allthanksSimon

DB Normalisation Help required

Posted: 03 Mar 2013 02:18 PM PST

I have the data as below. I need to normalise the table with this data.. can someone help me how perfect i can normalise this?[b]Table A: (Master)[/b]ComputerNameOSOSVersionManufacturer[b]Table B:[/b]SoftwareNameSoftwareVersionSoftwareLiscencekeyInstalledversionI have attached data in Attachment.Requirement:1. [b] Both tables are having duplicate values. [/b]2. One Software will be used in multiple computers and one computer will use multiple softwaresPlease normalise this table and provide me a solution to proceed further.

Group by help

Posted: 03 Mar 2013 04:53 PM PST

Hi all This seems like a simple thing to be able to do but I am really struggling with itSome DDL[code="sql"]create table table1 (name nvarchar(50), purchase_date datetime,product nvarchar(50),quantity int)insert into table1values('John','2013-03-02 14:53:47.000','Car','2'),('Mike','2013-03-02 15:04:17.000','Car','3'),('John','2013-03-02 15:04:29.000','Car','5')[/code]How can I get the following select statement to work?[code="sql"]select name,MAX(purchase_date),product,quantityfrom table1group by name[/code]What I need returned is a distinct list of the names with the max purchase_date, including the product and the quantity for that specific max purchase dateIs this possible with a group by statement?Thanks

cursor

Posted: 03 Mar 2013 04:49 AM PST

Hello,Following is my requirement.[quote][code="other"]Table rows:SLN Id StartDate EndDate Duration TimeElapsed1 1 1/1/2012 10.12 1/1/2012 10.13 0day 00:01:00 0-days 00:01:001 1 1/1/2012 10.14 1/1/2012 10.15 0day 00:01:00 0-days 00:03:001 1 1/1/2012 10.15 1/2/2012 10.16 1day 00:01:00 1-days 00:04:00 (total- counter reset) 2 2 1/1/2012 10.12 1/1/2012 10.13 0day 00:01:00 0-days 00:01:002 2 1/1/2012 10.14 1/1/2012 10.15 0day 00:01:00 0-days 00:03:002 2 1/1/2012 10.15 1/2/2012 10.16 1day 00:01:00 1-days 00:04:00 (total- counter reset) [/code][/quote]I need to calculate Duration and TimeElapsed.Can somebody please give me sample query for this requirement.... I have all these values in my temp table, so either I use cursor or call a function and use cursor inside it... or is there any other option Achieving this C# is quite a cake walk ... Is using cursor the only option or do we have more options in db side.Many thanks

Grouping sets

Posted: 20 Feb 2013 03:39 AM PST

Hi All,how to use the groupingsets to a query having more than 32 columns plz help out

Hierarchy example - Ken Henderson's book - not working

Posted: 23 Feb 2013 10:16 AM PST

I'm working on the hierarchy lesson in Ken Henderson's The Guru's Guide to Transact-SQL. He wants to show two things. First, how to write a query that populates a temporary table (#org_chart) with all possible chartdepth levels detected in the staff table. Secondly, how to write a query that shows all the boss/subordinate relationships regardless of the levels by which they are removed from each other. But, I am having trouble with the first part, the query for building the #org_chart table. I've written it below, verbatum from book. It is syntactically correctly but when I run it adds no subsequent rows to the #org_chart table. Will someone tell me what is missing? Run in this order, the DDL, query 1, and query 2, should produce a table with 40+ rows:[code="plain"] --DDL for creating and populating staff tablecreate table staff(employee int primary key, employee_name varchar(10),supervisor int null references staff (employee))insert staff values (1, 'groucho', 1),(2, 'chico', 1),(3, 'harpo', 2),(4, 'zeppo', 2),(5, 'moe', 1),(6, 'larry', 5),(7, 'curly', 5),(8, 'shemp', 5),(9, 'joe', 8),(10, 'curly joe', 9);--query #1--establishes first 10 rows of table, with first level of chart depthselect chartdepth=1, employee=o2.employee, supervisor=o1.employeeinto #org_chartfrom staff o1 inner join staff o2 on (o1.employee=o1.supervisor)--query #2 does not work for me--it is supposed to populate #org_chart with remaining levels of chartdepth. --A while loop repeats the insert as many times as necessary to process all levels in staff tablewhile (@@rowcount > 0) begininsert #org_chart (chartdepth, employee, supervisor)select distinct o1.chartdepth+1, o2.employee, o1.supervisorfrom #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)where o1.chartdepth=(select max(chartdepth) from #org_chart)and o1.supervisor<>o1.employeeend--if following the execution of query #1 you run the below query about 4-5 times, it accomplishes what query #2 is supposed to be able to --accomplish with a While loopinsert into #org_chartselect distinct o1.chartdepth+1, o2.employee, o1.supervisorfrom #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)where o1.chartdepth=(select max(chartdepth) from #org_chart)[/code]Why is query #2 not working? Thanks.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog