SQL Server 2008 :: Slow Row Number Over Partition

Jul 28, 2015

I have a simple table with 4 columns

(idAuxiliarPF(BIGINT+PK), pf(BIGINT+FK), Data(DateTime), Descr(NVARCHAR))that has aprox. 50k rows.

I need to create a partition of the data to join to another table, the query that i have:

SELECT
ROW_NUMBER() OVER (PARTITION BY pf ORDER BY Data DESC, idAuxiliarPF DESC) AS RN,
pf,
Data,
Descr
FROM dbo.PFAuxiliar
WHERE Data <= GETDATE()This query takes around 40 seconds to return the results

If i remove the Descr column, the query it takes no time.

SELECT
ROW_NUMBER() OVER (PARTITION BY pf ORDER BY Data DESC, idAuxiliarPF DESC) AS RN,
pf,
Data
FROM dbo.PFAuxiliar
WHERE Data <= GETDATE()I have two indexes, Clustered (idAuxiliarPF), NONClustered(pf).

How can i improve the performance of this query?

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Row Number Over Partition

Dec 5, 2013

I am having problems getting my last revision number out when i am trying to use inner joins along with the row_number over partition

I am using 2 tables, tbl_acyear_lookup & tbl_targets

tbl_acyear_lookup columns = (pk)- academic_year_id, academic_year
looks like this:
1, 2010/2011
2, 2011/2012
3, 2012/2013

tbl_targets columns = targetID, Academic_Year_ID,Course_Mode,UK_ENROL, INT_ENROL, Notes, Revision_Number

I have one stored proc that uses the Row_number over partition that looks like this:

Select TargetID, Academic_Year_id, Course_Mode, UK_Enrol, Int_Enrol, Notes, Revision_Number from
(SELECT ROW_NUMBER() OVER (partition by [Academic_Year_id] order by [Revision_Number] DESC) as [RevNum],TargetID, Academic_Year_id, Course_Mode, Target_Year, UK_Enrol, Int_Enrol, Notes, Revision_Number
FROM tbl_targets where course_mode=@course_mode) RV where (RV.RevNum=1)

Now the next store proc needs to use the above but i need to add the Academic_year from the tbl_acyear_lookup table also add filter the target_year ='year 1'

View 2 Replies View Related

SQL Server 2008 :: Create Partition On Existing Table?

Mar 4, 2015

Can we create the Partition on Existing Table?e.g Create table t ( col1 number(10,0), Col2 Varchar(10)) ;After the table Creation can we alter the table to partition the table.

View 2 Replies View Related

SQL Server 2008 :: Automate New Partition Creation In Table Partitioning?

May 8, 2013

we planning to create partitioning on existing tables. The partitioning is on date column, there should be one partition for each year.

Creating of new partitions should be automated, and also we dont have any plans of archiving old data, all we want is that new partition creation should be automated.

View 6 Replies View Related

SQL Server 2008 :: Verify Partition Data Load Syntax

Feb 28, 2015

What is the syntax to verify that the partition data is loaded into the correct partition.

View 0 Replies View Related

SQL Server 2008 :: Loading Data Into New Partition Table Online

Mar 1, 2015

When you load the data into a new partition table, can it to done online without any downtime? because I have few tables that are around 250 gigs and more.

View 5 Replies View Related

SQL Server 2008 :: Verify Partition Data Load Syntax?

Mar 2, 2015

What is the syntax to verify Partition data load.

View 1 Replies View Related

SQL Server 2008 :: Partition Sliding Window Causing Loads Of Blocks

Oct 29, 2015

We have a massive database with an almost massive amount of traffic to and from it.

I've been requested to implement a sliding window partitioning with 2 partitions an active and passive 1,I managed to test this on a very small testbed last month.

I currently moved 97k table on to the partition function leaving me another 26 k to go

I'm using the following stored procedure to implement the sliding window

CREATE PROCEDURE [dbo].[ManageFactSlidingWindow](@pFunction nvarchar(max),@pSchema nvarchar(max),@FG nvarchar(max),@moveDays int)
/*****************************************************************************
PROCEDURE NAME: [ManageFactSlidingWindow]
AUTHOR: Arshad Ali
CREATED: 02/24/2013
DESCRIPTION: This stored procedure manages sliding window for the partitioned table

VERSION HISTORY:
DATE EMAIL Company DESCRIPTION

[Code] .....

When I try to move the partition even a single day I get loads of locks.

View 0 Replies View Related

SQL Server 2008 :: No Option To Automatically Partition Table Based On Unique Values Of Column?

Jun 17, 2015

A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.

View 4 Replies View Related

Transact SQL :: Row Number Over Partition By Multiple Columns

Sep 22, 2015

I have the following query

WITH summary AS
(SELECT tu.SequenceNumber,
tu.trialid,
tu.SBOINumber,
tu.DisplayFlag,

[Code] ....

I am having trouble with the RowNumber Over Partition By portion of the query. I would like the query to return only the first occurrence of each sboinumber in the table for each trial id. It is only giving me the first occurrence of each sboinumber. I tried including the trialid in the partition by clause, but that is not working.

Sample Data
SequenceNumber   TrialID      SBOINumber
1                           1            5000
2                           1            5000
3                           2            5000
4                           2            5000
5                           1            5001
6                           3            5001
7                           3            5001

Should return SequenceNumber 1 and 3, 5, 6

View 11 Replies View Related

T-SQL (SS2K8) :: Number Of Rows And Range For Each Partition In A Table?

Mar 13, 2015

Is it possible to show the number of rows and the range for each partition in a table ?

This shows me the range but not the row count per partition

SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv
ON sprv.function_id=spf.function_id
WHERE (spf.name=N'myDateRangePF')
ORDER BY [ID] ASC

View 4 Replies View Related

T-SQL (SS2K8) :: Updating Existing Table With Max (value) And Row Number (partition By 2 Columns)

Sep 15, 2015

I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null

job_cdpermit_nbrreference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1991100003
ABC1992100004
ABC1993100005
ABC2880100006
ABC2881100007
ABC2881100007
ABC2882100008
ABC2882100008

View 2 Replies View Related

SQL Server 2008 :: Calculate Number Of Days

Oct 8, 2015

I have client table which has client_id Eff_from and Eff_to columns.Eff_from and Eff_to are the dates that client is eligible for service. I need to know the average number of days from the day that he became not eligible and new eligibility date .

CLIENT_IDEFF_FREFF_TO
1001 12/24/200712/8/2010
100112/13/20123/26/2013
1001 5/27/20138/2/2013
10019/24/201310/30/2016

for expl days between
12/8/2010 and 12/13/2012
3/26/2013 and 5/27/2013
8/2/2013 and 9/24/2013

then AVG them.

View 3 Replies View Related

SQL Server 2008 :: Restore Number Of Databases Using Powershell

Aug 15, 2012

I have a new problem with doing a restore of a number of databases using powershell. The script I'm using is based mainly on this one (Part 2 in particular): [URL] .....

The problem I'm having is around the RedgateGetDatabaseName function. My hunch is that its down to the different version of red gate and how sqlbackup works. Basically when the call is made to the function it is returning both the Database Name and the number of row's that the SQL command in the function has ran. I've tried to include a SET NOCOUNT ON at the start of the SQL command in the function but its still returning the now count.

View 5 Replies View Related

SQL Server 2008 :: Select Alternate Number Of Rows?

Jan 28, 2015

A simple Query to select alternate rows from a table ?

View 9 Replies View Related

SQL Server 2008 :: How To Get Output Based On Sequential Number

Feb 13, 2015

The below data come from table table1. Instead of below result Ex1: I need output similar to the ex2.

Ex1:

CaseNumberStart CaseNumberEndExported
15000013150000131
15000014150000141
15000504150005041
15000505150005051

Ex2:

CaseNumberStart CaseNumberEndExported
15000013150000142
15000504150005052

How to get the result similar to Ex2, instead of Ex1. (ie., case-number is in sequential order then no need to break), And it should suit large dataset, I will finetune, if any performance issue.

View 1 Replies View Related

SQL Server 2008 :: Append Records With Different Number Of Columns?

Feb 16, 2015

I have a table of Customers & their data in about 20 Columns.

I have another table that has potential Customers with 3 Columns.

I want to append the records from Table 2 onto Table 1 to the Columns with the same names.

I've thought of using UNION ALL or Select Insert but I'm mainly stuck on the most efficient way to do this.

There is also no related field that can be used to join the data as these Customers in table 2 have no Customer ID yet as they're only potential Customers.

Can I just append the 3 columns from Table 2 to the same 3 columns in table 1?

View 2 Replies View Related

SQL Server 2008 :: How To Check Page Split Number

May 11, 2015

I am working now on optimization of an update query for a particular table and I want to measure the number of page splits after each update. How to check it?

View 6 Replies View Related

SQL Server 2008 :: Copying ID Number To Another Field In Table

Jun 26, 2015

I need to have a table that has a primary key

CREATE TABLE [dbo].[testing](
[tid] [int] IDENTITY(1,1) NOT NULL,
[sometext] [varchar](150) NOT NULL,
[idcopied] [varchar](50) NULL,
CONSTRAINT [testing_PrimaryKey] PRIMARY KEY CLUSTERED

..and eveytime I add 'sometext' as another row, the tid # needs to be duplicated to idcopied field

insert into [testing] (sometext,idcopied) values ('some junk',@@identity)

???

View 4 Replies View Related

SQL Server 2008 :: Maximum Number Of Sessions Has Been Reached

Jun 29, 2015

We have a big software that run a warehouse distribution center, written in .NET Backhand is a SQL Server 2008 R2 STD database.

Now, it seems there is a problem with the sessions not being properly closed after each call to the DB. Here is the message got form SQL:

DESCRIPTION:A new connection was rejected because the maximum number of connections on session ID 57 has been reached. Close an existing connection on this session and retry.

In the .NET code, connection is made with the following code:

If oConn Is Nothing Then oConn = New SqlConnection
If oConn.State = ConnectionState.Open Then oConn.Close()
With oConn
.ConnectionString = "Server=" & Server & ";Database=" & DB & ";User ID=" & User & ";Password=" & Pass & ";Connection Timeout=" & 5 & ";MultipleActiveResultSets=" & True
.Open()
End With

This code is called once, at the opening of the software

StoredProc are call with the code:
Try
oCmd = New SqlCommand
With oCmd
.Connection = oConn
.CommandType = CommandType.StoredProcedure

[Code] .....

So every Command is closed after execution, yet, they stay active in the SQL Server. Is there something I'm missing here?

View 3 Replies View Related

SQL Server 2008 :: Update The Document Number Row For 3k Rows?

Aug 4, 2015

I have a table where I would like to update the document number row for 3k rows. The problem I have is that the documents come in sets of two (version 1 and 2) but both have different numbers. Picture it like this below:

DOCNUM: 4445787 Version 1
DOCNUM: 4445790 Version 2

It should be the same docnum (ie 4445787 Version 1, 4445787 Version 2).

The challenge is how can we assign the new docnum for version 1 to be also for version 2 as well. Basically in SQL we need a way to

1. Find a way to distinguish the pair of documents in the target db that are the same even though they have different docnums.

2. Update them so that the docnums match.

View 7 Replies View Related

SQL Server 2008 :: Get Total Number Of Attachments Of Group ID

Sep 10, 2015

Copy and paste the code to review sample:

Create table #temp(
ID varchar (20),
ID_BegAttach varchar (20),
ID_EndAttach varchar (20),
ID_AttachLvl varchar (20),
ID_NumofAttach varchar (20)

[Code] ...

I'd like to get the column ID_NumofAttach to be populated by the total number of ID_Attachlevel column by the same ID_BegAttach or ID_EndAttach and populate where ID_Attachlevel is 0.

View 9 Replies View Related

SQL Server 2008 :: How To Result Query With Repeat Sequence Number

Mar 5, 2015

I want to make a query with the result like this, where Seqno is the result query which has repeating sequence number for same Field1

Field1Field2SeqNo
BVFUSBVFUS011
BVFUSBVFUS021
BVFUSBVFUS031
BVFRPBVFRP012
BVFRPBVFRP022

[Code] ....

I want the result also no ordered by Field1. it just as the natural order of the table..

View 3 Replies View Related

SQL Server 2008 :: Auto-Incremented Number Series To A Block

Mar 18, 2015

I want to assign consecutive numbers to a block of data, where block of data is based on days consecutive to each other i.e., one day apart.

Date format is: YYYY-MM-DD

Data:
TestId TestDate
----------- -----------------------
1 2011-07-21 00:00:00.000
1 2011-07-22 00:00:00.000
1 2011-07-27 00:00:00.000
1 2011-07-29 00:00:00.000
1 2011-07-30 00:00:00.000

[Code] ....

My Attempt:
WITH cte AS
(
SELECTTestId,
TestDate,
ROW_NUMBER() OVER(
PARTITION BYTestId

[Code] ....

Expected Output:
TestId TestDate OrderId
----------- ----------------------- --------------------
1 2011-07-21 00:00:00.000 1
1 2011-07-22 00:00:00.000 1
1 2011-07-27 00:00:00.000 2
1 2011-07-29 00:00:00.000 3

[Code] ....

The OrderId is the column I am trying to obtain using my following cte code, but I can't work around it.

View 0 Replies View Related

SQL Server 2008 :: Generate Increment Number With Conditional Restart

May 22, 2015

I need to create a script that adds an incrementing suffix to two columns, but restarts based on the value of another column. I found a similar question in the SQL Server 2000 forum, but it doesn't quite fit and also I'm working with SQL Server 2008 R2. The code below both creates a table with test data and tries to carry out the task. If you run this, you will see that the VISITNUM column has a value of UNS in row 4, UNS.1 in row 5 and UNS.2 in row 6. In row 7 it's V200, then in rows 8 and 9 it's UNS.3 for both. The same suffix gets applied to the VISIT column, but of course if I can solve this for VISITNUM then adding the suffix to VIST as well will be easy.

What I need is for row 8 to have UNS and row 9 to have UNS.1. In other words, any time the VISITNUM is UNS several times in a row, I need to add that ".X" suffix, but if a row has something other than UNS, I need to start over again the next time it's UNS again.

CREATE TABLE #testing(
KitID varchar(20),
SubjID varchar(20),
VISIT varchar(60),
VISITNUM varchar(20),

[code]....

View 8 Replies View Related

SQL Server 2008 :: Relation Between Max Degree Of Parallelism And Number Of CPU Cores

Jun 12, 2015

I have got a question on max degree of parallelism and CPU cores.

If max degree of parallelism = 1, this signifies that sql will use serial execution plan (unless u change it in query level with MAXDOP hint). In serial plan, will the query use all CPU cores (say in my server I have 16 core processors)?

If in serial execution plan only one thread works, then what the other threads doing ? Idle (I may have a defined max server worker thread = 32767(by default)

Unable to create a relationship between this parameters.

View 3 Replies View Related

SQL Server 2008 :: Divide Total Rate By Number Of Records

Jun 22, 2015

my table includes detailed records with total Rate repeated in each record:

CREATE TABLE Table1
(
Providerid varchar (6) NOT NULL,
Providername char (30) NOT NULL,
Clientid varchar (15) NOT NULL,

[code]....

View 1 Replies View Related

SQL Server 2008 :: Count Number Of Files Deleted From Share Path?

Apr 23, 2015

SET NOCOUNT ON
Declare @daysOld int,@deletedate nvarchar(19) ,@strDir varchar(250)
declare @cmd11 nvarchar(2000)
declare @mainBackupDir varchar(2000),
@result1 nvarchar(max);

[Code] ....

View 1 Replies View Related

SQL Server 2008 :: Way To Check To Find Number Of Rows And Size Of A Table

Apr 29, 2015

How can we monitor the all tables in all databases and send notifications to the team.Is there a way to check to find the no of rows and size of a table last month and find out growth % now

View 4 Replies View Related

SQL Server 2008 :: How To Calculate Number Of Days Since Start Of Fiscal Year

Jul 29, 2015

Our fiscal year starts on July 1st. Each month they call a period - so July is period 1, August is period 2, etc.

They are wanting a report that pulls numbers for a given period. There are parameters for them to select the fiscal year and the fiscal period, and then it calculates the numbers for that period. That part works fine.

Now they want me to do some calculations, and one of them is to divide one of the numbers by the # of days since the fiscal year. So if they choose July, it would be 31 days. If they choose August, it would be 61 days, etc. How can I set this up to calculate the number of days when they really aren't entering a start date, it's just a fiscal year and period.

Is there a way to calculate a date field that is 07/01/xxxx where xxxx is the fiscal year they chose? Also a way to calculate a date field that would be the last date of the month for the fiscal period and year they chose?

I suppose I could add 2 other parameters where they enter the start of the current fiscal year, and the last day of the period they're running it for, and use a datediff to calculate that. Just seems kind of redundant.

View 8 Replies View Related

SQL Server 2008 :: Calculate Number Of Months Between Dates For Multiple Records?

Oct 7, 2015

I have a challenge and I'm not sure the best route to go. Consider the following dataset.

I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.

CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012

What I want to know is the average number of months between the purchases. And do this for each customer.

View 6 Replies View Related

SQL Server 2008 :: Deleting Large Number Of Rows With Foreign Key And Mirroring Setup

Feb 19, 2015

We have a database. It is enabled for mirroring. We need to delete the old records. That is around 500k records from a table. But it has foreign key relation. How to do in Production servers these kind of deletes?

View 2 Replies View Related

SQL Server 2008 :: Tell Number Of Times People Use SSMS To Change Client Data?

Mar 21, 2015

I'm trying to quantify the number of times folks use SQL Server Management Studio to change client data in one of our production databases. Does SQL Server keep this statistic? How do I get to this data?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved