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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Unique filtered index

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
Hi Friends   Here is my scenario Employee ( EmployeeID int, EmployeeName varchar(50), EmployeeCode varchar(15), IsDeleted bit ) I created a non clustered unique filtered index as below, but it doesn't work for me. That is to say, I cant create two rows with same EmployeeCode and different IsDeleted values. I want to maintain the unique filtered index on Employee code having IsDeleted = 0 CREATE UNIQUE NONCLUSTERED INDEX [U_Nix_User_EmployeeCode] ON [dbo].[Employee] ( [EmployeeCode] ASC ) WHERE ([IsDeleted]=(0)) GO If that is the way it works, Do I create trigger to enforce such a constraint? Thanks in advance for any help Best Regards Asharaf MCTS, http://asharafaliptb.wordpress.com

View Complete Post

More Related Resource Links

SQL 2005 Merge Replication : sysmergepublications unique index 'nc1sysmergepublications' fails

I've got one SQL 2005 SP2 server setup as Publisher and distributor which replicates through Merge pull subscriptions on SQL 2000 SP4 machines. Some of these SQL 2000 machines does republishing too. However, after a columndrop on one of the articles at the SQL 2005 machine by using sp_repldropcolumn the whole subscription sync came down on me with failures all over. All had the same error :

"Cannot insert duplicate key row in object 'dbo.sysmergepublications' with unique index 'nc1sysmergepublications'"
This procedure was called by sp_MScreateglobalreplica.

I have found that there are invalid publications in sysmergepublications, but only on publishers for the SQL 2000 subscription machines(excluding valid republishing publications). Deleting these pubID's does not help at all. With the first sync of any pull subscription (after the delete) they re-appear on the SQL 2005 machine.

However, I need to get the pull subscriptions going again. How can I clean the system tables to prevent any errors like above or duplicate keys in the sysmergesubscription table... Please do not recommend an overhaul. I need step by step procedures to get it going.

(index + unique constraint) limit per table?


 [1] tells:

SQL Server 2005              Maximum sizes/numbers
Database Engine object     SQL Server 2005 (32-bit)
Nonclustered indexes
per table                           249

UNIQUE indexes
or constraints per table      249
XML indexes                      249

Does it mean that:

Number of (nonclustered indexes + unique constraints = 498) per table?
or 249 since unqiue constraints are implemented through unique indexes?

Is (nonclustered indexes + XML indexes) = 498 or 249?

Maximum Capacity Specifications for SQL Server 2005 


Cannot insert duplicate key row in object 'Table' with unique index 'Column_Id' Problem


All, We have a User where there is an Access Application installed. Every Day he enters some Account Numbers in it. This Access application is hooked to SQL Server 2008 which updates a table in SQL Server. Now last 2 weeks user had some Connection Issues so he did not enter any Account Numbers in the Access application instead he just noted them on a Excel sheet.

Now I did not want him to insert manually, so I took that Excel sheet, loaded account numbers in a Temp table and tried to insert all the Missing Account Numbers in the Main table. But it failed with the following error: Note: Account_No has a Unique Non Clustered Index on it and it is not a Primary key also. How can I insert them? (There are no duplicate account numbers as well that I am trying to insert its all Unique Accout Numbers)

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.Table_Name' with unique index 'Accoutn_No'.
The statement has been terminated.


Unique content within master page based on conditions met


 Hi all-

I would like to present users with unique content based on certain conditions being met as they land on the home page (default.aspx).  The condition logic will be in the VB code behind Page Load event. The content will go in a content placeholder as specificed from the master page. 

However, the content change wouldn't be something small (ie making a panel or label visible or not) but rather it will be different HTML,  Divs and databound gridview content.  The HTML content will be stored in the database.

Can someone point me in the right direction for best practices on how to accomplish this?


Unique ID throughout entire request



I am hoping somene here could advise me on a better solution to my present problem.  First a little background information on the application. 

I have a web application using the 3.5 framework.  The structure of this application is as follows: UI -> WebService   -> Business Layer -> DataAccess Layer.   What I want to accomplish is for every request (click on a link or button) have a unique identifer that follows this request through its entire lifecycle.  This unique identifer can be accessible anywhere within this request.  We use Response.Redirects which could easily be changed to Server.Transfers (although I do not want to do that) but  the problem lies with the web service calls.  Currently, a GUID is set in the ApplicationRequest.  This GUID checks for a guid value in the RawURL and if so, assigns this value to a HttpContext Item.  If not, HttpContext.Items gets a new one created.  However, this idea is wrong and will need to be revisisted.  The bigger problem is with the web service calls.  Without changing every web service method signature, and every call for that matter, is there a way of getting this GUID there.  I created a web method in every web service so that each time I instantiate a web service object in the UI, before calling any

Unique text value in databse column


Hi Everyone!

I know this is probably a very basic question but how do I keep a column's text values unique in SQL server (ie for a unique username)?  The column is not an integer it's varchar(50), and it's not the primary key but I could make it the primary key if recommended.

The front end could handle it;  where the user would enter text, it's validated, then a query is done to see if that username is available.  If so, then user can decide to choose it and an insert is done, if not they pick a new username and process repeats.  With this approach, the responsibility of keeping the value unique solely rests on the front end, nothing on the database side.

Is this the correct way to handle this? 

Thank you!


Showing filtered Records in Crystal Report as per User Requirement


ReportDocument reportDocument = new ReportDocument();
    ParameterField paramField = new ParameterField();
    ParameterFields paramFields = new ParameterFields();
    ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
    protected void Page_Load(object sender, EventArgs e)
        if (!IsPostBack)
            paramField.Name = "@Ext";
            //*Remember to reconstruct the paramDiscreteValue and paramField objects
            paramDiscreteValue.Value = 44854
            CrystalReportViewer1.ParameterFieldInfo = paramFields;

Array List with unique id while adding student info

Hello Frds,

I'm having arraylist to add student info but i have to create unique id per record below is my class.

Student objStudent=new Student(123,"Nj");

public class Student
  private string name;
private int RollId;
public string Name

    get { return name; }
set { name = value; }

public int Id
    get { return RollId; }
set { RollId = value; }
public Employee(string name, int id)


how to create index for a column


Hi Friends,

                       I need to create the index for a column and accessing that in sqlserver 2005. Please any one help me.


reusable components and unique ids


I'm trying to create reusable "zones" but am having trouble figuring out how to give them all unique ids.

What I'd like is to be able to create a component which can be put on an mvc page using the usercontrol syntax or using RenderAction or RenderPartial.  I want to be able to update these components' content using ajax, so I need someway to reference them by id.  However, there is no mechanism in MVC that wraps anything in a div or something like that.

I've learned that if I extend ViewUserControl and put the controls on the page like so:

<Zones:TestControl runat="server" ID="TestControl1" />

Then I can use the 'ClientID' property to get the unique ID generated.  However, if I use RenderAction or RenderPartial in another request, obviously the control hierarchy is missing, so the ID is just 1.  So to get around this I made a simple base class like so:

public class BaseZone<TModel> : System.Web.Mvc.ViewUserControl<TModel>
        protected string zoneId;

        public string ZoneId
                string zoneId = ViewData["ZoneId"] as string;
                if (String.IsNullOrEmpty(zoneId))
                    return this.C

Deleting items from the index (custom protocol handler)

I created a custom protocol handler. I successfully emit entries into the index. I just can't seem to delete them during an incremental crawl.

I tried:

1. no emitting child item during the enumeration of child entries in a folder
2. returning PRTH_E_OBJ_NOT_FOUND from CreateAccessorEx

doing either 1, 2, or both did nothing. The entries are still there.

what i need to do to delete these entries from the index during an incremental crawl?


Ingest google Search Index into Sharepoint 2007


Hi Folks

 I am wondering  if there is a way to ingest google search index into SharePoint index such that when people search via SharePoint they see the Google results too… incorporated/federated inline with the SP results (not as a separate "box" ).

We have Google search in our environment and also share-point search. Share-point search for sharepoint site but Google search is for all other environments. Now i am thinking if we ingest the Google search index into share-point then may be its great deal to provide user a platform to search everything on one place.




i am at entry level administrator

Can't start crawl becasue of index move operation


Hi there,

I can't start crawl task. The log says that "Deleted by the gatherer (The start address or content source that contained this item was deleted and hence this item was deleted.)" But I did not change the path of content sources and When I trie to start crawling job it says "Crawling might be paused because a backup or an index move operation is in progress. Are you sure you want to resume this crawl?"

What is index move operation? What should I do? I'll really appreciate the solution greatly. Thanks in advance.



"Content for this URL is excluded by the server because a no-index attribute." in crawl logs


Hi All,

I am getting following error message in Crawl Logs

" Content for this URL is excluded by the server because a no-index attribute. "

Any help in this regard will be greatly appreciated.



Contact Selector - Form tab index is lost after selector resolves names on blur


I'm working with an InfoPath2007 form developed to be filled-out through the browser on a SharePoint 2007 site, and I'm testing using Internet Explorer 7.  It's a straight-forward form, with a number of text fields and two Contact Selector controls.

The issue I'm encountering is with the Contact Selector control, where if the actor only enters a partial name into the text-field and then TABs away from the control the form will perform its auto-postback to look-up and resolve the partial name (showing the name resolution dialog is necessary), and afterward the actor's cursor is focused on the next field in the form.  The actor can type text into the focused field, but any subsequent pressing of the TAB key returns the actor's focus to the browser's ADDRESS bar, which is incorrect as it should proceed to the next field in the form.  I understand that the Contact Selector needs to perform its postback to perform the name resolution (so that there is no way through the InfoPath form designer to disable postbacks for the control), but why does the browser not know how to continue the TAB order of the fields on the form even when a field has focus?

Problems creating a BDC file that use a unique select statment


I'm trying to build an BDC file that will pull information back from two tables in an Oracle database, I can import it without any errors, but then it tells me I do not have a filter when I try and select an item.  When I add a filter I get an error when I import the file.  The error I get is the following.
"Application definition import failed. The following error occurred: The IdentifierName Attribute of the TypeDescriptor named 'TestDate' of the Parameter named ':TestDate' of the Method named 'Get_TestTwelve_List' of the Entity named 'TestTwelve' does not match any of the defined Identifiers for the Entity. Error was encountered at or just before Line: '133' and Position: '16'."

Here is the section of

      <Property Name="RdbCommandText" Type="System.String"><![CDATA[

QueryEx (Search) results and Custom Page Index


Hi All,

I done a web part which allow the user to query the search.asmx. It return the result fine against which I apply some xslt. However I need a mean to create a Page index. I only return the result based on a range defined in the query.

I need somehow to obtain the number of result the search is returning in total against what I only want to display on the screen.

The only way I sort of figure to do that is by first make a search just to obtain the number of result it will return in total and then make a search defining a range.

Has someone done something similar?


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