Thursday, June 6, 2013

[SQL Server 2008 issues] Query Tuning

[SQL Server 2008 issues] Query Tuning

Query Tuning

Posted: 02 Jun 2013 08:40 PM PDT

Hi all,How to do query tuning. Is there any methods for query tuning.how to find Query cost .What is the importance of this in tuning.

Passing multiple values through one parameter in a stored procedure

Posted: 04 Oct 2012 02:00 AM PDT

Below is a script to create a table, and one to create and execute a stored procedure on the table, I am trying to build a report that passes multiple values in one parameter, but the stored procedure takes the inputs as a comma delimited list. I came across some sites that gave script for a splitlist function that will individually delimit the list but when I put that into the stored procedure it is not recognized. Does anybody see anything wrong with what I have here and if so, have a solution?Thanks a bunch!CREATE TABLE [dbo].[Customer]( [CustomerID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [MiddleName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NOT NULL )GOINSERT INTO dbo.CustomerValues( 'John','M.','Lynch'),('Jason','TE','Whitten'),('Jay','OldSchool','Novachek')GO------------------------Create stored procedureCREATE PROCEDURE Example -- Add the parameters for the stored procedure here @CustomerID int = 0 ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT CustomerID, LastName FROM dbo.Customer WHERE CustomerID IN (select * FROM dbo.Splitlist(@CustomerID, ',')) -------Here is the problem, I cannot get the sp to recognize the function unless I delimit the query and set it as a variable -----and append the where statement to the end. How do I do this?ENDGO-----Execute stored procedure for the tableDECLARE @return_value intDECLARE @CustomerId intEXEC @return_value = [dbo].[Example] @CustomerId = 1---------Would like to be able to execute for IN ('1','2','3') GO

Sharing Data between client and Server two Way

Posted: 05 Jun 2013 04:44 PM PDT

Please solve my queryOne of my Silverlight application is running.This Application is using one central database (sql server 2008) and three other client databases (sql server 2008) at other server locations.Our requirement is with minor changing or without changing in Silverlight applicationWhen we make change in central database (insert update and delete in multiple tables) these change Automatically occurs in all client database.and when any change made in any client database all client as well as central database should be updated automatically.For this I read some about Sync Framework and SQL Server 2008 Change Tracking.But i have no idea about how will I do this.Please help me. How can we achieve this.

How can we find whether statistics were updated with FULLSCAN, or with a sampling

Posted: 05 Jun 2013 07:23 AM PDT

How can we find whether statistics were updated with FULLSCAN, or with a sampling?

Fizz Buzz interview questions for phone screens?

Posted: 05 Jun 2013 05:51 AM PDT

We're trying to find basic SQL interview questions where the candidate can answer over the phone. There are surprisingly a large number of candidates who can't answer simple query questions.We've asked a variant of the Fizz Buzz question over the phone and we're just expecting to hear "CASE statement" and "modulus" from them. Another simple question without them needing to write anything down is, List duplicate values in a table. We just basically need to hear "Group By <field>" and "having count(*) > 1". Any other basic [i]querying [/i]questions for over the phone? We already have an assortment of standard non-querying questions such as diff between union and union all. We're bringing in several people that can answer some sql questions but apparently can't write queries so we need to do a better job of phone screening with querying questions. Thanks!

Reduce lob logical reads &gt;&gt; varbinary &gt;&gt; image

Posted: 05 Jun 2013 09:48 AM PDT

Hello guys,I have little problem with select, which use table with <DATA TYPE IMAGE> I optimize the query and reduce logical reads, but nevertheless there is lob logical reads...Is there any chance reduce this ? Or somebody has experience with image in table or do you know any different issue? [quote]SET STATISTICS IO ONSELECT [IMAGE_PP] FROM [MCS_MESDB].[dbo].[TB_CODE_ITEM] WHERE ITEM_CODE='CP080'SELECT SUM(datalength([IMAGE_PP])) / 1048576.0FROM [MCS_MESDB].[dbo].[TB_CODE_ITEM] WHERE ITEM_CODE='CP080'(1 row(s) affected)Table 'TB_CODE_ITEM'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 706, lob physical reads 0, lob read-ahead reads 110.(1 row(s) affected)Table 'TB_CODE_ITEM'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 2, lob physical reads 0, lob read-ahead reads 0.[/quote]I tried select just one row with clasule where and found select for DATALENGTH >> first result some binary data >> second result I think that it will be size of the image >> 0.910820007 MB but second has just 2lob reads, its possible get same result with normal select ? Or is this column for so much pages ??? Thanks

SSIS Derived Column Transformation Expression Divisor 0 Error

Posted: 05 Jun 2013 09:46 AM PDT

Sorry if there's another post that covers this, but I couldn't find one. And I know I've had to deal with this before, but I don't remember what method I had used.But here's the expression: (ExcessCapacity * RouteVehicleCostAlloc)/ TotalPayrollCost and I confirmed that TotalPayrollCost is showing 0 in the data viewers.I thought there would be a solution using ISNULL and NULLIF together, but I just can't quite get that to work. Any suggestions?>> This is for clarification: I just learned that the Transformation Editor doesn't allow NULLIF, so that won't work. But I meant to include what I was trying to do and it took but I still got an error:ISNULL(TotalPayrollCost) ? 0 : (ExcessCapacity * RouteVehicleCostAlloc) / TotalPayrollCost <<Thanks in advance!

(Change in Planning) How can I add some indicator to the record which states that it is deleted by the stored procedure.

Posted: 05 Jun 2013 01:34 AM PDT

Hi frinds,I tried to search it down..for the solution , but i think now i have to make a change in planning.How can i add any thing as a indicator to state that , particular record is deleted, with the stored procedure, during Incremental ELT.I actually don't want to delete it, but just want to make some kind of indication that record is deleted.for an example as I also posted yesterday.Source tableStudent ID Name Class Grade Last Updated1 Pratik 12th A 2013-06-042 Jigar 11th B 2013-05-04Destination table.Student ID Name Class Grade Last Updated Status1 Pratik 12th A 2012-12-04 2 Jigar 11th B 2012-11-04 3 Daljit 12th A 2012-09-08 Destination tableAfter applying the stored procedure which takes column dynamically..the result should be. Student ID Name Class Grade Last Updated Status1 Pratik 12th A 2013-06-04 2 Jigar 11th B 2013-05-04 3 Daljit 12th A 2012-09-08 DeletedI am using this stored procedure.http://www.sqlservercentral.com/articles/EDW/77100/Please help me or give me some hint about How I can achive this.?Thanks.

OPENROWSET BULK misses last row of file

Posted: 12 Oct 2010 05:21 AM PDT

I have a problem whereby some of our import files are missing the row terminator (carriage return) in the last row of the text file. When I use OPENROWSET BULK to import the files, the last row of the file is missed when it excludes the row terminator ie. just EOF. This would be OK if OPENROWSET returned an error (MAXERRORS=0), but instead it just ignores the last row. Because we have so many files to import, I am faced with having to write a CLR component to verify that the last row terminator exists before the file is imported. From my searches online, I don't see that anyone else has raised this issue, so I am wondering whether I have missed the obvious.Here's a simplified clip of my script (the dynamic SQL IS required because of runtime variations in file paths and table/field names):[code="sql"]SET @OpenRowSetSQL = 'INSERT INTO MyInsertTable (Field1, Field2 Field3, Field4) SELECT Field1, Field2, Field3,@SomeOtherVariable FROM OPENROWSET(BULK ''c:\MyImportFile.txt'', FORMATFILE=''MyFormatFile.xml'', MAXERRORS=0) txt' EXEC sp_ExecuteSQL @OpenRowSetSQL[/code]The format file looks like this: [code="other"]<?xml version="1.0"?> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <RECORD> <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/></RECORD> <ROW> <COLUMN SOURCE="1" NAME="Username" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="2" NAME="ActionRef" xsi:type="SQLNVARCHAR"/> <COLUMN SOURCE="3" NAME="ReadOnly" xsi:type="SQLBIT"/> <COLUMN SOURCE="4" NAME="ScopeID" xsi:type="SQLNVARCHAR"/></ROW> </BCPFORMAT>[/code][actually the row terminator for field ID 4 is "backslash r backslash n", but when I post to this forum the backslash n isn't shown, presumably because its a control character sequence]And as I mentioned, if the row terminator exists on the last row, everything is fine. But if the row terminator is missing from the last row, all but that last row is imported - but without returning an error or indication of skipped bytes. I don't even get an "unexpected EOF" message.I'd appreciate any pointers with either trapping the error, or forcing import of that last row, even if the EOF is encountered before the row terminator.Thanks!

SSIS - What is the best practice for creating a fixed length flat file.

Posted: 29 May 2013 01:36 AM PDT

I am running into the problem of SSIS putting the CRLF in random places at the end of the row. If we assume I am going to start from scratch, can I fix this issue in the Stored Procedure I use in my OLE source, or can I fix it on the SSIS side?I did see the following at MSDN but it did not make any sense to me since I don't see where to do this in the connection manager.[i][b]To make this work so that each row is exactly the same length, you may need to add a zero length column as the last one in the connection manager, and set the row delimiter on that one.[/b][/i]I did read as possibly using the advanced editor on the OLE adapter and adding a column there but how do I create a zero length column. If I send the header in the first row it is going to be at least 1 character.Any help would be greatly appreciated.FYI, I am using ragged right.

SSMS Express Edition Management Tools 2008 connecting to SQL Server 2012 EE Eval Copy?

Posted: 05 Jun 2013 07:32 AM PDT

Can the SSMS Express Edition 2008 Management Tools be used to connect to SQL Server 2012 EE Eval Copy? What about SQL Server 2005 Manangement Tools connecting to SQL Server 2012 EE Eval Copy?Thanks, Kevin

Weird Behavior of a Stored Proc

Posted: 05 Jun 2013 05:43 AM PDT

I have a stored procedure that's called by a web application. Every now and then, the web call will time out for no apparent reason. But I can still run the proc smoothly in SSMS. The way I stumbled on to resolve the timeout issue is to drop the proc and recreate it. Nothing has been changed, but the web app will be back to normal. Any thoughts on why this is happening?

Multiple tables inserts

Posted: 05 Jun 2013 04:54 AM PDT

Hello,I have next task:From one database, 3 tables (each 3 fields are chosen) to insert in another database's fields from 4 tables.First source database is simple, will create a view.Any good documentation on how not to break relationships in destination database.In addition, how to just update the destination after first upload of all.Thanks,Brano

Import of huge XML file

Posted: 21 May 2013 06:01 PM PDT

Hi All!I have an xml file of 44 Gb (Not Meg, its really GB)Delivered by the Danish custom authorities.My problem is simple - How to import such a beast?I have seen a limit of 2.1 Gb everywhereBest regardsEdvard Korsbæk

Primary data file still growing when a secondary filegroup exists and checked as default?

Posted: 05 Jun 2013 02:05 AM PDT

I have a 750gb data file that is in the primary filegroup. It is still set to autogrowth 100mb. A secondary data file and filegroup was created and checked as the default. The size is about 112gb. Sorry if a stupid question but why is the primary data file still growing when a second data file has been added? I would like to not have the primary file grow any larger. Is that possible?

How To Transparently Relocate SQL Server connections from old to new SQL Server without User/Client Actions/Changes

Posted: 05 Jun 2013 03:49 AM PDT

We have a single SQL Server running 2 named instances (no default instances) and one is running on SQL Server 2005 and the other SQL Server 2008R2. We did this because we were going thru a transitionary period upgrading our primary accounting DB from SQL 2005 to SQL 2008R2 and we had but one server to use so SQL 2005 and SQL 2008R2 were installed. This has worked well for us over the last few months but now it's time to move to an updated server on a new SANS. The server that hosts these 2 versions of SQL Server is a VMWare instance and so we have 2 choices for moving to the new SANS. 1) We can either move the entire VM instance so that when its all done the same 2 instance names are back up and running and to the any client/user everything is the same as far as the login credentials for SQL Server.OR2) We can setup a new VMWare instance on the sans, install just SQL Server 2008R2 and then move the DB from the old server to the new. The benefit to #2 is we get rid of SQL Server 2005 which we no longer need and we start anew (i.e. some of the OS level stuff can be changed per the IT Admins). The downside is that we'd have a new instance name and so anything and everything that has the SQL Server Name/Instance hard coded (and there are quite a few both internal and external to our network) would have to be changed/updated to new instance name.The benefit to #1 is no changes to any client/user/service that connects to the DB would need to be updated. The downside is we aren't moving forward.Aside form using the SQL Utility to create aliases on every client computer, Does anyone have any suggestions on how I can do the best of both worlds, setup a new SQL Server 2008 R2 instance and when it comes time to go live I can have all users/clients that were connecting to the current SQL Server will then connect to the new ine without having to change any of their SQL specific connection settings such as SQL Server Name/Insatnce?Thanks

set statistics time question

Posted: 05 Jun 2013 03:30 AM PDT

Hi All,I'm checking the performance of 2 scripts, they get the same data.When I check the execution time with [code="sql"]set statistics time on;[/code]I get the following results:[code="plain"] SQL Server Execution Times: CPU time = 546 ms, elapsed time = 662 ms.[/code][code="plain"] SQL Server Execution Times: CPU time = 937 ms, elapsed time = 1030 ms.[/code]But in SSMS status bar it's showing me 00:00:02 seconds.And it seems that the query with the shorter execution times is taking longer to show.Now can someone explain to me why the status bar shows 2 seconds when the elapsed time is 1030 ms?Thanks...

Help with restore backups

Posted: 05 Jun 2013 03:29 AM PDT

I have two backup files that need to be restored to the same database. First file is already restored. How do I append (restore) the second fileto the same database. I don't see append option through GUI in 2008

Audit changes in table using stored procedure

Posted: 05 Jun 2013 01:45 AM PDT

I would like to ask for help with creating a stored procedure to generate below information:1- A time stampt for record creation in a table, a time stamp shows when was updated, and which user last the record. I know that by querying sys.dm_db_index_usage_stats you can get the information, but I like to have a store procedure created in master database to run it whenever any changes are made in any database.Thanks,Lava

Synchronization between SQL table and SharePoint List

Posted: 05 Jun 2013 02:25 AM PDT

I am at a lost on how best to synchronize data between a SharePoint List and a SQL Table. I have installed the adapter found here: [url]http://sqlsrvintegrationsrv.codeplex.com/[/url]. I've got it to pull the data from the SharePoint List to my table. What I need now, and don't know how to do, is when changes are made to the list or new information is add, how do I reflect those changes in the SQL table? I don't need to write back to the list only pull from it. I know about SharePoint BCS, but we don't have access to it with our implementation of SharePoint (not sure why). So, SSIS is my only option. I am not an SSIS expert, so any step by step directions someone can point me to/provide would be most appreciated. Thanks.Using SQL Server 2008 and SharePoint 2010

Qery to fetch X months old data

Posted: 04 Jun 2013 08:02 PM PDT

Hi All,I Need all your help to find out the best approach for fetching out the past Records in a table.1.I have a CREATE_DT column in my Table .2.Based on that date i need to fetch the X Months Old data , Parameter @Months 3. IF @Months =0 then i need to fetch all months data in my table .4. i have 2 approaches to achieve this but , considering the amount of volume I'm dealing with it's giving a performance hit.Could you guys please help me in achieving the best way to achieve this functionality[code="sql"]CREATE TABLE #DummyData( ID INT IDENTITY(1,1) ,NAME VARCHAR(200) ,CREATE_DT DATETIME)INSERT INTO #DummyDataVALUES('User 1',DATEADD(MONTH,-22,GETDATE()))INSERT INTO #DummyDataVALUES('User 2',DATEADD(MONTH,-20,GETDATE()))INSERT INTO #DummyDataVALUES('User 3',DATEADD(MONTH,-12,GETDATE()))INSERT INTO #DummyDataVALUES('User 4',DATEADD(MONTH,-11,GETDATE()))INSERT INTO #DummyDataVALUES('User 5',DATEADD(MONTH,-8,GETDATE()))INSERT INTO #DummyDataVALUES('User 6',DATEADD(MONTH,-7,GETDATE()))INSERT INTO #DummyDataVALUES('User 7',DATEADD(MONTH,-6,GETDATE()))INSERT INTO #DummyDataVALUES('User 8',DATEADD(MONTH,-5,GETDATE()))INSERT INTO #DummyDataVALUES('User 9',DATEADD(MONTH,-4,GETDATE()))INSERT INTO #DummyDataVALUES('User 10',DATEADD(MONTH,-3,GETDATE()))INSERT INTO #DummyDataVALUES('User 11',DATEADD(MONTH,-2,GETDATE()))INSERT INTO #DummyDataVALUES('User 12',GETDATE())DECLARE @Months INT =1DECLARE @MIN_DATE DATETIME DECLARE @MAX_DATE DATETIME =GETDATE()/*********************************Approach 1***********************************/SET @MIN_DATE =DATEADD(MONTH,-@Months,GETDATE())SELECT 'APPROACH1',*FROM #DummyDataWHERE --CREATE_DT BETWEEN @MIN_DATE AND @MAX_DATE ( (@Months >0 AND CREATE_DT BETWEEN @MIN_DATE AND @MAX_DATE) OR (@Months<=0 AND CREATE_DT BETWEEN CREATE_DT AND @MAX_DATE) ) /*********************************Approach 2***********************************/ IF @Months >0BEGIN SET @MIN_DATE =DATEADD(MONTH,-@Months,GETDATE())ENDELSEBEGIN SELECT @MIN_DATE =MIN(CREATE_DT) FROM #DummyDataEND SELECT 'APPROACH2', * FROM #DummyDataWHERE CREATE_DT BETWEEN @MIN_DATE AND @MAX_DATEDROP TABLE #DummyData[/code]

Scheduled Job

Posted: 05 Jun 2013 01:08 AM PDT

HiWe use a CMS to run our jobs on all the servers. I have logged onto ssms on the cms server and created a job under the Multi-Server-Jobs folder.The cms server only has the system databases and a redgate database for our sql monitor, so the problem (if it is a problem) is that i cant seem to select the databases i want the server to run the job against (the job is in the form of Stored proc).In the Job step properties under the General tab, i have the step name, type (Transace-SQL script), Run as (this is blank) and the database (this currently shows MASTER). Then in command box i have EXEC dbo.StoredProcName.Under the 'TARGETS' tab i have selected all the servers i want the job to run against.So in summary I am concerned that I will be running this Sp against the MASTER db (which is selected in the database section of the properties of the job on the CMS)when i need to be running it against the database X on the other servers

SQLCMD Utility and Input Parameters

Posted: 04 Jun 2013 11:34 PM PDT

Hello EveryoneHow is your day going so far?I am working with some SQL Command scripts. I was asked by an Oracle DBA, and front-end developer if the SQL Server SQLCMD line can contain input parameters. I cannot find anything in the SQL BOL that indicates this. I am thinking that it cannot.Does anyone have experience with the SQLCMD line Utility that has used it in a similar way?Thank you in advance for your comments, suggestions and assistanceAndrew SQLDBA

SQL 2012 Restore Issue

Posted: 04 Jun 2013 09:16 PM PDT

SQL 2012 Restore Issue.Anybody ever experience this I'm trying to restore a copy of DB 'Y' from another server.I already database named DB 'Y ' on the test server. I'm trying to create another copy of the database from the backup and call it DB 'ABC'. But I get exclusive access to ABC could not be obtained.DB ABC doesn't exist on this server, so for me the error makes no sense. All other restores work fine as long as the original db name isn't the same as any other dbs on the server

Why is my SQL Server slow?

Posted: 04 Jun 2013 09:52 PM PDT

Hey,I have a SQL 2008 R2, running on Windows Server 2008, with all latest service packs and updates. It runs approx. 30 databases that totals over 500GB (data and logs). The server has 8 cores (each hardly ever getting above 50%) and 16 GB RAM (fully used); it is a VMWare server in a datacenter. The webapplication (other server) that our customers use runs okay, and the speed (queries etc.) is good. There are no other applications running on this server. Last weekend the SQL Server was restarted.Lately, when we run the MMS (I know, not best practise), we encounter that it runs very slow. Sometimes altering a simple little query takes 30 seconds. Opening a folder with 200 Stored Procedures takes more than a minute to expand. An application (running on another server) that connects to SQL, gave this error after a restart today: [font="Courier New"]SQL Connection failed: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=4; handshake=14981;[/font]What can I do to find out what is going on? Thanks,Raymond

Activity monitor &gt; %processor time

Posted: 04 Jun 2013 08:59 PM PDT

Hi Team,In my sql server 2008R2, Activity monitor > Overview.%Processor time is not showing.do we need to do any configuration to view % Processor Time.[img]C:\Documents and Settings\574731\My Documents\My Pictures\Processor Time.jpg[/img]Please suggest....

Schemas and selecting tables with out fully qualified names.

Posted: 04 Jun 2013 10:03 PM PDT

Im a little confused. I am moving a database from 2005 to 2008. The user the application connects as is called webuser and is the owner of the database as well as the schema also called webuser.All tables are named "webuser.table1", "webuser.table2" etc.If I perform the following "Select * from table1"I receive and Msg 208, Level 16, State 1, Line 1Invalid object name 'fs_categories'. error.If I perform the following:"Select * from webuser.table1"Then I get the correct result.I'm confused why this is as the user is set up in the database with webuser as it's default schema. This problem doesn't happen in the original 2005 instance only the 2008 instance.Thanks.

Execution plans

Posted: 04 Jun 2013 09:41 PM PDT

Hi All,one of the application created in .net and using sql in the backend is given to me for identify the problematic queries ,long running queries. i used the profiler and used tuning template to capture data.After capturing data i saved that into sql table.after querying that table i found that 12 queries are taking more than 4000 milli seconds . which we consider as long running queries. now what to do next.i copied one query from table and pasted in ssms and try to create execution plan. but i am not getting execution plans. Is there any way that i can get execution plans for those queries.when i am trying to create execution plans it is showing errorMsg 208, Level 16, State 1, Line 1Invalid object name 'dbo.PriceGroup'.The queries taking more than 4000 milli seconds areselect business0_.Id as Id0_, business0_.Code as Code0_, business0_.CountryCode as CountryC3_0_ from dbo.[Business] business0_ order by business0_.Code ascselect laborrate0_.Id as Id15_, laborrate0_.CostRate as CostRate15_, laborrate0_.SellRate as SellRate15_, laborrate0_.CurrencyId as CurrencyId15_ from dbo.[LaborRate] laborrate0_select labortype0_.Id as Id16_, labortype0_.CountryId as CountryId16_, labortype0_.LaborRateModelId as LaborRat3_16_, labortype0_.Description as Descript4_16_ from dbo.[LaborType] labortype0_ order by labortype0_.Description ascselect risk0_.Id as Id57_, risk0_.Type as Type57_, risk0_.Description as Descript3_57_ from dbo.[Risk] risk0_ order by risk0_.Description ascselect risksurvey0_.Id as Id41_, risksurvey0_.Text as Text41_, risksurvey0_.ParentStepId as ParentSt3_41_, risksurvey0_.Sequence as Sequence41_ from dbo.[RiskSurveyQuestion] risksurvey0_ order by risksurvey0_.ParentStepId asc, risksurvey0_.Sequence ascselect risksurvey0_.Id as Id40_, risksurvey0_.ParentQuestionId as ParentQu2_40_, risksurvey0_.ResultsInStepId as ResultsI3_40_, risksurvey0_.ResultsInRiskId as ResultsI4_40_, risksurvey0_.Text as Text40_, risksurvey0_.Sequence as Sequence40_ from dbo.[RiskSurveyAnswer] risksurvey0_ order by risksurvey0_.ParentQuestionId asc, risksurvey0_.Sequence ascselect countrydis0_.Id as Id5_, countrydis0_.Disc_ID as Disc2_5_, countrydis0_.Name as Name5_, countrydis0_.CountryId as CountryId5_ from dbo.[CountryDiscountType] countrydis0_ order by countrydis0_.Name ascselect pricegroup0_.Id as Id24_, pricegroup0_.ProductGroupCode as ProductG2_24_, pricegroup0_.PriceGroupDesc as PriceGro3_24_, pricegroup0_.PriceFamilyCode as PriceFam4_24_ from dbo.[PriceGroup] pricegroup0_ order by pricegroup0_.ProductGroupCode asc

Querying sys.dm_db_partition_stats instead of sys.sp_spaceused

Posted: 04 Jun 2013 10:24 PM PDT

We have a daily process that saves into a table the storage info returned by the SP, but it's procedural, slow, and requires manipulating the character output to get just the numbers. After searching your forums, I've found that the SP references the DMV above.For a table with a clustered index and no non-clustered indexes, it's very easy to match the output from each; for a table that has non-clustered index(es), it seems more complicated. Although we currently don't use multiple partitions, I suppose that might be a further complication?Just wondering if anyone has already written a query using the DMV to get the same (single line) output that the SP does (without the "KB" characters)?Thanks,~ Jeff

insert records into table which record insert wrong that record only rollback remains insert

Posted: 04 Jun 2013 10:11 PM PDT

hi friend i have small doubt in sql server plz tell me how to solve issuei want insert records into emp table from 1 t0 3 records. emp table contains only id column and datatype is intwhenever i insert records that time any errore occured that record will be skiped and remaing records loaded into tablei mean supose 1st and 3rd records insert correctly and 2nd record insert wrong way either select table wrong or insert record value wrongi try query like bellowbegin tran insert into emp values(1) if(@@errore<>0)rollback transave tran s1 insert into emp12 values(2) if(@@errore<>0)rollback tran s1save tran s2 insert into emp values(3)commit tranhere i wrongly insert emp12 table record. where ever i insert inncorrect format that records only rollback and remaing records must be loaded into emp tableonce i execute above query only 1st recor inserted and 3rd record not inserted into emp tablebut i want insert 3rd record also. i want out put like id 1 3 plz tell me how to solve this issuse in sql server.plz tell me that query in sql server.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog