Database

Richard Andrew x6431-May-17 13:05

31-May-17 13:05

Thanks for your response. I only chose 150,000 as a theoretical number to make my point.

There would never be that many rows, but in our main LOB application, some developers in the past have loaded up around 10,000 rows, making the form rather slow to open!

The difficult we do right away...
...the impossible takes slightly longer.

Mycroft Holmes31-May-17 13:01

Mycroft Holmes

31-May-17 13:01

I agree with Richard, 150k records to the UI is only valid if you are doing a data dump and there are more efficient way to do that.

Never underestimate the power of human stupidity
RAH

Richard Andrew x6431-May-17 13:07

31-May-17 13:07

Yes, I agree with you and Richard. I'm working on creating a generic way to load the data only on demand. It must be a business object agnostic way.

Thanks. Smile | :)

The difficult we do right away...
...the impossible takes slightly longer.

Mycroft Holmes31-May-17 13:21

Mycroft Holmes

31-May-17 13:21

If you are using SQL Server I think there is a method of loading a "page" of data which my be valid. Obviously I have never used it, I limit UI lists to a max of 2k. After that filters become mandatory.

Never underestimate the power of human stupidity
RAH

Richard Andrew x6431-May-17 13:28

31-May-17 13:28

We have one form that displays Purchase Orders, and the data goes back to 2009. There are thousands upon thousands of records.

How can you limit how many rows you load, and yet still allow the user to type in a column filter string in order to find any of the PO's from any point in time?

The difficult we do right away...
...the impossible takes slightly longer.

Eddy Vluggen31-May-17 13:21

Eddy Vluggen

31-May-17 13:21

Richard Andrew x64 wrote:
IOW, I don't want my form to only appear on screen after all 150,000 rows have been pulled from SQL Server.

Then how?

You mentioned a "form", so I'd assume WinForms with a DataGridView. In which case you don't use a datatable, but a list. You add to the list from a different thread, in batches of 50. Virtualize the DataGridView. Start the actions as soon as the form is displayed.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Richard Andrew x6431-May-17 13:36

31-May-17 13:36

Eddy Vluggen wrote:
You add to the list from a different thread, in batches of 50

Thank you. That's one of the avenues I am exploring. I have to find some way to make our application more responsive while still enabling access to thousands of rows.

The difficult we do right away...
...the impossible takes slightly longer.

Eddy Vluggen1-Jun-17 0:35

Eddy Vluggen

1-Jun-17 0:35

Loading from a background-thread sounds ideal then; the IDataReader will return an array of objects, and stuffing them into a list is fast. List<object[]> Smile | :)

Walkthrough: Implementing Virtual Mode in the Windows Forms DataGridView Control[^] - fastest way to display a huge amount of data.

..and searching the list of object-arrays could also be done on a background-thread, simply by going through the list Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Jörgen Andersson31-May-17 22:18

Jörgen Andersson

31-May-17 22:18

ADO.Net support paging the data into the dataset if you use an Adapter.
Paging Through a Query Result[^]
DbDataAdapter.Fill Method (DataSet, Int32, Int32, String) (System.Data.Common)[^]

Wrong is evil and must be defeated. - Jeff Ello

Richard Andrew x6416-Jun-17 11:20

SQL server: How to get backup file information using SMO

16-Jun-17 11:20

Thank you, this looks very promising. Smile | :)

The difficult we do right away...
...the impossible takes slightly longer.

w1424326-May-17 17:52

Re: SQL server: How to get backup file information using SMO

26-May-17 17:52

SQL server 2014/2016, SMO.

Using SMO to backup a databse to 'D:\SQLBK\Mon\dbc.bak' on server:

public int BackupDB(string serverName, string dbName, string backupFolder)
{
Server oServer = new Server(new ServerConnection(ServerName));
Backup oBackup = new Backup();

oBackup.Action = BackupActionType.Database;
oBackup.Database = dbName;
oBackup.Initialize = false;
oBackup.Incremental = false;
oBackup.LogTruncation = BackupTruncateLogType.Truncate;

string sBackupFilename = Path.Combine(backupFolder, dbName + ".bak");
oBackup.Devices.AddDevice(sBackupFilename, DeviceType.File);

oBackup.SqlBackup(oServer);

return 0;
}

How to get original database information from backup file 'D:\SQLBK\Mon\dbc.bak' through SMO? e.g. database name.

Eddy Vluggen2-Jun-17 1:44

Eddy Vluggen

2-Jun-17 1:44

w14243 wrote:
How to get original database information from backup file 'D:\SQLBK\Mon\dbc.bak' through SMO? e.g. database name.

The database-name is not part of the backup. You backup the data, not meta-data on the file.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

How to create SQL Server backup folder

w1424326-May-17 17:44

SSRS - dynamic columns depending on client parameter

26-May-17 17:44

SQL Server 2014/2016, using SMO.

Want backup database 'dbName' of server 'serverName' to server folder 'backupFolder'.
1.Backup remote database, not backup local database.
2.The 'backupFolder' is on remote server, e.g. "D:\SQLBK\Mon".

public int BackupDB(string serverName, string dbName, string backupFolder)
{
Server oServer = new Server(new ServerConnection(ServerName));
Backup oBackup = new Backup();

oBackup.Action = BackupActionType.Database;
oBackup.Database = dbName;
oBackup.Initialize = false;
oBackup.Incremental = false;
oBackup.LogTruncation = BackupTruncateLogType.Truncate;

string sBackupFilename = Path.Combine(backupFolder, dbName + ".bak");
oBackup.Devices.AddDevice(sBackupFilename, DeviceType.File);

oBackup.SqlBackup(oServer);

return 0;
}

The 'backupFolder' is folder on 'serverName', not local folder.

1.If 'backupFolder' is not exist on 'serverName', the 'SqlBackup' command will cause exception on 'folder not exist'.

2.If use 'Directory.CreateDirectory(backupFolder)', then local folder will be created, not remote folder.

So I want:

1. The 'SqlBackup' command can auto create specified backup folder. How to do it?

2. If above can't be done, then how to create 'backupFolder' on 'serverName' by SMO or other method?

Danpeking24-May-17 23:33

Danpeking

24-May-17 23:33

Hi, I have a report which will produce different numbers of columns depending on the client that is selected. There may be a dozen clients and the columns might be named differently so it's all completely dynamic. I'm not quite sure how to arrange this in an SSRS report or even if it is possible. Can anybody please shed light on how I can do this? Thanks

modified 25-May-17 6:21am.

Sql server: create table with column that support unique and multiple nulls

w1424323-May-17 20:03

Re: Sql server: create table with column that support unique and multiple nulls

23-May-17 20:03

SQL server 2014/16.

Want create a table. The table has some columns that need support:
1.Can contain multiple nulls.
2.If not null, then must be unique.
3.The columns has no relationship.
4.Not entire row is unique. Each column is unique.

How to make the CREATE TABLE command?

It seems SQL server can only support one null when using UNIQUE.

Below example is what I wanted:
CREATE TABLE UniqueTest (
col1 int,
col2 int unique null
);

INSERT INTO UniqueTest VALUES (1, 1);
-- SUCCESS

INSERT INTO UniqueTest VALUES (2, 2);
-- SUCCESS

INSERT INTO UniqueTest VALUES (3, 2);
-- FAIL

INSERT INTO UniqueTest VALUES (4, NULL);
-- SUCCESS

INSERT INTO UniqueTest VALUES (5, NULL);
-- SUCCESS

I had searched internet, lots of articles are discussed in old SQL server version, e.g. 2005/2008.
I think the SQL server 2014/2016 has a new CREATE TABLE option to meet my requirement, but I don't know.

The solution from internet would be:
create index on specific column.

But:
1.My table will has 10~30 columns that need unique and nulls. If each column create an index, then will be 10~30 indexes in a table. Is it possible?
2.Lots indexes will lower efficiency. Is it?

Maciej Los23-May-17 20:27

Maciej Los

23-May-17 20:27

An answer has been posted here: Sql server: create table with column that support unique and multiple nulls

Re: Sql server: create table with column that support unique and multiple nulls

w1424323-May-17 20:45

Re: Sql server: create table with column that support unique and multiple nulls

23-May-17 20:45

That question can't be replied, so I post it here.

From the answer, it is unique on 'col1+col2'. But my requirement is that col1 and col2 have their self unique constraint.

Below example is what I wanted:

CREATE TABLE UniqueTest (
col1 int,
col2 int unique null
);

INSERT INTO UniqueTest VALUES (1, 1);
-- SUCCESS

INSERT INTO UniqueTest VALUES (2, 2);
-- SUCCESS

INSERT INTO UniqueTest VALUES (3, 2);
-- FAIL

INSERT INTO UniqueTest VALUES (4, NULL);
-- SUCCESS

INSERT INTO UniqueTest VALUES (5, NULL);
-- SUCCESS

Richard Deeming24-May-17 8:22

Richard Deeming

24-May-17 8:22

Filtered Indexes and IS NOT NULL - Brent Ozar Unlimited®[^]

SQL

CREATE TABLE UniqueTest (
   col1 int,
   col2 int null
);

CREATE UNIQUE INDEX UX_UniqueTest_col2
ON UniqueTest (col2)
WHERE col2 Is Not Null;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Sql server: create table with column that support unique and multiple nulls

w1424324-May-17 17:32

Re: Sql server: create table with column that support unique and multiple nulls

24-May-17 17:32

Below is my concern:

1.My table will has 10~30 columns that need unique and nulls. If each column create an index, then will be 10~30 indexes in a table. Is it possible?
2.Lots indexes will lower efficiency. Is it?

Richard Deeming25-May-17 0:51

Richard Deeming

25-May-17 0:51

1. Yes.

2. Lots of indexes will potentially decrease the performance of inserts and updates, but no more so that any other method of enforcing your requirements, and with less chance of making a mistake.

A table with 30 columns might be a candidate for further normalization. But you would need to examine the data to determine that, and test what effect that might have on the performance and complexity of your queries.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Sql server: create table with column that support unique and multiple nulls

ZurdoDev24-May-17 3:15

ZurdoDev

24-May-17 3:15

Just use a trigger and move on. You have custom logic that SQL does not support.

There are two kinds of people in the world: those who can extrapolate from incomplete data.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: Sql server: create table with column that support unique and multiple nulls

Richard Deeming24-May-17 8:19

Richard Deeming

24-May-17 8:19

RyanDev wrote:
You have custom logic that SQL does not support.

Are you sure about that? Poke tongue | ;-P

Filtered Indexes and IS NOT NULL - Brent Ozar Unlimited®[^]:

A filtered unique index can get you around the problem, like this:
CREATE UNIQUE INDEX uq_UsersAllowNulls_DisplayName on dbo.UsersAllowNulls ( DisplayName )
    WHERE DisplayName IS NOT NULL;
GO

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Sql server: create table with column that support unique and multiple nulls

ZurdoDev24-May-17 8:24

ZurdoDev

24-May-17 8:24

Cool.

There are two kinds of people in the world: those who can extrapolate from incomplete data.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: Sql server: create table with column that support unique and multiple nulls

w1424324-May-17 17:45