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)
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 .
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?
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.
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
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
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?
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.
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
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.
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%'
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.
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
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.
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.
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 ,
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?
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.
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?
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
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
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)
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?
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?
