Implementating user notifications list using Aerospike

Since ScyllaGreg is putting in the good word for Scylla, I thought I'd represent for Aerospike, where I work. Of all the C* variants I like ScyllaDB the best, but I believe that in the case you describe, and in general for row-oriented situations, Aerospike Database will perform better than ScyllaDB using less hardware. I've written a few Medium articles (@rbotzer) explaining why, based on realistic use cases.
Modeling
Here's one possible way to model this in Aerospike.
I am assuming that you always want to access the notifications of a specific user. For that reason, the key for a record (a row in Aerospike-speak) would be the user_id. The rest of the data fits in a single bin containing the following key-ordered Map structure:
{ epoch: [ item_type, item_id, other_data ] }

Where epoch is a way to simplify the created_at datetime into an integer representing minutes, seconds or milliseconds since an arbitrary epoch, such as the date your app went live (ex: minutes since 2020-10-01 00:00). You can choose the resolution that makes sense to you in order to avoid an overwrite. Either way, Aerospike uses MessagePack to serialize this map data, and MessagePack has the nice side effect of rightsizing numeric values into a minimal storage size.
Querying
Now to query this data we'll use the Map operations API:

Adding any information is a simple Map put or put_items, which upsert the data into the Map.
To get all the notifications for a specific user, you just get() the record by user_id.
To paginate through the notifications for a specific user you use Map get_by_index_range(KeyValue, i, 10) with i = 0, 10, 20, ...
To get all the notifications between two points in time (October 2nd) you would use Map get_by_key_interval(KeyValue, 1440, 2880)
To get all the notifications of type 'comment' you would use Map get_all_by_value(KeyValue, ['comment', *])
To clear out all the notifications in October 2020 you would use Map remove_by_key_interval(Count, 0, 44639)

You can see that there's currently no way to AND a query for a date range and a query for item type. However, Aerospike is extremely fast, with the data being retrieved after looking up a single key and operating on the record data, which is stored contiguously. You can easily get slightly more data (everything in a date range) and filter for the item_type on the application side.
Notice that this modeling approach requires zero secondary indexes. It just uses the power of Aerospike's even data distribution, extremely fast primary index  lookups, and single IO record reads from storage.
Future for Querying
Aerospike Database 5.2 added Expressions, which can already do things like chain a Map get_by_index_range => get_all_by_value => get_by_index_range, but only in the context of a filter. This means that you can use a complex expression as a conditional for whether to apply an operation to a query, scan, batch read or single record operation.
In the near future you'll be able to apply an expression to the operation, which will solve the AND limitation. If you wanted to find all the notifications in a time range that are of a specific type and also paginate through them, you would
exp(get_by_key_interval(KeyValue, 1440, 2880), get_all_by_value(KeyValue, ['comment', *]), get_by_index_range(KeyValue, 0, 10))
Alternative Modeling
As I described in Aerospike Modeling: IoT Sensors, you can choose to partition the user notifications by day, with the key being userID:YYYYMMDD.
This would allow you to keep the size of the records reasonable if you expect too many notifications. It also makes it convenient to purge data past a certain age with deletes.
However, I suspect you will not run into this problem. Let's assume a notification is 40 bytes, then 256 notifications take at most 10KiB. In reality those are probably smaller, and as I mention in the article, MessagePack will rightsize some of those data types. If you use (the Aerospike EE feature) compression it'll be smaller still. Even if it is 40 bytes, the default 1MiB write block can hold 26,000 notifications for a single user, and you can trim those daily with a remove_by_value_range.
Another nice thing about partitioning by days is that only one record is ever written to ('today').

Now to address your comment about secondary indexes in Aerospike, it's true that  they're currently stored in memory, however the memory cost isn't as bad as you expect.
First, Aerospike doesn't buffer data in memory like all C* variants. In the classic Aerospike Database deployment you store all the data on SSD, and only the indexes consume memory. If you read my Medium posts, I go into why Aerospike is faster than ScyllaDB at retrieving a random record out of an arbitrarily large stack of records.
Every record has a 64 byte entry in the primary index. As for secondary indexes, the absolute worst case if you have a single key per distinct value indexed (and you should never use a secondary index in such a situation) would cost you 85 extra bytes.
A quick calculation for 1 billion records is 10^9 * (64+85) = 139GiB. If you have a 3 node Aerospike cluster the memory cost is 46GiB per node. Considering that you don't need memory for the data, and there's very little overhead on top of that, you end up with a quite affordable RAM consumption that fits on a small cluster.
Going forward, quite a bit of change will be coming to secondary indexes in Aerospike, however I still suggest modeling in a way similar to described above. Using Map and List operations on a single record will be faster and consume less resources than a query using secondary indexes in a C* database.


Problems on filegroup size -- SQL Server 2005

Removing data from a filegroup never reduces its size. It just frees space to be used for other purposes in the filegroup. In a monthly partitioned table you should rotate the filegroups. You will need 14 partitions for 12 months (you always need an empty partition in the beginning and an empty one in the end) and you do the rotation by switching out the oldest month partition, truncating the switched out table, then switching in a new partition for the next month which reuses the filegroup freed by switching out the oldest month.

If you ever find yourself merging or splitting partitions with data, you're doing it wrong. See How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005.

And, to answer the actual question in the title, an empty filegroup can be 1) shrunk or 2) dropped to reclaim the disk size. Not that I would ever bother with a tiny 200mb size filegroup. But again, you never have to do it if you implement partitioning correctly.


Our final solution was to split the query so we did pivoting and denormalization in separate steps. This is probably a cleaner solution anyway since it follows the "just do one thing" principle so will probably be easier to maintain.

Thanks for all of the help!


Accidently Named all tables in my database starting with a number

In your situation, I would probably use a script something like this to generate all the sp_rename commands for me:

SELECT SCHEMA_NAME(schema_id) as schemaname, name as tablename,
cmd = 'EXEC sp_rename ''' + SCHEMA_NAME(schema_id) + '.' + name + ''', '''
  + SUBSTRING(name, 6, 99) + ''';'
FROM sys.tables
WHERE type = 'U'
AND is_ms_shipped = 0
AND name LIKE '2019[_]%'

This takes the name from the sys.tables system table, and finds all the user-created tables with a name starting with 2019_. It generates a list of sp_rename commands, which you can copy into a new SSMS window and carefully review to make sure they look correct before running.

Note that this is going to break any other objects (views, triggers, stored procs,  etc.) that refer to these tables, so I hope this database is very early in development.


Can I use mysql/mariadb databases on different OS?

Check out the manual here. However, if you're transferring between 32/64 bit, then you'll have to use mysqldump, see here which gives you a text-based dump of the SQL statements you'll have to run to install your database. The commands for mysqldump vary according to whether you're using MyISAM or InnoDB, so just be aware of that.

On Linux, there are more tools available than for Windows - personally I would recommend Linux - you have mydumper (an enhanced replacement for mysqldump) as well as backup methods which aren't available on Windows such as Xtrabackup from Percona (free Open Source hot backup programme for InnoDB) and LVM snapshots. There are many other useful tools in the MySQL ecosphere that ony work (or are far easier to use) on Linux than on Windows. I know that the primary Oracle/MySQL development environment is Unix based (I did read this somewhere, sorry can't remember), either Linux or Solaris.


One record, multiple updates in one query (MySQL)

I might be missing something here, but maybe it only works for the first match because it sets t1.amount = 0, which means that record is excluded from a repeated invocation of the same command (whose WHERE clause requires t1.amount > 0)?


Basically we don't need any Postgresql server to take the backup. Postgresql client is enough for this.
But you must have sudo privilege to install this client.
For Ubuntu:
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >>  /etc/apt/sources.list.d/pgdg.list
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-client

For RedHat/CentOS/Amazon Linux:
Download the rpm here:
https://yum.postgresql.org/repopackages.php#pg96
yum install postgresql

Dump the Database
pg_dump -h remote_address -U username -D dbname -Fp > backup.sql

More about Pg_dump:
https://www.postgresql.org/docs/9.6/static/app-pgdump.html


Sql Server 2012 Change Data Capture weird behaviour

Here is the MSDN

Large Object Data Types
Columns of data type image, text, and ntext are always assigned a NULL value when __$operation = 1 or __$operation = 3. Columns of data type varbinary(max), varchar(max), or nvarchar(max) are assigned a NULL value when __$operation = 3 unless the column changed during the update. When __$operation = 1, these columns are assigned their value at the time of the delete. Computed columns that are included in a capture instance always have a value of NULL.

By default, the maximum size that can be added to a captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement is 65,536 bytes or 64 KB. To increase this size to support larger LOB data, use the Configure the max text repl size Server Configuration Option to specify a larger maximum size. For more information, see Configure the max text repl size Server Configuration Option.

You are using a nvarchar(max). I've got the same problem.
I fix it by changing my varchar(max) with varchar(255). For an address 255 should be enough.

Hope this help


Select Oracle clob field from remote table over dblink

Remote database:

SQL> create table t1(id number, c1 clob);

Table created.

SQL> insert into t1 values(1, 'HELLO WORLD!');

1 row created.

SQL> insert into t1 values(2, LPAD('A', 4000, 'A'));

1 row created.

SQL> update t1 set c1 = c1 || c1 where id = 2;

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> commit;

Commit complete.

SQL> select id, length(c1) from t1;

        ID LENGTH(C1)
---------- ----------
         1         12
         2     128000

Local database:

SQL> desc t1@s112
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 C1                                                 CLOB

SQL> select c1 from t1@s112 where id = 1;
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables

no rows selected

SQL> select to_char(c1)  from t1@s112 where id = 1;

TO_CHAR(C1)
--------------------------------------------------------------------------------
HELLO WORLD!

The problem is, CHAR types are limited to 4000 bytes, so:

SQL> select to_char(c1) from t1@s112 where id = 2;
select to_char(c1) from t1@s112 where id = 2
*
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:
128000, maximum: 4000)
ORA-02063: preceding line from S112

A possible workaround:

SQL> create global temporary table t1_tmp on commit preserve rows as select * from t1@s112 where 1 = 2;

Table created.

SQL> insert into t1_tmp select * from t1@s112 where id = 2;

1 row created.

SQL> commit;

Commit complete.

SQL> select id, length(c1) from t1_tmp;

        ID LENGTH(C1)
---------- ----------
         2     128000

The above is from a 11.2 database.

Starting with 12.2, the limitation of selecting a LOB through a dblink was removed, no workaround needed.


There are several methods to audit database transactions, but they all affect performance more or less

SQL Server Change Tracking and SQL Server Change Data Capture don't show who, when, and how executed the transactions. On the other hand, SQL Server Change Data Capture shows the old and new values for the UPDATE statements. Here is a useful set of comparison notes: SQL Server 2008 Change Tracking (CT) and Change Data Capture (CDC) and Comparing Change Data Capture and Change Tracking

SQL Server Auditing  shows who, when, and how, but doesn't show the old and new values for the UPDATE statements

The methods that read the database transaction log files don't add overhead, as there is not additional change capturing. Besides fn_dblog, that can return results not easy to understand, there are third party tools, such as ApexSQL Log

There are two more auditing tools from ApexSQL - ApexSQL Audit which uses triggers, so can impact performance on a high transaction database, and ApexSQL Comply that uses SQL traces

SQL Server database auditing techniques

Disclaimer: I work for ApexSQL as a Support Engineer


Ms-Access 2007-2010 SQL - from single row record how to create multiple rows

Easy answer is to do this in a function in the built-in VBA editor in Microsoft Access.

Assuming you have two tables, Source and Dest that both have 3 columns, col1, col2, and col3.

Sub SplitIt()

    Dim rstSource As Recordset
    Dim rstDest As Recordset
    Dim Items() As String

    ' use dbOpenSnapshot to open the source table READ-ONLY
    Set rstSource = CurrentDb.OpenRecordset( _
            "SELECT col1, col2, col3 FROM Source;" _
            , dbOpenSnapshot)
    ' use dbOpenDynaset to open the destination table READ-WRITE
    Set rstDest = CurrentDb.OpenRecordset( _
            "SELECT col1, col2, col3 FROM Dest;" _
            , dbOpenDynaset)

    With rstSource
        ' .BOF is Beginning of the table
        ' .EOF is End of the table
        ' Checking if both are false means there are records in the
        ' source table
        If Not (.BOF And .EOF) Then
            ' get the first record from the source table
            .MoveFirst
            Do
                ' if col3 is NULL (empty)
                If Nz(!col3, "") = "" Then
                        ' add a new record into the destination table
                        ' with data from the source table for col1 and col2
                        rstDest.AddNew
                        rstDest!col1 = rstSource!col1
                        rstDest!col2 = rstSource!col2
                        ' set col3 to NULL (empty)
                        rstDest!col3 = Null
                        ' save the new record
                        rstDest.Update
                Else ' if col3 IS NOT NULL
                    ' convert col3 into an array of strings
                    Items = split(Nz(!col3, ""), ";")
                    ' loop through the array of strings
                    For a = LBound(Items) To UBound(Items)
                        rstDest.AddNew
                        rstDest!col1 = rstSource!col1
                        rstDest!col2 = rstSource!col2
                        ' col3 is set to the current item in the array
                        rstDest!col3 = Items(a)
                        rstDest.Update
                    Next a
                End If
                ' load the next record from the source table
                .MoveNext
            ' repeat until the end of the source table is reached
            Loop Until .EOF
        End If
        ' close the source table
        .Close
    End With
    ' close the destination table
    rstDest.Close

End Sub

Then run the SplitIt() function from the Immediate Window.  Press CTRL-G then type:

SplitIt

and press [Enter]

As an aside; there is an excellent resource on understanding For...Next code at http://msdn.microsoft.com/en-us/library/5z06z1kb.aspx


Is it possible to tell when databases, schemas and tables were created in Postgres?

From this post:

;WITH CTE AS
(
    SELECT
        table_name

        ,
        (
            SELECT
                MAX(pg_ls_dir::int)::text
            FROM pg_ls_dir('./base')
            WHERE pg_ls_dir <> 'pgsql_tmp'
            AND  pg_ls_dir::int  <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE table_name)
        ) as folder

        ,(SELECT relfilenode FROM pg_class WHERE relname ILIKE table_name) filenode

    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE'
    AND table_schema = 'public'
)

SELECT
    table_name
    ,(
        SELECT creation
        FROM pg_stat_file(
            './base/' || folder || '/' || filenode
        )
    ) as creation_time
FROM CTE;

But, from the comment to this post here, this method is sometimes unreliable. Use at your own risk! You might also be interested in this - it appears that PostgreSQL is finally going down the road of an Oracle wait interface.

If you'd care to file a feature request and post the link in a comment here (with @Vérace in the body), I'd be happy to file a "me-too".


looking for a lighter alternative to cognos express

I have deployed  Logi Reports at a client and the free version is just fine for the users where I am.  You can pay for the upgraded version which offers OLAP-like abilities. 2016 Update Logi no longer issues a free version. The paid version now has dashboard and analytic tools.

This product was mentioned in another post iccube as a free OLAP but I have not used it.  It looks to me like it tries to emulate the built in features that Oracle has on a Java platform.

You don't mention the infrastructure or technical skills of the client.  These are important considerations.  A tool that is "too complicated" will only results in calls to you to dumb it down.

The features I recommend considering are:

how many brands of database can it connect to?  Does it work with the versions you have?
How easy is to configure access and permissions with the client's infrastructure? Active Directory is a standard for many places and should be an option.
How fast is the rendering engine or how much patience does your client have to wait for results?  (Of course database structure has a much bigger effect on performance than the reporting engine)
Do you want a product with wizards that make it easy to deploy reports?
Can you craft a query in SQL and then drop it into the report?  This is incredibly handy for testing and development.


Is it better to create another table to possible null values in relational databases?

You are touching on an almost philosophical argument here: should NULLable values be allowed at all given they violate the "closed world" assumption of the model relational databases are derived from (see relevant sections of http://en.wikipedia.org/wiki/Null_(SQL) and a number of other questions here such as Why shouldn't we allow NULLs? for more notes on that). To avoid unknown values splitting properties that might be unknown (or simply not applicable in all circumstances) into their own relation (table) so that you don't have values that are unknown but instead if a value is unknown it simply doesn't exist. While this satisfies the theory, there are practical implications that make it less desirable:

A JOIN operation is not free in most RDBMSs, searching for the properties in the extra table(s) increases the work the engine needs to do to satisfy your queries.
If the existence of properties is not codependent (i.e. each proerty can be unknown irrespective of the others) then to carry this implementation to its conclusion you sometimes end up needing a table for each property.
The extra joins add to the complexity of your queries and can reduce maintainability.
Updates also become less intuitive in places: blanking a property now becomes a DELETE operation and updating one may be either an INSERT or an UPDATE.

Of course there are circumstances where that first point is reversed and breaking properties off improves efficiency:

Most RBDMSs use page based storage and breaking optional information off from core data means you can fit more core data rows in a given page. Depending on your data size, RAM, and storage infrastructure this could be significant in reducing the amount of IO required for some large queries (but often isn't: try not to "over optimise" around this point, at least without running good benchmarks to make sure things are improving not worsening).
This is of course assuming that your queries are designed so they pick up only what they need so the engine won't need to care about the drawing in the extra information anyway.
Similarly there is less writing to do if you are only updating core information in an INSERT or UPDATE, and if you have constraints or triggers around those extra properties this processing is potentially avoided on each row change.

Your example looks like it is storing images (or at least large bits of data in blob type columns. There are two extra considerations here:

Most database engines store large data "off page" anyway, so if you avoid SELECT * you get the rows-per-page bonuses "for free" anyway.
Does moving them elsewhere allow you to share the large data between multiple entities rather than storing the same blob more than once, or is the information (if present) unique to a given row?

tl;dr: So no hard and fast answer I'm afraid. My advice would be to do whatever best fits your conceptual model of your data and therefore requires less thought on your part to maintain. This usually, but not always, means using NULLable columns rather than separate tables. Unless your data is truly massive the performance differences are going to be negligible.


SQL Server Error, "Invalid usage of the option FIRST in the FETCH statement."

SQL Server has implemented the OFFSET and FETCH clauses as part of the ORDER BY clause, as pointed by the other answers and documented in their documentation.
The SQL standard on the other side, has both of these clauses as independent:
<query expression> ::=
[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

If someone wants this feature implemented in full compliance with the standard, they can always make a request to the SQL Server team, through the Connect channel. In fact, MS has commented - in a different request about offset and fetch:

Connect item: SQL Denali: add total rows counter to SELECT statement - by Alexey Rokhin
Answer: Posted by Microsoft on 24/11/2010 at 11:34
The requirement that OFFSET/FETCH requires ORDER BY is a restriction in this release. In the ANSI SQL standard (SQL:2011) where the new OFFSET/FETCH clauses are proposed, ORDER BY is optional. The restriction in SQL Server has to do with limitation in our parser technology that cannot handle the optional syntax without making OFFSET a reserved keyword. We may remove it in the future.
Now with respect to ...

Until then, if one wants to use OFFSET and FETCH without a specific ORDER BY, a workaround is to add a "do nothing" order by clause. Example:
SELECT
...
ORDER BY (SELECT NULL)
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY;


Here is a super easy project example with 2  apps and  3 schemes that cross each other with ForeignKey

https://github.com/mullerivan/DjangomultipleSchema
The idea is to define each model with the schema and  table  that need to use

class Meta:
    db_table = u'"app1\".\"on_app_one"'


Apply SQLServer Service Pack to a Single Node Cluster

Nothing particular to worry about, but you will incur downtime as the SP runs its stuff in "script mode" during which users cannot connect.

It might go without saying, but I'd ensure you have a valid (i.e. tested) backup of the databases (including the system databases) on the affected system prior to doing any patching, whether on a single-node cluster, a multi-node cluster, or even a stand-alone instance.  You can never have too many backups.


Can we reconstruct/reset a broken Postgresql data directory without losing data?

It may be possible to rescue some of your data. It will not be straightforward.

Hopefully you took a full file-level backup of your postgres directories as soon as you noticed the problem. A recent filesystem backup will also he useful.

Then, subscribe to the postgresql-general mailing list and post a full description of your problem there. Bear in mind it is the weekend and you will need to be patient.


The uncommitted transactions are not escaped.  They do get replayed.  However, any process running across the new data from these transactions know to ignore them as they are not committed.  And any data marked for removal by an uncommitted transaction is not ignored, because the removal has not yet been committed.  This is the same way that in-progress transactions are ignored during normal operation, i.e., when there has been no crash.

Once the transaction is known to be aborted, then anyone who stumbles upon data from that transaction can clean it up.  Once recovery has finished, any transaction which has not had its commit record replayed by the end of recovery is known to be aborted.


Control InnoDB buffer pool allocation in MySQL (5.7+)

I am sorry to bring this up but only MyISAM does what you asking for when it comes to caching indexes for a specific table. I have written about this before. At this time, using MyISAM is like MySQL sacrilege, but if you are that desparate to cache indexes independently and your tables are read only, you can try converting them to MyISAM and setting up dedicated keycaches. CAVEAT : YOU DIDN'T HEAR THIS FROM ME !!
BACK TO REALITY...
Perhaps you should consider taking snapshots of the Buffer Pool.
Whenever you get the InnoDB Buffer Pool into the state you want, run this
SET GLOBAL innodb_buffer_pool_dump_now = 1;

This will generate a file called ib_buffer_pool (usually located in datadir), containing a map of all tablespace_ids that are in the Buffer Pool at that moment. You could copy this file to some folder and retrieve it later.
You could reload ib_buffer_pool by running
SET GLOBAL innodb_buffer_pool_load_now = 1;

This will copy all .ibd pages (data and indexes) back into the InnoDB Buffer Pool.
You should consider taking a snapshot of the buffer pool before running a backup and reloading the buffer pool afterwards.
For more information, please read the MySQL Documentation on innodb_buffer_pool_dump_now and innodb_buffer_pool_load_now.


Try shutting down the database and then issuing the startup command.  Then try your script.

If you get the same error verify basic connectivity by connecting from sqlplus with the same name and password.

Check your log file location and the any operating system error logs as well.


Assuming a person can only submit one and only one vote, you just need two tables, PlaceVotes and PostVotes, as follows

create table dbo.PlaceVotes (
 Id int not null,
 PlaceId int not null,
 UserId int not null,
 CreatedDate datetime not null,
 Value int not null,
 constraint unique index ux_userid (UserId, place),
 constraint primary key pk_placevotes(id)
)

create table dbo.PostVotes (
 Id int not null,
 PostId int not null,
 UserId int not null,
 CreatedDate datetime not null,
 Value int not null,
 constraint unique index ux_userid (UserId, postid),
 constraint primary key pk_postvotes(id)
)

This way, the voting event is represented as one entity.


How to make variable update for each instance of for-each loop?

You can create the variable either at the foreach loop container or at the global scope, by making sure that the foreach container is selected when you create the variable, or nothing is selected when you create the variable.
Inside the foreach loop container you can then add an Expression Task in order to change the value of the variable at run time.
Depending on what you need exactly, you could also use a Script Task for this purpose.
There's a similar question here with the relevant details:
https://stackoverflow.com/questions/4052864/ssis-set-variable-at-runtime


"Stored Procedures Invoked/sec" counter always increasing

There are several different types of performance counters, five of which are used by SQL Server (at least in terms of what you will find in the sys.dm_os_performance_counters DMV). You can see this via the following query:

SELECT DISTINCT [cntr_type]
FROM sys.dm_os_performance_counters
ORDER BY [cntr_type];

Returns:

65792
272696576
537003264
1073874176
1073939712

Those values mean:

Noncomputational Counter Types

65792 == PERF_COUNTER_LARGE_RAWCOUNT == "Raw counter value that does not require calculations, and represents one sample which is the last observed value only."

Counter Algorithm Counter Types

272696576 == PERF_COUNTER_BULK_COUNT == "Average number of operations completed during each second of the sample interval."
1073874176 == PERF_AVERAGE_BULK == "Number of items processed, on average, during an operation. This counter type displays a ratio of the items processed (such as bytes sent) to the number of operations completed, and requires a base property with PERF_AVERAGE_BASE as the counter type."

Base Counter Types

1073939712 == PERF_LARGE_RAW_BASE == "Base value found in the calculation of PERF_RAW_FRACTION"

Basic Algorithm Counter Types

537003264 == PERF_LARGE_RAW_FRACTION == "Ratio of a subset to its set as a percentage. This counter type displays the current percentage only, not an average over time."

You can find more info in the following blog post: Interpreting the counter values from sys.dm_os_performance_counters

The Stored Procedures Invoked/sec counters in the SQLServer:Broker Activation category are of type 272696576 (i.e. "PERF_COUNTER_BULK_COUNT"). These values are cumulative. You need to capture a value into a variable, wait for N seconds (i.e. WAITFOR DELAY '00:00:30'; -- 30 seconds), capture a second value, and then divide the difference between the values by N (i.e. SELECT  (@var2 - @var1) / 30.0;). For example:

DECLARE @Sample1 BIGINT,
        @Sample2 BIGINT,
        @Time1 DATETIME,
        @Time2 DATETIME;

SELECT @Sample1 = [cntr_value],
       @Time1 = GETDATE()
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Databases' -- SQLServer:Broker Activation
AND   [counter_name] = N'Transactions/sec' -- Stored Procedures Invoked/sec
AND   [instance_name] = N'_Total';

WAITFOR DELAY '00:00:30.000'; -- 30 second pause/sleep

SELECT @Sample2 = [cntr_value],
       @Time2 = GETDATE()
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Databases' -- SQLServer:Broker Activation
AND   [counter_name] = N'Transactions/sec' -- Stored Procedures Invoked/sec
AND   [instance_name] = N'_Total';

SELECT (@Sample2 - @Sample1) AS [Difference],
       (DATEDIFF(MILLISECOND, @Time1, @Time2) / 1000.0) AS [Seconds],
       (@Sample2 - @Sample1) / (DATEDIFF(MILLISECOND, @Time1, @Time2) / 1000.0);


Table Permissions Sanity Check: GRANT not working

Ok, so my broader question still stands (and will likely build that out in its own forum post) but for now, I'll answer the immediate problem here.

My GRANT query was syntactically incorrect and that was producing the error.

My query:

GRANT SELECT,INSERT,UPDATE,DELETE ON "schema1.new_table_name" TO "role1";

Should really be:

GRANT SELECT,INSERT,UPDATE,DELETE ON "schema1"."new_table_name" TO "role1";

(I was missing closing Quote marks after the schema name, and opening quote marks before the table name)


Alter table add column query blocking a table or taking so much time

The table will need to be physically rewritten on disc to include the extra column.   For this to happen the table has to be exclusively locked while this is happening.  If the applications running on this database are not shutdown then you may be waiting sometime before this exclusive lock on the table can be given.

Given you mentioned that this table has several million rows the alter table statement is going to take quite some time (this also depends how much data is stored in each record.  You will in all likelyhood need to stop all access to this table during this time (well it will be locked anyway).

Sorry if the answer is a little vague you did not really give many details about the size of the table and how much data is currently stored there.


How to retrieve a column which is not included in group by

You can use a window aggregate function in a subquery (derived table or CTE, that's not important):

SELECT BusnPartClientSuppContractId,
       DivBranchId
FROM (
    SELECT BusnPartClientSuppContractId,
           DivBranchId,
           cnt = COUNT(*) OVER (PARTITION BY DivBranchId)
    FROM (
        -- the tmp subquery as it is, no change
        ) tmp
    ) t
WHERE cnt = 1
ORDER BY DivBranchId ;

Unrelated to the question but the TOP 1 subquery is not deterministic because it doesn't have an ORDER BY. So the whole query is not deterministic and may yield different results per execution, even if the data don't change.


Stored Procedure RAISERROR - Transaction Count Msg Returned

You can only get the "Transaction count mismatch" error if you have an open transaction prior to enter this stored procedure. The problem is that BEGIN TRAN will increment @@TRANCOUNT by 1 each time it is called, and COMMIT will decrement by 1 each time it is called, yet ROLLBACK will always reset back to 0 since there truly is no such thing as a nested transaction in SQL Server.

In your case, you have an active transaction before this stored procedure is called, but the ROLLBACK in the CATCH block resets @@TRANCOUNT back to 0, hence the error.

The trick to handling nested stored procedures, each having their own error handling, is to not call BEGIN TRAN if you are already in an active transaction (it doesn't do anything anyway outside of open you up to the possibility of getting this error ;-). The template that I prefer using is found in the following answer of mine, also here on DBA.SE:

Are we required to handle Transaction in C# Code as well as in stored procedure


database design question - How to make the table 3NF and have no orphans

The closer to reality you map, the more flexible the design usually is (and that usually means normalize it!).

I wouldn't worry about orphans if you have your F-key constraints set. In the case of deleting a NIC though, remember reality...did we really set the NIC on fire and crush it into oblivion? If so, then in your example, deleting a NIC will delete the port also...which is good. For making what is plugged in appear for normal "active" views, slot_no IS NOT NULL can be a part of the WHERE clause.

For both slot_no and the case of loopback (no NIC), having a NULLable column in InnoDB doesn't hurt anything, maps well to reality, and is made for just such a case. It means "I don't have one" in both columns, which is perfect.

If it were me, and I thought I might want metadata about the ports (lanes, speed, CPU association), I'd even go as far as having an additional table for slots which are referenced by NICs (some multi-jack cards appear as more than one) by that slot_no column you have.

Off the top of my head, here are some natural views (WHERE clause with "slot_no IS NOT NULL" would work in your 2 table design too):

If you start your queries include an inner join to slot, NICs which are not plugged in (but metadata retained) will not appear for active listings
For viewing metadata about all NICs (past and present), outer join instead
For an auto-configuring script, unplugged NICs or rearranged NICs can retain their metadata and port associations without them showing in active SELECTs (see #1)

In any case, be sure to keep the NIC info with the NICs.


How can I seamlessly upgrade the major version of an AWS RDS postgres database?

This is a good question,
working in cloud environment is tricky sometimes.

You can use pg_dumpall -f dump.sql command, that will dump your entire database to a SQL file format, In a way that you can reconstruct it from scratch pointing to other endpoint. Using psql -h endpoint-host.com.br -f dump.sql for short.

But to do that, you will need some EC2 instance with some reasonable space in disk (to fit your database dump). Also, you will need to install yum install postgresql94.x86_64 to be able to run dump and restore commands.

See examples at PG Dumpall DOC.

Remember that to keep integrity of your data, it is recommended (some cases it will be mandatory) that you shutdown the systems that connect to the database during this maintenance window.

Also, if you need speed up things, consider using pg_dump instead pg_dumpall, by taking advantage of parallelism (-j njobs) parameter,
when you determine the number of CPUs involved in the process, for example -j 8 will use until 8 CPUs. By default the behavior of pg_dumpall or pg_dump is use only 1. The only advantage by using pg_dump instead pg_dumpall is that you will need to run the command for each database that you have, and also dump the ROLES (groups and users) separated.

See examples at PG Dump DOC and PG Restore DOC.


For SQL Server, What is the proper way to update a table?

Study the following:

BEGIN TRANSACTION, ROLLBACK TRANSACTION, COMMIT TRANSACTION

Also read Paul Randall's A SQL Server DBA myth a day: (26/30) nested transactions are real
Also read XACT_ABORT

@@ROWCOUNT
IF ...ELSE
GOTO
@@TRANCOUNT
More advanced topics, optional until they're not:

SET TRANSACTION ISOLATION LEVEL
Gail Shaw's intro to deadlocks

Then re-read this general technique:

At the start of the code, GOTO :TheEnd

partial protection against a few "only run part of the code, not all the code" mistakes

At the end of the code, GOTO :TheBeginning

partial protection against a few "only run part of the code, not all the code" mistakes

SET XACT_ABORT ON and start a transaction
Code a single DML statement with appropriate WHERE and JOIN clauses
Code a check how many rows it affected; if it's not exactly as many rows as you expected, roll back the transaction.

repeat the above coding as many times as you have statements

default to rolling back the transaction.

code the COMMIT only after you're sure it's all correct.
run the COMMIT only after getting a cup of coffee, in case you have a moment of fridge horror

Check to verify transaction count is 0


Error Code: 1845. ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE

According to my research, this is a bug

https://bugs.mysql.com/bug.php?id=92194

which is explaining
 " For ALTER TABLE, ALGORITHM=INSTANT was incorrectly rejected on tables
created in a MySQL version prior to 8.0.12 "
" Fixed in 8.0.14. "

I didn't tested it with 8.0.14. I will post here the results once I tested it with 8.0.14.

[update]

Tested it in 8.0.14 and it works


Getting error running DBCC Checktable(tablename, repair_allow_data_loss)

From the comments I see that you have it narrowed down to a single corrupt row. You may be able to get the underlying data (or most of the data at least) in that row via a hex editor. Paul Randal has good information on how to crack open MDF files this way and here is an article by Kendra Little as well.

https://littlekendra.com/2011/01/24/corrupthexeditor/

The article is on how to corrupt a database with a hex editor but you've already got that so look at the steps right below "It’s the Moment We’ve Been Waiting For: Trash That Page".

Extracting information in this way can't be healthy for the database, so ensure you are using a backup!

Depending on the data, you can usually see enough to piece together the corrupt values.


Unique key in SQL Power Architect? Forward Engineering

I'm running Power Architect 1.0.6 on a Mac and the following instructions work for me.

Highlight the table you want to add the constraint to
Click on the "New Index" button on the toolbar and the "Index Properties" dialog appears, with sections for the name and type of constraint and a list of columns.
Enter a name for your constraint
Tick the "Unique" button
Tick the check box next to each column over which you want to define the constraint
Finally, click the "OK" button to add the constraint.

Hope this helps.


SQL Server 2012 Replication - MSrepl_commands growing all set to in progress

So we're working now, I'm not 100% sure which part of this fixed the issue but I'm putting it down for if anyone else has this issue in the future where there doesn't seem a logical reason for the distribution growth.
Our distributor is on a windows failover cluster across two machines along with a Reporting Instance (and a MSDTC).

The issue occurred after a restart where the distributor moved from
server 2 to server 1 running along side reporting(and MSDTC).

After server 2 restart was complete Reporting was moved over to
Server 2.

This is when the issue was noticed.

The Distributor was moved over to Server 2 (Alongside reporting) but this seemed to have no beneficial effect

The Distributor was moved back to Server 1 and MSDTC was moved to server 2 with Reporting
At this point running the sp_MSdistribution_cleanup with a max time of just shy of how long the server had been running for (by 6 hours) and it cleared out everything (Took close to 3 hours to complete but it got there)


How to import sql file in stored procedure in MySQL?

Not everything can be done in SQL.  In particular, it is not possible (without a UDF) to reach outside of MySQL to do file operations (other than LOAD FILE).  This is a security limitation.

The "right" way to deal with a requirement is to code it in your application language (PHP / Java / Python / ...).  Some steps would be SQL; some would be file operations.


Delete from GROUP BY with multiple GROUP columns

DELETE t1
FROM data t1
JOIN data t2 USING (channel_id,timestamp)
WHERE t1.id > t2.id


How to restore a file group from file group backup?

I think you might need to backup the transaction log (taillog) again at the end of your restore sequence and then restore that taillog backup.

The following demo worked for me:

USE [master]
GO

ALTER database [FGRestoreTEST] set offline with ROLLBACK IMMEDIATE;
DROP DATABASE [FGRestoreTEST]
GO

--Create sample database
CREATE DATABASE [FGRestoreTEST] ON PRIMARY (
    NAME = N'FGRestoreTEST'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST.mdf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    )
    ,FILEGROUP [secondaryFilegroup] (
    NAME = N'secondaryFilegroup'
    ,FILENAME = N'C:\SQLServer\secondaryFilegroup.ndf'
    ,SIZE = 5120 KB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024 KB
    ) LOG ON (
    NAME = N'FGRestoreTEST_log'
    ,FILENAME = N'C:\SQLServer\FGRestoreTEST_log.ldf'
    ,SIZE = 2048 KB
    ,MAXSIZE = 2048 GB
    ,FILEGROWTH = 10 %
    )
GO

--Insert some test data
use [FGRestoreTEST]
go
create table TestTable (id int) on secondaryFilegroup
insert into TestTable values(1),(2)

--Backup the secondary filegroup and then backup the transactionlog
USE [master]
GO

BACKUP DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup' TO DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH INIT

BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\tlog.trn'
WITH INIT;

--Restore the secondary filegroup and then restore the transactionlog
--taken in the previous step
use [master]
go
RESTORE DATABASE FGRestoreTEST FILEGROUP = 'secondaryFilegroup'
FROM DISK = N'C:\SQLServer\secondaryFilegroupBackup.bak'
WITH norecovery

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\tlog.trn'
WITH recovery;

--At this point, you should have seen the messages about
--Additional roll forward past LSN ... is required to complete the restore sequence
--Take an additional log backup and then restore with recovery
BACKUP LOG FGRestoreTEST TO DISK = N'C:\SQLServer\Taillog.trn'
WITH INIT;

RESTORE LOG FGRestoreTEST
FROM DISK = N'C:\SQLServer\Taillog.trn'
WITH recovery;

--Verify you can select from TestTable
use [FGRestoreTEST]
go

SELECT *
FROM TestTable

UPDATE: You added additional comments

  I tried your solution, but before the restore of the database, i
  deleted 100 columns from the table (I had table with 1000 rows, after
  the delete query they remain 900). Everything else was the same. So at
  the end all files are accessible, but the table doesn't restored and
  the rows remain 900.

Have a look at SQL Server Tricks: How Can I Restore a Subset of My Tables to A Prior Point in Time? - specifically the section Why Filegroup Level Restores Don’t Solve This Problem.

  Each filegroup-restore sequence restores and recovers one or more
  filegroups to a point consistent with the database.

  Why does it have to be this way? It’s because SQL Server doesn’t track
  transaction status by filegroups, and its mission is to ensure
  transactional consistency within a database. If we could restore
  individual filegroups to different points in time and bring everything
  online, we wouldn’t have a way to ensure consistency.


You can create 3-4 different data flow tasks within your control flow (inside the Foreach loop container).

Place a sql script before the DF containers.  You can have the script actually do something or not.  The idea is to create a conditional from this task that leads to the 3-4 data flow tasks.

On the connector for each DF object, add a constraint that the variable == "somevalue".  Since each Connector has a different report type value, only one will execute.


Two insert statements inside trigger - Duplicate entry '521-2' for key 'PRIMARY'

I have been baffled by a similar situation. Use variables to store the values of NEW.column and remove the join from the 2nd Insert:

USE users_web;

DELIMITER |
CREATE TRIGGER doku_users_ins AFTER INSERT ON users_web.users
       FOR EACH ROW
       BEGIN

                DECLARE new_pass VARCHAR(255);         -- choose the datatypes
                DECLARE new_email VARCHAR(255);        --
                DECLARE new_user INT;                  --

                SET new_pass = NEW.pass ;
                SET new_email = NEW.email ;
                SET new_user = NEW.user ;

                INSERT INTO users_web.doku_users (login, pass, email)
                       VALUES (new_user, md5(new_pass), new_email);

                INSERT INTO users_web.doku_usergroup (uid, gid)
                       SELECT du.uid, '2' FROM doku_users du
                            WHERE new_user = du.login;

       END |

DELIMITER ;


Can I generate a list of all combinations between two columns

This is called a cartesian product or a CROSS JOIN:

SELECT
    a.name,  b.item
FROM
    table1 AS a
  CROSS JOIN
    table2 AS b;


A UNION will not have an index.  A "derived table", which is what you have inside the parens, may be given an index -- it depends first on which version of MySQL you are running.  Please provide EXPLAIN SELECT ....

Get rid of LEFT so that the Optimizer can start with the UNION.  And add INDEX(href) in C so that the JOIN can work the other direction.

If you really need NULL for u.name, then you need the LEFT, and there is no hope for optimizing.

(The default for UNION is UNION DISTINCT, which is slower than UNION ALL.)


Try to use mongodump and mongorestore. mongodump works more forcefully then repair. Hope it will work and keep updated.


Is it possible for total participation constraints to be enforced by an attribute?

This would be one way to enforce the constraint.

-- User USR exists.
--
user {USR}
  PK {USR}

-- Restaurant RST exists.
--
restaurant {RST}
        PK {RST}

-- Fare (food, drink) FAR exists.
--
fare {FAR}
  PK {FAR}

-- Restaurant RST serves fare FAR.
--
menu {RST, FAR}
  PK {RST, FAR}

FK1 {RST} REFERENCES restaurant {RST}
FK2 {FAR} REFERENCES fare       {FAR}

-- User USR placed (his/her) order number ORD#,
-- from restaurant RST.
--
order {USR, ORD#, RST}
   PK {USR, ORD#}
   SK {USR, ORD#, RST}

FK1 {USR} REFERENCES user       {USR}
FK2 {RST} REFERENCES restaurant {RST}

-- User USR ordered QTY of fare FAR
-- from restaurant RST in (his/her) order number ORD#,
-- as item number ITM# of that order.
--
order_item {USR, ORD#, RST, ITM#, FAR, QTY}
        PK {USR, ORD#, RST, ITM#}
        AK {USR, ORD#, RST, FAR}

  FK1 {USR, ORD#, RST} REFERENCES
order {USR, ORD#, RST}

 FK2 {RST, FAR} REFERENCES
menu {RST, FAR}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Using suffix # to save on screen space.
Works for SQL Server and Oracle, for others use _NO.
For example, rename ORD# to ORD_NO.


It seems you need in simple

SELECT Paddock,
       Date_Time_Completed Season_Start,
       COALESCE(LEAD(Date_Time_Completed) OVER (PARTITION BY Paddock
                                                ORDER BY Date_Time_Completed),
                CURRENT_DATE) Season_End,
       Crop,
       YEAR(Date_Time_Completed)
           || '_'
           || ROW_NUMBER() OVER (PARTITION BY Paddock
                                 ORDER BY Date_Time_Completed) Season_Name
FROM source_table


Oracle: create index on json, Json item lenght is too long long?

I could not find any official reference to this, but by experimenting, I found that a JSON record value that is longer than 32K triggers this error.

Maybe this will be fixed in a future release, like this similar bug, that is said to be fixed in 12.2 (this bug is about having JSON identifies longer than 64 bytes, not this error):

Creating a JSON Index Fails with DRG-50850 (Doc ID 2110588.1)

Until then, I would try to use shorter records, or open a Service Request.

And the experiment:

CREATE TABLE tj  (js CLOB  CONSTRAINT c_js CHECK (js IS JSON));

I can easily create an index on "large" (over 200K character) JSON documents, so first a JSON with a lot (50000) of short (2 characters) records:

declare
  c clob;
begin
  c := '{';

  for i in 1..50000
  loop
    c := c || to_clob('"Attr') || to_clob(i) || to_clob('" : "') || lpad(to_clob('X'), 2, 'X') || to_clob('",');
  end loop;

  c := c || '"End" : 0 }';

  insert into tj values (c);
end;
/

The length of this single JSON is:

select dbms_lob.getlength(js) from tj;

DBMS_LOB.GETLENGTH(JS)
----------------------
                938906

Creating the index:

SQL> CREATE INDEX tj_i1 ON tj (js)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

Index created.

Now try a JSON with 1 long record:

SQL> truncate table tj;

Table truncated.

SQL> drop index tj_i1;

Index dropped.

declare
  c clob;
begin
  c := '{';

  for i in 1..1
  loop
    c := c || to_clob('"Attr') || to_clob(i) || to_clob('" : "') || lpad(to_clob('X'), 32800, 'X') || to_clob('",');
  end loop;

  c := c || '"End" : 0 }';

  insert into tj values (c);
end;
/

So our JSON has 1 long record, the overall length is:

select dbms_lob.getlength(js) from tj;

DBMS_LOB.GETLENGTH(JS)
----------------------
                 32825

Now creating the index:

SQL> CREATE INDEX tj_i1 ON tj (js)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

CREATE INDEX tj_i1 ON tj (js)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50850: ORA-06515: PL/SQL: unhandled exception JSON item length is too long
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366

The same works when below the 32K limit:

SQL> truncate table tj;

Table truncated.

SQL> drop index tj_i1;

Index dropped.

declare
  c clob;
begin
  c := '{';

  for i in 1..1
  loop
    c := c || to_clob('"Attr') || to_clob(i) || to_clob('" : "') || lpad(to_clob('X'), 32700, 'X') || to_clob('",');
  end loop;

  c := c || '"End" : 0 }';

  insert into tj values (c);
end;
/

Length, and index:

select dbms_lob.getlength(js) from tj;

DBMS_LOB.GETLENGTH(JS)
----------------------
                 32725

CREATE INDEX tj_i1 ON tj (js)
INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

Index created.


Estimated vs. Actual rows and multi-column statistics

You've actually got a few questions in here, so I'll break 'em out individually.

  My problem/question is that the estimated number of rows for the above
  query is 256, the actual number of rows is 560K.  I want to understand
  why there is such a big difference between these two numbers?

In order to answer that question, the first thing we would need is the actual execution plan for the query.  In SQL Server Management Studio, you can get that by clicking Query, Include Actual Execution Plan.  Run the query, click on the Execution Plan tab, and right-click anywhere in the whitespace to click Save Plan As.  Save that, and post it somewhere for people to download and examine.

The next thing we would need is the output from DBCC SHOW_STATISTICS for the stats on that table.  You've hinted at the output, and that's a good start, but the raw output will help us understand exactly what's going on.

  If I run a DBCC SHOW_Statistics, the density section has both columns
  in it, the histogram does not. Does SQL server produce a histogram for
  the combination of columns in multi-column statistics?

No.

  I have an index on (TaskExecStatusID,TaskExecUpdatedDate),

If you frequently use the query in the example (with TaskExecUpdateDate IS NULL), then you might check out filtered indexes.  They're a new feature in SQL Server 2008 that allows you to put a where clause on your index, basically.

http://sqlfool.com/2009/04/filtered-indexes-what-you-need-to-know/


is it good to add index on column used in group by clause?

Adding an index on X will improve your performance here a bit, ideally you'd want a composite key for X then Z as you want to do analysis for each X of everything where Z is over the 100 mark so for the index to be truly effective you need both.

At that point however you're building an index for a specific query so will the additional processing on insert / updates / deletes / re indexing / storage space all worth it for the one query (if its something that's going to be used over and over and in other locations then it may be, it may also be useful in different queries you already have)

Depending on the rest of your table structure and content you could have your clustered key as X,Z and that would do the job. if you've got columns a,b,c,d,e,f,g where a is an identity field that you're clustered on then this as a secondary index would help.

Ultimately however I've found the only way to know if an index is going to help, is to test it. The best will in the world can't ensure every specific index will help in the overall system only testing can do that.


What do you expect this query to do?

regexp_like is a regular expression version of the LIKE statement so it makes sense to use it in the same sorts of places that you would use a LIKE.  You wouldn't try to directly SELECT the result of a LIKE statement.  You could, however, embed the regexp_like in a case statement.  For example

SQL> ed
Wrote file afiedt.buf

  1  select (case when regexp_like( 'https://www.hotmail.com' ,'(f|ht)tps?:')
  2               then 'true'
  3               else 'false'
  4           end) does_it_match
  5*   from dual
SQL> /

DOES_
-----
true

regexp_like is a function that returns a boolean.  Oracle SQL, however, does not support the boolean data type so you cannot directly SELECT the result of the function just like you couldn't SELECT a function you write that returns a boolean.


Is it bad form to use "LIMIT 1" on queries that should only return one row anyway?

For SELECT, I think one of the main considerations has to do with performance.

When using LIMIT 1 statistics on row correlations could play a part in determining slower/faster query execution. Although this may not be immediately observable with small data sets, it could become an issue as row numbers and index size grows (depending on DB).

Take this example, where a Postgres user encountered different execution times due to the DB's different assumptions about execution costs (based on available statistical data in the pg_statistic catalog and particularly correlation values) and therefore, resulted in different index scanning strategies.

As a rule of thumb I would use the LIMIT clause only where necessary rather than to externalize my expectations.

For UPDATE and DELETE as already mentioned, the behavior is highly DB dependent.


Killing long running queries with user notification

This feature has been proposed with an implementation, but did not make it into version 12.  Maybe it will be in version 13.  (If you can test the patch and provide feedback, you can increase the odds of that happening)


Migrating from SQL Server 2008 R2 to SQL Server 2014

Your plan sounds OK to me. I have written a detailed answer describing each step at upgrading from lower version of sql server to higher version.

To help automate most of the process use PowerShell dbatool written by Chrissy LeMaire [migrates over 25 components including databases, jobs , logins, etc]. Here is a small video as well to get you started and how it works.

make sure you do full regression testing as moving to sql server 2014, we got hit by the new cardinality estimation (CE) bug and had to use TF 9481 to use the old CE.


Unsupported redo log format (0). The redo log was created before MySQL 5.7.9

You must upgrade to 5.7 as a first step.  Then you can upgrade to 8.0.

If Mac is trying to make a bigger leap, then they are doing it wrong.

You can probably see what version is installed (without hitting that problem) by doing

mysqld --version


Use --hex-blob:

mysql> create database dbase;
Query OK, 1 row affected (0.00 sec)

mysql> use dbase;
Database changed
mysql> create table hexhex ( id binary(16) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into hexhex values (UNHEX('DEADBEEF'));
Query OK, 1 row affected (0.01 sec)

mysql> select * from hexhex;
+------------------+
| id               |
+------------------+
| ޭ▒               |
+------------------+
1 row in set (0.00 sec)

mysql> quit
Bye
root@ironforge:~# mysqldump --hex-blob -p dbase > dbase.sql
Enter password:
root@ironforge:~#

This produces INSERT statements in the format:

INSERT INTO `hexhex` VALUES (0xDEADBEEF000000000000000000000000);

.. which are import-safe.

Re-import it, and check it's right:

root@ironforge:~# mysql -p dbase < dbase.sql
Enter password:
root@ironforge:~# mysql -p dbase

mysql> use dbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from hexhex;
+------------------+
| id               |
+------------------+
| ޭ▒               |
+------------------+
1 row in set (0.01 sec)

mysql> select hex(id) from hexhex;
+----------------------------------+
| hex(id)                          |
+----------------------------------+
| DEADBEEF000000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql>


Yes, the logical reads include non-leaf level index pages.

For the index scan in the first query, the first logical page is located by traversing the b-tree to find the first index leaf page, followed by a scan of the entire index in logical order. These stats include the 2 non-leaf pages and all index leaf pages.

For the index seek in the second query, the first logical page satisfying the criteria is located by traversing the b-tree followed by a scan of the leaf pages. These stats include the same 2 non-leaf pages the full scan needed to locate the first page (assuming no rows exist before the key value zero or those are in the first page). The same number of leaf pages are touched because all pages qualify for the seek predicate.

The equality predicate in the third query touches the same number of non-leaf pages of the b-tree hierarchy as the first 2 queries, although maybe different non-root pages, Only a single leaf page is needed to satisfy the predicate for a total of 3 logical IOs.


Sure you can, you can append the SAMPLE clause to your selects:

For example:

select * from table sample(20);

sample_clause

  The sample_clause lets you instruct the database to select from a
  random sample of data from the table, rather than from the entire
  table.

  BLOCK
  BLOCK instructs the database to attempt to perform random block
  sampling instead of random row sampling.

  Block sampling is possible only during full table scans or index fast
  full scans. If a more efficient execution path exists, then Oracle
  Database does not perform block sampling. If you want to guarantee
  block sampling for a particular table or index, then use the FULL or
  INDEX_FFS hint.

  sample_percent
  For sample_percent, specify the percentage of the
  total row or block count to be included in the sample. The value must
  be in the range .000001 to, but not including, 100. This percentage
  indicates the probability of each row, or each cluster of rows in the
  case of block sampling, being selected as part of the sample. It does
  not mean that the database will retrieve exactly sample_percent of the
  rows of table.

  ...


Consistency errors for a SharePoint database

Restore from your backup if possible.

"Allow data loss" is really a terrible option.

This excellent article will help you:

http://www.sql-server-pro.com/dbcc-checkdb.html


I need to copy data from remote Postgres server that has limited disk space and limited connectivity to a server

It sounds like you don't need a local server, just a queue file that you write the data to then insert in batches into the server when it's available.

Just log to a local CSV file or something.

Alternately, you could use something like Londiste (from Skytools) to replicate the rows from a local server to the remote. It's pretty fault-tolerant.

Finally, the team I'm involved in will soon be releasing a new replication option for PostgreSQL that would meet your needs rather nicely. It's a few weeks/months off production release, though.


How to write this query in SQL Server Compact in more elegant way?

There is no multi value CASE statement in T-SQL. However, you could do a join to a constant value row source like this:

SELECT *
  FROM dbo.Statuses AS S
  LEFT JOIN (VALUES(1,'Success','AStatus','Expected:1'),
                   (0,'Failure','AStatus','Received:0')
            ) AS SV(AStatus, Status, Reason, Message)
    ON S.AStatus = SV.AStatus;


create incremental number in oracle sql query

I think this will work (based on  this page ( http://psoug.org/definition/LEVEL.htm ) as a starting point):

WITH counter
AS ( SELECT LEVEL seq
       FROM DUAL
     CONNECT BY LEVEL <= 4 )
SELECT (2008 + seq - 1) myYear
  FROM counter
 ORDER BY 1
;

This should return:

myYear
------
  2008
  2009
  2010
  2011

Adjust 2008 and 4 to get different results.


I have this solution for MySQL 5.7, where you have to do the work manually. In the case of MySQL 8.0+ you can simply use JSON_TABLE

SELECT
    JSON_EXTRACT(C.AddressIdentifiers, CONCAT('$[', Numbers.N - 1, '].Identifier')) AS Identifier,
    JSON_EXTRACT(C.AddressIdentifiers, CONCAT('$[', Numbers.N - 1, '].AddressType')) AS AddressType,
FROM
(
    SELECT @row := @row + 1 AS N FROM
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T2,
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T1,
    (SELECT @row:=0) T0
) Numbers -- Natural numbers from 1 to 100
INNER JOIN Customers C ON Numbers.N < JSON_LENGTH(C.AddressIdentifiers)


How much will using a varchar(15) PK affect my tables performance?

I don't know that the PK is your issue. To me, doing a request for all 400k rows, 15 seconds doesn't sound too bad. PKs and indexes really come into the equation when you're trying to filter the set (using WHERE conditionals).


store result set in temporary table, variable or separate variables within a trigger

The first method is the best because of the least overhead for the queries generated. In fact, in the MySQL Stored Procedure Language, you want as few declared variables as possible.

The second is not possible since the MySQL Stored Rrocedure Language does not have object support. A clumsy but workable UPDATE JOIN is possible if you...

update the question with the CREATE TABLE statement for the product table
show us the trigger defintion you have thus far

The third method is not possible because explicit DDL and DDL via Dynamic SQL are not allowed in MySQL Triggers.

You may have to create a regular table using either the MyISAM or MEMORY storage engine. Then, you can have the trigger compile your data to a table that actually exists. MyISAM is better because should a server go down, the compiled data thus far is on disk. MEMORY tables are faster to write to, but are gone on system restart.

DO NOT USE CREATE TEMPORARY TABLE AT ALL because such tables only last as long as the DB connection lives, and would be private unto the call of the trigger also. Even worse off, if you are using MySQL Replication and you run STOP SLAVE on the slave, any tables created via CREATE TEMPORARY TABLE disappears from the SQL Thread and replication breaks immediately when you run START SLAVE and those temp tables no longer exist.


How to resolve error 15517 when DBO is set to SA?

Try this to verify the owner of the database:

SELECT name AS DB, SUSER_SNAME(owner_sid) AS owner FROM sys.databases

If the owner is not sa, you can do ALTER AUTHORIZATION or use the deprecated command sp_changedbowner:

USE [*db*]
GO
EXEC sp_changedbowner 'sa'
GO

Even though it is deprecated, this procedure is still available in later versions and I have had more luck with that command for avoiding putting the database into single user mode.

Here is the sp_changedbowner doco


Read performance when indexing one column vs indexing multiple columns

The index is a guide to the main table.  So your first query will use the Index on column A to identify which rows in the table have the data it wants, then does a key lookup to get the rest of the data and do further filtering if necessary.

So your second query will also use Index A and then do a key lookup and then do further filtering as required.

But you can have just a single index on A,B and C and your first query will still use that index (providing that A is the first column indexed).

So you can have your cake and eat it too.  I wouldn't create both indexes, I would just create the one on A,B and C.  However, If your table is narrow and only has four or five columns then I would only index on A.


File is getting generated into folder, But not a csv file

On your BCP command use the -t switch to specify which field delimiter you want to use
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server?view=sql-server-ver15


The -b parameter applies to the batch, not statements within the script.  If you separate each statement with a GO batch terminator, you will get the behavior you expect.

Remember that SQLCMD sends the entire batch of statements at once to SQL Server for execution.  You'll need T-SQL control flow statements (including TRY/CATCH) to handle errors on the server side, if needed.


Dramatic decrease in SQL Azure performance after scaling to new edition

I conducted a series of performance tests against the new service tiers (as mentioned by another poster above).

I tested I/O rates back in July and have now also tested the memory (i.e. max buffer pool size) in each tier:
http://cbailiss.wordpress.com/2014/11/11/azure-sql-database-memory-limits-by-service-tier/

(I was going to add this as a comment on dark_perfect's comment above, but on this site I don't have enough reputation to add comments yet).


changing a range into discrete values for a field in oracle

Assuming you have inserted the data into Oracle perhaps using an external table, you could use the connect by clause of a hierarchical query to generate the additional rows you need.

Setup to simulate the source data:

DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1 (Zip Varchar2(7), Ground Varchar2(3), Air Varchar2(3));
INSERT INTO t1 VALUES ('010-041','005','305');
INSERT INTO t1 VALUES ('150-163','004','304');

Solution:

CREATE TABLE t2 AS (
  SELECT substr(zip,1,3) + Level - 1 Zip, Ground, Air
  FROM t1 a CONNECT BY level <= substr(zip,5,3)-substr(zip,1,3)
  );

If you'd rather do more work on the Excel side, then you could create the following table in Oracle:

CREATE TABLE t1 (Zip Number(3), Ground Varchar2(3), Air Varchar2(3));

Then run a formula like this for each row in Excel:

=CONCATENATE("INSERT INTO t1 (SELECT ",MID(A2,1,3),
    "+Level-1,'",B2,"','",C2,"' FROM dual connect by level <=",MID(A2,5,3)-MID(A2,1,3),");")

This will produce insert statements that you can then run in Oracle.  They will look like this:

INSERT INTO t1 (SELECT 010+Level-1,'005','305' FROM dual connect by level <=31);


Query the definition of a materialized view in Postgres

Looks like 9.3 and up you can do:

select * from pg_matviews;
select * from pg_matviews where matviewname = 'view_name';

More info found here: https://stackoverflow.com/questions/29297296/postgres-see-query-used-to-create-materialized-view


For a "large" delete (more than, say, half the table), do something like:
CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real WHERE ...  -- what to keep
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

The INSERT..SELECT is slow; the other steps are fast.  No need to "shrink" unless the original table was created with innodb_file_per_table=OFF, in which case the disk usage will increase with this process.
More big-delete tips for more cases:  http://mysql.rjweb.org/doc.php/deletebig


Identify threads with maximum difference in time

One thing to note a query execution does not go in parallel for all operators. It is decided per operator cost and your setting for 'Cost Threshold of Parallelism' and 'Max Degree of Parallelism'.

In theory you are looking at operations (operator with in execution plan) that had the highest delta in runtime between threads.

Depending on how much you collect, I suggest you create some indexes to speed things up. Let me know if you have any issues running my code.

/*============================================================================
PerThreadcollection.sql
Written by Taiob M Ali
SqlWorldWide.com

This script will show where queries (certain tasks within execution plan) are running
in parallel and difference in time for the longest thread and the shortest thread is high (excluding co-ordinator thread).

Instruction to run this script
--------------------------------------------------------------------------

--You will have to adjust @howManyTimes Variable values based on your requirements
--You will have to adjust @WaitSec Variable values based on your requirements

============================================================================*/

USE [DbaDB]
GO
--Creating table to hold raw data
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [DbaDB].[dbo].[PerThreadcollection](
    [timestamp] [datetime] NOT NULL,
    [session_id] [smallint] NOT NULL,
    [status] [nvarchar](30) NOT NULL,
    [command] [nvarchar](32) NOT NULL,
    [blocking_session_id] [smallint] NULL,
    [wait_type] [nvarchar](60) NULL,
    [exec_context_id] [int] NULL,
    [task_state] [nvarchar](60) NULL,
    [text] [nvarchar](max) NULL
) ON [PRIMARY]

GO

--Collecting raw data
DECLARE @howManyTimes int=0
DECLARE @WaitSec char(2) = '30'
DECLARE @Delay char(8)
SET @Delay = '00:' + '00:' +@WaitSec

WHILE(@howManyTimes<101)
BEGIN
INSERT INTO [DbaDB].[dbo].[PerThreadcollection]

SELECT
        GETDATE(),
       er.session_id,
       er.status,
       er.command,
       er.blocking_session_id,
       er.wait_type,
       ot.exec_context_id,
       ot.task_state,
       st.text
FROM   sys.dm_exec_requests er
       JOIN sys.dm_os_tasks ot
         ON ( er.session_id = ot.session_id )
       CROSS apply sys.Dm_exec_sql_text(er.sql_handle) st
WHERE  er.session_id IN (SELECT session_id
                         FROM   sys.dm_os_tasks
                         GROUP  BY session_id
                         HAVING Count(exec_context_id) > 1)
WAITFOR DELAY @Delay
SET @howManyTimes = @howManyTimes + 1
END
GO

 ;WITH MinExecution (sessionID, execContextId, queryText, mintime)
 AS
 (
  Select session_id, exec_context_id, [text], MIN(timestamp) AS [MinTime]
  FROM [DbaDB].[dbo].[PerThreadcollection]
  WHERE exec_context_id <>0
  GROUP BY session_id, exec_context_id, [text]
),

MaxExecution (sessionID, execContextId, queryText, maxtime)
 AS
 (
  SELECT session_id, exec_context_id, [text], MAX(timestamp) AS [MaxTime]
  FROM [DbaDB].[dbo].[PerThreadcollection]
  WHERE exec_context_id <>0
  GROUP BY session_id, exec_context_id, [text]
)

SELECT sessionId, queryText, (MAX(duration)-MIN(duration)) AS [deltaInSeconds] from
(
SELECT
  mi.sessionID, mi.execContextId,mi.queryText ,DATEDIFF(ss, minTime, maxTime) AS [duration]
FROM MinExecution AS mi
JOIN MaxExecution AS mx
ON mi.sessionID=mx.sessionID
  AND mi.execContextId=mx.execContextId
  AND mi.queryText=mx.queryText) as t
GROUP BY sessionId, queryText
ORDER BY [deltaInSeconds] DESC

GO


Oracle sequence generation performance during persists of large data sets

The larger the CACHE on the sequence, the fewer times that you'll need to wait for Oracle to acquire the latch to be able to update the data dictionary when a new set of values need to be generated.  There is definitely at least the potential for some performance benefit by increasing the size of the cache.  On the other hand, if the CACHE is already at 10,000, you're only going to need to update the data dictionary 20,000 times in the course of a 200 million row data load (assuming one call to the sequence.nextval per row).  Without seeing your system, it seems unlikely that these 20,000 updates would account for a meaningful fraction of the time you're spending in a 200 million row data load.  You would potentially shave a couple of seconds off the load by increasing the size of the cache but that probably isn't meaningful given the amount of time you're likely spending on I/O.


Msg 21, Level 21, State 1, Line 1 Warning: Fatal error 9001

That error means that the log for your database (or for tempdb) is not avaialble for some reason.  That's a serious problem that is usually fixed by restarting the server.

If the problem recurs, it could be many things having to do with problems with your storage system.  One possible thing to check is to see if your database's log file (or tempdb's log file) is running out of space (not sure if that can cause this error or not).


user permission cache in session or check from database?

For maximum performance, caching (i.e., precomputation) is pretty much a requirement. Unfortunately, though, there's no cookie-cutter answer for how to implement it. It's not as simple as picking either solution x or solution y.

What you want to do is come up with a strategy that caches the information you need as close to the request for the information as possible, while still meeting the business requirements and staying within the architectural limitations (which, by the way, you may want to change to encourage the use of caching, not only for this, but for other areas, too).

Here are a few factors and some questions related to each to get you started thinking about how best to implement this for your own scenario:

The application architecture. Are you using a caching tier? Are user sessions sticky on the web tier, or is it round-robin? (Should you change this?) Are there any other applications accessing the database for the same security information besides this particular web application?
The requirements for the application. Should changes to the security permissions take effect immediately (i.e., on next page load), or is a delay acceptable? How long a delay? Immediate effects generally limit our ability to use caching to its maximum effectiveness, but that's definitely not to say it's infeasible to cache at all.
How expensive it is to compute the required information? Will it be computed on-demand, or proactively when the underlying data changes? Synchronously, or asynchronously? Caching can be introduced within the database itself, which alone may be enough to satisfy the immediate performance requirements.
How frequently will the information be needed? You say it's needed for every page on the site, but what does that mean in terms of requests/sec and overall server load? Could the mechanism this information is retrieved by be optimized further?
How frequently will the information be changing? Since you're asking about caching at all, I assume it doesn't change frequently, but that could be a bad assumption on my part, so I'm mentioning it. If the information changes frequently, pay particular attention to the performance of the cache invalidation strategy you put in place.

As I eluded to, there are multiple opportunities and configurations for caching in this scenario, using any combination of: the data tier, the web (application) tier, and the caching tier (if applicable). Of course, the more levels of caching you do, the more complex the environment and codebase becomes, so you definitely want to watch out for that.


SSMS Activity Monitor Expensive Query start time

There are no such options!

However, you can create your own result set as shown in the following snippet.

Please, notice a couple of things.

First, you can change a period of time you are interested in by changing the first line of the snippet.

Second, the WHERE clause is applied on the last_execution_time column.

And, finally you can change the order if you are interested in expensive queries based on CPU or logical writes.

DECLARE @filterDateTime DATETIME = '2019-10-16 11:09:00.000'

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset
                                                                            WHEN-1
                                                                            THEN DATALENGTH(qt.TEXT)
                                                                            ELSE qs.statement_end_offset
                                                                        END - qs.statement_start_offset) / 2) + 1),
              qs.execution_count,
              qs.total_logical_reads,
              qs.last_logical_reads,
              qs.total_logical_writes,
              qs.last_logical_writes,
              qs.total_worker_time,
              qs.last_worker_time,
              qs.total_elapsed_time / 1000000 total_elapsed_time_in_S,
              qs.last_elapsed_time / 1000000 last_elapsed_time_in_S,
              qs.last_execution_time,
              qp.query_plan
FROM sys.dm_exec_query_stats qs
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
     CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE last_execution_time >= @filterDateTime
ORDER BY qs.total_logical_reads DESC; -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time


Add replica from node 1 can't find node 2 while installing an Always ON AG lab on virtual machines

There are a couple of different questions here.

Question #1

  'The following required directories do not exist on replica WINN02\SQL_AG_02 : C:\Program Files\Microsoft SQL
  Server\MSSQL14.SQL_AG_01\MSSQL\DATA.

While I would not put any database files on the C:\ drive, the error is saying that the

C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_AG_01\MSSQL\DATA

folder does not exist on the WINN02 VM, resulting in the database files not being found.

Resolving he issue

You should move these database files first to a non c:\ disk and then check that both folder structures are the same with

SELECT DB_NAME(database_id), physical_name FROM SYS.MASTER_FILES

If your C:\ drive is full, your VM's are done for. Resulting in a crash and possible corruption.

Question #2

  Cannot open backup device
  '\Winn01\nzfagsharefolder\BackupLocDb_cbbf8fb2-f6e8-4aa9-8995-675364932027.bak'.
  Operating system error 5(Access is denied.). BACKUP DATABASE is
  terminating abnormally. (Microsoft SQL Server, Error: 3201)

This error happens due to the ag creation wizard trying to take full & log backups of the databases to later restore on the secondary instance.

Resolving he issue

You should look into granting the service accounts for both instances the FULL control permission on the \Winn01\nzfagsharefolder\location. And make sure that your user account has enough rights (sysadmin).

Question #3

  The problem seems about connection. So I made some connection test. 1.
  When I try to connect instance node2 via computer name, in node1, it
  shows connection error, attached the screenprint

The connection issues from node1 to node2 are a different problem.

There are a few things to check, but if you can connect via IP you could look into DNS name resolving.

Resolving he issue

Using the FQDN to connect, looking into the tcp settings settings in the configuration manager such as the port used (E.G. changing dynamic ports to static ports).

Another solution could be enabling the browser service.


Depends on your requirements.
A standby database is typically a copy of the primary database. Meaning you can not restore and recover a past state of the database. If something goes wrong in the primary database, that change depending on configuration may be immediately replicated in the standby database. This can be handled by using flashback or configuring a standby to lag behind on purpose.
With backup, a database can be recovered to any point of time in the past within the recovery window or redundancy retention policy, which could be days, weeks or even more. It may be required to create a copy of a past state of the production database in a test environment. Some companies may have regulations so they need to preserve past states of the database for a specific amount of time, or they need to be able to restore and recover their database as of in any past point in time in a specific period.
Yes, I have seen customers with Data Guard who did not make database backups, but it is uncommon.


I'm not sure if that is what you're trying to achieve, but to count working days you need a range of dates.
IMHO the best way to deal with dates is by using a calendar table. Keep in mine that you need to add holidays and days off to your calendar. You can find a lot of samples googling a bit.
If you don't have/want to use a calendar table, you can take advantage of generate_series function.
create table t (dt timestamp);
insert into t values ('20200110'),('20200203'),('20200715');

select
    date_part('month', dts) mt,
    count(*) as wd
from
    (select
        generate_series(date_trunc('month', dt)::date,
                        (date_trunc('month', dt)::date
                                    + interval '1 month - 1 day')::date,
                        interval '1 day') dts
     from
        t) t2
where
    date_part('dow', dts) <> 6
group by
    date_part('month', dts);

mt | wd
:- | -:
7  | 27
1  | 27
2  | 24

db<>fiddle here


Is a geographically distributed application with SQL Server replication a good idea?

Replication from one box to area servers, then having those area servers replicate to the store servers within that area is easy.  Setting up the replication to feed from two different area servers to each store server isn't really going to be doable.  You could probably hack it to work, but it wouldn't be all that easy.

You would be better off setting up the area servers with high availability so that the chance of the area server going offline in minimal.


What happens to an SSIS package when I alter a table that is used as a destination?

If you aren't changing the destination table name or the name or properties of any existing columns, then the package may continue to run without any changes.

If you need to actually include these new columns in your import, though, then you will probably need to update the package.

If you don't have the dtsx file, then I assume the package is published in an SSISDB?

Here are some articles that discuss editing/exporting a published package for different SQL/SSIS versions:

How to export a package from SSISDB?
Editing Published SSIS Package in SQL Server 2012.


How can I visualize the entire delete cascade of an existing database?

Dbeaver has Diagram tool. Just drug and drop your tables and you'll see relationships


I have two suggestions:

1) I hate bringing up commercial products but there is a $49.00 tools to Migrate MSSQL to MySQL

2) Try MySQL's MSSQL Migration forums for further suggestions

UPDATE 2011-06-03 18:03 EDT

There is an old product that went EOL back in January 2010 called the MySQL Migration Toolkit. If you can get a hold of it, you can use it.

UPDATE 2011-06-03 18:06 EDT

I found the archives !!! Here is the MySQL Migration Toolkit

UPDATE 2011-06-03 18:11 EDT

Here is the MySQL Migration Toolkit Overview

UPDATE 2011-06-03 19:08 EDT

Another commerical product ($29)

UPDATE 2011-06-03 19:30 EDT

Here is a list of Freeware tools that Migrate MSSQL to MySQL.

UPDATE 2011-06-15 17:47 EDT

Get the WhitePaper (PDF) from Oracle on the Guide to Migrating Microsoft SQL Server to MySQL (Still Commercial)

UPDATE 2012-08-21 01:24 EDT

According to this MySQL WebPage, the section MySQL Workbench: Database Migration Wizard claims that the MySQL Workbench has the capability of Migrating DB Objects from SQL Server to MySQL.


Can a slave with a dynamic ip update the master

Only the master would need a fixed IP address, since its the slaves that pulls data. You say UPDATE the master in your question, but it is not possible for a slave to update a master, unless you have a MASTER to MASTER replication setup. As you are using the word slave, then that assumes that the Slave server only pulls data but doesn't update the Master.

As for security, when it comes to grants, you would need to give your replication user access to the Master using a wild card.

On the Master MySQL server

GRANT REPLICATION SLAVE ON *.*
TO 'replication'@'192.168.1.%'
IDENTIFIED BY 'replicationuserpassword';

Where percent represents 0 to 254. You could also just put % like this

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replicationuserpassword';

So that the replication user can access the master server from anywhere

Here is a post I answered on setting up replication.

If the slave will not always be online, there is a threshold of transactions that will be available. If you wait past that threshold, it will no longer be possible to synchronize.

On the master MySQL server, in the my.cnf file, you might find an entry like this:

expire_logs_days    = 1

Which means that only 24 hours of binary logs will be conserved on the master server.

By default, MySQL does not automatically remove binary log files. Considering the amount of space they could take up, it is unlikely that there is no automatic removal.


Active Directory to authenticate user access to Postgres DB through ODBC driver. Is this possible?

Active Directory has 3 kinds of authentication: NTLM (NT LAN Manager), Kerberos (GSSAPI), and LDAP. Active Directory is essentially a fancy LDAP server with extra authentication mechansims

LDAP is centralized authentication, but not single sign-on. Your password is the same as when you login to your computer, but you need to login to the database again

NTLM is single sign-on, but only works on Windows. PostgreSQL supports it via SSPI

Kerberos is also single sign-on, but works with Windows and UNIX/Linux/Mac too. You login to your computer, get a Kerberos ticket, and Postgres uses this ticket to authenticate you. Postgres needs to be setup as Service Principal in Active Directory

I would say that Kerberos or SSPI is what you want.

Start with https://www.postgresql.org/docs/current/static/auth-methods.html


Concatenate 2-3 table field values in 1 table field in access

You shouldn't need to concatenate the fields at all. You can find out whether a customer has filled out the survey for a given month and year by using all 3 columns:

SELECT * FROM Surveys
WHERE CustomerID = 1
AND SurveyMonth = Month(Date())
AND SurveyYear = Year(Date())

The Date() function returns the current system date. The Month() and Year() functions return the month and year, respectively, of the date passed to them.

A suggestion: store the entire date a survey was taken as a Date type in the database rather than using separate columns for month and year. This will simplify lookups and storage, making the query above look more like:

SELECT * FROM Surveys
WHERE CustomerID = 1
AND Month(SurveyDate) = Month(Date())
AND Year(SurveyDate) = Year(Date())

where SurveyDate is a column of Date type.


I see two problems with your date filter:

You're applying TO_DATE on a DATE datatype. It is not only superfluous, but it actually causes unexpected behaviour.

Here's how Oracle analyses the expression TO_DATE(data_inicio,'yyyy/mm/dd'):

Oracle searches for a function named TO_DATE that has two arguments (DATE, VARCHAR2)
Since no such function exists, Oracle looks for related functions that are accessible with implicit conversions.
(Un)fortunately, a single TO_DATE function exists, so there is no ambiguity here, the function choosen is TO_DATE(VARCHAR2, VARCHAR2).
Your date is converted to a VARCHAR2 with the current session date format, which means that the behaviour is unpredictable. The expression is equivalent to:

TO_DATE(TO_CHAR(data_inicio, 'current NLS_DATE_FORMAT'), 'yyyy/mm/dd')

In this case you will lose the time component of the date if your NLS_DATE_FORMAT is YYYY/MM/DD.

You should never rely on implicit conversions because the behaviour is context dependent.
Your interval filter is wrong: two intervals [A,B] and [C,D] will intersect if and only if:

A <= D
and B >= C

Your query should look like:

SELECT COUNT(*) AS COUNT
  FROM indicador_dados
 WHERE id = '23'
   AND date_start  <= to_date('2013/05/16', 'yyyy/mm/dd')
   AND date_end >= to_date('2013/05/15', 'yyyy/mm/dd')


Use index intersection feature in MongoDB 2.6 as compound shard key

In order to deploy a shard key in your case (name, age) you need to pre-create an index on (name, age).

The shard key can be unique, but I don't see how a unique constraint fits here, for example isn't it quite likely to have two same records? (name:Mary age:26?).

You cannot enforce a unique constraint directly to the database without a compound index.

Index intersection is to combine two index on the run-time. For example, you have an index on name and one on age and a query {name='somename' and age='someage'}. Prior to 2.6, you needed a compound index on (name, age). On 2.6, the optimizer can use these individual indexes to execute the above query.


Does SQL Server prioritize select and update statements?

There is no priority in SQL Server for commands which ever comes first would be executed first. There would always be nanosecond/microsecond difference, of course you would not be able to notice it.


How to identify all WALs created during start_backup and stop_backup?

The file names are 24 hex digits long, broken into 3 fields of 8 digits each.  The first is the timeline, which you probably don't need to worry about here.  The other two are called the "segment" and the "file".  They are really a single counter, the "file" rolls over by incrementing the "segment" by one and going back to zero.

Oddly, only the last two digits of the "file" chunk are ever non-zero.  (This is apparently so that the offset into a logical 'segment' fits in a 32 bit uint, which was important at some point in the distant past)

In 9.1 and 9.2, the "file" skips FF.  So from FE, it rolls back over to 00 while incrementing the segment number.  In 9.3, it switches so that FF is used.  So the sequence for your version would go:

0000000100000000000000FD
0000000100000000000000FE
000000010000000100000000
000000010000000100000001
...
00000001000000BE000000F9
00000001000000BE000000FA
00000001000000BE000000FB
00000001000000BE000000FC
00000001000000BE000000FD
00000001000000BE000000FE
00000001000000BF00000000
00000001000000BF00000001
00000001000000BF00000002
00000001000000BF00000003
00000001000000BF00000004

Getting the files from pg_xlog is tricky, because those files get recycled (i.e. renamed to have the name of some future file-name, then overwritten once it gets around to needing that file).  This means that the timestamps on the files are all screwy.  A recycled log file will have the name of some future file, but the contents and timestamp of some past file.

If a file was recycled before you had a chance to copy it, then your backup has failed and you need to try again with a higher wal_keep_segments.

And of course if you copy the "current" file before it was finalized, it will be missing the changes that occurred after the point you copied it.

It is much easier to get them from the wal archive, because that doesn't have the recycling problem and because they don't show up at all until they are finalized.


Can't delete a login due to db ownership — but no dbs are owned by this login

You should check the ownwers of the databases with the following script:

SELECT d.name, sl.name
    FROM sys.databases as d JOIN sys.syslogins AS sl
        ON d.owner_sid = sl.[sid]

You might then find out that a database is linked to on of your Windows/AD accounts.

There is a slight difference between a SQL Login (native or Windows account) having the role db_owner and an account being the database owner.

If you do find a database with an account linked to the Windows/AD account you want to get rid of, then execute the following command while connected to that database:

USE <databasename>
GO
sp_changedbowner '<new_account>'

  IMHO: Changing the owner to 'sa' is a viable option.

Good luck.


As SQL, show global status like 'com_%' will show you a break down of the types of queries. Any high volume of update, replace, delete should be an indication that you shouldn't be using MyISAM.
Given there are 5kqps SHOW FULL PROCESSLIST at any instant should give you a sample of activity.
Don't worry about IOPs, focus on the queries and watch the benefits flow from that. If queries are contained to the primary key there is no reason for them to result in disk IOPs. Filesystem cache is also used so that's another buffer preventing IOPs.
If its the IOPs (hardware not handling it) that's the problem consider moving to InnoDB. If you value your data, move to InnoDB.


SQL Allow user to run Read Only Stored Procedures

The closest you could come is by putting them into a specific role, and granting that role explicit execute access on just those stored procedures. But you would have to identify all of those stored procedures manually.

Even if a procedure were somehow marked in the metadata as "this procedure doesn't write," the permissions system has no way to say "only do things with read-only objects."