Showing posts with label table. Show all posts
Showing posts with label table. Show all posts

Thursday, March 29, 2012

Filter in Replication

Hello,
I have a table JobDescription with the columns:
JobId
Email
StartTime
EndTime
Priority
etc.
I want the Filter in that way that only the rows of the JobDescription of a
certain Email Adress will be synchronized.
Can anybody tell me how this work?
thx
mathon
But which rows from which email would be displayed is only clear when the
user logs in the application with his email-adress. So I think the static
filter is not the right. Should I use dynamic filter? - How do I have to
quote it in that way in the Publication Process and then in my code? -
Unfortunately I haven't found a good sample for that.
mathon
|||Yes - dynamic filters is the way to go. If this is per
user, you can have a column for the loginname (the data
returned from select suser_sname()) and use that in the
filter. The dynamic filter option is a subset of the
horizontal filters part of the wizard and will appear if
you have selected the advanced options (first checkbox in
the wizard). Your dynamic filter will look like this:
SELECT <published_columns> FROM [dbo].[JobDescription]
WHERE JobDescription.SuserSName = SUSer_SName().
If you don't want another column in the table, and want
to map logins to email addresses, you can publish the
mapping table and apply the dynamic filter to that table,
or alternatively encapsulate the whole thing in a UDF to
be used in the where clause.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Dear Mr. Ibison!
Let me reflect once to know If I'll be right:
The method in the Login-mask selects the the Email of the User (Engineer) ->
SELECT Email FROM Engineer WHERE UserId=? AND password=?.
So in the Publication Wizard:
1) First I check "Horizontally, by filtering the rows published data"
2) Then I check "Yes, enable dynamic filters"
3) Then I select the Table to filter - [dbo].[JobDescription]
4) Then the Select Statement looks as follows:
SELECT <published_columns> FROM [dbo].[JobDescription] WHERE
[dbo].[JobDescription].EmailEngineer = EngineerLogin()
But when I want to go further the following error occurs:
"The WHERE clause 'JobDescription.EmailEngineer = EngineerLogin()' could not
be executed on table '[dbo].[JobDescription]'.
Error 195. 'EngineerLogin() is not a recognized function name.
Do you know what went wrong? (The function exists in my class-file.)
Regards
mathon
"Paul Ibison" wrote:

> Yes - dynamic filters is the way to go. If this is per
> user, you can have a column for the loginname (the data
> returned from select suser_sname()) and use that in the
> filter. The dynamic filter option is a subset of the
> horizontal filters part of the wizard and will appear if
> you have selected the advanced options (first checkbox in
> the wizard). Your dynamic filter will look like this:
> SELECT <published_columns> FROM [dbo].[JobDescription]
> WHERE JobDescription.SuserSName = SUSer_SName().
> If you don't want another column in the table, and want
> to map logins to email addresses, you can publish the
> mapping table and apply the dynamic filter to that table,
> or alternatively encapsulate the whole thing in a UDF to
> be used in the where clause.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>
|||Mathon,
firstly, I must clarify that the ability to filter data
on a per subscriber basis is part of merge replication
only (out of the box), so that is the scenario we are
looking at.
Your login mask may or may not be an issue. Is the
username and password used to log into sql server - ie is
this a SQL Server login? If it is, then filtering will be
simpler. In the wizard, ensure the advanced options are
enabled, and after selecting horizontal filters, select
the dynamic option. Your EngineerLogin() function as far
as I can see is a function you've created in your VB
code, so is not accessible to SQL Server (CLR integration
is a posibility in SQL 2005 but not SQL 2000). So, to map
the login name to the email address, you'll need a user-
defined function to be created in SQL Server. This
function will take suser_sname() as a parameter and will
return the email address as a return value.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
The password and userId is the log-in information for my mobile application
and not the login for the sql server. Is this more complicated?
regards
mat
"Paul Ibison" wrote:

> Mathon,
> firstly, I must clarify that the ability to filter data
> on a per subscriber basis is part of merge replication
> only (out of the box), so that is the scenario we are
> looking at.
> Your login mask may or may not be an issue. Is the
> username and password used to log into sql server - ie is
> this a SQL Server login? If it is, then filtering will be
> simpler. In the wizard, ensure the advanced options are
> enabled, and after selecting horizontal filters, select
> the dynamic option. Your EngineerLogin() function as far
> as I can see is a function you've created in your VB
> code, so is not accessible to SQL Server (CLR integration
> is a posibility in SQL 2005 but not SQL 2000). So, to map
> the login name to the email address, you'll need a user-
> defined function to be created in SQL Server. This
> function will take suser_sname() as a parameter and will
> return the email address as a return value.
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||OK - in that case there might be issues. Do you use the
same login to SQL Server for everyone? Is the mobile
device used by an individual user or multiple users?
In the easiest scenario, the device is used by the same
user each time. Typically you'll be using a sql server
login which doesn't differentiate between users, so we
can't rely on this in the filter. If this is true, you
could set the filter to use HOST_NAME(). In the merge
agent's job step for this subscriber, add -HOSTNAME
[emailaddress] to filter the data (replace emailaddress
with the individuals email address). The filter will be
as follows:
SELECT <published_columns> FROM [dbo].[JobDescription]
WHERE JobDescription.email = HOST_NAME().
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
Yes the device is always used by the same user and everybody has the same
login and password (sa,sa) to the SQL Server database. But i do not exactly
know what you mean with merge agents job step? Where should I add
-HOSTNAME[emailaddress]? And is it then still dynamically when I am not able
to filter according to the login a user makes in my mobile application?
regards
mat
"Paul Ibison" wrote:

> OK - in that case there might be issues. Do you use the
> same login to SQL Server for everyone? Is the mobile
> device used by an individual user or multiple users?
> In the easiest scenario, the device is used by the same
> user each time. Typically you'll be using a sql server
> login which doesn't differentiate between users, so we
> can't rely on this in the filter. If this is true, you
> could set the filter to use HOST_NAME(). In the merge
> agent's job step for this subscriber, add -HOSTNAME
> [emailaddress] to filter the data (replace emailaddress
> with the individuals email address). The filter will be
> as follows:
> SELECT <published_columns> FROM [dbo].[JobDescription]
> WHERE JobDescription.email = HOST_NAME().
> HTH,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Mathon,
the hostname will be dynamic in the sense of each
subscription will be able to get different data. This
means that each mobile device can have different data. It
doesn't mean that if a different user logs onto the
device he'll see different data - the data is partitioned
according to device. If there is a 1:1 relationship
between users and devices then this is fine.
The merge agent is a job. The middle step runs the merge
executable. In this step there will be a number of
parameters , and at the end you can add -HOSTNAME with a
value. If you are doing this programatically, the
HOSTNAME is a property of the merge object.
I recommend that you set it up and have a play around
with the dynamic filtering in a test environment to see
if it meets your requirements.
HTH,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Mr. Ibison!
Unfortunately I am not so familiar with the Replication Mechanism. In my
application I realized the replication only in that way that all my tables
would be replicated and no filters were set. I do this in the Publication
Wizard in the SQL Server Enterprise Manager. In the code I only quote then
the properties for the Replication like login, publisher, etc. (The
Replication works, but as said I want to filter the one table
JobDescription.)
So I am not sure how I should execute your quoted steps to reach my aim. Can
you probably give me a simpler and/or more detailed explanation?:-/ sorry..
mathon

Filter Help Needed

Good Day to All,
Need some help with Filters in SQL Reporting 2000.
Have a table report setup with a DS going to a Store Proc. need to add a
filter that is a bit complex. Don't know which filter section I should be
putting this in but in all cases I've run into errors.
Basically when the data is brought back from my Store Proc, I need to filter
out the data according to the following:
IF CBalance < 0
THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
ELSE (TTLBalance > (CBalance*0.25))
I keep getting this error that the filter comparison fails and I should
check the data Types returned by the filter expression.
Please help...you might want to cast your fields as varchar in your query. This has
been the answer to many filter comparison problems I have had in the
past.
Eric wrote:
> Good Day to All,
> Need some help with Filters in SQL Reporting 2000.
> Have a table report setup with a DS going to a Store Proc. need to add a
> filter that is a bit complex. Don't know which filter section I should be
> putting this in but in all cases I've run into errors.
> Basically when the data is brought back from my Store Proc, I need to filter
> out the data according to the following:
> IF CBalance < 0
> THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> ELSE (TTLBalance > (CBalance*0.25))
> I keep getting this error that the filter comparison fails and I should
> check the data Types returned by the filter expression.
> Please help...|||Thanks Topher that was it. Odd the filter section can't handle comparisons
other then varchar / string like values. I wonder if this carried over into
the newer version.
Thanks again!!!
"Topher" wrote:
> you might want to cast your fields as varchar in your query. This has
> been the answer to many filter comparison problems I have had in the
> past.
>
> Eric wrote:
> > Good Day to All,
> > Need some help with Filters in SQL Reporting 2000.
> > Have a table report setup with a DS going to a Store Proc. need to add a
> > filter that is a bit complex. Don't know which filter section I should be
> > putting this in but in all cases I've run into errors.
> >
> > Basically when the data is brought back from my Store Proc, I need to filter
> > out the data according to the following:
> > IF CBalance < 0
> > THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> > ELSE (TTLBalance > (CBalance*0.25))
> >
> > I keep getting this error that the filter comparison fails and I should
> > check the data Types returned by the filter expression.
> >
> > Please help...
>

Filter does not work on a group?

I must be missing something. I have a table with two groups. One groups on
the user who created the record the other on the date the record was created.
I sort on the username group by name and the createdate group with a
category type.
My dataset pulls information for the current year however in this table I
want to only display data for the last week. If I apply the filter to the
table I receive expected results however if I apply the filter to the group
nothing comes back.
For either group if I put "= #2/22/2005#" for the expression "<=" as the
operator and "= Fields!CreateDate" as the Value I return 0 records despite
having some. I know my syntax is correct becaus this works at the table
level.
Can anyone tell me what I am missing?And you have date values in there more recent than 2/22/2005?
2/22/2005 <= Fields!CreateDate
I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
the value.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
>I must be missing something. I have a table with two groups. One groups
>on
> the user who created the record the other on the date the record was
> created.
> I sort on the username group by name and the createdate group with a
> category type.
> My dataset pulls information for the current year however in this table I
> want to only display data for the last week. If I apply the filter to the
> table I receive expected results however if I apply the filter to the
> group
> nothing comes back.
> For either group if I put "= #2/22/2005#" for the expression "<=" as the
> operator and "= Fields!CreateDate" as the Value I return 0 records despite
> having some. I know my syntax is correct becaus this works at the table
> level.
> Can anyone tell me what I am missing?|||Same result. I know there are values past 2/22/2005 because I see them
without the filter applied. The filter works at the table level just not the
group level. This is strange.
"Jeff A. Stucker" wrote:
> And you have date values in there more recent than 2/22/2005?
> 2/22/2005 <= Fields!CreateDate
> I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
> the value.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
> news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
> >I must be missing something. I have a table with two groups. One groups
> >on
> > the user who created the record the other on the date the record was
> > created.
> > I sort on the username group by name and the createdate group with a
> > category type.
> >
> > My dataset pulls information for the current year however in this table I
> > want to only display data for the last week. If I apply the filter to the
> > table I receive expected results however if I apply the filter to the
> > group
> > nothing comes back.
> >
> > For either group if I put "= #2/22/2005#" for the expression "<=" as the
> > operator and "= Fields!CreateDate" as the Value I return 0 records despite
> > having some. I know my syntax is correct becaus this works at the table
> > level.
> >
> > Can anyone tell me what I am missing?
>
>

Filter delete rows on subscriber

How does SQL server replication delete rows on the client that no longer
matches the filter? I need this to decide if I want to add a new table into
replication.
TIA.
Rajesh
There is a table with a large number of rows (5-6 million), with only a few
rows (2000-3000) replicated to each client. Each client needs to get a
different set of rows.
I want to know whether:
1. The filter is applied on the subscriber to delete the rows?
2. Or the IDs to be deleted is sent to the subscriber by the publisher?
3. Or the IDs on the subscriber is sent to the publisher, for which the
publisher responds with the IDs that should no longer be available in the
subscriber.
Due to the large number of rows, I'm just worried that #2 and #3 will lead
to a lot of inefficeincy. And if that is the case, I would rather sync this
data outside of replication.
Rajesh
"Rajesh" wrote:

> How does SQL server replication delete rows on the client that no longer
> matches the filter? I need this to decide if I want to add a new table into
> replication.
> TIA.
> Rajesh
sql

Filter Cube Data

Hi

i am using pivot table 11 to display the data from SSAS Cube. the problem is

1- pivot table fetches all the data and display it according to the design. but what i need is

a- the user could only see the data which was posted by himself.

b. He must be unaware of the data posted by other users.

in database table there is a field named userId which is also which is part of dimenssion in myCube.

i dont want to use filteraxes for this purpose actually i want the user to be unaware of other users of system also

any single helping word will be appriciated

Regards

Salman Bashir

Hi Salman,

You can define custom dimension security rules for the "UserID" attribute - the MDX function: UserName (which returns "domain\user") is typically used for this purpose:

http://msdn2.microsoft.com/en-us/library/ms175366.aspx

>>

SQL Server 2005 Books Online

Granting Custom Access to Dimension Data

...

Understanding the IsAllowed Property

The IsAllowed property determines whether the database role can access attribute members. By default, a database role that has access to a dimension cannot access attribute hierarchies.

Understanding the AllowedSet Property

The AllowedSet property uses a Multidimensional Expressions (MDX) expression to determine which attribute members can be viewed by the database role (the allowed set). The allowed set can include no (default), all, or some attribute members. If you allow access to an attribute and do not define any members of the allowed set, access to all members is granted. If you allow access to an attribute and define a specific set of attribute members, only the specifically allowed members are visible. Specifically defining an allowed set may limit the visibility of attribute members added after the allowed set is defined.

...

Understanding the VisualTotals Property

The VisualTotals property indicates whether the aggregated cell values that are displayed are calculated according to all cell values or only according to the cell values that are visible to the database role.

By default, the VisualTotals property is disabled (set to False). This default setting maximizes performance because Analysis Services can quickly calculate the total of all cell values, instead of having to spend time selecting which cells values to calculate.

However, having the VisualTotals property disabled could create a security issue if a user can use the aggregated cell values to deduce values for attribute members to which the user's database role does not have access. For example, Analysis Services uses the values for three attribute members to calculate an aggregated cell value. The database role has access to view two of these three attribute members. Using the aggregated cell value, a member of this database role would be able to deduce the value for the third attribute member.

If a user can deduce values for attribute members to which the user's database role does not have access, security best practice dictates that you enable (set to True) the VisualTotals property for the attribute. When you enable the VisualTotals property, a database role can only view aggregated totals for dimension members to which the role has permission. For example, enabling the VisualTotals property means that the database role will see an aggregated total that includes only those states (that is, members of the State attribute) that are visible to the role. The aggregated total will not include the values for all states.

Understanding the DefaultMember Property

The DefaultMember property determines the data set that is returned to a client when an attribute is not explicitly included in a query. When the attribute is not explicitly included, Analysis Services uses one of the following default members for the attribute:

If the database role defines a default member for the attribute, Analysis Services uses this default member.
If the database role does not define a default member for the attribute, Analysis Services uses the default member that is defined for the attribute itself. The default member for an attribute, unless you specify otherwise, is the All member (unless the attribute is defined as non-aggregatable).
|||

Dear sir

i am really thankful to you for your such a quick reply. it is no doubt very much helpful but my requirments are a bit different

i have custom role management system and i dont use the roles and user accounts in operating system but there are roles which are defined in my personal database and there are user accounts under those roles.

whenever a user inserts a record in database it leaves a refference tag (His LoginName (which is from a table named users in the same database and have no relation with os or domain users) with that record) which identify that which user is owner of that record.

when user sign in with my application (basically with my own role management system) a global reference to his LoginName (which is from User table in my role management system) is created when his session starts. now from this application he opens a form on which a pivot table is placed which is getting data from the cube (whose datasource is the database i defined in 2nd paragraph) now i want pivot table to send a LoginNameas parameter and cube send back the data with this username and all calculations must be performed on this filter data

|||

Actually, dynamic dimension data security as described above should still work - if you set the SSAS "CustomData" connection property to the custom "LoginName" managed by your app. Then you would access the user "LoginName" via the "CustomData" MDX function:

http://msdn2.microsoft.com/en-us/library/ms145582.aspx

>>

SQL Server 2005 Books Online

CustomData (MDX)

Updated: 17 July 2006

Returns the value of the CustomData connection string property if defined; otherwise, null.

...

For example, this function can be used in a dynamic security expression to select the allowed/denied set members for the string value in the CustomData connection string property.

>>

|||

Hi Salman,

were you able to figure out the solution. if yes could you please post you example details.

Thanks

Milind

|||

I am also having the same problem of filtering the cube data based on users defined in a database. were you able to find a solution for this?

Thanks,

Maximus11

Filter Cube Data

Hi

i am using pivot table 11 to display the data from SSAS Cube. the problem is

1- pivot table fetches all the data and display it according to the design. but what i need is

a- the user could only see the data which was posted by himself.

b. He must be unaware of the data posted by other users.

in database table there is a field named userId which is also which is part of dimenssion in myCube.

i dont want to use filteraxes for this purpose actually i want the user to be unaware of other users of system also

any single helping word will be appriciated

Regards

Salman Bashir

Hi Salman,

You can define custom dimension security rules for the "UserID" attribute - the MDX function: UserName (which returns "domain\user") is typically used for this purpose:

http://msdn2.microsoft.com/en-us/library/ms175366.aspx

>>

SQL Server 2005 Books Online

Granting Custom Access to Dimension Data

...

Understanding the IsAllowed Property

The IsAllowed property determines whether the database role can access attribute members. By default, a database role that has access to a dimension cannot access attribute hierarchies.

Understanding the AllowedSet Property

The AllowedSet property uses a Multidimensional Expressions (MDX) expression to determine which attribute members can be viewed by the database role (the allowed set). The allowed set can include no (default), all, or some attribute members. If you allow access to an attribute and do not define any members of the allowed set, access to all members is granted. If you allow access to an attribute and define a specific set of attribute members, only the specifically allowed members are visible. Specifically defining an allowed set may limit the visibility of attribute members added after the allowed set is defined.

...

Understanding the VisualTotals Property

The VisualTotals property indicates whether the aggregated cell values that are displayed are calculated according to all cell values or only according to the cell values that are visible to the database role.

By default, the VisualTotals property is disabled (set to False). This default setting maximizes performance because Analysis Services can quickly calculate the total of all cell values, instead of having to spend time selecting which cells values to calculate.

However, having the VisualTotals property disabled could create a security issue if a user can use the aggregated cell values to deduce values for attribute members to which the user's database role does not have access. For example, Analysis Services uses the values for three attribute members to calculate an aggregated cell value. The database role has access to view two of these three attribute members. Using the aggregated cell value, a member of this database role would be able to deduce the value for the third attribute member.

If a user can deduce values for attribute members to which the user's database role does not have access, security best practice dictates that you enable (set to True) the VisualTotals property for the attribute. When you enable the VisualTotals property, a database role can only view aggregated totals for dimension members to which the role has permission. For example, enabling the VisualTotals property means that the database role will see an aggregated total that includes only those states (that is, members of the State attribute) that are visible to the role. The aggregated total will not include the values for all states.

Understanding the DefaultMember Property

The DefaultMember property determines the data set that is returned to a client when an attribute is not explicitly included in a query. When the attribute is not explicitly included, Analysis Services uses one of the following default members for the attribute:

If the database role defines a default member for the attribute, Analysis Services uses this default member.

If the database role does not define a default member for the attribute, Analysis Services uses the default member that is defined for the attribute itself. The default member for an attribute, unless you specify otherwise, is the All member (unless the attribute is defined as non-aggregatable).
|||

Dear sir

i am really thankful to you for your such a quick reply. it is no doubt very much helpful but my requirments are a bit different

i have custom role management system and i dont use the roles and user accounts in operating system but there are roles which are defined in my personal database and there are user accounts under those roles.

whenever a user inserts a record in database it leaves a refference tag (His LoginName (which is from a table named users in the same database and have no relation with os or domain users) with that record) which identify that which user is owner of that record.

when user sign in with my application (basically with my own role management system) a global reference to his LoginName (which is from User table in my role management system) is created when his session starts. now from this application he opens a form on which a pivot table is placed which is getting data from the cube (whose datasource is the database i defined in 2nd paragraph) now i want pivot table to send a LoginNameas parameter and cube send back the data with this username and all calculations must be performed on this filter data

|||

Actually, dynamic dimension data security as described above should still work - if you set the SSAS "CustomData" connection property to the custom "LoginName" managed by your app. Then you would access the user "LoginName" via the "CustomData" MDX function:

http://msdn2.microsoft.com/en-us/library/ms145582.aspx

>>

SQL Server 2005 Books Online

CustomData (MDX)

Updated: 17 July 2006

Returns the value of the CustomData connection string property if defined; otherwise, null.

...

For example, this function can be used in a dynamic security expression to select the allowed/denied set members for the string value in the CustomData connection string property.

>>

|||

Hi Salman,

were you able to figure out the solution. if yes could you please post you example details.

Thanks

Milind

|||

I am also having the same problem of filtering the cube data based on users defined in a database. were you able to find a solution for this?

Thanks,

Maximus11

Tuesday, March 27, 2012

Filter a Report's Table using multiple Like conditions using Or

I would like to filter a report using multiple Like conditions

or - Change a filter in Reporting Services to OR rather than AND..

Example: (SameFieldName Like *1100) or (SameFieldName Like *1200).

When I try doing this on the Report's table properties - Filters tab - the And/Or automatically changes to "And" with using the "Like" Operator.

The only time "Or" appears is when I use the " = " Operator.

Or can someone show me how to use an expression to filter on multiple Like conditions.

Thanks!

Try the following filter:

Filter expression:
=(Fields!FName.Value like "*1100" OR Fields!FName.Value like "*1200")

Filter operator:
=

Filter value:
=True

-- Robert

|||You got it! It works!

Thank you

BBK

Filter a Model Table?

I am using RS 2005. I am setting up a Model for use within Report Builder so our clients can write their own reports.

A 2 part question, simple question first:

1. How can I filter the records in a Model Entity? I thought this would be possible from a Perspective but it is not.

2. How can I filter the records in a Model Entity based on the locale of the person who is logged on? And also based on their permissions?

TIA

You are correct that this is not possible with Perspectives. Note that Perspectives do not secure your data in any way.

You can use Model Item Security and Security Filters to expose different records to different users. A security filter could include a formula filter condition that uses the GETUSERCULTURE function. However, this seems a little odd, since culture is trivial to “spoof”, so it doesn’t really secure anything.

--Bob

|||Thanks for the reply Bob.

So I am in SQL Server Management Studio, I have double clicked on the model and gone to Model Item Security. But, I cannot see where to set up a Security Filter? Also, when I seach on SQL Server 2005 BOL for "Security Filter" it finds nothing.

|||Hi, can someone please help me with this ... please. People keep talking about Security Filter Scripts but I cannot find them anywhere ...|||Ummm, well can someone then please just tell me if this is a difficult question? I have seen a few other posts on the same question and the answer is never posted. Can someone at MSFT please just put me out of my misery and tell me either how to use this facility or, even better, refer me to the associated documentation or, not so good, that the feature is a figment of our collective imaginations. :)|||SecurityFilters is a collection property of a model entity. You "turn on" security filters by adding at least one filter attribute to this collection (i.e. if this collection is empty, security filters are "off" and all users with permission to the entity will be able to see all rows). Each filter in the collection defines a set of rows to which a user or group may be granted access. You can grant access to a specific user or group by giving them permission to see the filter attribute using Model Item Security. Users will have access to the UNION of all rows exposed by the security filters for which they have permission. Note 1: Filter attributes are typically used only for security filters, so the Hidden property is usually set to true. Note 2: Model entities also have a DefaultSecurityFilter property which can be used to grant access to some set of rows for users that do not have access to any of the filter attributes in the SecurityFilters collection.|||I am afraid this is getting very frustrating for me. Your answer here sounds very nice Bob - but it does not tell me how to do it. Also if I search google and microsoft for SecurityFilters or look for books I find no further help. I have a book on Report Services 2000, but obviously it does not cover of Report Models (a 2005 feature).

I am using v8.0.50215.44 of Visual Studio 2005. And I am using v9.00.1187.00 of Microsoft SQL Server Management Studio. Maybe these versions are too old?

If I open up the model designer and click on a model entity to see it's properties, then where do I add in collection properties for the model entity? Especially, how can I say it is a filter attribute? I can see no such properties.

TIA|||I'm sorry this has been frustrating. Let me try to fill in some of the gaps here.
SQL 2005 documentation ("SQL Books Online") is not available on the web yet. It sounds like you have the July CTP build installed; I'm not sure what state the documentation was in back then. Even if you can find the SecurityFilters property, it may have just been stub docs at that point.
If you want, you can download the September CTP docs here . RTM bits (including docs) are now available to MSDN subscribers, and will be publicly available after the launch next week.
SecurityFilters is a property on a model entity. You should see it near the end of the list in the property grid in Model Designer. Like several other model entity properties (e.g. IdentifyingAttributes, DefaultDetailAttributes, SortAttributes), it contains a set of references to model attributes you have previously defined. The only constraint on this particular collection is that all attributes referenced by it must have DataType=Boolean and IsFilter=True. The easiest way to create such an attribute is to select the entity on which it will be defined, right-click on an empty area of the attribute list, and choose New->Filter.
Hopefully this is enough to help you get started.|||Many thanks Bob! Yes, the problem has been that I have had the July CTP build. That info looks perfect to get me started - I appreciate your help.

I'll go get the Septmeber CTP build and doco now and work from there.

I'm looking forward to the release date - I am registered for the one day launch here in NZ - should be grand! :)|||Current books online still has very few things to say about secufity filters on report models. Does anyone know of any sites or articles that would greatly explain how to do this?|||

Hi Bob,

Your article on http://msdn2.microsoft.com/en-us/library/ms365343.aspx is very helpful, but when I use SQL Server Management Studio to set Model Item Security for differnt security filters, it seems "Permissions" property surpass "Model Item Security" property.

For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;

in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.

My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?

I posted my issue on http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1905805&SiteID=17, I attach my post here:

"

I read the following on article http://msdn2.microsoft.com/en-us/library/ms365343.aspx:

"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."

So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.

I can only get one result at a time but not both:

either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.

Anybody please please help me? Thank you!

"

Report server is using Custom Authentication.

Thank you for your help.

Temple1

Filter a Model Table?

I am using RS 2005. I am setting up a Model for use within Report Builder so our clients can write their own reports.

A 2 part question, simple question first:

1. How can I filter the records in a Model Entity? I thought this would be possible from a Perspective but it is not.

2. How can I filter the records in a Model Entity based on the locale of the person who is logged on? And also based on their permissions?

TIA

You are correct that this is not possible with Perspectives. Note that Perspectives do not secure your data in any way.

You can use Model Item Security and Security Filters to expose different records to different users. A security filter could include a formula filter condition that uses the GETUSERCULTURE function. However, this seems a little odd, since culture is trivial to “spoof”, so it doesn’t really secure anything.

--Bob

|||Thanks for the reply Bob.

So I am in SQL Server Management Studio, I have double clicked on the model and gone to Model Item Security. But, I cannot see where to set up a Security Filter? Also, when I seach on SQL Server 2005 BOL for "Security Filter" it finds nothing.|||Hi, can someone please help me with this ... please. People keep talking about Security Filter Scripts but I cannot find them anywhere ...|||Ummm, well can someone then please just tell me if this is a difficult question? I have seen a few other posts on the same question and the answer is never posted. Can someone at MSFT please just put me out of my misery and tell me either how to use this facility or, even better, refer me to the associated documentation or, not so good, that the feature is a figment of our collective imaginations. :)|||SecurityFilters is a collection property of a model entity. You "turn on" security filters by adding at least one filter attribute to this collection (i.e. if this collection is empty, security filters are "off" and all users with permission to the entity will be able to see all rows). Each filter in the collection defines a set of rows to which a user or group may be granted access. You can grant access to a specific user or group by giving them permission to see the filter attribute using Model Item Security. Users will have access to the UNION of all rows exposed by the security filters for which they have permission. Note 1: Filter attributes are typically used only for security filters, so the Hidden property is usually set to true. Note 2: Model entities also have a DefaultSecurityFilter property which can be used to grant access to some set of rows for users that do not have access to any of the filter attributes in the SecurityFilters collection.|||I am afraid this is getting very frustrating for me. Your answer here sounds very nice Bob - but it does not tell me how to do it. Also if I search google and microsoft for SecurityFilters or look for books I find no further help. I have a book on Report Services 2000, but obviously it does not cover of Report Models (a 2005 feature).

I am using v8.0.50215.44 of Visual Studio 2005. And I am using v9.00.1187.00 of Microsoft SQL Server Management Studio. Maybe these versions are too old?

If I open up the model designer and click on a model entity to see it's properties, then where do I add in collection properties for the model entity? Especially, how can I say it is a filter attribute? I can see no such properties.

TIA|||I'm sorry this has been frustrating. Let me try to fill in some of the gaps here.
SQL 2005 documentation ("SQL Books Online") is not available on the web yet. It sounds like you have the July CTP build installed; I'm not sure what state the documentation was in back then. Even if you can find the SecurityFilters property, it may have just been stub docs at that point.
If you want, you can download the September CTP docs here . RTM bits (including docs) are now available to MSDN subscribers, and will be publicly available after the launch next week.
SecurityFilters is a property on a model entity. You should see it near the end of the list in the property grid in Model Designer. Like several other model entity properties (e.g. IdentifyingAttributes, DefaultDetailAttributes, SortAttributes), it contains a set of references to model attributes you have previously defined. The only constraint on this particular collection is that all attributes referenced by it must have DataType=Boolean and IsFilter=True. The easiest way to create such an attribute is to select the entity on which it will be defined, right-click on an empty area of the attribute list, and choose New->Filter.
Hopefully this is enough to help you get started.|||Many thanks Bob! Yes, the problem has been that I have had the July CTP build. That info looks perfect to get me started - I appreciate your help.

I'll go get the Septmeber CTP build and doco now and work from there.

I'm looking forward to the release date - I am registered for the one day launch here in NZ - should be grand! :)|||Current books online still has very few things to say about secufity filters on report models. Does anyone know of any sites or articles that would greatly explain how to do this?|||

Hi Bob,

Your article on http://msdn2.microsoft.com/en-us/library/ms365343.aspx is very helpful, but when I use SQL Server Management Studio to set Model Item Security for differnt security filters, it seems "Permissions" property surpass "Model Item Security" property.

For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;

in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.

My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?

I posted my issue on http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1905805&SiteID=17, I attach my post here:

"

I read the following on article http://msdn2.microsoft.com/en-us/library/ms365343.aspx:

"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."

So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.

I can only get one result at a time but not both:

either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.

Anybody please please help me? Thank you!

"

Report server is using Custom Authentication.

Thank you for your help.

Temple1

Filling out missing data in subsequent records?

Hi

I receive several datafiles from another system that are more or less in a Excel pivot table like format.
That is the first row representing the current data is fully filled, while subsequent rows, representing historic data are left partly empty.
Current rows and historic rows have different identifiers, e.g. rectype=0 or 1
Filling out the missing data on the historic record should be simple, if only all current rows would be filled.
Some current rows aren't filled, so the stuff like the following doesn't work:
update t1
set t1.colA =
(
select top 1 t2.colA
from mytable AS t2
where t2.rowid <= t1.rowid
and t2.cola <> 0
order by t2.rowid desc
)
from mytable AS t1

Somehow I need to check for the rectype, so I don't fill out rows with data from a previous entity

Any suggestions before I revert to using a cursor?

And while we are at it: I am in for an easy way to do this for all (about 60) colums in one move?

Before you ask:
After filling everything out we process the file to arrive at a few handy fromto tables, so we can use the correct data about the entity's status at a particular point in time elsewhere

I am using MS SQL Server 2005, and solutions are allowd to use any specific trickery that MSSQL allows.

Many thanks for any constructive thoughts

Cheers

DrioWould this do the job? My changes are highlighted

UPDATE t1
SET t1.colA =
(
SELECT TOP 1 t2.colA
FROM MyTable AS t2
WHERE t2.rowid < t1.rowid
AND t2.colA <> 0
ORDER BY t2.rowid DESC
)
FROM mytable AS t1
WHERE rectype = 0

I havn't tested this code - it's only in my head (/on the screen) so don't use it on your live data ;)|||Thnak you georgev:
for the tagline (I won't do it again; a real eye-opener)
for the small correction in my code and for the direction.

It goes wrong where there are history record after the current reccord with no data.Obvioulsy they get filled from the previous current record that hadd data.

My interim solution
1. update all current records wh data with a dummy value
2. use the fill out query
We then have to check the marked records and see if we can find a pattern that allows us the handle them in code (otherwise someone has to go through them manually; only .25% of total)

Thanks for you swift response

Cheers

Drio

filling in missing values

I have a table that keeps track of click statistics for each one of my dealers.. I am creating graphs based on number of clicks that they received in a month, but if they didn't receive any in a certain month then it is left out..I know i have to do some outer join, but having trouble figuring exactly how..here is what i have:

select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1

this dealer had no clicks in april so this is what shows up:
joe blow 10-2004 567
joe blow 09-2004 269
joe blow 08-2004 66
joe blow 07-2004 30
joe blow 06-2004 8
joe blow 05-2004 5
joe blow 03-2004 9To have all the dealers needed:

select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
RIGHT OUTER join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1

To have all the dates, that is something else ...
SQL Server can only base itself on what it finds in the database ...
If there is notking in tblstats (s) for that moment and you get your months from that table it will not work ...
Create an extra table (or an temp table) that will store all months and create an outer join on that table

Filling in date records

Hi:
This should be pretty simple, but I'm struggling with it. I've got
a table with two columns, Site and dtm_Date. I start with one record
for site that represents the minimum date for that site, and I want to
fill up the table with rows for every date up to the day before the
query is run. So if I start with:
Site dtm_Date
SiteA 11/27/2005 12:00:00AM
SiteB 11/29/2005 12:00:00AM
and I run my SQL Code on Dec 3, 2005, I want to end up with a table
like:
Site dtm_Date
SiteA 11/27/2005 12:00:00AM
SiteA 11/28/2005 12:00:00AM
SiteA 11/29/2005 12:00:00AM
SiteA 11/30/2005 12:00:00AM
SiteA 12/1/2005 12:00:00AM
SiteA 12/2/2005 12:00:00AM
SiteB 11/29/2005 12:00:00AM
SiteB 11/30/2005 12:00:00AM
SiteB 12/1/2005 12:00:00AM
SiteB 12/2/2005 12:00:00AMThe easiest approach is to use a Calendar table. Search the archives of this
newsgroup & you'll find several methods to create one.
Once you have it, you come up with something along the lines of :
SELECT tbl.site, Calendar.dt
FROM Calendar
LEFT OUTER JOIN tbl
ON Calendar.dt BETWEEN tbl.dtm_Date AND CURRENT_TIMESTAMP ;
Anith|||If you plan to insert data into a table, you need to have a source
for the data. In this case, a convenient source is a calendar
table containing all possible SQL Server dates. You can create the
table this way:
create table AllDates (
d datetime primary key
)
go
declare @.mindate datetime set @.mindate = '19000101'
declare @.maxdate datetime set @.maxdate = '21001231'
insert into AllDates values (@.mindate)
while @.@.rowcount > 0 begin
insert into AllDates
select d + tabsize
from AllDates, (
select datediff(day,min(d),max(d))+1 as tabsize from AllDates
) M
where d <= @.maxdate - tabsize
end
go
Once that's done, you can
INSERT INTO yourTable
SELECT yourTable.Site, AllDates.d
FROM yourTable, AllDates
WHERE AllDates.d > yourTable.dtm_Date
AND AllDates.d <= getdate() - 1
[not tested]
This assumes there is exactly one row per Site in your table,
so it will fail if you run it twice, for example.
Steve Kass
Drew University
blairjee@.gmail.com wrote:

> Hi:
> This should be pretty simple, but I'm struggling with it. I've got
> a table with two columns, Site and dtm_Date. I start with one record
> for site that represents the minimum date for that site, and I want to
> fill up the table with rows for every date up to the day before the
> query is run. So if I start with:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> and I run my SQL Code on Dec 3, 2005, I want to end up with a table
> like:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteA 11/28/2005 12:00:00AM
> SiteA 11/29/2005 12:00:00AM
> SiteA 11/30/2005 12:00:00AM
> SiteA 12/1/2005 12:00:00AM
> SiteA 12/2/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> SiteB 11/30/2005 12:00:00AM
> SiteB 12/1/2005 12:00:00AM
> SiteB 12/2/2005 12:00:00AM
>|||I think the Calendar idea is probably a better idea, but you can accomplish
your goals with a cursor:
go
set nocount on
DECLARE @.markdate datetime ,@.site varchar(10)
DECLARE sites_cursor CURSOR
FOR
Select site from sitedate order by site desc
OPEN SITES_cursor
FETCH NEXT FROM SITES_cursor INTO @.site
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.markdate = (select min(dtm_date) from sitedate)
WHILE @.markdate < getdate()
BEGIN
Insert into sitedate values(@.site,@.markdate)
select @.markdate = dateadd(d,1,@.markdate)
END
FETCH NEXT FROM SITES_cursor into @.site
END
Close SITES_cursor
DEALLOCATE SITES_cursor
<blairjee@.gmail.com> wrote in message
news:1133804570.141750.248940@.g49g2000cwa.googlegroups.com...
> Hi:
> This should be pretty simple, but I'm struggling with it. I've got
> a table with two columns, Site and dtm_Date. I start with one record
> for site that represents the minimum date for that site, and I want to
> fill up the table with rows for every date up to the day before the
> query is run. So if I start with:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> and I run my SQL Code on Dec 3, 2005, I want to end up with a table
> like:
> Site dtm_Date
> SiteA 11/27/2005 12:00:00AM
> SiteA 11/28/2005 12:00:00AM
> SiteA 11/29/2005 12:00:00AM
> SiteA 11/30/2005 12:00:00AM
> SiteA 12/1/2005 12:00:00AM
> SiteA 12/2/2005 12:00:00AM
> SiteB 11/29/2005 12:00:00AM
> SiteB 11/30/2005 12:00:00AM
> SiteB 12/1/2005 12:00:00AM
> SiteB 12/2/2005 12:00:00AM
>

Filling a DDL from a SQL DB?

Hello All
I am wanting to fill a drop down list in ASP.NET using C# from a SQL database table using a stored procedure. I have my Sproc. But using ASP.NET C# I have no idea how to do this. Can someone give me a good example, and if not too much trouble, place comments in the code, and give an explanation. I am just learning ASP.NET after moving from Classic. Things are alot different.

Thank You in advnace for all your help

Andrewhttp://aspauthors.com/aspnetbyexample/ch06/

Monday, March 26, 2012

FillFactor considerations

I have a table with about 200 million rows of data. I add a couple million rows of data each week to the table in a single load process. The table is used for reporting purposes only and there are never (not intentionally at least) any updates or deletes to the table. The data is always being added to the "end" of the table with the new AsOfDate being the main factor in the clustered index.

My question is this: Since I'm not "inserting" rows that would split pages, should I have my FILLFACTOR for the table set to 100, or am I missing something? I obviously want to save physical hard drive space, but I also don't want to slow down the import process.

BTW, I'm using SQL2000

If you are certain that your data loads is 'always' putting the new data at the end of the clustered index, you may have a good case for NOT leaving empty space on the data pages -and a FILLFACTOR of 100 would be in order.

FILLFACTOR 100 on Empty Table

Hi,
I have a nightly process that starts with empty tables to create tables that
will be read-only durring the day. At the end of the process I would like
to have the indexes compacted to FILLFACTOR = 100. My process creates the
tables in one code module and finializes their processing in another code
module. I would prefer to keep the CREATE INDEX statements with the CREATE
TABLE statements (I know that my tables will populate faster without the
nonclustered indexes, but I don't care if it takes 5 minutes instead of 3;
reliable maintenance is a more important factor).
I'm thinking that if a table is empty that it shouldn't matter whether I
create the indexes with a FILLFACTOR = 1 or = 100; I'll just have one page
per index. And, once I start adding data, the FILLFACTOR is not a factor in
index maintenance. (BOL says FILLFACTOR is only for index creation, I'm
assuming that index pages will always be split 50/50 as records are
inserted.)
However, once my processing is done, and if I have used FILLFACTOR = 100,
then I can use DBCC DBREINDEX 'TableName' and all indexes will be compressed
to 100% FILL. This way I don't have to add every index in my early code
module to the final code module, thereby making maintenance more reliable.
So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
factor for insert performance when starting with a completely empty table?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:uwbh8yucGHA.536@.TK2MSFTNGP02.phx.gbl...
> So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
> factor for insert performance when starting with a completely empty table?
Yes; every observation in your post is correct.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

FILLFACTOR 100 on Empty Table

Hi,
I have a nightly process that starts with empty tables to create tables that
will be read-only durring the day. At the end of the process I would like
to have the indexes compacted to FILLFACTOR = 100. My process creates the
tables in one code module and finializes their processing in another code
module. I would prefer to keep the CREATE INDEX statements with the CREATE
TABLE statements (I know that my tables will populate faster without the
nonclustered indexes, but I don't care if it takes 5 minutes instead of 3;
reliable maintenance is a more important factor).
I'm thinking that if a table is empty that it shouldn't matter whether I
create the indexes with a FILLFACTOR = 1 or = 100; I'll just have one page
per index. And, once I start adding data, the FILLFACTOR is not a factor in
index maintenance. (BOL says FILLFACTOR is only for index creation, I'm
assuming that index pages will always be split 50/50 as records are
inserted.)
However, once my processing is done, and if I have used FILLFACTOR = 100,
then I can use DBCC DBREINDEX 'TableName' and all indexes will be compressed
to 100% FILL. This way I don't have to add every index in my early code
module to the final code module, thereby making maintenance more reliable.
So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
factor for insert performance when starting with a completely empty table?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:uwbh8yucGHA.536@.TK2MSFTNGP02.phx.gbl...
> So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
> factor for insert performance when starting with a completely empty table?
Yes; every observation in your post is correct.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

Fill-factor 100 for a CLUSTERED UNIQUE IDENTITY field

hi,
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.
Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.
|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.

Fill-factor 100 for a CLUSTERED UNIQUE IDENTITY field

hi,
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx
.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx
.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.sql

Fill-factor 100 for a CLUSTERED UNIQUE IDENTITY field

hi,
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.|||hi,
thanks for replying me.
increasing (decreasing too?) the WIDTH of a row because of
an update is a new information for me, and i would also
say it is quite shocking for me.
i allways thought table structure is a fixed-width, so the
space for all of the fields is allocated in the same time
and PLACE in advance for each inserted record.
or maybe do you mean that the index may be compuond by
varchar fields so changing the values may change the
lenght of the compound index-value, for example an index-
value coumpound by 2 fields may be changed from "X"+"A"
to "X"+"ABCDEF"
(so if, for example, the table contains integer fields
only, is Fill-factor 100 still be the optimal option even
when updates are concerned)
i guess it's a long story to explain why a clustered index
is actually needs to contain ALL fileds ...?
i probablly missing something (or a lot of things)...
thanks again.
edo.
>--Original Message--
>Not quite true.
>You're on the right track as far as inserts are
concerned, but updates are
>another matter.
>Because clustered indexes contain ALL columns, any
updates that increase the
>width of the row might cause a page split.
>Therefore, whether 100% is optimal or not depends on
whether there are any
>updates to the table.
>HTH
>Regards,
>Greg Linwood
>SQL Server MVP
>"edo" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
>> hi,
>> i have a table with IDENTITY field.
>> i set a CLUSTERED UNIQUE index on this field.
>> i wonder whether is it right to set this index's Fill-
>> factor to 100?
>> i though that if the values of this field are always
auto-
>> incremented so there would never be a need to split
places
>> between records, so there is no need to save space for
new
>> records(or updated records) that might need to be placed
>> between any other prior inserted records.
>> is it true?
>> is it the optimal choise for this case?
>> thanks,
>> edo.
>
>.
>|||Hi Edo,
On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
>hi,
>thanks for replying me.
>increasing (decreasing too?) the WIDTH of a row because of
>an update is a new information for me, and i would also
>say it is quite shocking for me.
>i allways thought table structure is a fixed-width, so the
>space for all of the fields is allocated in the same time
>and PLACE in advance for each inserted record.
This is only true if the row contains no varying length columns. Each
table that holds at least one varchar, nvarchar or varbinary column has
rows with varying length.
(snip)
>i guess it's a long story to explain why a clustered index
>is actually needs to contain ALL fileds ...?
Not at all. The clustered index determines the order in which rows are
stored in the data file. Suppose you have a clustered index on an integer
column, there are rows with values 1 and 3 for that column and you then
insert a row with value 2. In that case, the database will store the
entire row between the rows vor key value 1 and 3, not only the key value.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo's answered most of your questions already here but I'll just answer
that qn you put about whether 100% fillfactor is optimal if all columns are
fixed width such as integer. I'd say that that answer to that is yes - if
the columns are fixed width, then the underlying storage requirements will
never grow for a given row, so there should not be any requirement to split
storage pages. Even if there is some obscure cause of page splits in this
scenario, I'd suggest that would be rare & therefore the 100% fillfactor
would still be optimal.
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aeatc0poos36e86c2tcthsforql0rbs5lk@.4ax.com...
> Hi Edo,
> On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
> >hi,
> >
> >thanks for replying me.
> >
> >increasing (decreasing too?) the WIDTH of a row because of
> >an update is a new information for me, and i would also
> >say it is quite shocking for me.
> >
> >i allways thought table structure is a fixed-width, so the
> >space for all of the fields is allocated in the same time
> >and PLACE in advance for each inserted record.
> This is only true if the row contains no varying length columns. Each
> table that holds at least one varchar, nvarchar or varbinary column has
> rows with varying length.
>
> (snip)
> >i guess it's a long story to explain why a clustered index
> >is actually needs to contain ALL fileds ...?
> Not at all. The clustered index determines the order in which rows are
> stored in the data file. Suppose you have a clustered index on an integer
> column, there are rows with values 1 and 3 for that column and you then
> insert a row with value 2. In that case, the database will store the
> entire row between the rows vor key value 1 and 3, not only the key value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.

Fillfactor

I have a very simple insert statement into a table containing about a
million rows.
It runs a fraction of a second for about 1000 times and then takes about 5
seconds, and then again fraction of a second for about 1000 inserts. There
are 3 indexes on the table with fillfactor 70.
Am i right by guessing that at the time when insert takes 5 seconds the page
splits? Should i decrese fillfactor to 50? Or could it be something else?
Thanks.
Perhaps there are other possible sources, but it would more likely be your
log files or data files growing. I suppose the quick and dirty check is to
simply monitor both files before and after to see if growth occured.
Autogrowth is evil in my mind.
A single page split is taking a 8K allocated space and splitting it in two
locations. While you don't want this to happen a ton, this is not a task
that would take 5 full seconds, or at least the stall would not be after
record 997 was inserted. I suppose if the records you are inserting are all
being inserted into the SAME part(page) of the index, this could be
problematic.
If the index is related to your performance issues, you could remove the
index prior to the insert, insert, and then rebuild the indexes? This is an
expensive operation too though.
Mark
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||1) you can monitor page splits using a perf mon counter. I doubt very much
this is the issue
2) the issue could be log flushes, checkpoints, blocking, something random
hitting the server hard. examine waiting tasks (not sure which version you
are on so can't specify exactly how to do that) when the delay occurs. also
run sp_who2 active to check for blocking.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||I agree with the others that it is probably not related to the fill factor.
I would guess blocking first, disk queues next (checkpoints etc.) and maybe
even file auto growths. Make sure there is plenty of free space in the data
and log files.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||I see that i have a lot of checkpoints by running a trace.
This might be my problem
How do i minimize checkpoints?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>I agree with the others that it is probably not related to the fill factor.
>I would guess blocking first, disk queues next (checkpoints etc.) and maybe
>even file auto growths. Make sure there is plenty of free space in the data
>and log files.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>
|||Checkpoints are there for a reason which is to limit recovery times in the
event of a crash. There is a setting called the recovery interval that can
affect when checkpoints happen but that is not the solution. If checkpoints
hinder the activity that much you do not have proper disk configurations to
handle the load. It sounds like you probably have your tran log files on the
same drive array as the data files. To deal with lots of writes it is
imperative you separate the logs from the data files onto different physical
(not just logical) arrays. Also consider adding more write cache and check
the read / write ratio of the disk controller. If it is not 100% write back
then change it and you will most likely see improvements with checkpoints.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:uo901MBnIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I see that i have a lot of checkpoints by running a trace.
> This might be my problem
> How do i minimize checkpoints?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>