I have a large fact table spread across tens of partitions (appx. 1TB each). I found that the business does not need much of the columns in the table. So, as an optimization action, I decided to get rid of these un-needed columns.What is the efficient way to achieve this? Can I simply drop these columns from the table, or use a new table with the reduced structure?
View 2 RepliesI have deleted nearly 30 million rows from a table. But however when I used the sp_spaceused command to calculate the data occupied by the table I don't see any difference in the data size of the table. In fact the data has increased to few MBs after the deletion, but not much.
View 8 Replies View RelatedHow to use temporary table in SSIS ?
View 2 Replies View RelatedI have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.
Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?
1. XML
2. Spatial
3. Clustered
4. Non-Clustered
I have a SQL VIEW with col1, col2, col3. I need to add a new column to the view col4 coming from a TABLE in SQL Server.
View 4 Replies View RelatedI am putting together an invoice for my company. I have a text box describing each section of the invoice, followed by a table to list out the charges. I am using multiple tables based on what type of charge the client is receiving.
I would like to hide each section if there are no items purchased of that type. I can do this with the table using the expression "=CountRows() < 1", but I do not know how to refer to that table (call it Tablix1 for the sake of discussion) for the text box. I've tried using a ReportItems function as my basis, without success.
I am trying to create a sample table in the Azure SQL Data warehouse but its giving me a syntax error Incorrect syntax near the keyword 'CLUSTERED'.
CREATE TABLE [dbo].[FactInternetSales]
( [ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
[Code] ....
what's the correct syntax
How do I correctly populate a fact table with the surrogate key from the dimension table?
View 4 Replies View RelatedI have table having around 100 million rows.Everyday we have an ETL process in which table will be trucnated and relaoded. Will creating a partition on the table increase the inserting speed?
View 4 Replies View RelatedBelow is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
We have SQL Enterprise Manager (8.0). Is there any way to Copy a Table from one database to another with only the Structure (design) - not all the Data?
I can't find any option in the Import Data Wizard that only copies the Table structure.
Any help is appreciated. Thanks
I have a table that looks like this:
ID Type
123 Phone
123 Meeting
123 Phone
and I would like the data to look like this
ID phone Meeting
123 2 1
How do I do this?
In order to export data to a 3rd party provider, I build five separate tables to store the data. Every table has a different layout, except for the first four columns. They are record type, SSN, employee id and another id number.
Basically, I have to sort that data by SSN then by record type. Each employee will have multiple records.
However, that data will need to be "merged" into one table to be exported.
I have created a table that defines the first four columns, but then has one large "filler" field that will contain the rest of the data. How can I copy data from five different tables with five different layouts into one table?
Any suggestions?
Thanks,
Steve Hanzelman
Hello E'body
I have an application with MSAccess as front end and SQLServer as backend. have quite a bit of tables. i wanted to write a stored procedure which exports a SQL Server table (both Structure & Data) to a new Access MDB file. i know with the use of DTS its possible but i need to code it down. i need to perform this at runtime. so can anybody help.
Its urgent.
Bye.
Lax
Hi all
I have a Table with Following structure ( a Tree Structure )
PK Parent Level Code
--- -------- ------- ------
1 0 0 100
2 1 1 101
3 1 1 102
4 2 2 103
5 3 2 104
6 4 3 105
The same as following Tree as you can see
1__
| 2__
| 4__
| 6
| __
3__
5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that
Could any one help me.?
PK Parent Level Value First-Parent' Code 2nd-Parent's Code 3rd-Parent's Code
---- -------- ------ ------- --------------------- ---------------------- -----------------------
1 0 0 100 NULL NULL NULL
2 1 1 101 100 NULL NULL
3 1 1 102 100 NULL NULL
4 2 2 103 101 100 NULL
5 3 2 104 102 100 NULL
6 4 3 105 103 101 100
Any help greatly would be appreciated.
Kind Regards.
Hello. I want to ask about the possibility of copying both a tablestructure and it's contents from aSQL server table to a table within MS access. The problem cannot besolve with a permanent table structure at the target location.The names of the columns are essentially data with the application andso are subject to change. I am targeting a solution using SQL QueryManager.The approach I have tried (with failure) isSELECT *INTO <linkedserver table>FROM <local table>This should create and copy. However, I am not sure if this isachievable with this approach.Refer to the dialogue;-------------------------------------------------------USE MASTERGOEXEC sp_addlinkedserver@SERVER = 'Freddie',@PROVIDER = 'Microsoft.Jet.OLEDB.4.0',@SRVPRODUCT = 'OLE DB Provider for Jet',@DATASRC = 'C: empHMIS_Recipe.mdb'-- I am not sure if this is requiredEXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULLSELECT * FROM Freddie...FRED -- This is OKSELECT * INTO #Temp FROM Freddie...FRED -- This is OK-- This fails - Refer errorSELECT * INTO Freddie.FRED65from #tempServer: Msg 2760, Level 16, State 1, Line 1Specified owner name 'Freddie' either does not exist or you do not havepermission to use it.-- This also fails and I thought reflected the above select with naming- Refer errorSELECT * INTO Freddie...FRED65from #tempServer: Msg 117, Level 15, State 1, Line 2The object name 'Freddie...' contains more than the maximum number ofprefixes. The maximum is 2.EXEC sp_dropserver 'Freddie',@droplogins = 'droplogins'------------------------------------------------------------Thank you.Regards JC...
View 3 Replies View RelatedI have two identical table structure with at least 60 or more columns!!
Table A
ID numeric(18, 0) Primary Key,
RefID numeric(18, 0),
CoNum Varchar(15),
PrevCode Varchar(15),
DispCode VArchar(15),
EffDate Date,
Sal numeric(18, 0),
SDeposit numeric(18, 0) Etc....
I need to compare Data between two table and provide result like this!
RefId ID, CoNum, PrevCode, DispCode, EffDate, Sal, SDeposit
1, ,No Match, ,20,5200,0
So condition like. If table A Column Data does not Equal Table B Column data then
If datatype is Date then display Difference of Days.
if Datatype is Numeric then display Difference between value.( it display 0 if doesn't have difference)
if Datatype is Varchar then display No Match word.
Hello, can anyone tell me if it is possible to conditionally join tables? If so, how could it be done?
What I would like to do is create a query similar to the one below but include the rows in the Asset table where the corresponding Alert field is NULL.
SELECT A.AssetId, A.IndustryId, A.RegionId, A.RevenueId, AL.AlertId
FROM Alerts AS AL INNER JOIN
Assets AS A ON AL.IndustryId = A.IndustryId AND AL.RegionId = A.RegionId AND AL.RevenueId = A.RevenueId
WHERE AlertId = 1
The output I am after would be the first row in the Assets table. But since the RevenueId column is NULL, I get nothing. The only time the above query will work is if all three Id columns are populated. That is not always going to be the case. Please don't suggest changing table structures or adding data. That is not an option.
Below are the table structures and sample data.
CREATE TABLE [dbo].[Alerts](
[AlertId] [int] NOT NULL,
[IndustryId] [int] NULL,
[RegionId] [int] NULL,
[RevenueId] [int] NULL,
CONSTRAINT [PK_Alert] PRIMARY KEY CLUSTERED
(
[AlertId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Assets](
[AssetId] [int] NOT NULL,
[IndustryId] [int] NOT NULL,
[RegionId] [int] NOT NULL,
[RevenueId] [int] NOT NULL,
CONSTRAINT [PK_Assets] PRIMARY KEY CLUSTERED
(
[AssetId] ASC,
[IndustryId] ASC,
[RegionId] ASC,
[RevenueId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (1, 2, 5, NULL)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (2, 2, 5, 1)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (3, 2, NULL, NULL)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (4, NULL, 5, NULL)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (5, 3, NULL, 4)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (6, NULL, 4, NULL)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (7, 3, 4, 1)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (1, 2, 4, 3)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (1, 2, 5, 1)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (2, 2, 5, 5)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (2, 3, 4, 5)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (3, 2, 4, 1)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (4, 2, 5, 4)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (5, 3, 5, 1)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (5, 2, 4, 5)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (5, 3, 1, 1)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (6, 3, 2, 4)
Thanks.
I have a column in which Email data is available like
clicuanan@aspenms.com(M)
jteply@mac.com(M)
How to extract in the below format
clicuanan@aspenms.com
jteply@mac.com
tjones@jpmc.com
I am preparing for the exam anybody know of any braindump sites that have data
warehousing braindumps?
Hello,
I am new to SQL Server. I want to know about DATA WAREHOUSING AND OLAP. Please help me.
Can anyone suggest a good resource that outlines data warehouse solutions using SQL 7.0 or higher?
View 1 Replies View RelatedHi,
I need to implement/set up the Data warehouse/Data mart in one of the department in my company by using SQL server 2005. Do any body knows the steps what I need to follow?
It will be more appreciate that, if any body gives some of the links which will help me to do the implementation/development of the same.
I do have the basic idea however I may face some of the difficulties when I start such as, does the SQL server reporting service allow the end user to customize the report based on their needs etc.?, so any of them having experience in this field please reply me.
Thanks
Ajith Nair
I am working on to create a data warehouse. I have made a database which will be the data warehouse and will consist of dimension and fact tables. I know that other than dimension and fact table a data warehouse should also consist of a meta data, now my question is what should be the structure of metadata and all the information it should have?
View 2 Replies View RelatedHi:
I am planning to give the microsoft 70-019 data warehousing exam. I am not able to find any good resources to prepare for the exam. Please help!
Thanks in advance
Hi
We are starting with designing a datawarehouse for my company. I have done some reading on the concepts and steps involved, but what I am seriously lacking is some examples. I'd like to read through some real examples of data warehouses that worked including the full design diagrams.
Can anyone direct me to some good sites for this?
Thanks,
TeaaZA
Hello, everone:
Anybody can offer technical information about DTS appied in data warehousing? Website, tutorial, forum and books will be great. Thanks.
ZYT
How do you run a stored procedure on PDW via SSIS? I've tried Execute SQL Task and Execute T-SQL Task but in both cases the task will run and complete almost immediately. Task shows success, no errors, but nothing happens in PDW. PDW admin console does not even register the query. Procedures run fine manually from SQL Server Object Explorer connection.
View 3 Replies View RelatedHow to use Stored Procedures in SSIS?
View 2 Replies View RelatedIs it possible to write a SP (Automate) to generate STATISTICS on any database and then use the output to create the stats on that database.
I ran the tuning adviser and it suggested indexes with lot of STATISTICS on the dev environment. This dev environment is replicated in several other environment with data size in these environment varying. I would like to know if I can create a SP which generates STATISTICS information pertaining to specific database environment for the query in question for tuning.
SSRS : I want to add parameter to my Cube from report is generating ...
View 5 Replies View RelatedI have a table that is increasing quite largely each day. By now, I have average 300 million of records over 2.5 years. Before we received our new interface, the data we received was aggregated and thus not that big.The problem is that the table is so huge that I cannot use the Slowly Changing Component. I was thinking about making a temp table where I load the incremental data before I load it into the final data mart table.Based on this temporary table I use a script to compare the temp data with the already existing data in the data mart. However, this requires a compare of each records (300 mil records).
View 3 Replies View RelatedQuestion: Is it feasible to use a star schema dimensional model for an OLTP system that incurs few (750 per day)Sales Orders transactions?
Background: My customer wants to replace an existing OLTP system database because it runs on Oracle and their in-house expertise is in SQL Server. The original database developers that designed the Oracle DB have apparently retired. The Oracle database has been over-normalized, to say the least. The number of sales orders being entered daily is small: about 500-750 per day. These entries are done at the five clerks' convenience, from a paper form, and are very unlikely to ever be entered in quick succession. Nothing else gets regularly entered into this database except for the occasional change to a customer, but new customers are very few and far between.
I've designed a star schema for the replacement database with the Sales Order Header and Sales Order detail table combined into a single 'fact' table, and I've introduced some duplication into dimension tables (like customer) in order to eliminate some of the joins (and confusion) that were built into the original database.
I've never tried this before. Is there any reason this would not or should not work?