Can I create a partition in table based on column value in sql server?
I'm working on a table in SQL Server that has let's say three columns:
employeeid (PK) | customer id | employee name.
I need employee id based on employee name and customer id. Let's say, my table has 100000 records and just 5 customers. So, if I have partition based on Customer ID my retrieval process would be faster I believe. Again, it is not fixed that there will always be these 5 customers, it may increase or decrease, but they will always be finite and limited (let's say won't exceed 20). Is it possible to have partition in my table based on customer id? Or is there any other way to improve or fasten lookup process?
sql
sql-server query-optimization partitioning asked Nov 16 '18 at 8:40
Hardik PatelHardik Patel
1415
|
show 3 more comments
I'm working on a table in SQL Server that has let's say three columns:
employeeid (PK) | customer id | employee name.
I need employee id based on employee name and customer id. Let's say, my table has 100000 records and just 5 customers. So, if I have partition based on Customer ID my retrieval process would be faster I believe. Again, it is not fixed that there will always be these 5 customers, it may increase or decrease, but they will always be finite and limited (let's say won't exceed 20). Is it possible to have partition in my table based on customer id? Or is there any other way to improve or fasten lookup process?
sql
sql-server query-optimization partitioning asked Nov 16 '18 at 8:40
Hardik PatelHardik Patel
1415
1
What types of queries you want to optimize? Getting all rows for particular customer?
– gotqn
Nov 16 '18 at 9:37
My query would be to get employee id based on employee name and customer id. It could be one record or more. But I believe it will not exceed 10 records. In 80% cases, it would be single record. e.g. select employee_id from table where customer_id=111 and employee_name='xyz'
– Hardik Patel
Nov 19 '18 at 5:56
So, it will beSELECT MAX([customer id]) FROM ... WHERE [employee name] = 'Hardik'?
– gotqn
Nov 19 '18 at 5:59
It will be select employee_id from table where customer_id=111 and employee_name='Hardik'. It is more like reverse lookup.
– Hardik Patel
Nov 19 '18 at 6:15
Is it possible an employee_id to have different employee_name for different rows? I think it must be same, right?
– gotqn
Nov 19 '18 at 6:31
|
show 3 more comments
I'm working on a table in SQL Server that has let's say three columns:
employeeid (PK) | customer id | employee name.
I need employee id based on employee name and customer id. Let's say, my table has 100000 records and just 5 customers. So, if I have partition based on Customer ID my retrieval process would be faster I believe. Again, it is not fixed that there will always be these 5 customers, it may increase or decrease, but they will always be finite and limited (let's say won't exceed 20). Is it possible to have partition in my table based on customer id? Or is there any other way to improve or fasten lookup process?
sql
sql-server query-optimization partitioning asked Nov 16 '18 at 8:40
Hardik PatelHardik Patel
1415
I'm working on a table in SQL Server that has let's say three columns:
employeeid (PK) | customer id | employee name.
I need employee id based on employee name and customer id. Let's say, my table has 100000 records and just 5 customers. So, if I have partition based on Customer ID my retrieval process would be faster I believe. Again, it is not fixed that there will always be these 5 customers, it may increase or decrease, but they will always be finite and limited (let's say won't exceed 20). Is it possible to have partition in my table based on customer id? Or is there any other way to improve or fasten lookup process?
sql
sql-server query-optimization partitioning sql
sql-server query-optimization partitioning asked Nov 16 '18 at 8:40
Hardik PatelHardik Patel
1415
asked Nov 16 '18 at 8:40
Hardik PatelHardik Patel
1415
edited Nov 19 '18 at 6:15
Hardik Patel
asked Nov 16 '18 at 8:40
Hardik PatelHardik Patel
1415
asked Nov 16 '18 at 8:40
Hardik PatelHardik Patel
1415
asked Nov 16 '18 at 8:40
Hardik PatelHardik Patel
1415
1415
1
What types of queries you want to optimize? Getting all rows for particular customer?
– gotqn
Nov 16 '18 at 9:37
My query would be to get employee id based on employee name and customer id. It could be one record or more. But I believe it will not exceed 10 records. In 80% cases, it would be single record. e.g. select employee_id from table where customer_id=111 and employee_name='xyz'
– Hardik Patel
Nov 19 '18 at 5:56
So, it will beSELECT MAX([customer id]) FROM ... WHERE [employee name] = 'Hardik'?
– gotqn
Nov 19 '18 at 5:59
It will be select employee_id from table where customer_id=111 and employee_name='Hardik'. It is more like reverse lookup.
– Hardik Patel
Nov 19 '18 at 6:15
Is it possible an employee_id to have different employee_name for different rows? I think it must be same, right?
– gotqn
Nov 19 '18 at 6:31
|
show 3 more comments
1
What types of queries you want to optimize? Getting all rows for particular customer?
– gotqn
Nov 16 '18 at 9:37
My query would be to get employee id based on employee name and customer id. It could be one record or more. But I believe it will not exceed 10 records. In 80% cases, it would be single record. e.g. select employee_id from table where customer_id=111 and employee_name='xyz'
– Hardik Patel
Nov 19 '18 at 5:56
So, it will beSELECT MAX([customer id]) FROM ... WHERE [employee name] = 'Hardik'?
– gotqn
Nov 19 '18 at 5:59
It will be select employee_id from table where customer_id=111 and employee_name='Hardik'. It is more like reverse lookup.
– Hardik Patel
Nov 19 '18 at 6:15
Is it possible an employee_id to have different employee_name for different rows? I think it must be same, right?
– gotqn
Nov 19 '18 at 6:31
1
1
What types of queries you want to optimize? Getting all rows for particular customer?
– gotqn
Nov 16 '18 at 9:37
What types of queries you want to optimize? Getting all rows for particular customer?
– gotqn
Nov 16 '18 at 9:37
My query would be to get employee id based on employee name and customer id. It could be one record or more. But I believe it will not exceed 10 records. In 80% cases, it would be single record. e.g. select employee_id from table where customer_id=111 and employee_name='xyz'
– Hardik Patel
Nov 19 '18 at 5:56
My query would be to get employee id based on employee name and customer id. It could be one record or more. But I believe it will not exceed 10 records. In 80% cases, it would be single record. e.g. select employee_id from table where customer_id=111 and employee_name='xyz'
– Hardik Patel
Nov 19 '18 at 5:56
So, it will be
SELECT MAX([customer id]) FROM ... WHERE [employee name] = 'Hardik'?– gotqn
Nov 19 '18 at 5:59
So, it will be
SELECT MAX([customer id]) FROM ... WHERE [employee name] = 'Hardik'?– gotqn
Nov 19 '18 at 5:59
It will be select employee_id from table where customer_id=111 and employee_name='Hardik'. It is more like reverse lookup.
– Hardik Patel
Nov 19 '18 at 6:15
It will be select employee_id from table where customer_id=111 and employee_name='Hardik'. It is more like reverse lookup.
– Hardik Patel
Nov 19 '18 at 6:15
Is it possible an employee_id to have different employee_name for different rows? I think it must be same, right?
– gotqn
Nov 19 '18 at 6:31
Is it possible an employee_id to have different employee_name for different rows? I think it must be same, right?
– gotqn
Nov 19 '18 at 6:31
|
show 3 more comments
1 Answer
1
active
oldest
votes
First, partitioning is not a performance feature. Yes, you can partition your table, but it is too small (with 100'000 records doesn't worth it) and partitioning needs clear boundaries. Let's say your customer IDs are 1, 2, 3, 4 and 5. Then next customer (#6) comes in and it will reside in the same partition as customer #5. What you actually need is non-clustered index on CustomerID, and probably with Employee Name as included column.
answered Nov 16 '18 at 11:14
Andrey NikolovAndrey Nikolov
3,5981621
A unique clustered index on CustomerID and EmployeeID could also be appropriate depending on the queries. Like partitioning, only the rows for a given customer will be touched as long as CustomerID is specified in WHERE/JOIN clauses.
– Dan Guzman
Nov 16 '18 at 11:42
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53334191%2fcan-i-create-a-partition-in-table-based-on-column-value-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
First, partitioning is not a performance feature. Yes, you can partition your table, but it is too small (with 100'000 records doesn't worth it) and partitioning needs clear boundaries. Let's say your customer IDs are 1, 2, 3, 4 and 5. Then next customer (#6) comes in and it will reside in the same partition as customer #5. What you actually need is non-clustered index on CustomerID, and probably with Employee Name as included column.
answered Nov 16 '18 at 11:14
Andrey NikolovAndrey Nikolov
3,5981621
A unique clustered index on CustomerID and EmployeeID could also be appropriate depending on the queries. Like partitioning, only the rows for a given customer will be touched as long as CustomerID is specified in WHERE/JOIN clauses.
– Dan Guzman
Nov 16 '18 at 11:42
add a comment |
First, partitioning is not a performance feature. Yes, you can partition your table, but it is too small (with 100'000 records doesn't worth it) and partitioning needs clear boundaries. Let's say your customer IDs are 1, 2, 3, 4 and 5. Then next customer (#6) comes in and it will reside in the same partition as customer #5. What you actually need is non-clustered index on CustomerID, and probably with Employee Name as included column.
answered Nov 16 '18 at 11:14
Andrey NikolovAndrey Nikolov
3,5981621
A unique clustered index on CustomerID and EmployeeID could also be appropriate depending on the queries. Like partitioning, only the rows for a given customer will be touched as long as CustomerID is specified in WHERE/JOIN clauses.
– Dan Guzman
Nov 16 '18 at 11:42
add a comment |
First, partitioning is not a performance feature. Yes, you can partition your table, but it is too small (with 100'000 records doesn't worth it) and partitioning needs clear boundaries. Let's say your customer IDs are 1, 2, 3, 4 and 5. Then next customer (#6) comes in and it will reside in the same partition as customer #5. What you actually need is non-clustered index on CustomerID, and probably with Employee Name as included column.
answered Nov 16 '18 at 11:14
Andrey NikolovAndrey Nikolov
3,5981621
First, partitioning is not a performance feature. Yes, you can partition your table, but it is too small (with 100'000 records doesn't worth it) and partitioning needs clear boundaries. Let's say your customer IDs are 1, 2, 3, 4 and 5. Then next customer (#6) comes in and it will reside in the same partition as customer #5. What you actually need is non-clustered index on CustomerID, and probably with Employee Name as included column.
answered Nov 16 '18 at 11:14
Andrey NikolovAndrey Nikolov
3,5981621
answered Nov 16 '18 at 11:14
Andrey NikolovAndrey Nikolov
3,5981621
answered Nov 16 '18 at 11:14
Andrey NikolovAndrey Nikolov
3,5981621
answered Nov 16 '18 at 11:14
Andrey NikolovAndrey Nikolov
3,5981621
3,5981621
A unique clustered index on CustomerID and EmployeeID could also be appropriate depending on the queries. Like partitioning, only the rows for a given customer will be touched as long as CustomerID is specified in WHERE/JOIN clauses.
– Dan Guzman
Nov 16 '18 at 11:42
add a comment |
A unique clustered index on CustomerID and EmployeeID could also be appropriate depending on the queries. Like partitioning, only the rows for a given customer will be touched as long as CustomerID is specified in WHERE/JOIN clauses.
– Dan Guzman
Nov 16 '18 at 11:42
A unique clustered index on CustomerID and EmployeeID could also be appropriate depending on the queries. Like partitioning, only the rows for a given customer will be touched as long as CustomerID is specified in WHERE/JOIN clauses.
– Dan Guzman
Nov 16 '18 at 11:42
A unique clustered index on CustomerID and EmployeeID could also be appropriate depending on the queries. Like partitioning, only the rows for a given customer will be touched as long as CustomerID is specified in WHERE/JOIN clauses.
– Dan Guzman
Nov 16 '18 at 11:42
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53334191%2fcan-i-create-a-partition-in-table-based-on-column-value-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
What types of queries you want to optimize? Getting all rows for particular customer?
– gotqn
Nov 16 '18 at 9:37
My query would be to get employee id based on employee name and customer id. It could be one record or more. But I believe it will not exceed 10 records. In 80% cases, it would be single record. e.g. select employee_id from table where customer_id=111 and employee_name='xyz'
– Hardik Patel
Nov 19 '18 at 5:56
So, it will be
SELECT MAX([customer id]) FROM ... WHERE [employee name] = 'Hardik'?– gotqn
Nov 19 '18 at 5:59
It will be select employee_id from table where customer_id=111 and employee_name='Hardik'. It is more like reverse lookup.
– Hardik Patel
Nov 19 '18 at 6:15
Is it possible an employee_id to have different employee_name for different rows? I think it must be same, right?
– gotqn
Nov 19 '18 at 6:31