Database Design - Creating Multiple databases to avoid the headache of limit on table size I need to design database to store information contained in millions of log files generated by devices on trial on network.

My first approach was storing all the information in tables contained in a single database. But this approach seems to fail as the data to be stored is too large. So, I thought of creating separate database for each device and storing each device log files in separate database. My question is if this approach can spare me of the headache involved with table size limit. Below are given my platform specification

Operating System - Windows 7
File System - NTFS
Storage Engine - InnoDB
Table Type - InnoDB

I want to build my database design in a way so that I need not to apply shrink of tables procedures in future

In my answer to your previous question, I mentioned how the size of an individual table is 2TB when you use innodb_file_per_table. There may be a way to surpass the table limit by doing just the the opposite, leaving innodb_file_per_table disabled.
With innodb_file_per_table disabled, there are three ways you can go about this:
TECHNIQUE #1 : Use multiple system tablespaces in ext3
You could chain InnoDB tablespaces 2TB at a time. Start off with this
innodb_data_file_path=ibdata1:10M:autoextend:max:2048G

When ibdata1 is about to hit 2TB, add another ibdata file
innodb_data_file_path=ibdata1:2048G:ibdata2:10M:autoextend:max:2048G

When ibdata2 is about to hit 2TB, add another ibdata file
innodb_data_file_path=ibdata:2048G:ibdata2:2048G:ibdata3:10M:autoextend:max:2048G

and so on...
UPDATE 2013-06-01 20:45 EDT
I just discovered something with reference to setting the max filesize for a system tablespace. Here is the situation:
Client has the following InnoDB files
[root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  2 00:15 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  2 00:15 ibdata2

and has this setting
innodb_data_file_path=ibdata1:346M;ibdata2:500M:autoextend:max:10240000M

MySQL was reporting table full errors.
I tried creating a new file ibdata3 with
innodb_data_file_path=ibdata1:346M;ibdata2:2048G;ibdata3:10M:autoextend

That value did not work
I tried using new sizes for the max value

2046G
2045G
2046000M

After trying for 15 min with different combinations, none of these values worked. Looks like mysqld was never going to startup again. I decided to try the exact number of bytes for the filesize of ibdata2:
innodb_data_file_path=ibdata1:346M;ibdata2:2196875759616;ibdata3:10M:autoextend

When I started mysql, LO AND BEHOLD, MySQL was up.

ibdata3 was created at 10M
ibdata3 was soon growing in 8M chunks
Client was happy once again

Moral of the story : At times, you may need to use the exact filesize for a system tablespace in innodb_data_file_path
TECHNIQUE #2 : RAW DISK PARTITION
According to MySQL 5.0 Certification Study Guide, Page 428

Any raw partitions in the configuration must exist but must have the modifier newraw listed after the size of the file specification. newraw tells InnoDB to initialize the partition when the server starts up. New partitions are treated as read-only after initialization. After InnoDB initializes the tablespace, stop the server change newraw to raw in the partition specfication, and restart the server. For example, to use a 10GB Unix partition named /dev/hdc6, begin with a configuration like this:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Gnewraw

Start the server and let InnoDB initialize the tablespace. Then stop the server and change the configuration from newraw to raw:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Graw

After changing the configuration, restart the server.

Just create a RAID10 Disk with whatever size you need. Just don't put any filesystem on it.
TECHNIQUE #3 : Use ext4 with TECHNIQUE #1
You could chain InnoDB tablespaces 16TB at a time. Start off with this
innodb_data_file_path=ibdata1:10M:autoextend:max:16384G

When ibdata1 is about to hit 16TB, add another ibdata file
innodb_data_file_path=ibdata1:16384G:ibdata2:10M:autoextend:max:16384G

When ibdata2 is about to hit 16TB, add another ibdata file
innodb_data_file_path=ibdata:16384G:ibdata2:16384G:ibdata3:10M:autoextend:max:16384G

And so on...


Will one nvarchar column affect query performance? I have one column that I recently converted to nvarchar(100) to store Asian characters. The other columns in this table, and the other tables in the same database, use varchar, int, bit and datetime. Since that change, some of my queries now take ten to twenty seconds to return less than fifty records. The tables store a thousand records or less.

I've read nvarchar has an associated IO performance cost with it, but this bad for such a small record set? Do I have to adopt an all or nothing approach with nvarchar (convert all varchar to nvarchar) or have I missed something obvious?

Without knowing more details, it's impossible to tell, but you might be suffering from implicit conversion.  There are a number of sites with information on the topic that you can use to help troubleshoot your particular query.

The short answer is that no, you should not need to convert all columns to nvarchar.  If you are trying to join an nvarchar column to a varchar column, however, you could see a major performance impediment.


How can i Update the status of field with a trigger? This is my example code:

create trigger MyTrigger on myTable
after update
as
begin
   set nocount on;
   update talleres set status='active' where FirstDate=getdate()
end
go

create trigger MyTrigge2 on MyTable
for update
as
begin
   declare @d DATETIME=getdate();
   set nocount on;
   update talleres set status='Active' where FirstDate=format(@d,'MM/dd/YYYY')
end
go

Please consider creating a minimal, complete, and verifiable example for this and any future questions you create.  As it is, your question is extremely imprecise and open to a lot of interpretation about what the actual problem is you are trying to solve.

Aside from that, there are several issues I see immediately.

SQL Server DML triggers use the "special" virtual tables, inserted and deleted, as placeholders containing the rows that will have been modified.  You should use them in your statements to only update the affected rows.  Microsoft Docs has a great article about that here.

An example for your first trigger:

create trigger MyTrigger on myTable
after update
as
begin
   set nocount on;
   update talleres
   from talleres t inner join inserted i on t.talleres_id = i.talleres_id
   set status='active'
   where FirstDate=getdate()
end
go

In the above example, I'm assuming you have a column that uniquely identifies rows in the talleres table and the MyTable table.

The WHERE firstdate = getdate() construct is likely going to be problematic since getdate() returns a very precise value that is perhaps unlikely to match any rows in talleres.


Is it safe to use "Safety" as a schema name? I was considering using "Safety" as a new schema name in SQL Server. In SSMS, the term Safety is colored as a keyword, but this term is not listed as a reserved keyword in the BOL. The database does not complain if I create and use it, but is there any reason why I should avoid using it?

The word "safety" was used as a PivotTable service option back in the days of SQL 2000.  It's still around for backwards compatibility:

http://msdn.microsoft.com/en-us/library/aa237323(v=sql.80).aspx

Otherwise, it's okay to use as a schema name.


TNSNAMES.ORA is located in Windows 2012 Server Can you please any one help me for the following
TNSNAMES.ORA is located in Windows 2012 Server? Can you please give the correct path for the same.

it's in your $ORACLE_HOME\network\admin

for example:
C:\app\myusername\product\11.2.0\client_1\network\admin\tnsnames.ora


Partitioning with AUTO_INCREMENT primary key I am looking to partition a MySQL table by the month of the entry timestamp column (time that the row was inserted). The table in question uses an AUTO_INCREMENT column as a primary key as this table is just INSERTed into by a triggered process in aws.

When I try to partition by month I get an error saying it is not allowed and when I look up the error it seems that I need to include the primary key as part of the partition.

  All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

ALTER TABLE TABLE
    PARTITION BY RANGE(MONTH(entry_ts)) (
    PARTITION JANUARY VALUES LESS THAN (2),
    PARTITION FEBRUARY VALUES LESS THAN (3),
    PARTITION MARCH VALUES LESS THAN (4),
    PARTITION APRIL VALUES LESS THAN (5),
    PARTITION MAY VALUES LESS THAN (6),
    PARTITION JUNE VALUES LESS THAN (7),
    PARTITION JULY VALUES LESS THAN (8),
    PARTITION AUGUST VALUES LESS THAN (9),
    PARTITION SEPTEMBER VALUES LESS THAN (10),
    PARTITION OCTOBER VALUES LESS THAN (11),
    PARTITION NOVEMBER VALUES LESS THAN (12),
    PARTITION DECEMBER VALUES LESS THAN (13)
    );

CREATE TABLE `TABLE` (
  `HEADER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STATUS` int(11) NOT NULL DEFAULT '0',
  `CREATED_AT` varchar(45) NOT NULL,
  `TYPE` varchar(45) NOT NULL,
  `VERSION` varchar(45) NOT NULL,
  `ID` varchar(45) NOT NULL,
  `ORIGIN` varchar(45) NOT NULL,
  `PAYLOAD` json NOT NULL,
  `ENTRY_TS` datetime NOT NULL',
  PRIMARY KEY (`HEADER_ID`)
)

Simply put, you need to put entry_ts in the PRIMARY KEY before you can partition the table.
The table needs to look like this:
CREATE TABLE `TABLE` (
  `HEADER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STATUS` int(11) NOT NULL DEFAULT '0',
  `CREATED_AT` varchar(45) NOT NULL,
  `TYPE` varchar(45) NOT NULL,
  `VERSION` varchar(45) NOT NULL,
  `ID` varchar(45) NOT NULL,
  `ORIGIN` varchar(45) NOT NULL,
  `PAYLOAD` json NOT NULL,
  `ENTRY_TS` datetime NOT NULL',
  PRIMARY KEY (`HEADER_ID`,`ENTRY_TS`)
);

So, run this:
ALTER TABLE `TABLE`
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (`HEADER_ID`,`ENTRY_TS`);

Then, you can partition the table since ENTRY_TS is part of the PRIMARY KEY.
ALTER TABLE TABLE
    PARTITION BY RANGE(MONTH(entry_ts)) (
    PARTITION JANUARY VALUES LESS THAN (2),
    PARTITION FEBRUARY VALUES LESS THAN (3),
    PARTITION MARCH VALUES LESS THAN (4),
    PARTITION APRIL VALUES LESS THAN (5),
    PARTITION MAY VALUES LESS THAN (6),
    PARTITION JUNE VALUES LESS THAN (7),
    PARTITION JULY VALUES LESS THAN (8),
    PARTITION AUGUST VALUES LESS THAN (9),
    PARTITION SEPTEMBER VALUES LESS THAN (10),
    PARTITION OCTOBER VALUES LESS THAN (11),
    PARTITION NOVEMBER VALUES LESS THAN (12),
    PARTITION DECEMBER VALUES LESS THAN (13)
    );

Please try this in Staging with an empty table.
GIVE IT A TRY !!!


Convert a datetime column to number of seconds In my SQL Server database, I have a datetime column.

What is a good way to create a new column that represents the long value for the datetime column? The long would represent a number of seconds.

I thought if I can convert it to longs, it would make it easier to do group by queries over time periods, as I could just divide the long number by fixed amounts.

The table is static, Won't be updating or deleting data.

Create a new column (ALTER TABLE) then run an UPDATE on it

UPDATE
  MyTable
SET
  NewIntColumn = DATEDIFF(SECOND, '19000101', MyDateTimeColumn)

19000101 is the SQL Server epoch. You can use 19700101 for Unix epoch for example


Restore a Single File from FileStream We have an document management application uses SQL Server 2012 to store document index information and uses a Windows file share to store the documents.  We are working on migrating to store the documents within a SQL FileStream.  However, we have encountered one issue.

According to this document:
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/FILESTREAM%20Design%20and%20Implementation%20Considerations.docx

It states that “It is not possible to back up a single file as a unit of the filegroup or to restore a single file from the filegroup backup. Keep in mind that you can export a single file when you restore the primary filegroup and then the FILESTREAM backup to a separate instance“.

There are numerous times when a client asks us to restore a single file because they accidentally modified it.  With our current system (using a Windows file share that is backed up continuously) we can go to our backup system (Microsoft DPM) then select and restore just a single file to it's original location.  If we use FileStream to store the documents within SQL, how can we restore just one document, not the entire database?

In short you can't.  You would have to restore the entire database to another machine, then copy the needed file from the restored machine and put it into place on the production server.  Single object restores and single row restores (which is what restoring a single file effectively is) have never been supported via the native tools.


SQL Server 2005 Database move and rationalisation - Recommended maintenance processes We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct.

I inherited this database and it came to me in a bit of a mess truth be told - on top of that I've basically learnt to be a SQL Server developer on this server (no educational background in computing) so I've made plenty of my own mistakes on it. We're underfunded as a department (public sector), and so lacking expertise, pulling ourselves up by our bootstrings. The database started out as a back room thing but has become very important to our organisation and given this the performance is frankly embarrassing.

So we have issues such as

history of Autoshrink usage
History of undersized storage - never really enough space on the server for the db to grow so I'd guess the files are probably quite internally fragmented.
There are multiple collation types (for no good reason),
Inappropriate text datatypes all over the place (lots of Nvarchar, nchar, ntext when we have no need of ascii characters, and text where varchar would be more appropriate)

The new server is a 64bit virtual server, storage on RAID.

My plan after installing and upgrading is to:

Remove a vast amount of tables we've identified (with confidence) as redundant (about 1/3 of db size!)
Normalise collations to 1 type (after some analysis and testing we're certain this won't cause us problems - we have no requirement for case sensitivity)
Normalise text data types to varchar/char
Set database file sizes to the expected usage for the coming 2 years plus 50%

Is there anything else I should be considering after/before these processes - and do any of decisions here look off mark? For example the file size issue - is this something I need to worry about on RAID?

Shrinking and Storage

Enable "Instant File Initialization"

Disable autoshrink

Size your storage properly (15% free per drive)

RAID what? RAID level matters and SQL Server accesses the files differently. Log file access is generally sequential and Data is generally random.

tempdb

Separate data and log files (see RAID, below) - get a good tempdb strategy. We have tempdb logs on a log drive and our tempdb files (1/4-1/2 the number of cores) on their own drive. While you're at it, make sure that tempdb has an appropriate number of data files, that their initial sizing is exactly the same, and that they have exactly the same growth factor. While you're at it, read up on Trace Flag 1118

Schema Changes

If you're going to be making these changes, make sure you have a good, durable backup. What happens in 9 months when someone actually needed those tables?

Can your application actually withstand the changes from NVARCHAR, NCHAR, and NTEXT? While you're at it, NTEXT is deprecated so moving to TEXT would not be good. Check out VARBINARY or VARCHAR(MAX) instead.

You can change the collation all you want, but it won't affect any of the existing data.

Compatibility

Change the db compatibility levels to 100 if you can.

Tuning Option

Check out some of these trace flags none of them may be appropriate. Some of them might be, "it depends." Also, read about trace flag 4199.

Tooling

I'd also recommend coming in to this install with a good tool set. Create a DBA database for this toolkit. I'd start with sp_WhoIsActive by Adam Machanic, sp_Blitz by Brent Ozar. I'd also look for Kendra Little's work with scheduling and storing the results from sp_WhoIsActive. Finally, I'd start up a way of capturing Waits and Queues so that you have a good history of your server's profile.

Look into getting a monitoring solution.

Finally (and maybe firstly), check out Glenn Berry's excellent series on provisioning a new SQL Server Instance.

Part 1, Part 2, and Part 3


WAL backups with pg_dump not possible? I am hoping to move to WAL backup from pg_dump. But I am confused by this note in 4.3  documentation:

  Perform the backup, using any convenient file-system-backup tool such
  as tar or cpio.

And in postgres 9.0 documentation:

  Perform the backup, using any convenient file-system-backup tool such
  as tar or cpio (not pg_dump or pg_dumpall).

So I am not allowed to run pg_dump? Or it's not allowed only for 9.x ? Filesystem dumps are useless to me as I need to restore 64-bit machine dumps on 32-bit. And I need to be able to restore separate databases.

If pg_dump is forbidden, is the only solution for me to setup slave replication and run pg_dump on slave?

You are confused because you're mixing up a database base backup and a SQL dump (with pg_dump or pg_dumpall) which are actually two completely different things. Take a good look at the PostgreSQL documentation about Backup and Restore. These are the links to the 9.1 docs, but the basic principles of this stuff haven't changed since at least version 8.3 (except for streaming replication which was introduced with 9.0)

A database base backup is basically a file system level copy of the data directory (and any additional tablespace directories) at a certain point in time. This file system level backup can be created with any tool you like (e.g. tar or cpio). But in order to be able to recover from this backup additional information is needed: the WAL segments (binary transaction log) that have been created during the backup plus the exact start and end position of the backup creation process in the WAL segments. In recovery mode PostgreSQL is started with the backup data directory and it will replay all transactions from the available WAL archives (unless a certain end time or transaction id is configured for point-in-time recovery). The scope of a base backup is a database cluster.
A SQL dump is the plain text or binary format representation of the schema and/or data information of one (pg_dump) or all (pg_dumpall) of the databases in a cluster. It is also possible to dump just certain schemas or tables. In contrast to a base backup a SQL dump covers only an exact snapshot of the database at a single point in time. The essential difference to a base backup is that the SQL dump is independent of the physical database layout. System architecture, OS, file system and for most parts database version do not matter, the SQL dump is only the information that makes up a database and can therefore be easily restored on another system.

So in your case using pg_dump or pg_dumpall for backup and pg_restore or psql to restore the dump is the way to go.

Hope that helps.


Postgresql SELECT item from JSONB array - array as top-level json object I have JSONB column which stores the data in this format

[
    {
        "item1": "value1",
        "item2": "value2"
    },
    {
        "item1": "value3",
        "item2": "value4"
    }
]

I am trying to select 'item1' with query

SELECT jsonb_array_elements(data)->'item1' FROM test_table

Error I am getting is:

cannot extract elements from an object

How should I change the query to make it work?

You need to unnest the array elements to rows in the from clause:

select i.dt -> 'item1' as item1
from test_table t
  cross join jsonb_array_elements(t.data) as i(dt)


Mathematical equality of two SQL statements Is there a way to get check the mathematical equality of two SQL statements?

I have two SQL statements:

SQL_STATEMENT_1
SQL_STATEMENT_2

Running both statements on data and comparing the output does not help at all.

The set maths behind the statements needs to be evaluated, like a equation solver does.

Out of scope of my question are things like:

comparisons other then equality (greater than, less than, LIKE, ...)
stored procedures, or triggers
Common Table Expressions (WITH)

In the scope:

Subselects: WHERE other_id IN (SELECT id FROM other WHERE ...)
JOINS

What is the mathematical equality of two SQL statements? For me two queries are equivalent if, when given both the same of any dataset, they return the same result set.

As you pointed out, SQL queries, a superset of relational algebra, can be very complex.  We can mix subqueries, use stored procedures and functions (deterministic or not) which will make you query looks more like real code.  If you are talking about these kinds of queries then it is going to be really hard.  In fact it is probably no different than the "are two algorithms equivalent" problem.

Under those conditions it is probably impossible.

However...

...it might be feasible if the two queries you want to compare are strict set operations.  If so, you can convert the queries to relational algebra and then work it out following equivalence rules.  If you have a selection/restriction with nontrivial boolean conditions then you might end up needing to prove than thoses conditions are also equivalent.  You'll then need to rely on boolean algebra and you'll probably end up doing a truth table.

As you can see this is going to be a lot of work and, as far as I know, nothing exists to compute all of that automatically.  Nevertheless, I found some tools that you might found useful if you want to tackle the task:

RelaX a relational algebra calculator
Truth table generator


Error publishing SQLCLR C# Function using Visual Studio 2017 I'm trying to (learn) publish a SQLCLR function using Visual Studio 2017. (It's a simple function that sends an email.)

As a reference I used this articles on CodeProject and MSSQLTips:

Create, Run, Debug and Deploy SQL CLR Function with Visual Studio 2013 Database Project

Send Email from SQL Server Express Using a CLR Stored Procedure

On project properties->SQLCLR I've set:

Permission level : UNSAFE

And according to the article I've updated target databases:

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

And after I've got the error I've followed this advice on MSSQLTips article:

  If you get error messages when trying to compile the code you may need to alter the database using the following command and then try again to create the assembly and the stored procedure.

ALTER DATABASE msdb SET trustworthy ON

I've tried using two different target databases:

SQL-Server 2017 LocalDB
SQL-Server 2017 Express

I can build the project without errors, but when I publish the project I receive one error when it executes the next command:

CREATE ASSEMBLY [dbSysmac]
    AUTHORIZATION [dbo]
    FROM 0x5F8A900003000000...
    WITH PERMISSION_SET = UNSAFE;

  (47,1): SQL72014: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly 'dbSysmac' failed because assembly 'dbSysmac' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly.

What I'm doing wrong?

I've updated target databases:

 sp_configure 'clr enabled', 1;

Just to be clear, "clr enabled", and sp_configure in general, are instance-level, not database-level configuration.

  I've followed this advice on MSSQLTips article

Yikes. Well, that article on MSSQLTips is more of a problem than the error that you are getting. It would be best if you forgot everything you saw in that article and never looked at it again. It is full of bad advice:

SQLCLR not required for sending email in SQL Server Express (I have tested this and it does work). HOWEVER, it does not work in SQL Server Express LocalDB :-(
You should use SqlString instead of String for input parameter types (and for return types)
Deploying to msdb? Maybe it's just me, but I would never deploy code to msdb (and I would only add code to master if it is physically required, such as when marking a proc as either a start up proc or as a system proc).
UNSAFE permission set? Why? For email? The assembly should be EXTERNAL_ACCESS. Code (assemblies, logins, users, or anything else) shouldn't be given more permissions than necessary.
Setting TRUSTWORTHY ON for msdb is two problems in one:

Setting TRUSTWORTHY ON is lazy. Sure, it's fine for doing proof-of-concepts / quick tests, but it is not a good long-term / production-level option. It's a huge security risk. Instead, Module Signing should be used. Here is a guide I wrote for how to accomplish this, and do so within Visual Studio:
SQLCLR vs. SQL Server 2017, Part 2: "CLR strict security" - Solution 1
TRUSTWORTHY is already ON for msdb. At least it is ON by default.

The @body input parameter should be NVARCHAR(MAX), not NVARCHAR(4000). This is, after all, HTML email (i.e. myMessage.IsBodyHtml = True)
No, absolutely do not store your plain-text email password in the .NET code. DLLs / Assemblies are not encrypted. ALL strings are stored as-is at the end of the EXE / DLL / Assembly. It takes almost no effort to display the contents of an assembly, and if you follow this advice, your SMTP server, login, and password will be stored for anyone to see. Either pass those in from a variable, or do a simple query to select from a table. You might even be able to get them from the Windows Registry.

What database are you actually publishing to? Assuming that you are publishing to another database that is not msdb, then you, at least simplistically, in this moment to move forward, need to set TRUSTWORTHY ON for the DB that you are publishing to. But again, enabling TRUSTWORTHY is just for testing (please see: PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining).

Now, if you are executing the ALTER DATABASE... manually and then still get the error upon publishing, then most likely you have the "deploy database properties" option selected (in Visual Studio: go to "Project Properties" | "Debug" tab | "Deployment Options" area towards the bottom). TRUSTWORTHY is one of those database properties. So, when "deploy database properties" is checked, then every deployment will set the options to match what is defined in the project. And by default, Trustworthy is not enabled.

Your options are:

If you are deploying database properties:

To enable Trustworthy in Visual Studio (for deployments): go to "Project Properties" | "Project Settings" tab | "Database Settings..." button | "Miscellaneous" tab. Check the box for "Trustworthy" and then click the "OK" button.
If you are not deploying database properties:

Enable trustworthy in the database where you are deploying the assembly (enabling TRUSTWORTHY in msdb won't help)
Once you have this working correctly in development, handle security properly before pushing to production:

SQLCLR vs. SQL Server 2017, Part 2: "CLR strict security" - Solution 1

For learning more about SQLCLR in general, please visit: SQLCLR Info. Be sure to check out the "Stairway to SQLCLR" link, which is a series I am writing on this topic on SQL Server Central.


Optimize MySQL insertion - insert multiple records with condition (per record) although not new to SQL, up until now I used the basic stuff - basic crud, joining tables etc. I have a special case now where I'm expecting a lot more insertions that goes something like this (pseudocode):
foreach(id_of_student in students){
    {class} = students[id_of_student].class;
    {grade} = students[id_of_student].grade;
    INSERT INTO grades (id_of_student,class,grade) VALUES({id_of_student},{class},{grade})
    WHERE grade.notSetManually=1 ON DUPLICATE KEY UPDATE
}

Of course I would like to not run it as a loop and access the db 50 times for each grade sheet (my app checks up to 50 grade sheets per minute). Can this be done in one big query, in a more optimized way? I saw that there is LOOP in mysql, but is it just syntactic sugar when it comes to the burden on the DB?

Another thing - this ON DUPLICATE KEY UPDATE means that as long as my key is {id_of_student},{class}, this will update and not add new line, right?

IODKU works well with SELECT:
INSERT INTO t
    (unique_key, counter, time)
    ON DUPLICATE KEY UPDATE
        counter = VALUES(counter) + 1,
        time = NOW()
  SELECT unique_key, 1, NOW()
      FROM ...

Notes:

Need unique key (or set of columns)
See how a counter can be updated


Error 13141 in mongos shell while trying to write to a result of the mapReduce invocation to a sharded collection In our db init script, we have the following lines:

var conn = new Mongo();
var admin = conn.getDB("admin");
var db = conn.getDB("foodb");
admin.runCommand({ "enableSharding": "foodb" });
// ...
db.runCommand({ "create": "foo_stats" });
admin.runCommand({ "shardCollection": "foodb.foo_stats", key: { "_id": "hashed" } });

Then, when I try to perform a mapReduce() on another collection and write to foo_stats, the following happens (this is a simplified example which we actually tried after the "real" one failed):

mongos> db.foo_data.mapReduce(function(){ emit(this.foo_id, null)}, function(key, values) { return {};}, {"out": {"replace": "foo_stats","sharded": true}});
2016-05-31T06:50:07.061-0700 E QUERY    Error: map reduce failed:{
 "code" : 13141,
 "ok" : 0,
 "errmsg" : "exception: Chunk map pointed to incorrect chunk"
}
    at Error (<anonymous>)
    at DBCollection.mapReduce (src/mongo/shell/collection.js:1353:15)
    at (shell):1:16 at src/mongo/shell/collection.js:1353

What can be the cause of this error? In the documents from the foo_data collection, foo_id is a string that should theoretically match the regex: ^[A-Za-z0-9\-_]{16,48}$

As at MongoDB 3.2, the only supported key for a sharded output collection for Map/Reduce is the _id field (non-hashed).

There are known issues with Map/Reduce output to a sharded collection using a hashed shard index; the two features don't play well together yet and this isn't a supported combination. The documentation currently only suggests that _id can be used, however there could be an explicit note that hashed indexes are not supported yet.

There's a relevant Jira which you can watch/upvote: SERVER-16605: Mapreduce into sharded collection with hashed index fails.

  What can be the cause of this error?

I believe your specific chunk map error is likely due to the hashed index and sharded Map/Reduce both assuming they should create the initial chunks for an empty collection.

When you create a Shard a Collection Using a Hashed Shard Key:

  If you shard an empty collection using a hashed shard key, MongoDB automatically creates and migrates empty chunks so that each shard has two chunks. To control how many chunks MongoDB creates when sharding the collection, use shardCollection with the numInitialChunks parameter.

With a Sharded Collection as Output, Map/Reduce also tries to create the initial chunks:

  For a new or an empty sharded collection, MongoDB uses the results of the first stage of the map-reduce operation to create the initial chunks distributed among the shards.


Trying to connect a PostgreSQL database to Eclipse I have a Postgres (version 9.2) database running on a Centos 7 machine, and I'm trying to connect to it from Eclipse Oxygen on a Windows 10 machine using the Data Source Explorer.

When I put in all the information on the Connection Details screen in Eclipse and hit "Test Connection", I get this error:

org.postgresql.util.PSQLException: The connection attempt failed.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:250)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
    at org.postgresql.Driver.makeConnection(Driver.java:454)
    at org.postgresql.Driver.connect(Driver.java:256)
    at org.eclipse.datatools.enablement.internal.postgresql.PostgreSQLJDBCConnection.createConnection(PostgreSQLJDBCConnection.java:87)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:105)
    at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:54)
    at org.eclipse.datatools.enablement.internal.postgresql.PostgreSQLJDBCConnection.<init>(PostgreSQLJDBCConnection.java:47)
    at org.eclipse.datatools.enablement.internal.postgresql.PostgreSQLConnectionFactory.createConnection(PostgreSQLConnectionFactory.java:51)
    at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)
    at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(ConnectionProfile.java:359)
    at org.eclipse.datatools.connectivity.ui.PingJob.createTestConnection(PingJob.java:76)
    at org.eclipse.datatools.connectivity.ui.PingJob.run(PingJob.java:59)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:56)
Caused by: java.net.SocketTimeoutException: connect timed out
    at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
    at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.SocksSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at org.postgresql.core.PGStream.<init>(PGStream.java:69)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:152)
... 14 more

I'm pretty new to Centos and Postgres, so I'm not entirely sure what all of this means. I know that the information that I put in to connect to it is correct. I'm able to ping the Centos machine from Windows and I'm able to ping the Windows machine from Centos. I am also able to connect to the Centos machine from Windows with Putty, if that helps.

Thanks in advance.

This line shows the problem:

Caused by: java.net.SocketTimeoutException: connect timed out

As a rule of thumb, typically in Java stacktraces, the first "Caused by" (printed last) is what you want.

Your Win10 can't connect to the postgresql on your CentOS. PostgreSQL uses typically the TCP port 5432.

It gets a connection timeout error, which means your win10 absolutely doesn't get an answer. Nothing. If your postgresql wouldn't allow the connection, or if your postgresql wouldn't listen on your tcp port 5432, you had got "authentication error" or "connection refused" error.

But your win10 doesn't get anything, not even an error. As if your server wouldn't even exist.

You can check the availability of your postgresql with a

telnet your.centos.ip 5432

command.

The most probably cause of your problem is that the default firewall configuration of your centos simply filters out the 5432 port. Enable it.

The second most probable cause is that you simply typed in a false IP.

If you solved that problem, then

your telnet command won't hang up
your java software will connect, or at least it will have a different error (authentication error or connection refused are the most likely).

Making postgresql listening on the network requires also some different settings, beside your firewall configuration:

You have to set up your postgresql to listen on all IP addresses, not only on localhost. This can be done with a listen_addresses line of your postgresql.conf.
You have to set up some authentication (md5 password is the most likely) in the pg_hba.conf.

First connect your postgresql with some client, for example with pgadmin or with the command line psql tool (it comes also with cygwin). After it works, only then play to connect it from Java.


Oracle: Sum function "not a single-group group function error" I have an ORDER_LINE table in which I am trying to multiply the NUM_ORDERED of parts by the QUOTED_PRICE for each ORDER_NUM. I am adding SUM() function to group by ORDER_NUM, because if I didn't, there would be multiple entries for the same order.

ORDER_LINE Table:

ORDER_NUM   PART_NUM    NUM_ORDERED     QUOTED_PRICE
21608       AT94        11              21.95
21610       DR93        1               495
21610       DW11        1               399.99
21613       KL62        4               329.95
21614       KT03        2               595
21617       BV06        2               794.95
21617       CD52        4               150
21619       DR93        1               495
21623       KV29        2               1290

Query:

SELECT ORDER_NUM, SUM(NUM_ORDERED * QUOTED_PRICE) AS TOTAL_AMOUNT
FROM
ORDER_LINE
ORDER BY ORDER_NUM;

Expected Result:

ORDER_NUM   TOTAL_AMOUNT
21608       241.45
21610       894.99
21613       1319.8
21614       1190
21617       2189.9
21619       495
21623       2580

  Error ORA-00937: not a single-group group function

If I remove the Sum function I get the following result:

SELECT ORDER_NUM, (NUM_ORDERED * QUOTED_PRICE) AS TOTAL_AMOUNT
FROM
ORDER_LINE
ORDER BY ORDER_NUM;

ORDER_NUM   TOTAL_AMOUNT
21608       241.45
21610       495
21610       399.99
21613       1319.8
21614       1190
21617       1589.9
21617       600
21619       495
21623       2580

Unless I'm missing something, you just need to add GROUP BY ORDER_NUM, don't you?

SELECT ORDER_NUM, SUM(NUM_ORDERED * QUOTED_PRICE) AS TOTAL_AMOUNT
FROM
ORDER_LINE
GROUP BY ORDER_NUM
ORDER BY ORDER_NUM;


Database Mail sending functionality not working on local system I am using Database Mail functionality to send mail from a SQL Server 2008 database via following stored procedure execution:

EXEC sp_send_dbmail @profile_name = 'MyProfile',
                    @recipients = 'abc@companyname.com',
                    @subject = 'Test message',
                    @body = 'Congrats Database Mail Received By you Successfully.'

I have tried with my gmail account profile on my local system it's working properly but not with my company or outlook profile.

Error message:

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 . Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond abc.j.i.ooo:pp). )

Reference

http://blogs.msdn.com/b/suhde/archive/2009/07/12/how-to-configure-sql-server-database-mail-to-send-email-using-your-windows-live-mail-account-or-your-gmail-account.aspx

What would be the problem?

Thanks

I'm not sure what you are talking about with "gmail profile". SQL Server Database Mail has its own profiles and has nothing to do with any email clients on your machine.

When you create a Database Mail profile (SQL Server Management Studio, Management, Database Mail) you must specify the ip address of the SMTP server or servers to use. What you are confiruing is the "outgoing email server" for Database Mail (similar to configuring an Internet email client like Thunderbird or Windows Mail). Those SMTP servers must be configured to allow connection and to allow relaying from the SQL Server machine's ip address, something the email administrators will be able to do.

That error message suggests to me that either the above has not been configured or that there is a firewall between the SQL Server machine and the SMTP server that is blocking the traffic.


MySQL proxies_priv ignored in --skip-name-resolve mode I'm running MySQL (MariaDB) inside docker container and with every start, it shows the following warning:

  [Warning] 'proxies_priv' entry '@% root@mariadb-3702996102-qbr18' ignored in --skip-name-resolve mode.

So, it writes the current container hostname in proxies_priv and since I have option skip-name-resolve in my.cnf it gives the warning.

Is there a way to hide this warning? Perhaps there's an option to not write the hostname variant to proxies_priv?

The warning is useful because it tells you that this proxy privileges will never be matched.
Your options are:

you delete this privilege
you change the @ part to % or to a defined ip address
you disable skip-name-resolve (which will slow down MySQL, which will now depend on the DNS resolver speed: https://dev.mysql.com/doc/refman/5.7/en/host-cache.html)

Update
Just tested MySQL 5.6 and MySQL 5.7, and it seems that even if the warning appears, localhost is replaced by 127.0.0.1 with --skip-name-resolve. A bug was opened: https://bugs.mysql.com/bug.php?id=81441
Clearly MySQL should only warn if the domain isn't localhost, as with a disabled resolver the rule won't apply at all, only @your.ip.address.number and @localhost would work.


What configurations affect the speed of DDL statements in PostgreSQL I would like to setup PostgreSQL in such a way that dropping and recreating the entire database (~100 tables/views + a few rows per table) is as fast as possible.
This is for a development environment where data integrity is not important.
This database reset will happen very often.

What are the configuration options that might help with this (or psql meta commands that can help speed this process up)

EDIT:
Imagine i have a single sql file (that i constantly edit and save) that creates everything needed in an empty database.

BEGIN;
CREATE TABLE ...
INSERT INTO ...
CREATE TABLE ...
INSERT INTO ...
COMMIT;

I need it to run as fast as possible, right now it takes a few seconds even for about 10 tables, i would like it to be under 1 sec

In what sense is database integrity not important? Even if you don't care about integrity, if the production database doesn't accept entries in table baz because of a foreign key constraint against table bar, do you want that constraint emulated in development? Or do you want to find out you forgot about it when you move to production? Personally, I would not sacrifice integrity in development.

Official toolkit, pg_dump, pg_restore

I would personally use pg_dump and pg_restore. They're configurable, user-friendly and very easy to use. That said, there are faster options but I have never had a need for them for my use cases. The official tools do not support binary dumps, but they also work between versions fairly well.  pg_restore -1 will wrap the whole thing in a transaction and disable WAL.

Raw Data with TABLESPACE

If you're willing to make raw backups you can get far greater speed. You can do this,

Create a a folder somewhere like /data/dbs chown it to be owned the user/group postgres

mkdir -p /data/dbs;
chown postgres:postgres /data/dbs;

Make this into a TABLESPACE

CREATE TABLESPACE dbspace LOCATION '/data/dbs';

Create a database there..

CREATE DATABASE foobar WITH TABLESPACE = dbspace;

Now you can connect to -d foobar. And do some stuff.

CREATE TABLE foo ( idfoo serial PRIMARY KEY, name text );
CREATE TABLE baz ( idbaz serial, idfoo int REFERENCES foo );
INSERT INTO foo (name) VALUES ('foo'), ('bar');
INSERT INTO baz (idfoo) SELECT idfoo FROM foo;

Now you can disconnect.. you can copy the data-directory to backup. this process can be scripted easily. For me,

the data directory is PG_9.5_201510051 for you it may different.
the command to stop PostgreSQL runs though /usr/sbin/service (I use Ubuntu)

So for me, it looks like this.

sudo service postgresql stop
sudo cp -pR PG_9.5_201510051/ backup
sudo service postgresql start

Reconnect.

DROP TABLE foo CASCADE;

Then when disconnect and again stop the service we can restore the backup (also easily scripted),

service postgresql stop;
cp -pR backup/ PG_9.5_201510051/

Upon reconnecting, foo is magically back and restored. You can't get faster than this...

Not fast enough?

If this isn't fast enough. In the event your database has TB of data or something crazy.

You can use rsync to only synchronize files changed.
You can likely use ZFS snapshots or BTRFS snapshots on a block device mounted to the data directory. YMMV.


Which size is correct? I have to estimate the size of a DB2 DB; I used several methods and the problem is I get huge differences.
In order to simplify the problem, I'll focus on a unique table.

Use of the following SQL command :

  SELECT
  TABNAME,
  TABSCHEMA,
  SUM(DATA_OBJECT_P_SIZE) AS DATA_OBJECT_P_SIZE,
  SUM(INDEX_OBJECT_P_SIZE) AS INDEX_OBJECT_P_SIZE,
  SUM(LONG_OBJECT_P_SIZE) AS LONG_OBJECT_P_SIZE,
  SUM(LOB_OBJECT_P_SIZE) AS LOB_OBJECT_P_SIZE,
  SUM(XML_OBJECT_P_SIZE) AS XML_OBJECT_P_SIZE
  FROM
  SYSIBMADM.ADMINTABINFO
  WHERE TABNAME IN (‘TABLENAME')
  GROUP BY
  TABNAME,
  TABSCHEMA

  For table A, I get a total of 135MB

Use of the "Estimate Size" functionnality in DB2 Control Center

  For table A, I get a total of 3.5MB

At this point, the difference is REALLY huge. So i digged a little and decided to make an exact copy of table A (without indexes) with the 2 following commands

     CREATE TABLE B AS (SELECT *  FROM A) DEFINITION ONLY;
     INSERT INTO B SELECT * FROM A;

  Next I mesured the size of B (which is a clone of table A) : both methodes give me 3.5MB for this table

So here is my problem : I would like to know why does the SQL command tells me data weighs 38 times more on the original table ? I suspect preallocation and/or fragmentation because the original table is used for insert/update/delete statements, but commands like REORG (with reclaim extent), ALTER TABLESPACE REDUCE (and RUNSTATS) don't change the result.

Any clue ?

There is a handy dandy utility in DB2 LUW (in at least version 9.7 and higher). I'm not sure when it exactly entered the product.

Sizing tables! And stored procedures that help calculate size.

First you need to run the stored procedure CREATE_STORAGEMGMT_TABLES, specifying the tablespace where you wish to put these (I recommend SYSTOOLSPACE). Once you have those you can run the CAPTURE_STORAGEMGNT_INFO stored procedure. It is generally recommended that you first run RUNSTATS on all tables with RUNSTATS ON TABLE (TABLESCHEMA.TABLENAME) ON KEY COLUMNS AND INDEXES ALL. Then run call capture_storagemgmt_info(0,<SCHEMA_NAME>,<DATABASE_NAME>) at least the first time (as per recommendation from IBM). After that you can run it for specific objects. This gives you the size in bytes for everything, tablespaces, tables, indexes, partitions, your database itself....

You do need to prune these tables manually as they will grow over time with use. (The reason is they just INSERT new snapshots of what sizes are.) The nice thing is you can use these to trend growth for your objects and even the whole database. If you wish to prune the tables, I tend to use delete from systools.stmg_root_object where stmg_timestamp < (current timestamp - 30 day). There is a cascading DELETE from that parent object (at least right now) that takes care of cleaning out all the child tables.

Here is an example of a report I run across this

select
t.main_tbspace as tablespace,
o.obj_schema as schema,
o.obj_name as table,
(max(t.estimated_size))/1024/1024 as maximum_size_in_mb,
(min(t.estimated_size))/1024/1024 as minimum_size_in_mb,
(avg(t.estimated_size))/1024/1024 as average_size_in_mb,
count(t.stmg_timestamp) as snapshots
from
systools.stmg_object as o,
systools.stmg_table as t
where
o.obj_id = t.obj_id and
o.stmg_timestamp = t.stmg_timestamp
group by t.main_tbspace,o.obj_schema,o.obj_name
order by maximum_size_in_mb desc, average_size_in_mb desc, minimum_size_in_mb desc
with UR"

And if you ever don't want the tables anymore, there is always the DROP_STORAGEMGTM_TABLES stored procedure.

Hope this helps!


Using the auto increment id from one query as the fk of the next insert I'm not sure if I'm overcomplicating this or whether I can't see the wood for the trees.

I have two tables, both have auto incrementing ID's.

Table 1:
ID - auto
field - int
other field - varchar

Table 2:
ID - auto
Table1Id - FK
field - varchar

the first table stores an article and the 2nd stores the location of the images related to it.

For my form that uploads the data all information to be stored is taken.  I need to run two inserts simultaneously (which I realise is daft) using the same ID.

My plan(flawed) is to insert into table 1, get the id and then insert into table 2.

one of the problems if is a user accesses the site in the gap between the two inserts and sees a mess.

is there an easier way, I think I've made a mistake or I'm missing something obvious as it shouldn't be this difficult. :)

This is (in part) where transactions come in.
Assuming your transaction isolation level is "read committed", "repeatable read" or "serializable" (MySQL defaults to "Repeatable read" and anyone setting it to lower than "read committed" is committing crimes against the laws of the universe), other transaction are blind (to some extent) to changes made within your transaction.

So you should:

START TRANSACTION;
INSERT #1;
INSERT #2;
COMMIT;

The other transactions will not be able to read your first INSERTed row (though they can find trace of its existence via locks -- probably not interesting to you). They will only be able to read both your changes or none of them.

This, again, depends on the assumption that these transactions don't come from a connection with "Read committed" isolation level -- which, unfortunately -- is something anyone is able to change for their own connection.


Can't use CAST, Convert with UDT in Sql Server 2014 My tables consist of many decimal columns with different precision.
I wanted to standardize the usage of precision by creating several UDTs with predefined precision and use them as column type instead.
I verified most of the cases that I need can be done without using  CAST or CONVERT.
I only have one question left-
How can I explicitly reduce precision using udt (Last line in the script)

It there any other way a value could be converted into a UDT?
Is there any other approach to enforce using specific precision when defining a new column?

/*
DROP TABLE TestPrices;
DROP TABLE TestQty;
DROP TABLE TestResult;
DROP TYPE udtPrice;
DROP TYPE udtQty;
GO
*/

CREATE TYPE udtPrice
FROM DECIMAL(10, 4) NOT NULL;

CREATE TYPE udtQty
FROM DECIMAL(10, 6) NOT NULL;
GO

CREATE TABLE TestPrices (Price udtPrice)
CREATE TABLE TestQty    (Qty udtQty)
CREATE TABLE TestResult (Cost udtPrice)
GO

INSERT INTO TestPrices VALUES (23.1234)
INSERT INTO TestQty VALUES (2.123456)
INSERT INTO TestQty VALUES ('4.1234564353454354543543543')
select * from TestQty

INSERT INTO TestResult
SELECT p.Price * q.Qty AS res FROM TestPrices p,TestQty q

SELECT p.Price * q.Qty AS res FROM TestPrices p,TestQty q
SELECT * FROM TestResult

--how can I explicitly reduce precision using udt
SELECT TOP 1 CAST(Qty as udtPrice) FROM TestQty

Thank you in advance.

I agree with the comment about providing a minimal, complete and verifiable example of your problem.  I'm not sure why you're trying to use CAST/CONVERT when attempting to define a UserType.  Simply use the base SystemType.

DROP TABLE IF EXISTS TestUdt;
DROP TYPE IF EXISTS DecimalPrecision5Scale4;
DROP TYPE IF EXISTS DecimalPrecision11Scale2;
GO

CREATE TYPE DecimalPrecision5Scale4
FROM DECIMAL(5, 4) NOT NULL;

CREATE TYPE DecimalPrecision11Scale2
FROM DECIMAL(11, 2) NOT NULL;
GO

CREATE TABLE TestUdt
(
TestCol1 DecimalPrecision5Scale4,
TestCol2 DecimalPrecision11Scale2
);

--Verify precision and scale of UDT

SELECT NAME
    ,precision
    ,scale
FROM sys.columns
WHERE NAME LIKE '%TestCol%';

| NAME     | precision | scale |
|----------|-----------|-------|
| TestCol1 | 5         | 4     |
| TestCol2 | 11        | 2     |

UPDATE - 2018-09-11 - Regarding OP comment

  how can I explicitly reduce precision using udt

You could create a @temp Table Variable (or #temp table) with UDT columns for the precision you want, insert the raw data into the temp table (letting SQL Server do the conversion) and then select from the @temp table

Declare @TestConvert table(Qty udtPrice)
insert into @TestConvert select qty from TestQty
SELECT TOP 1 Qty from @TestConvert

| Qty    |
|--------|
| 2.1235 |


Database design for holding read/unread content state In forum application I have member table and post table. What I need is to keep track of what posts have been read by which members. What I thought about is to have a junction table with two columns: member_id and post_id. Each time a post is seen (read) by a member a record is inserted.

I am going to need to fetch/calculate/perform:

Number of unread posts for each member
Get all unread posts for each member
Given list of posts, indicate which ones are unread by particular
member
Mark all posts as read for each member separately

And I am going to need to do this on every request, since 1) and 2) is displayed in sidebar widget present on all pages and 3) is going to be needed when browsing through posts (also very common).

The model works in theory (and in practice under not much load), but sure it does not seem like a scalable solution. I am going to get ~1,000 posts per day viewed by ~500 members which gives 500,000 records a day in junction table...

I am a programmer not DB expert, thus I am asking for some ideas on how to optimize this situation.

It may be better to mark unread entries - read entries will accumulate over time. Unread entries you could kill after let"s say 90 days (show all stuff older than 90 days as read to avoid buildup.

You may also want to just have a top marker per forum - i.e. you can not have a specific post unread. This will seriously cut down on the number of database tnreis.


I need help resolving a retrieval problem I have the following tables. I need to update the download.refer to show the ip_lookup.id value where (download.ADDRESS >= ip_lookup.start_ip AND download.ADDRESS <= ip_lookup.end_ip) AND download.FILENAME is not NULL. The ip_lookup table has more than 5M records with both IPv4 and IPv6 ip's. I need the download.refer field filled for reporting what ip downloaded which files along with country, area, and city. Generating the report in real time with a join takes over 7 minutes. I guess I could add the country, area, and city fields to the download table and use a trigger to fill them when a record is added but that seems like a waste of space. Does anyone have any suggestions?

CREATE TABLE `download` (
  `ID` int(25) unsigned NOT NULL AUTOINCREMENT,
  `LOG_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ADDRESS` varbinary(16) NOT NULL,
  `FILENAME` varchar(60) CHARACTER SET utf8 COLLATE COLLATE=utf8_general_ci; DEFAULT NULL,
  `refer` int(25) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `ip_lookup` (
  `id` int(25) UNSIGNED NOT NULL AUTOINCREMENT
  `start_ip` varbinary(16) NOT NULL,
  `end_ip` varbinary(16) NOT NULL,
  `continent` varchar(2) COLLATE utf8_general_ci NOT NULL,
  `country` varchar(50) COLLATE utf8_general_ci NOT NULL,
  `area` varchar(50) COLLATE utf8_general_ci NOT NULL,
  `city` varchar(50) COLLATE utf8_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Thanks in advance

EDIT: This is the query that I used to generate the report that took 7 minutes:

select
  d.ADDRESS,
  i.country,
  i.area,
  i.city
from download as d
inner join ip_lookup as i on d.ADDRESS
where (inet6_aton(d.ADDRESS) >= inet6_aton(i.start_ip)  AND (inet6_aton(d.ADDRESS) <= inet6_aton(i.end_ip)) AND d.FILENAME is not null and d.country is null;

varbinary can be used directly in a SQL range:

SELECT d.ADDRESS,
       i.country,
       i.area,
       i.city
FROM download AS d
INNER JOIN ip_lookup AS i
   ON d.ADDRESS BETWEEN i.start_ip AND i.end_ip
      AND length(d.ADDRESS) = length(i.start_ip)
WHERE d.FILENAME IS NOT NULL
  AND d.country IS NULL;

The lengths are a double check they are comparing same IP versions. It may not be needed.

To ensure the ip_lookups are quick

CREATE INDEX ip_range ON ip_lookup (start_ip, end_ip)

If you aren't using the id of ip_range maybe the above should be the primary key.


Dodgy T-SQL Query Execution driving me crazy SQL Server 2019.  Here's a link to a gist with the xml plan.
Hello, I am having a heck of a time understanding why this query is taking 0.02 seconds to execute when it can't find a record with one of the included statuses. When it finds a record that has one of the included statuses it tends to be much faster. I am guessing that is because the query stops once it finds 1 row that matches.
SELECT TOP 1 IDNum,
             FORMAT(Date, 'M/d/yy') AS theDate,
             Status,
             Rate
FROM   theDB
       INNER JOIN DomainTable
               ON theDB.IDNum = DomainTable.IDNum
WHERE  DomainIP = '127.0.0.1'
       AND status IN ( 'Active', 'To ReActivate', 'To Deactivate', 'Deactivate ASAP',
                       'SUSPENDED', 'SUSPENDED X', 'SUSPENDED Y', 'SUSPENDED Z' )
ORDER  BY theDB.IDNum DESC

(DomainIP belongs to DomainTable the others belong to theDB)
In the execution plan the largest cost is the TOP N SORT at 33% There is a clustered Index seek at 29%
There is a key lookup on DomainTable that is using 29%
The Index seek for the IP on DomainTable is 9%
My questions are:
is there any way to get that TOP N to not be so heavy?
0.02 seconds isn't that slow but also this query is pretty light. So I would like to optimize it as much as possible.
There are only four entries in DomainTable where the IP is 127.0.0.1 so it is essentially taking 0.2 sec to decide which one of those to return and it turns out the answer is none of them because the status isn't right on any of them.  Is there not a way to just create an index that keeps all of this information in RAM or something?

is there any way to get that TOP N to not be so heavy?

I think there's a little bit of a misunderstanding here about how execution plans work.

That number is just the estimated cost, which is a model SQL Server uses to determine what the most efficient execution plan will be.  It's not updated at runtime, so even if the operators used very little resources, the estimated costs are still displayed as they were when the plan was created.

Looking at the runtime plan you provided, Sentry One Plan Explorer makes it a little easier to see that some of those "expensive" operators didn't run at all (they are "greyed out"):

  I am having a heck of a time understanding why this query is taking 0.02 seconds to execute

I'm not sure how you're measuring the time here, but the execution plan indicates that the entire query took less than one milliseond to run, and took 9 milliseconds to compile.  See the excerpts from the plan XML:

<QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="48" CompileTime="9" CompileCPU="9" CompileMemory="736">
...
<QueryTimeStats CpuTime="0" ElapsedTime="0" />

Max has some good suggestions on general improvements to the query, definitely check those out.  But I thought it was worth pointing this out as well.

You might be able to eliminate the TopN Sort with an indexed view, but I'm not sure it's worth the overhead at this point.


Understanding where pg_toast is coming from I've got a fair amount of storage in pg_toast

             relation             |  size
----------------------------------+---------
 pg_toast.pg_toast_43934449       | 87 GB
 pg_toast.pg_toast_43934438       | 64 GB
 pg_toast.pg_toast_50877          | 35 GB
 pg_toast.pg_toast_16715          | 15 GB
 pg_toast.pg_toast_16813          | 13 GB
 pg_toast.pg_toast_5706469        | 1335 MB
 pg_toast.pg_toast_43934449_index | 1004 MB
 pg_toast.pg_toast_43934438_index | 942 MB
 pg_toast.pg_toast_16715_index    | 709 MB
 pg_toast.pg_toast_16813_index    | 548 MB
 pg_toast.pg_toast_50877_index    | 530 MB
 pg_toast.pg_toast_3518414        | 463 MB
 pg_toast.pg_toast_16994          | 339 MB
 pg_toast.pg_toast_46608          | 310 MB
 pg_toast.pg_toast_16994_index    | 92 MB
 pg_toast.pg_toast_22345124       | 68 MB
 pg_toast.pg_toast_46608_index    | 51 MB
 pg_toast.pg_toast_437018         | 43 MB
 pg_toast.pg_toast_5706469_index  | 15 MB
 pg_toast.pg_toast_3518414_index  | 13 MB
(20 rows)

Which is significant when the total size of the DB is currently about 420GB. This is totally expected as some of my tables are storing JSON as either text type (for some of my older tables) or jsonb for some of the newer ones.

A lot of these columns can be cleaned up at the application-level by just deleting some older data. The problem is that it's hard to know what's actually contributing to the pg_toast tables?

How can I reverse-trace a pg_toast to the actual row/column reference of another table?

You can use this query to find out all tables with TOAST tables:

SELECT oid::regclass,
       reltoastrelid::regclass,
       pg_relation_size(reltoastrelid) AS toast_size
FROM pg_class
WHERE relkind = 'r'
  AND reltoastrelid <> 0
ORDER BY 3 DESC;

To find out which of the columns in the table consumes most space, you could try a query like

SELECT sum(length(col1)) AS col1_size,
       sum(length(col2)) AS col2_site
FROM some_table;

Here col1 and col2 would be text, varchar, char or bytea columns, which are usually the largest ones. With other data types like jsonb you could cast to text to get an estimate.


A more efficient way to write multiple CASE statements SELECT CASE WHEN ( Table.COLUMN1) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( Table 1.FULL) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( CASE WHEN Table 3.DO = 'Z' THEN 'Y' END ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( NVL(( Table2.COLUMN3), ( Table2.COLUMN3 )) ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table 4. COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( NVL(( Table2.COLUMN3), ( Table2.COLUMN3)) ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( Table 3.FULL) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( ( Table2.COLUMN3) ) END ) IS NOT NULL THEN 1 ELSE 0 END,

  CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  then CASE WHEN ( CASE WHEN ( CASE WHEN ( Table.COLUMN1 ) IS NOT NULL AND ( Table.COLUMN2)
  IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( ( Table2.COLUMN3) ) END ) IS NOT NULL AND ( Table.COLUMN1) - ( CASE WHEN ( CASE WHEN ( Table.COLUMN1)
  IS NOT NULL AND ( Table.COLUMN2) IS NOT NULL
  THEN CASE WHEN ( Table.COLUMN1) - ( Table.COLUMN2) >= 180 THEN 'Y' ELSE 'N' END ELSE 'N' END ) = 'Y'
  THEN ( ( Table2.COLUMN3) ) END ) < 90 THEN 'Y' ELSE 'N' END END ) = 'Y' THEN 1 ELSE 0 END,

Is there a more efficient way to write this statement?

Here is one way to rewrite the query that should give the same results without nested CASE statements. I'm checking for NULLs first, to potentially short-circuit additional testing. I considered using a single WHEN, with an OR between each expression, but OR isn't deterministic so this may perform better. If those columns are mostly null (vs mostly filled), you could try rewriting it to test for IS NOT NULL instead of IS NULL, and see if there is a performance difference:

CASE
    WHEN
        Table.Column1 IS NULL
        OR Table.Column2 IS NULL
        OR Table1.Full IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
    THEN 1
    ELSE 0
END,
CASE
    WHEN
        Table.Column1 IS NULL
        OR Table.Column2 IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
        AND Table3.DO = 'Z'
    THEN 1
    ELSE 0
END,
CASE
    WHEN
        Table4.Column1 IS NULL
        OR Table.Column2 IS NULL
        OR Table2.COLUMN3 IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
    THEN 1
    ELSE 0
END,
CASE
    WHEN
        Table.Column1 IS NULL
        OR Table.Column2 IS NULL
        OR Table3.Full IS NULL
    THEN 0
    WHEN
        Table.Column1 - Table.Column2 >= 180
    THEN 1
    ELSE 0
END,
...


How do I delete unique rows from a table with duplicate keys? I have the following data:

      road        |  ID  | other column  | other column |
------------------+------+---------------+--------------+
MAIN ST           |  1   |     ...       |     ...      |
MAIN ST           |  2   |     ...       |     ...      |
MAIN ST           |  3   |     ...       |     ...      |
BERRY LN          |  4   |     ...       |     ...      |
BERRY LN          |  5   |     ...       |     ...      |
BERRY LN          |  6   |     ...       |     ...      |
JOHN RD           |  7   |     ...       |     ...      |
JOHN RD           |  8   |     ...       |     ...      |

I want all rows with duplicates in the road column removed. It does not matter to me which duplicates are removed. The end result can look something like this:

      road        |  ID  | other column  | other column |
------------------+------+---------------+--------------+
MAIN ST           |  3   |     ...       |     ...      |
BERRY LN          |  4   |     ...       |     ...      |
JOHN RD           |  8   |     ...       |     ...      |

where the id's are any value, as long as the road column is unique.

Is there a simple way to do this in SQL or T/SQL? I followed the guide here, but it only applies to duplicate keys with non-unique rows.

By using the ROW_NUMBER() ranking function, you could achieve that.  Here's an example below.  The top two queries are just for data verification.  I'm a firm believer that you should see what you will be deleting (and what will remain) before actually deleting data.

(Verification) Rows that will be deleted

;with cte as
(
    select
        *,
        row_num =
            row_number() over (partition by road order by ID)
    from dbo.YourTable
)
select *
from cte
where row_num > 1;

(Verification) Rows that will not be deleted

;with cte as
(
    select
        *,
        row_num =
            row_number() over (partition by road order by ID)
    from dbo.YourTable
)
select *
from cte
where row_num = 1;

Data Delete

Note: the below query, when modified to fit your environment will actually delete data.  Therefore, you should ensure that you really want to delete the data, and also have a backup of the data/database prior to performing any data modification.

;with cte as
(
    select
        *,
        row_num =
            row_number() over (partition by road order by ID)
    from dbo.YourTable
)
delete from cte
where row_num > 1;


How to create test environment for lost transactions I have a problem that I hope you can help me to solve. I'm using SQL Server 2008 R2.

I need to create a test environment in which I'll be able to artificially
'create' lost transactions. Due to network disconnects in the app I'm using, sometimes (very, very rarely) there are 'broken' transactions (some part of the data update goes to DB, but the rest is not which results in incomplete data in database). On my test machine I don't have a clue how to do it.

I know that's the problem with app design but I need to prove that this kind of behaviour is appearing so that the vendor of the app can fix this bug.

I really hope that you can help me out. I'm not a dba - I learn what I can to become one so if this question is 'elementary' please be understanding :)

The easiest way is to look into the transaction log and see where there is a sequence that you can 'split'. Start by reading How to read and interpret the SQL Server log to understand what I'm talking about.

Then look into your DB log for a sequence like this:

  lsn1 BEGIN XACT xid1
  lsn2 some update
  lsn3 COMMIT xid1
  lsn4 BEGIN XACT xid2
  lsn5 some update
  lsn6 COMMIT xid2

This sequence shows two operations that occurred in the DB in two distinct transactions. You want to show what happens if xid1 commits, but xid2 rolls back. Since catching the app right in between those two updates is difficult, you can simply restore the DB with STOP AT (see Recover to a Log Sequence Number (SQL Server)) and stop the recovery after lsn3 but before lsn6 (that is, xid 1 commits but xid2 will be forced to rollback). Now you have your DB in a logical inconsistent state (the first operation succeeded, the second failed).


How to automatically stagger transaction log shipping to minimize bandwidth usage peaks? The following question pertains to Microsoft SQL Server transactional log shipping (TLS).
We're using SQL Server 2008 R2 SP1, although the question is probably relevant for all recent editions.
Background
I have a primary data center (A) and a secondary, disaster recovery data center (B).  Let's say I have 64 databases that need to be log shipped of varying sizes, but no one database exceeds 50GB.
I'm currently using the default SQL Agent Jobs that SQL Server automatically creates, so I have 64 SQL Agent Jobs.
I want to log ship every database every 15 minutes.  Assume that's possible to do if I just transferred all the files back-to-back.
Issue
Let's also say that the path from A to B travels over the public internet, and so I am paying for the bandwidth to ship these logs.  I'm metered at the 95th percentile.  I want to smooth out or optimize bandwidth so as to minimize the chance of paying overages. I'm using backup compression.
Possible Resolution
My current idea is to write a script that will customize the start time of each backup job automatically. This script could be run as frequently as desired to maintain optimal transfers. Having it run regularly will enable new log shipping backup jobs for newly added databases to be optimized without human intervention.
The current SQL Agent Jobs all start with the string "LSBackup" such that I can list them all using:
SELECT * FROM msdb..sysschedules WHERE name LIKE 'LSBackup%'

I can fetch the list of all the SQL Agent Jobs and store them in a table variable, then iterate in a WHILE loop calling EXEC msdb.dbo.sp_update_schedule to update the @active_start_time to space the jobs appropriately.
What value should I use for the start time for each job?
In order to answer this I need to know how big the log backup file might be in order to optimally space the jobs.  Can you predict how large a transaction log backup will be before you run the backup? Alternatively, I could look at the past days' log backups on the local file system to determine the relative weights for each database. However, that's not really going to work for a brand new database that has little to no Transaction Log backup history.
Assuming it is possible to determine how relatively large each backup will be, what's an optimal algorithm to space them out to achieve minimal bandwidth impact when being metered at the 95th percentile?

DBCC SQLPERF (LOGSPACE) will return the size of the log file in MB & the current percentage used. From that you can calculate the current utilised portion of the log file in MB.

That by itself will not be sufficient to calculate log backup size, not least because you are using backup compression. However collecting this data over time & correlating it with the size of the backups might reveal a linear relationship.

You could insert a step into the Log Shipping backup job to record the size of the utilized portion of the log file and a another step after the backup to record the new size of the log file and the size of the backup.

Running this for a period and analyzing the results could allow you to estimate the size of the backup file.

Not the most straight forward method and to be honest it might not even work. If you are interested in giving it a go I would be interested in helping.


Sql service account password change for environment on Availability groups 2012 My current environment is on Availability groups sql 2012 with 1 Synchronous and 1 Asynchronous replica. Soon we need to change the sql service account passwords.

Can any one suggest to me do I need to take any extra step(s) while changing the passwords? so that it will not disturb any existing settings or configurations?

Hope below steps are correct:
1. Changing the sql service account password on primary server, then secondary servers and restarting them
2. Ensure the data synchronization is properly running.

I would do it in this order:

Change on Async Replica and restart SQL.

Make sure everything is still synchronizing after restart

Change Sync Replica and restart.

Make sure everything is still synchronizing after restart

Failover to Sync Replica
Change on former Primary (Current Sync Replica), and restart.

Make sure everything is still synchronizing after restart

You can then fail back to the old primary if need be, or leave on the Sync Replica if all things are equal.


How to compare two SQL Servers instance level objects for a migration? I'm currently working on a SQL Server migration (from a SQL Server 2005 to a 2008 R2 server, on a new physical server).

The databases themselves will be transferred using a database backup and restore script.
I have tested this fine and it works without problems.

My worry is rather regarding all the other instance/system objects, like Logins, SQL Agent jobs.
These have been scripted out from the source server and reapplied on the new server, and I'm afraid I have forgot some.
I can obviously visually compare using SQL Management Studio, which is what I did, but it is time-consuming and error-prone.
So, before plugging the plug off the existing server in due time, I would prefer to have a more "scientific" way to confirm that everything is in there...

I've looked for software doing this kind of comparison and have found only schema or data compare tools.
I guess I could compare the system tables like sys.sql_logins and msdb.dbo.sysjobs, etc, but I don't know them all.

In short, is there a way to quickly compare the instance-level objects of two servers, and if possible, their contents as well rather than just by name?

Thank you.

If you're only concerned about Agent Jobs and logins, I think the easiest approach is to leverage a linked server and querying the relative tables.  I would do this by first creating a linked server on the old instance that points at the new instance.  Once I had that, I could run the following queries:

Logins

SELECT * FROM [SQL2005].[master].[sys].[server_principals]
WHERE name NOT IN (SELECT name FROM [SQL2008].[master].[sys].[server_principals])

Agent Jobs

SELECT * FROM [SQL2005].[msdb].[dbo].[sysjobs]
WHERE name NOT IN (SELECT name FROM [SQL2008].[msdb].[dbo].[sysjobs])

There might be some inconsistencies with some server specific accounts/logins, but these queries should show you everything that exists in the old server that doesn't exist in the new server.  You should be able to extend this logic to other facets as long as you had system tables to query for it.

As for objects that need to be synchronized, I think the best approach is to review your instance for the objects that should be migrated.  Typically this would only be logins and Agent Jobs, but could also include linked servers (sys.servers), end points (sys.endpoints), and other objects.


MySQL schema design - how to decide between an `enum` vs. a table join In designing a MySQL schema, should I avoid using an enum type altogether, as I have been reading really strong, but favored opinions on this subject, such as:

Stackoverflow post on efficiency of enum vs a join
enum is data, not code
Blog: Reason why MySQL's enum data type is evil
Right time to use an enum

Or, is there a choice to be made specific to the use case in hand? If there is, what decision framework should I use, i.e. when to choose one of the two options?

For instance:

Currency: It's better to use a table join, since currency codes to be supported could change with time (since modification has been emphasized in many posts as anti-enum, but then what could not change with time?).
PaymentStatus: It's better to use enum type with values like, SUCCESS, FAILED, CANCELLED.
TierPlans: It's better to use a table join, as it would give the flexibility to add columns like monthlyPricing, annualPricing, discountRate, etc. in the future. (I am currently using enum instead, with values like FREE, PRO, MASTER; hadn't thought of pricing yet -_- ).

  I am finding that if I stress enough, I could find a table join being a better choice always, mutation safe route. Is increasing number of tables or table joins a symptom of bad design? How should I approach
  design in general, and design in such use cases specifically?

Note: I had asked this question on SO here. I am posting it here since I see that there is a closing request there, and I desperately need an answer to this confusing roadblock of mine. Understandably this might not be a good question to ask here too. I'd rather edit, if you may guide!

(Yes, there have been many heated discussions of ENUM vs TINYINT vs VARCHAR, both here and on stackoverflow.com.  I have put my 2-cents in on some of them.  I am addressing this Question to discuss nuances of the examples brought up.)

Currency is a tricky one.  When, for example, a devaluation occurs, does the currency_code change?

If so, then is the question is about how often the list of values is likely to change.

If it does not change, then you may have a mess for the data -- Is "XYZ", the old xyz currency, or the 'new'?

With things like country_code (with well-established 2-letter codes) and currency_code (3 letter standard), etc, I recommend using things like

country_code  CHAR(2) CHARACTER SET ascii
currency_code CHAR(3) CHARACTER SET ascii

Rationale:

Only 2 or 3 bytes, which is not much bigger than a 1-byte ENUM or TINYINT.
Avoids the extra bulk and overhead of utf8mb4, which might be the default for the table.
You can still build a secondary table to, for example, spell out the full name of the country or currency.
You won't need a JOIN except when you want the extra info.  (JOINing is most costly when you need to start from multiple Dimension tables in an attempt to look up rows in the Fact table.  A JOIN is reasonably cheap going the other direction.)


How do I minimise logging during a large insert? During a nightly scheduled task I am creating a large table from scratch using select into and then adding a clustered index. I would like to minimise the amount of logging during the whole process as I have limited bandwidth for log backup shipping.

I'm happy to consider an alternative approach if that would help.

In my experience, the best way to do this is to:

Create the EMPTY table
Create the clustered index (matching your source table)
INSERT using TABLOCK hint for minimal logging

Details are included in this MS Whitepaper.

Creating the table first, then indexing it on  the same key as the source eliminates sorting.

If the keys match, TABLOCK is used, and trace flag 610 is enabled, the operation will be minimally logged (logging pages not transactions or rows).

For me this is faster as adding the clustered index after SELECT INTO forces a sort which can be very costly and time consuming.

EDIT:

TF 610 is used solely to control minimal logging behavior when inserting into indexed tables.

I'm not aware of any issues with using it, but MS recommends (rather defensively I think) to test before using.


AD group cannot connect to SQL from excel We have a data warehouse and users are trying to browse the database directly from excel. We put those users in an AD group and give them read only permission to a couple of databases.

The problem is under such circumstance users are not able to establish connection from excel to SQL Server. "login failed for user ***" will be shown as error message.

However, if we add every single user's login to SQL Server security, they are able to access to databases from excel then.

Does anyone have any idea why this happens?

Ah group permissions in SQL Server have never worked out for me right off the bat. I would check this question and make sure you are setting it up right on the SQL side https://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server

Also I agree with John, if you add a member to a group they need to log off and back on again before AD will update with the new group.


In what way are Access tables linked with SQL server? I have a mdb file with lots of OBDC linked tables that are linked to the tables in SQL server elsewhere.
I can not see any information on how they are "linked". It is a little scary not knowing whether an accidental key press will effect the live database.

If the SQL server table is modified will it be automatically reflected in the table and when?
If I edit the linked Access table will it be reflected in the SQL server table?
(Trusted_Connection=YES any significance?)

If the SQL server table is modified will it be automatically reflected in the [linked] table and when? Yes, on next refresh or requery.
If I edit the linked Access table will it be reflected in the SQL server table?
Yes; as before, these updates will be visible on the other end on next refresh or requery.
Trusted Connection only controls how the connection is made to SQL Server, not behaviour after a connection is made.

The key concept here is that linked SQL Server tables are truly linked to ACCESS, not copied. If you desire a private copy of the data, copy the tables down instead of linking them.


Generate create table DDL from any arbitrary select query? Using allroundautomations PL/SQL Developer, is there a way, given any arbitrary select query like this:

select myfavoritenum, myfavoritetxt
from tablewithmanycolumns

to magically receive a create table statement:

create table justmyfavorites (
    myfavoritenum number,
    myfavoritetxt varchar2(30)
)

I'm aware of CREATE TABLE AS SELECT, but I don't necessarily want to create the table right then, or in that particular database, etc.

Attempting to Google up a solution, I also became aware of DBMS_METADATA.GET_DDL, but that appears to be for existing objects, not arbitrary queries.

One way to accomplish this, using Java, would be to prepare the query statement, then use the PreparedStatement.getMetaData() method to obtain the ResultSetMetaData object that has the list of columns that would be returned, as well as their data types.

Other database interfaces, such as ODBC and OCI, have similar functionality.


excessive CPU utilization by SQL server I have an issue with SQL server 2000. Its CPU utilization is high from 9.00 AM to 5.00 PM. Three applications use this server through three different instances but when the CPU utilization is high one instance is connected. I checked sp_who2 results which shows that there are many AWAITING COMMANDS and it takes up most of the CPU time. Please advise if more number of awaiting commands can cause high CPU utilization.

High CPU in SQL Server is very often caused by poor indexing

Unfortunately, SQL Server 2000 lacks the tools of later versions to track these down easily

Saying that, if you run SQL Profiler you will be able to find high CPU queries and start looking at query plans to work out what indexes are missing


Is it Important to Understand Each Normal Form? I have been studying database design and programming for quite some time now, but I still can't get a grasp of understanding each individual normal form (that is, 1NF, 2NF and 3NF).

Seeing as anytime the data is in Third Normal Form, it is already automatically in Second and First Normal Form, can the whole process actually be accomplished less tediously by fully normalizing the data from the start. I can accomplish this easily by arranging the data so that the columns in each table, other than the primary key, are dependent only on the whole primary key.

Questions

How important is it to understand each individual normal form if we can simply fully normalize the data less tediously by doing what I have described?

What I'm ultimately asking is: Is it important to go through the steps of each normal form when normalizing data, or is it appropriate to just go to Third Normal Form seeing as the result is ultimately the same?

Codd formulated the normal forms as a tool to help evaluate your data model for correctness:

  "To free the collection of relations from undesirable insertion,
  update and deletion dependencies; To reduce the need for restructuring
  the collection of relations, as new types of data are introduced, and
  thus increase the life span of application programs; To make the
  relational model more informative to users; To make the collection of
  relations neutral to the query statistics, where these statistics are
  liable to change as time goes by."

You are absolutely correct that if you design your data model and the resulting schema very well to begin with, it will already be in at least 3rd normal form.

That said, I believe that it is crucial to understand the relational model, and the normalization rules as helping guides. When you say:

  "I can accomplish this easily by arranging the data so that the
  columns in each table, other than the primary key, are dependent only
  on the whole primary key."

You are using the 3rd normal form.
A relation in 3NF, must also be in 2NF and 1NF.

You are asking if you need tools to validate your design, which is something no one can answer.
If you design a car, will you use available tools to validate that it is safe, fast, and reliable? or will you just trust that your original design achieved these goals without testing it with any tools?


DAC Connection using the SSMS for executing the code Can anyone help me on below . I am trying to execute the SQLCMD with DAC (-A switch) but unable to do so .

How can I do it both ways below :

Execute in SSMS Query Pane
Execute using PowerShell

Below is what I have tried with no luck .

EXEC master.dbo.xp_cmdshell "SQLCMD -S SQLServer -d Db1 -A
go
select count(*) AS FileCount from TableName
go"

output

  'select' is not recognized as an internal or external command,
  operable program or batch file. NULL

Message

  Msg 103, Level 15, State 4, Line 1 The identifier that starts with '

SQLCMD -S SQLServer -d Db1 -A
go
select count(*) AS FileCount' is too long. Maximum length is 128.

You should pass in your query according to sqlcmd syntax specifying -Q (or -q or -i for the script file) like this:

EXEC master.dbo.xp_cmdshell 'SQLCMD -S SQLServer -A -d Db1 -Q"select count(*) AS FileCount from TableName" '


Why might a table scan have a huge I/O Cost? I know that table scans are bad, and that every table should have a primary key and clustered index, but I'm wondering what might cause the spectacularly bad performance that I'm seeing below in SQL Server 2008 R2.

For testing's sake, I am doing a SELECT * FROM myTable on a table/heap with 1960 rows.  The table has the following columns: (int, datetime, int, int, float, smallint, uniqueidentifier). There are no indexes.  This table has had a long history of inserts and deletes.

I get the following stats from the table scan in the query plan:

<RelOp
  AvgRowSize="57"
  EstimateCPU="0.002313"
  EstimateIO="74.4157"
  EstimateRebinds="0"
  EstimateRewinds="0"
  EstimateRows="1960"
  LogicalOp="Table Scan"
  NodeId="0"
  Parallel="false"
  PhysicalOp="Table Scan"
  EstimatedTotalSubtreeCost="74.418"
  TableCardinality="1960">

And from SET STATISTICS TIME ON:

Table 'myTable'. Scan count 1, logical reads 100458, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

What factors could lead to such a small number of small rows requiring such a massive number of reads (and have the query plan predict the same)?

As per the comments:

See SQL Server “empty table” is slow after deleting all (12 million) records – Remus Rusanu

The answer to this question is: Ghosted Records, subsequently confirmed by the original poster. Posting here for completeness.


how to safely stop mysqldump? I (well, my cron script) tried killall mysqldump and that didn't end very well - mysql server stopped accepting connections after a while.

It was Debian Jessie machine with mysql 5.5.55-0+deb8u1.

Usage scenario was:

there was a long running (few hours) SELECT query which was either really that slow or the client that sent it was having problems (query state was Sending data), but all the other queries were happily coming and going (only the load was perhaps little higher).
in the night backup was being run with mysqldump --max_allowed_packet=2147483648 --hex-blob --single-transaction --master-data --routines --order-by-primary --databases db1 db2 db3... | pigz -p8 > backup.sql.gz. It never finished, probably because it was waiting for the SELECT above to finish first (guessing here - it was only thing which looked out of ordinary, and same setup worked OK for months).
cron job run in the morning, which had killall -q mysqldump which was supposed to safely terminate backup in case it was not finished by set time (notifying the admin to examine and fix the problem later), thus allowing people to continue working with mysql server normally.
result however was full connection table and thus no user able to log in to mysql server. There was FLUSH /*!40101 LOCAL */ TABLES query stuck in Waiting for table flush and hundreds of SELECT queries stuck in same Waiting for table flush state.
in addition, admin killing LOCK TABLES mysql query didn't help, as other SELECT queries remained in Waiting for table flush (which seems to be intended behavior?)

Restarting mysql server finally "fixed" the problem.  However, Wanting to avoid this situation (and emergency admin interventions) repeating , I'd like to safely terminate mysqldump backup in Debian Jessie mysql-5.5.55 (or upcoming Debian Stretch mariadb-10.1.23-8). Is there a way?

If not, what are other options for accomplishing mysql backup and avoiding server load in the morning (which is - in this case - almost as bad as completely hung server)?

(I'd like to stay with Debian Stable packages if at all possible)

Since you are using --master_data to take a consistent value of master status.

The internals of mysqldump will issue below commands to mysql server.

2017-05-31T04:39:05.843130Z    48 Query /*!40100 SET @@SQL_MODE='' */
2017-05-31T04:39:05.843273Z    48 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-05-31T04:39:05.843411Z    48 Query FLUSH /*!40101 LOCAL */ TABLES
2017-05-31T04:39:05.846031Z    48 Query FLUSH TABLES WITH READ LOCK
2017-05-31T04:39:05.846166Z    48 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-05-31T04:39:05.846279Z    48 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-05-31T04:39:05.846413Z    48 Query SHOW MASTER STATUS
2017-05-31T04:39:05.846539Z    48 Query UNLOCK TABLES
..
..... Here it continues to take backup of data and structures .

What had happened?:

Your backup would have just started and there was a query that was going on running on a particular table for a longer period of time before even FLUSH TABLES command and didn't release the lock on the table and FLUSH TABLES must have been waiting for that thread to get completed or keep trying to flush until the revision_version of that table is same as all tables.

Thus you get other threads blocked for other tables as well. As this is entire DBs*.Tables* level lock while flush tables was going on. Finally it would have got accumulated every new connection in processlist and piled up until max_connections and not allowing anyone to login.

Let's say if you have managed to login to the terminal and tried to kill flush tables, I don't think there is a way to pull back or rollback the flushed tables that has been done and release its own thread connection. So it might in a KILLED STATE for longer time. And thus you might have reached the last option that is to restart the server.

How to fix it?:

At the time of issue, when admin managed to login to mysql prompt.

Instead of issuing kill command on FLUSH TABLES thread, if kill was given to the thread running for long SELECT.There are chances that SELECT would have dropped and table is left FLUSH TABLES to acquire and update revision_version and release the lock for new queries. And backup would have continued. Since I don't think anyone expecting an answer on the other end waiting for the result for query running long hrs.

What is the long term solution?:

You have to ensure no such long time queries to be running at the time of backup.
Looks like this could be a new deployment or someone triggered a bad query and didn't bother to close the session.
Try to kill the query if a query is running more than Xsecs (Depends upon your requirement).
      Or
Have an adjustment with the teams to tune the queries or send it to Admin to give them the result data or give them a separate slave all together.


Order the result of a comma separated string generated with STUFF Given a schema like this:

CREATE TABLE Foo
(
    Id int PRIMARY KEY,
    Position int NOT NULL,
    Title varchar(10) NOT NULL
);

INSERT INTO Foo VALUES
(1, 3,  'Title3'),
(2, 10, 'Title10'),
(3, 1,  'Title1'),
(4, 12, 'Title12'),
(5, 2,  'Title2');

I need to generate a comma separated string ordered by Position:

'M' + Id + ' AS [' + Title + ']'

Desired result:

M1 AS [Title1], M2 AS [Title10], M3 AS [Title3], M4 AS [Title10], M5 AS [Title12]

I've tried:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title))
                   FROM Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,1,'');

SELECT @rows;

But it builds the result ordered by Id:

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]

If I add ORDER BY Id to the STUFF expression:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title))
                   FROM Foo
                   ORDER BY Position
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,1,'');

SELECT @rows;

Produces the next error:

  Msg 145 Level 15 State 1 Line 2
  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
  +

I can use a subquery ordered by Position:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title))
                   FROM (SELECT TOP 100 PERCENT Id, Position, Title FROM Foo ORDER BY Position) X
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,1,'');

SELECT @rows;

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]

But I wonder if there is another way to order the result without using a subquery. There are no duplicated titles.

dbfiddle here

As Aaron Bertrand pointed out in comments, the error is produced because I'm using SELECT DISTINCT; and in this example it is not necessary. Also, ORDER BY position is fully valid if the distinct is removed.

If DISTINCT is removed the query returns the desired value:

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title))
                   FROM Foo
                   ORDER BY Position
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,1,'');

SELECT @rows;
GO

| (No column name)                                                                  |
| :-------------------------------------------------------------------------------- |
|  M3 AS [Title1], M5 AS [Title2], M1 AS [Title3], M2 AS [Title10], M4 AS [Title12] |

dbfiddle here

I recommend having a look at this article, recommended by sp_BlitzErik:

Grouped Concatenation : Ordering and Removing Duplicates


How can I list Oracle editions granted? In using Oracle Edition Based redefinition, I am receiving the error:

ORA-38802: edition does not exist

when issuing the call:

DBMS_SESSION.SET_EDITION_DEFERRED("my_edition")

I know the edition exists because I can query the DBA_EDITIONS view and I can see the edition.  The problem must be that the user must not have been granted use privs on the edition.  I'd like to be able to verify which users have been granted use privileges on which editions.  How is this done?

DBA_TAB_PRIVS is what you're looking for. A demo is the best way to illustrate.

Create the edition.

SQL> create edition wibble;

Edition created.

SQL>

No grants yet:

SQL> select table_name as edition_name, grantor, grantee
  2  from dba_tab_privs
  3  where privilege='USE'
  4  and table_name='WIBBLE';

no rows selected

SQL>

Edition created, shouldn't be able to use it.

SQL> conn phil/phil
Connected.
SQL> alter session set edition = wibble;
ERROR:
ORA-38802: edition does not exist

Grant:

SQL> conn / as sysdba
Connected.
SQL> grant use on edition wibble to phil;

Grant succeeded.

SQL>

Should be able to use it now:

SQL> conn phil/phil
Connected.
SQL> alter session set edition=wibble;

Session altered.

SQL>

Grants are now visible:

SQL> select table_name as edition_name, grantor, grantee
  from dba_tab_privs
  where privilege='USE';

EDITION_NAME                   GRANTOR GRANTEE
------------------------------ ------- -------
ORA$BASE                       SYS     PUBLIC
WIBBLE                         SYS     PHIL

SQL>