.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Indexes on Partitioned Tables

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server
 

I have a table that is used for staging.  It has a unique clustered index on it that has the same partitioning as the table.  I want to know if there is a way for me to add a non-clustered, non-partitioned index to the table and still be able to do the partition swapping.  Are there any work arounds?  I have found that because the indexes are 'not-aligned' the swapping fails to insert into the table with the multiple indexes.

Thanks.




View Complete Post


More Related Resource Links

Does a Maintenance Plan "Rebuild Index" task on Tables + Views rebuild the full text indexes ?

  

As subject really.

I'm talking about a complete rebuild - not an incremental, or a re-organise.

SQL 2005

The full text indexes are only on individual tables, not Views.

I've asked a similar question before, but sufficiently different, and other is marked as Answered so thought I'd open a new Q : http://social.msdn.microsoft.com/Forums/en/sqltools/thread/5f17f4f1-fba9-436f-aedf-cfbfd89c8db2)

TIA


Problems joining Partitioned Tables and using the partitioning!

  

Hi,

I am joining three tables called "SNAP", "TABLE_A" and "TABLE_B" both of these 3 tables are partitioned by DAY_CODE column of type integer.
TABLE_A is about 10 million rows ( about 2 millions per day ), TABLE_B is like 60 million rows ( about 12 million per day ) and SNAP table is just a small table saying what days i have.

So, i am trying to use a query like this:

select ....
FROM SNAP S
INNER JOIN TABLE_A A
 ON S.DAY_CODE = A.DAY_CODE
INNER JOIN TABLE_B B
 ON S.DAY_CODE = B.DAY_CODE
 AND A.ID = B.TABLE_A_ID
WHERE S.DAY_CODE = 20100923

Like you can see, i am just using the SNAP table to align the filtering on the partitioned column. The problem is that the partitioned is not selected and somehow all columns of TABLE_B at least are being scanned for what the explain plan tells me. ( It runs for more than 12 hours and no results ).

If i change the query to:

Problems joining Partitioned Tables and using the partitioning!

  

Hi,

I am joining three tables called "SNAP", "TABLE_A" and "TABLE_B" both of these 3 tables are partitioned by DAY_CODE column of type integer.
TABLE_A is about 10 million rows ( about 2 millions per day ), TABLE_B is like 60 million rows ( about 12 million per day ) and SNAP table is just a small table saying what days i have.

So, i am trying to use a query like this:

select ....
FROM SNAP S
INNER JOIN TABLE_A A
 ON S.DAY_CODE = A.DAY_CODE
INNER JOIN TABLE_B B
 ON S.DAY_CODE = B.DAY_CODE
 AND A.ID = B.TABLE_A_ID
WHERE S.DAY_CODE = 20100923

Like you can see, i am just using the SNAP table to align the filtering on the partitioned column. The problem is that the partitioned is not selected and somehow all columns of TABLE_B at least are being scanned for what the explain plan tells me. ( It runs for more than 12 hours and no results ).

If i change the query to:

slow query, proper db setup, indexes, tables

  
I have a large set (~10 million) of time series data (date, time, and value) stored in text files.   These dates and times may repeat.   I would like to move these to a database (or several databases) and access them from a program.  I have sort of done this, the problem is that retrieving the data takes too long (say 30s).  I'd like to reduce the time it takes to access the data.

The steps I've done are:

1) Create a new database using SSMS.
2) Read in all of the year 2010 data into a new table
3) Accessed the data with the LINQ query from c#:
var timeSeriesData =
                from point in context.2010s
                where (new DateTime(point.Date.Year, point.Date.Month, point.Date.Day, point.Time.Hour, point.Time.Minute, point.Time.Second, point.Time.Millisecond)).CompareTo(_MinimumDateTime) >= 0
                    && (new DateTime(point.Date.Year, point.Date.Month, point.Date.Day, point.Time.Hour, point.Time.Minute, point.Tim

On rebuilding indexes of MSmerge_Contents and other replication system tables

  

Hello,

In my previous question leads me to the index rebuild of MSmerge_Contents system table. However, I am still not sure what the explicit and correct way to rebuild index is. 

Refering to examples in BOL, the syntax should be something like the following:

USE MY_DB;
GO
ALTER INDEX ALL ON msmerge_contents
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
 STATISTICS_NORECOMPUTE = ON);
GO

After checking system.indexes, I found that the fill_factor of 4 indexes of msmerge_contents are all 0. Does this mean I do not need to specify the value of FILLFACTOR? How about another 2 parameters, i.e. SORT_IN_TEMPDB and STATISTICS_NORECOMPUTE?

Do I need to specify ot

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

  
If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Temporary Tables - MS SQL Server

  
Usage of temporary tables in MS SQL Server is more developer friendly and they are widely used in development. Local temporary tables are visible only in current session while global temporary tables are visible across all sessions.

Temporary tables in SQL Server vs. table variables

  
When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Using a trigger or anything else to populate two tables

  

Hi! I'm creating an application that's supposed to first add a record to table1, and then get the ID from that record to use when adding a record to table2, to be able to associate these two records with eachother.

The user gets to type in some values that goes to table1, and some values that goes to table2, but before the insert statement for table2 is executed i need the ID from the recently added record in table1. Some dude told me to use a trigger for the autopopulate purpose, but does that really work when i also need to save some values that's user input, and when those values doesn't get saved in table1?

Are there any other way to do this or can i send values to a trigger? I'm new to triggers and stored procedures, i don't have any particular knowledge of this, any help is appreciated!

 

Regards, Monsterbadboll


merging multiple tables in a single dataset to single table

  

 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 


like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....



can some1 help me please.....


SqlDataSource UpdateCommand using 2 tables

  

I have two tables

Trans  with fields TransID, Date, CustomerID and some other stuff

Customer with fields CustomerID, Name, TaxId

On the screen the user only sees the fields Date and Customer Name. CustomerID is behind the scenes only.

 

I'm using SqlDataSource. Having no problems with SelectCommand. I don't know how to construct the UpdateCommand and InsertCommand.

Let's say the user changes the date, then I need to do an UPDATE.

UPDATE Trans SET Date = @Date, CustomerID = @CustomerID results in an error message and the record is not updated.

I get an error on the page that says "Sys.WebForms.PageRequestManagerServerErrorException: Input string was not in correct format".

 

I tried taking out the set for CustomerID and I still get the error on page.

 

Also, for inserting, the users will see a dropdownlist with Customer Names. I need to convert that to a CustomerID to be used in the new record being inserted in the database. I'm not sure how to do this.

 

Do I need to do something with Control Parameters?


How to display related tables in one crystal report and how to link this report with combobox?

  

Hi! I want to display a crystal report in my vb.net application. Suppose I have tables named student details, student marks, student address, etc... Now if I want to display all these details (fields of all tables) in one crystal report (with page breaks if necessary) then how will I achieve it. I will be providing a combo box in my application that contains list of student names. How can I link this combo box with the cystal report to dynamically display report for different student on selected index change of combo box? Help me friends. An example would be appreciable.


Data Points: Creating Audit Tables, Invoking COM Objects, and More

  

Dealing with error handling between T-SQL and a calling application, evaluating when a field's value has changed, and creating auditing tables in SQL ServerT are all common issues that developers must tackle.

John Papa

MSDN Magazine April 2004


Sql Scripts - Delete all Tables, Procedures, Views and Functions

  

In a shared environment you typically don't have access to delete your database, and recreate it for fresh installs of your product. 

I managed to find these scripts which should help you clean out your database.

Use at your own risk.

 

Delete All Tables

--Delete All Keys

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH

Join Two Tables and Prepare Report

  

Hello,

            I have a select query which is executing well. Now, I want to add one more field to that query. That field is not in the current query table, It is in the another table.


How do I join those two tables and get that field value in the existing select query.?


TIA


Migrating aspnet tables to dev server - having issues

  

Hi,

We're trying to migrate a one of our apps to our dev server for testing and development, but we're having problems with the membership functionality. We can add users, but there seems to be a disconnect with roles. We can query the aspnet_users table and find the new user in there, but when we query the aspnet_usersinroles table, that user id is not present.

We're also unable to run the Roles.GetUsersInRole("somerole") method. It returns 0 records. When I run Roles.ApplicationName, it returns the correct name, so .NET should be passing the correct app name.

We're just a little baffled. If anyone could shed some light on what could be the issue, we would appreciate it.

Thanks! :)


Uploading to SQL Server using AJAX muiltiple file uploader and dynamic SQL Server Tables

  

I am getting an error on the following code when trying to pload files directly to a database.  

 Incorrect syntax near ','.

 Incorrect syntax near 'image'.

 

    Private Sub Uploader_FileUploaded(ByVal sender As Object, ByVal args As UploaderEventArgs)

        Dim data() As Byte = New Byte((args.FileSize) - 1) {}

        Dim stream As Stream = args.OpenStream

        stream.Read(data, 0, data.Length)

    End Sub

 

Private Sub ButtonTellme_Click(ByVal sender As Object, ByVal e As EventArgs)

 

        Dim objConn As New SqlConnection("Data Source=mrpoteat.db.2798093.hostedresource.com; Initial Catalog=mrpoteat; User ID=mrpoteat; Password=Colgate23;")

        objConn.Open()

        Dim strCommandText As String = ""

        For index = 1 To Attachments1.Items.Count Step 1

            strCommandText += "pic" + index.ToString() + Space(5)

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend