Thursday, July 18, 2013

[SQL Server 2008 issues] Indexes with INCLUDE columns

[SQL Server 2008 issues] Indexes with INCLUDE columns

Indexes with INCLUDE columns

Posted: 17 Jul 2013 05:14 PM PDT

Good Day.Include columns sound wonderful , but I would like to know is How many columns should be allowed as INCLUDE columns ? I ran a missing index report once and it suggested up to 10 columns should be added to the INCLUDE columns list . I did not accept the advice as it did not look right at the time . Does it not cause unnecessary overhead on very busy OLTP systems ? I had instances where we were severely impacted by INClUDE columns where we only added 4 columns to the INCLUDE list . Thanks for you feedback in advance.Lian

How to get exact figures in fraction as in execl in sql server.

Posted: 17 Jul 2013 04:54 PM PDT

Hi,In Excel 29/30 gives me 0.966666667 that is 0.97But In Sql it gives as 0.I had tried the following code,but it gives as 0.Select Convert(Numeric(12,2),29/30)Please suggest me how to get answer as exact fraction in sql that is 0.97.Thanks in Advance!

create proc

Posted: 16 Jul 2013 09:52 PM PDT

create procedure insyenkcek@phone nvarchar(20),@SertiNom nvarchar(50),@ASA nvarchar(100),@telUnvan nvarchar(100),@kateqoriya nvarchar(100),@SHesab nvarchar(20),@Alam nvarchar(60),@IDCDMAalam nvarchar(30),@TelTarix nvarchar(50),@MuqBasTar nvarchar(50),@MuqSonTar nvarchar(50),@Milliyet nvarchar(60),@Cinsi nvarchar(50),@DoYer nvarchar(100),@DoTarix nvarchar(50),@SexVes nvarchar(60),@Elaqe nvarchar(50),@Odeme nvarchar(30),@Mebleg nvarchar(20),@BudceA nvarchar(60),@qeyd,@SenTar nvarchar(50),@MINIATS nvarchar(50),@XusKart nvarchar(20),@Eskiz nvarchar(50),@NarN nvarchar(30),@NarBTar nvarchar(30),@NarSTar nvarchar(50),@men nvarchar(100)asinsert natiq.YENCEK(phone,SertiNom ,ASA ,telUnvan,IDKat ,SHesab ,Alam ,IDCDMAalam ,TelTarix,MuqBasTar ,MuqSonTar ,Milliyet ,Cinsi ,DoYer,DoTarix ,SexVes ,Elaqe ,Odeme ,Mebleg ,BudceA ,qeyd,SenTar ,MINIATS,XusKart,Eskiz ,NarN ,NarBTar ,NarSTar ,men )select convert(int,@phone),select convert(decimal(18, 0),@SertiNom),select @ASA,select @telUnvan,(select Kod from natiq.kateqor where Name=@kateqoriya ),select convert(int,@SHesab) ,(select Kod from natiq.alamat where Name=@Alam) ,(select Kod from natiq.CDM where Name=@IDCMAalam),select @TelTarix,select @MuqBasTar ,select @MuqSonTar ,(select Kod from natiq.milli where Name=@Milliyet ),(select kod from cins where Name=@Cinsi) ,select @DoYer ,select @DoTarix ,select @SexVes ,select @Elaqe ,(select Kod from natiq.ode where Name=@Odeme) ,select @Mebleg ,(select Kod from natiq.budc where Name=@BudceA) ,select @qeyd,select @SenTar ,(select Kod from natiq.minats where Name=@MINIATS) ,select @XusKart ,select @Eskiz ,select @NarN,select @NarBTar ,select @NarSTar ,select @men -------getting errorPost 170, Level 15, State 1, Procedure insyenkcek, line 5Line 5: Incorrect syntax near ','.Post 156, Level 15, State 1, Procedure insyenkcek, line 19Incorrect syntax near the keyword 'select'.Post 156, Level 15, State 1, Procedure insyenkcek, line 19Incorrect syntax near the keyword 'select'.Post 156, Level 15, State 1, Procedure insyenkcek, line 19Incorrect syntax near the keyword 'select'.Post 156, Level 15, State 1, Procedure insyenkcek, line 20Incorrect syntax near the keyword 'select'.Message 137, level 15, the status 2, the procedure insyenkcek, line 20Must declare the variable '@ IDCMAalam'.Post 156, Level 15, State 1, Procedure insyenkcek, line 21Incorrect syntax near the keyword 'select'.Post 156, Level 15, State 1, Procedure insyenkcek, line 22Incorrect syntax near the keyword 'select'.Post 156, Level 15, State 1, Procedure insyenkcek, line 22Incorrect syntax near the keyword 'select'.Post 156, Level 15, State 1, Procedure insyenkcek, line 22Incorrect syntax near the keyword 'select'.Post 156, Level 15, State 1, Procedure insyenkcek, line 23Incorrect syntax near the keyword 'select'.Post 156, Level 15, State 1, Procedure insyenkcek, line 23Incorrect syntax near the keyword 'select'.Post 156, Level 15, State 1, Procedure insyenkcek, line 23Incorrect syntax near the keyword 'select'.Post 156, Level 15, State 1, Procedure insyenkcek, line 23Incorrect syntax near the keyword 'select'.Message 137, level 15, the status 2, the procedure insyenkcek, line 23Must declare the variable '@ qeyd'.Post 137, Level 15, State 2, Procedure insyenkcek, line 24Must declare the variable '@ SenTar'.Post 137, Level 15, State 2, Procedure insyenkcek, line 24Must declare the variable '@ MINIATS'.Post 137, Level 15, State 2, Procedure insyenkcek, line 24Must declare the variable '@ XusKart'.Post 137, Level 15, State 2, Procedure insyenkcek, line 24Must declare the variable '@ Eskiz'.Post 137, Level 15, State 2, Procedure insyenkcek, line 24Must declare the variable '@ NarN'.Post 137, Level 15, State 2, Procedure insyenkcek, line 25Must declare the variable '@ NarBTar'.Post 137, Level 15, State 2, Procedure insyenkcek, line 25Must declare the variable '@ NarSTar'.Post 137, Level 15, State 2, Procedure insyenkcek, line 25Must declare the variable '@ men'.

SQL Netbackup Job failures

Posted: 17 Jul 2013 08:41 AM PDT

Hi all,I have an instance where there are 2 databases and it is backed up by Netbackup MS sql client directly to tape.However, one of the database is failing when it runs on schedule, but when I manually backup through netbackup client, it works.....I was thinking if it's a permission issue and I checked the acct Netbackup is running on and it is Local System.....but the other db backup is running fine, so, I am not sure if it's a permission issue since it uses the same acct.Someone asked me to check the Autosys scripts.....I am not very familiar with it.If anyone have experience with netbackup plz advise.

SQL 2008 - Connecting to Integration services

Posted: 17 Jul 2013 05:49 AM PDT

From my SQL 2008 installed development machine, to connect to Integration services of an SQL 2008 server, do I have to do anything. I do have sysadmin access, but still i cant connect to IS of that server, geting an errorConnect to SSIS Service on machine "ABC" failed: Access is denied.I dont want to login into the server box and open the IS to view my packages. I should be able to connect and view them from my dev machine.

using between on string columns

Posted: 17 Jul 2013 12:14 PM PDT

This is puzzling.I've run across a case where using between on string columns returns rows I'm not expecting. The following code will demonstrate the issue[code]declare @t table(Start varchar(10) ,[End] varchar(10)) insert @tvalues('1500','1599') ,('2400','2799') ,('1','599') ,('2800','2999') ,('700','799') ,('A1','A3') ,('B4','B9') declare @val varchar(10)set @val = '2609' select *from @twhere @val between Start and [End] [/code]I'm only expect to get 1 row back. But instead 2 rows are returning. Here's what is coming back..Start End2400 27991 599I can't figure out why the 1, 599 row is coming back. Does anybody have any ideas why this is happening?Thanks!

Sending mail from On Prem server to Off prem exchange

Posted: 12 Jul 2013 05:20 AM PDT

Im having some troubles getting my SQL mail setup using an off premise exchange with office 365. I can get the server to send through googles smtp but cant get it to work with office365. Has anyone gotten this type of setup to work ? Ive found through searching that i may have to setup an SMTP server locally to route to the online exchange ?? Below is a link to a description.[url=http://blogs.technet.com/b/meamcs/archive/2013/02/25/how-to-configure-sql-database-mail-so-send-emails-using-office-365-exchange-online-a-walkthrough.aspx][/url]

Tell me if I'm crazy - Recursive CTE + View (Materialized, indexed?)

Posted: 17 Jul 2013 02:09 PM PDT

First off, I'm a .NET developer, and not a SQL wonk. Please be gentle. And thanks for your help.So I have a lattice (a multiparent hierarchy) of objects representing sets of people sliced along some axis. Currently these sets exist as documents in RavenDB. Example:Set{ Id: 'some-guid', Name: 'RegionName', Dimension: 'Location', ImmediateParents: ['containing-set-id', 'containing-set-id'], ImmediateChildren: ['contained-set-id', 'contained-set-id'], ContainedBy: ['denormalized', 'list', 'of', 'all', 'sets', 'that', 'contain', 'me']}Person{ ... ImmediateParents: ['sets', 'i', 'am', 'assigned', 'to', 'directly'], ContainedBy: ['denormalized', 'list', 'of', 'all', 'sets', 'that', 'contain', 'me']}The bulk of our queries are either direct reads from one of those lists, or "give me all the people contained by this set" query. Obviously our current implementation makes those queries really fast, but keeping all this updated is a delicate dance. It occurred to me that having a normalized representation in SQL, and then building a view on top of it to denormalize the "contained by" might get me off the dancefloor. And that if I could materialize (usage?) that view and index it, I could get roughly the same performance profile I have now, with the workload heavily weighted toward write time.Then tonight I learned of Recursive CTEs and the whole thing gelled for me. But I'm betting on some pretty big assumptions about features of Views that I don't understand. Am I sniffing up a good tree? Thanks for any insight.

Selecting records in table only when ALL related records in other table satisfy multiple conditions

Posted: 15 Jul 2013 03:23 AM PDT

Hello and thank you for your time. I feel like this should be simple but I can't seem to work it out….maybe I have been working on it so long that I need another perspective.I have a Lab_result_obr_p table that contains labs that have been resulted: The PK is LabNum which is the lab Number and the FK is OrderNum which is the order number. So in this table one order# can have multiple lab#sTable: Lab_result_obr_p:LabNum OrderNum A29E02FE D2C6DDA84A563D24 D2C6DDA80F53BC60 DC01E4EBGS43A689 DC01E4EBF943C7034 DF2654G7The other table is Lab_rsults_obx and it contains the lab components and individual component results. In this table, each lab# (uique_obr_Number) from above will have multiple lab comments and results Table: Lab_rsults_obxLabNem LabC Flag ValueA29E02FE WBC N 3.5A29E02FE RBC N 260 4A563D24 LMP: N 504A563D24 RH TYPE N DNR0F53BC60 BACTERIA N TNPGS43A689 MCV N 30GS43A689 MCH N 40F943C7034 RH TYPE Y NegativeI need to select all ngn_order_num from Lab_result_obr_p where all components of all labs has a Abnormal_Flag of N and does not have a value of TNP. So if an Order has two labs, I need all the components for both labs to have an N for Abnormal_Flag and to not have a value of TNP for the order number to be selected So for the data above my result would look like:ngn_order_num D2C6DDA8

Log shipping: New Index Not carried over

Posted: 17 Jul 2013 02:45 AM PDT

I have a few databases that are log shipping from ServerA to ServerB where they are available as a Standby/Read Only. Yesterday I dropped one index on ServerA.DatabaseA and created a brand new index on ServerA.DatabaseAThe Restore takes place at midnight and according to my history log and according to the errorlog, all the logs were applied to ServerB.DatabaseAYet, I do not see the newly created index and the old index is still on ServerB.DatabaseAPlease advise.

sys.dm_db_index_physical_stats - Doubt in the Results

Posted: 17 Jul 2013 07:45 AM PDT

For a table with 3 indexes, 1 clustered and 2 NC, Index Physical status dmv returned 53 rows with clustered index repeated 4 times with varying % fragmentation, similarly for other 2 indexes too.why multipls rows for one index?

How to get the month wise row of record if transaction present or not

Posted: 14 Jul 2013 03:06 AM PDT

Hi,Is that possible to write in single query to get the below results -I need to get the month wise records from the transaction table. If records for that month is not present, then previous month set of common columns values to be created for current month (Company, Customer ID, Account No, Type) with previous month amount as opening balance and current amount as 0 and closing balance as opening balance.thanks

Sending email to recipients based on result set

Posted: 17 Jul 2013 04:34 AM PDT

I am trying to find an article or guide for creating an SSIS package(best method?) with a for each loop container.I query a table, returning 5 columns, the result has the recipients email address, and name, i need to send the same email message to the entire result set.Using sql server 2008 r2 and database mail. do i place the send email task within the loop and somehow override the fields i want to overrride?Changing the body of the email appears do able as i would insert that into each pass, but changing the recipient seems to be a different story.Thanks :crazy:

Database Mirror Login SID for ##MS_PolicyEventProcessingLogin##

Posted: 16 Jul 2013 10:51 PM PDT

In William Thier's excellent SSC article "Configuring Database Mirroring" ( http://www.sqlservercentral.com/articles/Database+Mirroring/72009/ ) he mentions... "[i]If the mirrored database contains non domain users, SQL_LOGINs should be created on the mirror server with the same sid as the corresponding login on the principal server[/i]"We have a Login on both our PRINCIPAL & MIRROR, created by the SQL Server 2008 R2 E'prise installation, called [b]##MS_PolicyEventProcessingLogin## [/b]--- with different SID's.Does this Login need to be sync'd on the MIRROR w/ the same SID?

using the default trace

Posted: 17 Jul 2013 02:19 AM PDT

HiLate in the afternoon i had a support ticket raised for 'the app'. It was running slow around 9.30am untill 11.00amI checked the machine:processor time using 'sql monitor' and sure enough the processor was pegged up at 80 - 90% usage for the time period where 'the app' was running slow.I have used the back in time feature of sql monitor but all i can see query wise is the sql monitor queries.Can you use the default traces to look at what may have caused this peak in cpu and if so how do i go about it ?ThanksPJ:-)

Join of 2 subqueries

Posted: 17 Jul 2013 12:05 AM PDT

Hi All,I've been trying to do a join of 2 subqueries, but haven't been having much luck and keep getting errors. Can anyone please advise on what I'm doing wrong here? Thanks! Declare @SweepId as int = 10160, @RunType as varchar = 'Initial' Select *from(Select distinct ClaimId , LineNum , 0 as EncounterLineNum , EncounterType , InternalICN , PreviousDpwICN , 0 as ClaimFrequencyCd , EncounterPeriod , 2 as StatusCd , BypassReason , null as EncounterFileNm ,@SweepId as SweepId from IntEncTracking.EncounterList where bypassflag = 1) as q1 join ( Select ClaimId , 0 as LineNum , 0 as EncounterLineNum , EncounterType , InternalICN , PreviousDpwICN , max(ClaimFreqCd) as ClaimFreqCd , max(EncounterPeriod) as EncounterPeriod , case when exists (select 'x' from IntEncTracking.EncounterList el1 where el1.claimid = claimid and BypassFlag = 0) then 1 else 2 end stscd , case when @RunType = 'Initial' then 100 else 300 end as [StatusReasonCd] , null as EncounterFileNm , @SweepId as SweepId from IntEncTracking.EncounterList el where BypassFlag = 0) as q2 on q1.ClaimId = q2.ClaimId and q1.LineNum = q2.LineNum and q1.EncounterLineNum = q2.EncounterLineNum and q1.EncounterType = q2.EncounterType and q1.InternalICN = q2.InternalICN group by q1.ClaimId, q1.EncounterType, q1.InternalICN, q1.PreviousDpwICN order by q2.ClaimId, q2.LineNum, q2.EncounterLineNum, q2.EncounterType, q2.InternalICN

capture currently logged in users from sql

Posted: 17 Jul 2013 12:55 AM PDT

I want to capture users logged int o my database from 9-5 and then get the average per day.can i please have the guidence

parsing data in the middle of a field??

Posted: 17 Jul 2013 12:01 AM PDT

Hi I have a field with a data string similar to ""....@@T_700_ = "text goes here"@@T_301_ = "06/15/2013"@@T_069_ =.....I need to pickup what between @@T_700_ = " and "@@T_301_ so in the above I end up with[b] text goes here[/b]ThanksJoe

How to take backup of Single table and how to restore?

Posted: 13 Jul 2013 03:44 PM PDT

Hi... How to take backup of Single table and how to restore? is there any query like database backup?shivakumar...

Autogrowth of database with it's file name

Posted: 16 Jul 2013 11:09 PM PDT

I use below query to get auto-growth event occurred to databases in a sql server.[code="sql"]DECLARE @trcfilename VARCHAR(1000);SELECT @trcfilename = path FROM sys.traces WHERE is_default = 1SELECT COUNT(*)as no_of_autogrowths,sum(duration/(1000*60)) as time_in_min,FilenameFROM ::fn_trace_gettable(@trcfilename, default)WHERE (EventClass = 92 OR EventClass = 93)GROUP BY Filename[/code]It outputs number of auto-growths,time taken for auto-growth and logical name of the file. But i want physical name of the file(mdf and ldf file name) instead of logical name.I don't know whether from sys.traces i will get physical name or please help me with an alternate way to do it.

How to create database from .mdf file only

Posted: 10 Jan 2012 04:55 AM PST

Hi,I have installed SQL Server 2012 and downloaded sanple database from the below link, which has AdventureWorks2008R2_Data.mdf for download.http://msftdbprodsamples.codeplex.com/releases/view/55330Now, how to restore AdventureWorks database from AdventureWorks2008R2_Data.mdf file?Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog