DB Engine :: Partition Function With Where Clause

Oct 29, 2015

I am facing issue in generating total sum and daily sum from table ThresholdData.

DailyTransactionAmount should be sum of todays amount in the table
TransactionAmount should be sum of all amount in the table.

Basically,

1. I don't want to scan ThresholdData table twice.
2. I don't want to create temporary table/table variable/CTE for this.
3. Is there is any way to make it done in single query.

I hope, where criteria is not possible in partition function. I am trying query something as given below,

SELECT  TransactionDate,
  TransactionAmount,
  ROW_NUMBER() over (order by TransactionDate) AS TransactionCount,
  SUM(TransactionAmount) over (partition by id ) AS TransactionAmount, 
  SUM(TransactionAmount) over (partition by id ,CONVERT (DATE, @TodaysTransactionDate)) AS DailyTransactionAmount
 FROM ThresholdData
 WHERE id = @id
 AND transactiondate >= dateadd(d,-@TransactionDaysLimit,@TodaysTransactionDate)

View 2 Replies


ADVERTISEMENT

DB Engine :: Partition Table And Index

Jul 26, 2015

I have one partition table "tablea" with partition key dateentry on yearly basis and table have four partition with name y2013,y2014,y2013,y2015 with one partition schema . How I can create partition index on tablea that first time create partition  index  and next time I want to rebuild index only on y2015 partition . 

<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></iframe>.

View 3 Replies View Related

DB Engine :: Table Partition - Retrieving Records

Jul 2, 2015

I need to partitioning the table on which most expensive query run.

Every day 500000 lac records inserted/update in that table

How to create what impact on performance while retrieving the records.

View 5 Replies View Related

DB Engine :: Multiple Partition Schema On Single Table In Server

Apr 22, 2015

I want to  create multiple partition schema on a single table.

For example - i need to create partition  base on region id and Territory Id.

View 2 Replies View Related

Partition Function

Feb 25, 2008

Guys,

I have names in the database which I want partition by last name - for example last names starting with A, B, C, D should go to the file group 1. last names starting with E, F, G, H should go to file group 2.

I am trying to use the following function - but do I specify in the function that last names with with A, B, C, D should go to the file group 1

CREATE PARTITION FUNCTION myRangePF3 (char(20))
AS RANGE RIGHT FOR VALUES ('EX', 'RXE', 'XR');

Is there any way to modify partition function to accomplish this?

Any suggestions and inputs would help

Thanks

View 4 Replies View Related

Partition Scheme Using A Function

Feb 20, 2007

I can't seem to find a way to do the following:create table part_table (col1 int,col2 datetime) on psX (datename(week,col2))I want to partition based on the week number of a date field.So if I enter in data like the following in my part_table:(1, 1/1/2007) should go into partition 1 for week #1(52, 12/21/2007) should go into partition 52 for week #52 of the yearI tried adding in a computed column, but it says its nondeterministic.

View 4 Replies View Related

Partition Function And File Group

May 16, 2008

Am I correct in assuming, in partitioning scenarion - the NUMBER OF PARAMETERS in a partitioning function is equal to number of file groups?

------------------------
I think, therefore I am - Rene Descartes

View 8 Replies View Related

Partition Function - Unknow Range Column

May 4, 2006

Hi,I need to create a partition table but the column on which I need tocreate a partition may not have any logical ranges. So while creatingor defining partition function I can not use any range.likeCREATE PARTITION FUNCTION my_part_func (NUMERIC(7)) AS RANGE LEFT FORVALUES (1,100,1000);Is there any way to define partition function in SQL Server somethinglike Oracle HASH partitions where logical range is unkown?ThanksSameer

View 5 Replies View Related

How To Attach The Existing Table To New Partition Function/scheme?

May 26, 2008

Normally we create a new table for tabler partitioning, like:




Code Snippet
CREATE TABLE [dbo].[Sales]
(
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[SalesDate] [datetime] NOT NULL,
[col_01] varchar(50) NULL,
[col_02] varchar(50) NULL,
..
) ON [ps_Sales](SalesDate)
GO







with




Code SnippetCREATE PARTITION SCHEME [ps_Sales]
AS
PARTITION PFN_Sales TO ([FG_20080501], [FG_20080516], [FG_20080601], [FG_20080616],
[FG_20080701], [FG_20080716], [FG_20080801], [FG_20080816],
[FG_20080901], [FG_20080916], [FG_20081001], [FG_20081016])






But what if I have [dbo].[Sales] existed in db, that have attached to [PRIMARY]?

Alter table?

or I need to create a temp table first?

View 1 Replies View Related

SQL Server 2012 :: Why Partition Function Works For Datetime2 But Not For Datetime1

Dec 20, 2013

DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime) AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime = '2007-09-01 00:00:00.000';
WHILE @i < '2008-10-01 00:00:00.000'
BEGIN
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';

[Code] ....

Msg 7705, Level 16, State 2, Line 1
Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type.

However if I change to datetime2 it works

DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime2) AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime2 = '2007-09-01 00:00:00.000';
WHILE @i < '2008-10-01 00:00:00.000'
BEGIN
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';

[Code] ...

Is the data type of the column used for partitioning. All data types are valid for use as partitioning columns, except text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, or CLR user-defined data types.

In this case why isn't datetime works?

version is as follow:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

from [URL] .....

Table and index partitioning is supported in this edition

so I don't know why it fails!

View 1 Replies View Related

SQL Server Admin 2014 :: How A New Partition Function Apply For Current Data

Apr 15, 2015

I have a heavy database , More than 100 GB only for six month .every Query on it takes me along time and I dont have enough space to add more indexes.by a way I decided to do partitioning. I create a partition function , on date filed and all Data records per month was appointed to a separate file.And is partitioning only for Future data entry?

View 9 Replies View Related

DB Design :: Alter Partition Function In Data Vault Where There Is 4 Table Dependency?

Aug 28, 2015

I'm currently stuck with a table that has 350 mil records. Querying this table is insanely slow so I had a better look at existing yearly partitioning. I already managed to partition on a month level which increased the performance/querrying a lot. I did this on the staging table where I used an alter statement to split the 2015 partition by 12 months.

However, in our project we used Data Vault. This means that we have 4 tables (hub, sathub, link, satlink), all carrying 350 mil records. The problem is that altering the partition function does not work. The server cannot handle this action. What the best way is to do this, without having to drop/reload all tables.

View 17 Replies View Related

How To Force The Query Engine To Use An Index With An OR Clause?

Nov 16, 2007

I have the following table:

Create Table Item(
I_Code NVarChar(40) Primary Key NOT NULL,
I_MatID NVarChar(40),
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1 nvarchar(55),
I_Acquired nvarchar(35),
I_Info2 nvarchar(55),
I_Info3 nvarchar(55),
I_Date DateTime DEFAULT GetDate()

);

Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);

Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )


While this one use:

select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )


Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423

Some feedback?
Thanks

View 5 Replies View Related

DB Engine :: Indexing Strategy For Table With Two Columns In SPs WHERE Clause

Oct 1, 2015

I have an SP which concatenates 2 columns in a where clause - ie WHERE [Column1] + [Column2] = @var.  This, as far as I'm aware, is not going to access any seek on an index on a table.

My task is to create an index on this table and get the SP to access said index.

Aside from combining the two columns into one column on the table, how I can get an SP to access the newly created index when queried by the SP?

One thought of mine is to firstly index Column1 and allow the SP to access this index by inserting the rows into a temp table.  Then a search on the temp table to retrieve the records on a search on Column2.

View 8 Replies View Related

OVER Clause With Aggregate Function

Jul 2, 2015

Why aren't we getting proper MAX(DOJ) value in Weird_Maxgroup Column in the below query ? Similar thing happens with MIN() function as well.

Is there something that I am missing here ?

;WITH CTE AS
(
SELECT 1 AS ID, 10000 AS SALARY,GETDATE() AS DOJ
UNION ALL
SELECT 1, 13000,GETDATE() - 10
UNION ALL
SELECT 3, 190000,GETDATE() + 10

[Code] ....

View 9 Replies View Related

Upper Function In Where Clause

Jan 29, 2008

Hi expert, I would like to ask regarding the UPPER function in SQL Query. I was tryin' to create a scipt that will give me a result of all the names that are in UPPER case format, but when I tried to execute the script the result is not right, it also retrieves all the records that are in PROPER case.

SQL Script:
SELECT id, name FROM table_1 WHERE UPPER(name) LIKE 'DAR%'

Result:
ID NAME
-- -------
1 Darren
2 DARREN

View 2 Replies View Related

SSRS How To Use Where Clause While Using Sum() Function

May 9, 2007



Hi



Is there a way that I can sum the value of a field in a text box and use where clause with it..



For Example

I want



=Sum(Fields!TO_DATE_AMT_PL.Value) where date<=20040509



What is the syntax as I know in a text box, you can not use where the way I wrote it above...Thanks

View 5 Replies View Related

DB Engine :: Resource Governor Classifier Function Script

Aug 7, 2015

I want to create a classifier function where I can restrict all the logins except two logins for the resource pool.

Case Scenario is : I am creating a resource pool and on that pool I want to restrict the users for that particular pool but I want to allow some of the user IDs full access to that databses so how could I create a script for that scenario.

View 4 Replies View Related

DB Engine :: How To Get Multi-select Value In A Variable In Server Function

Jun 1, 2015

i have a column with mulitple ids stored with commas . i want to pass ids and get data along with name from the table..how to get multiselect value in a variable in  sql server function 

View 4 Replies View Related

Not Able To Do Insert And Select Clause With Aggregate Function

Aug 14, 2006

Hi,

Could some one help me how to do

insert into test2(id,name) values ((select max(id) from test1),'user1')
in MS SQL Server

its throwing "Subqueries are not allowed in this context. Only sc
alar expressions are allowed" Exception. Help is appreciated.

Thanks,
Murali

View 2 Replies View Related

Problems With ABS() Function Inside Sub Query (within WHERE-Clause)

May 28, 2008

Hello,

when trying to execute the following query with SQL CE 3.1 and OLEDB on WM2003:

SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND ABS(Measurement_Value) BETWEEN ? AND ?
GROUP BY Panel_Position)

i get this error returned:
0x80040E1DL -- DB_E_UNSUPPORTEDCONVERSION -- Requested conversion is not supported.

I don't know where inside the sql string a conversion is necessary/fails.
Surprisingsly when i modify the sql statement a little, it is executed WITHOUT ERRORS:

SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND Measurement_Value BETWEEN ? AND ?
GROUP BY Panel_Position)

The only difference between the 2 statements is the ABS() function inside the sub query.

More surprisingly, with the query analyser on the PDA i can execute both statements fine. I am absolutely confused now where i have to search for the mistake.

I would appreciate it very much if someone out there knows an answer or a hint and could tell me.

With kind regards,
Andre

View 5 Replies View Related

DB Engine :: Query / Function To Handle Subsidiaries In A Company Tree

May 2, 2015

I have a holding tree with more than 200 companies, and several layers. Each company is defined by:

- a unique ID
- a direct parent ID --> the company immediately above in the tree
- an ultimate parent ID --> the company that owns the entire tree, it is the same for all

Now I am looking for a function that returns the list of all companies below a given one in the tree, and there could be several layers underneath. Doing that for the ultimate parent is easy because all companies now who their ultimate parent is, but I am struggling to build a function that works for an intermediary parent in a dynamic way.

View 8 Replies View Related

SQL Server 2008 :: Aggregate Function Or GROUP BY Clause Error

Apr 13, 2015

While running the below query, getting the error: Am I missing any of the columns to include in the SELECT column_list?

Msg 8120, Level 16, State 1, Line 1

Column 'sys.master_files.database_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

<code>
select a.[Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate
from
(
SELECT database_id,[Database Name]= DB_NAME(database_id),
[Type]= CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END ,

[code]...

View 3 Replies View Related

SQL Server 2012 :: Error Message - Aggregate Function And Group By Clause

Feb 19, 2014

I'm trying to write a query to select various columns from 3 tables. In the where clause I use a set of conditions, but most important condition is that I only want to see all results from the different columns where the ph.ProdHeaderDossierCode contains at least 25 lines of processed hours. I tried this with group by and having, but I constant get error messages on all other columns that I want to see: "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I make this so I can see all information I need?

Here is my code so far:

selectph.CalculatedTotalTime,
ph.ProdHeaderDossierCode,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
fromdbo.T_ProcessedHour ph,

[Code] ....

View 8 Replies View Related

Using OPTION Clause Within CREATE FUNCTION Statement For Inline Table Functions

May 13, 2008

Hi!

I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error:

Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34

Incorrect syntax near the keyword 'option'.

Here is the function:


create FUNCTION [dbo].[ExpandedCTE]

(

@p_id int

)

RETURNS TABLE

AS

RETURN

(

with tbl_cte (id, tbl_id, lvl)

as

(

select


id, tbl_id, 0 lvl

from


tbl

where


id = @p_id

union all

select


t.id, t.tbl_id, lvl + 1
from

tbl_cte
inner join tbl t


on rnr.tbl_id = tbl_cte.id

)

select


id, tbl_id, lvl

from


tbl_cte

option (maxrecursion 0)

)


Please help!

Alexander.


P.S.
I'm really sorry if it is about syntax, but I could not find it in the documentation.

View 12 Replies View Related

Transact SQL :: Encrypt Java Code Using With Encryption Clause From Server Stored Procedure Or Function

Nov 3, 2015

How to encrypt the java application code using the 'with encryption' clause from sql server stored procedure or function.

View 3 Replies View Related

Transact SQL :: How To Create UNION Clause With Two Queries That BOTH Have WHERE Clause

Nov 4, 2015

I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?

I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....

I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.

Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?

View 13 Replies View Related

GROUP By Clause Or DISTINCT Clause

Jul 23, 2005

Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View 3 Replies View Related

Filtering Results In The Where Clause Vs A Having Clause

Oct 25, 2007

I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.

When on SQL Server 2000 the following statement ran without issue:

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN

(


SELECT TrackID

FROM dbo.Track_ID

GROUP BY TrackID

HAVING MAX(LegNum) = 1 AND


TrackID + 'x1' IN


(


SELECT

dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)

)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.

I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?


UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN
(



SELECT TrackID

FROM dbo.Track_ID

WHERE TrackID + 'x1' IN


(


SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions


ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)
GROUP BY TrackID

HAVING MAX(LegNum) = 1

)

View 3 Replies View Related

Help On Partition

Jul 6, 2006

i have a table named table1 sitting on a a partittion scheme px.

table1 is partitioned on u_id by tens,

first partion u_id=0 to 9

second partion u_id=10 to 19

third partion  u_id=20 to 29

ans so on and so forth

 

i have a table named table2 with

records having u_id = 13 to 16

obviously belonging to the second partion.

 

i want to load table2  to table1 as fast as i could with

the following requirements:

all contents of partition 2 of table1 must be deleted before loading

table2 ceases to exist after the operation

how will i do that.

 

thanks.

 

 

View 1 Replies View Related

Help With Partition

May 15, 2008

Hi All,

Can someone take a look at my code and tell me what i'm doing in wrong. The script runs fine but when i go to table property it says the table is not partitioned. Thanks for your help.


create database [mypartition]
go

--CREATE FILEGROUP
USE [mypartition]
GO
ALTER DATABASE mypartition ADD FILEGROUP Y2000_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2001_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2002_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2003_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2004_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2005_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2006_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2007_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2008_filegroup
ALTER DATABASE mypartition ADD FILEGROUP Y2009_filegroup


--CREATE FILES
USE mypartition
GO
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2000,
FILENAME = 'F:ss_datadatadetail_2000.ndf',
SIZE = 2MB)
TO FILEGROUP Y2000_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2001,
FILENAME = 'F:ss_datadatadetail_2001.ndf',
SIZE = 2MB)
TO FILEGROUP Y2001_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2002,
FILENAME = 'F:ss_datadatamdetail_2002.ndf',
SIZE = 2MB)
TO FILEGROUP Y2002_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2003,
FILENAME = 'F:ss_datadatadetail_2003.ndf',
SIZE = 2MB)
TO FILEGROUP Y2003_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2004,
FILENAME = 'F:ss_datadatadetail_2004.ndf',
SIZE = 2MB)
TO FILEGROUP Y2004_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2005,
FILENAME = 'F:ss_datadatadetail_2005.ndf',
SIZE = 2MB)
TO FILEGROUP Y2005_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2006,
FILENAME = 'F:ss_datadatadetail_2006.ndf',
SIZE = 2MB)
TO FILEGROUP Y2006_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2007,
FILENAME = 'F:ss_datadatadetail_2007.ndf',
SIZE = 2MB)
TO FILEGROUP Y2007_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2008,
FILENAME = 'F:ss_datadatadetail_2008.ndf',
SIZE = 2MB)
TO FILEGROUP Y2008_filegroup;
ALTER DATABASE mypartition
ADD FILE
(NAME = mypartition_detail_2009,
FILENAME = 'F:ss_datadatadetail_2009.ndf',
SIZE = 2MB)
TO FILEGROUP Y2009_filegroup;


--CREATE PARTITION FUNCTION
USE [mypartition]
GO
CREATE partition FUNCTION detail_part_function (varchar(10)) AS
RANGE LEFT FOR VALUES('2001','2002','2003','2004','2005','2006','2007','2008')
GO

--CREATE PARTITION SCHEME
USE [mypartition]
GO
CREATE PARTITION SCHEME detail_part_scheme AS
PARTITION detail_part_function TO
(Y2000_filegroup, Y2001_filegroup,Y2002_filegroup,Y2003_filegroup,Y2004_filegroup,Y2005_filegroup,Y2006_filegroup,Y2007_filegroup,Y2008_filegroup,Y2009_filegroup)
GO

-- Now just create a table that uses the particion scheme
USE [mypartition]
GO
/****** Object: Table [dbo].[partitioned_table] Script Date: 05/14/2008 09:44:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[partitioned_table](
[id] [int] NOT NULL,
[fiscal_year] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_partitioned_table] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON detail_part_scheme(fiscal_year)

GO
SET ANSI_PADDING OFF

View 7 Replies View Related

Partition On Value

Feb 9, 2006

I can see lot of documentation on Range Partitioning. Is there any other type of partition supported in SQL Server 2005?

For example, I have a Fact table having Billion rows. It has a column called BATCH_ID. A BATCH_ID corresponds to 10-20 Million rows and it is a running sequence number like 1,2,3 etc. (not an identity column). Is there anyway I can specify a partition function with BATCH_ID column as an int value? Will the SQL Server automatically does the partition on each int value in that case? If not, what is the best way to do it?

Thanks in advance for all help in this





View 3 Replies View Related

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause

May 14, 2008

2 examples:

1) Rows ordered using textual id rather than numeric id


Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id






Result set is ordered as: 1, 11, 2
I expect: 1,2,11


if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.




Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id




Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


and


Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View 12 Replies View Related







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