Manually set log file size after shrink SQL Server 2008 R2

Your file management can be a completely online operation.  You have two paths, depending on your need to retain your log information for recovery purposes:

Point in time recovery not needed

Convert the database to SIMPLE recovery.  Execute a checkpoint to write transactions to disk.
Flatten the log.
Resize the log to the appropriate size.

I also recommend setting a fixed growth amount and unlimited growth (so as to help manage your log better).  Note, fixed growth amount is very much an it depends amount, I'd recommend going with 1-2 GB initially depending on how much growth that log could expect to see.  Ideally, your log won't grow much, so this shouldn't have much of an impact.  If your log is growing regularly, you might need to revisit your size.

Accomplished using:

ALTER DATABASE [foo]
SET RECOVERY SIMPLE;

CHECKPOINT;

DBCC SHRINKFILE (foo_log,0);

ALTER DATABASE [foo]
MODIFY FILE (NAME=foo_log,SIZE=8000MB,MAXSIZE=UNLIMITED,FILEGROWTH=1000MB);

--Optional if you want the database in full recovery mode
--for point in time recovery going forward
ALTER DATABASE [foo]
SET RECOVERY FULL;

Point in time recovery needed

The biggest hangup will be that you can not shrink your log file past your currently active VLF segment.  To see this, you can use DBCC LOGINFO in the database context.  Any segment with a Status=2 is active.  To clear active segments, you will need to run a transaction log backup when no transactions are currently active in that segment.  Your steps are:

Run a transaction log backup.
Shrink your file. (Ideally flatten, but if your database is active this will be hard to do).
Repeat steps 1 and 2 until your log is an appropriate size, ideally as small as possible.
Resize the log to the appropriate size.

Accomplished using:

BACKUP LOG [foo] TO DISK='<Location of t-log backup>';

DBCC SHRINKFILE (foo_log,0);

--Repeat the above until your log file is small "enough"

ALTER DATABASE [foo]
MODIFY FILE (NAME=foo_log,SIZE=8000MB,MAXSIZE=UNLIMITED,FILEGROWTH=1000MB);

Some additional resources to understand what's going on here:

SQL Server Recovery Models
Kimberly Tripp's t-log recommendations
Database Checkpoints


DBForge can't connect with MySQL server, server is running

After hours of research I figured out that it was a problem of user authentication, I had only a user 'root@localhost'. Turns out I needed an user 'root'@'127.0.0.1'

After all of that, I discovered my version of DBforge does not support MySQL server v8.0


Joining timestamped records with the most-recent-prior records from another timestamped table

Guess I don't need to tell you that this is a seriously awkward data model. Anyway, I think this query would do what you're looking for:

SELECT subq2.sales_time, subq2.num_sold, subq2.effective_price,
   subq2.effective_price * subq2.num_sold AS total_sale_price
FROM (

  SELECT subq1.sales_time, subq1.num_sold, subq1.max_price_ts,
    (SELECT price FROM prices
     WHERE timestamp = subq1.max_price_ts) AS effective_price
  FROM
    (SELECT sales.timestamp AS sales_time, sales.amount AS num_sold,
      (SELECT MAX(prices.timestamp) FROM prices
       WHERE prices.timestamp <=  sales.timestamp) AS max_price_ts
    FROM sales
    ) AS subq1

  ) AS subq2

ORDER BY subq2.sales_time DESC;

There's probably a more concise way to write the above, perhaps using DISTINCT ON and ORDER BY to save having to fetch the price for the MAX time in a separate subquery, but I'll leave that as an exercise for the reader.

EDIT Alright, here's a simplified version which I think should work out to be equivalent but much faster.

SELECT DISTINCT ON (sales.timestamp)
       sales.timestamp AS sales_time,
       sales.amount AS num_sold,
       prices.price AS effective_price,
       prices.price * sales.amount AS total_sale_price

      FROM sales
INNER JOIN prices
        ON prices.timestamp <= sales.timestamp
  ORDER BY sales.timestamp DESC, prices.price DESC

If that doesn't work for you, it would be helpful for you to post a lot more information such as a minimal testcase and EXPLAIN ANALYZE showing how slow the query is for you.


Modelling a user-defined filter set in SQL

You shouldn't allow arbitrary queries for these filters. This opens the door to a world of pain, since arbitrary queries can take arbitrarily long and cause arbitrary mischief.

Rather, you should plan for certain types of filters that correspond to pre-defined queries, perhaps with some parameters that can be chosen by the user and are stored in a table.

You can implement these with functions or views.

Whenever a new type of filter is needed, you have to plan a new release.


Replication Server version 15.7.1 - Sybase ASE 15.5, how to change primary data server in RSSD?

You're going to need to provide more details on what you mean by 'migrate the current Sybase data server', eg, are you moving the dataserver? are you copying the dataserver? something else?

Also, there's generally nothing to do directly in the RSSD.  All commands should be issued either in the PDB, the PRS/RRS, and/or the RDB.

Generally speaking ...

If a database has been copied from elsewhere (eg, dump and load), you'll want to make sure the old repagent info is discarded and the old secondary trunc pt is removed, eg:

use new_db
go
-- in case repagent is running:
exec sp_stop_rep_agent 'new_db'
go
-- discard repagent config settings
exec sp_config_rep_agent 'new_db','disable'
go
-- in case sp_config_rep_agent didn't remove 2nd tp:
dbcc settrunc('ltm','ignore')
go

At this point you then use rs_init to add 'new_db' as a a new database/connection to the desired PRS.

NOTE: If 'new_db' is to be the active db in a WS setup, make sure the logical connection has been created before running rs_init.

Once you've added 'new_db' to the replication domain you'll need to continue with normal replication setup, eg:

make sure RDB(s) is added to the replication domain (if not already a member)
add route(s) to RRS (as needed)
setup repdefs/subs
assign any custom/modified classes to the DSI connections


Strange behavior when combining CONVERT(CHAR()) with RIGHT()

It's because of how the CHAR data type works.

Take a look at this example:

 DECLARE @d decimal(15,2)
 SET @d = 15.52

 SELECT CAST(@d AS CHAR(8)) AS CharD
 SELECT DATALENGTH(CAST(@d AS CHAR(8))) AS LengthCharD

 SELECT CAST(@d AS VARCHAR(8)) AS VarcharD
 SELECT DATALENGTH(CAST(@d AS VARCHAR(8))) AS LengthVarcharD

Notice the length of @d is 8 when it's CHAR but 5 when it's VARCHAR. CHAR is fixed width and thus padded with blanks.

Once you add '000000' to it and remove the decimal, you've now got a 13 character string: '0000001552< blank>< blank>< blank>.
When the RIGHT function gets just the right 6 characters, the result is 552< blank>< blank>< blank>.

'000000' is VARCHAR. VARCHAR + your CHAR string will result in a VARCHAR string but still includes the blanks from the CHAR.

VARCHAR does not pad with blanks and thus avoids this < blank> issue.


Sql Server's locks are described in good detail in the product documentation. A lock might or might not be escalated to page/table level, the behavior is documented, again, in Technet.

A fairly common tactic for deleting large amount of rows is to split up the delete in batches. Instead of deleting all the 5M rows at once like so,

DELETE FROM MyTable WHERE foo='bar'

rather try and delete data in, say, 100 * 50k row batches like so,

WHILE (@rows > 0)
BEGIN
    DELETE TOP (50000) FROM MyTable
    WHERE foo='bar'
    SET @rows = @@ROWCOUNT
END

By splitting the delete in batches, you should lock way less data than with a all-encompassing single-pass delete. YMMV, depending on the selectivity and other activity in your table.

In addition, keep in mind that delete is logged and you might get a nasty surprise with your transaction log size.

This article has nice an overview about how to batch delete data and mind the logs too.


First (common misunderstanding), distinct is not applied to individual columns, what you get is distinct rows. This is exactly the same as your GROUP BYdoes, so distinct is redundant.

Second, you need to determine which row among duplicates that you are interested in (or get a random one). You can use window functions to achieve this by enumerating ArchiveCatalogues per Artists:

 SELECT artist.ArtistID
    ,ArchiveCatalogues.IMAGER
    ,ArchiveCatalogues.AUCTION
    ,artist.surname
    ,artist.firstnames
    ,artist.dates
    ,artist.honorific
    ,artist.biog
    , row_number() over (partition by artist.ArtistID
                      -- add wanted order her as:
                      -- order by ...
                        ) as rn
FROM artist
JOIN ArchiveCatalogues
    ON ArchiveCatalogues.ARTIST = artist.surname
   AND ArchiveCatalogues.FIRSTNAME = artist.firstnames
WHERE artist.surname >= H *
  AND artist.surname < I

Note that I removed the group by since I'm not sure what the purpose was. Now you can select the first row from there:

SELECT ArtistID
     , IMAGER
     , AUCTION
     , surname
     , firstnames
     , dates
     , honorific
     , biog
FROM (
    SELECT artist.ArtistID
        ,ArchiveCatalogues.IMAGER
        ,ArchiveCatalogues.AUCTION
        ,artist.surname
        ,artist.firstnames
        ,artist.dates
        ,artist.honorific
        ,artist.biog
        , row_number() over (partition by artist.ArtistID
                          -- add wanted order her as:
                          -- order by ...
                            ) as rn
    FROM artist
    JOIN ArchiveCatalogues
        ON ArchiveCatalogues.ARTIST = artist.surname
       AND ArchiveCatalogues.FIRSTNAME = artist.firstnames
    WHERE artist.surname >= H *
      AND artist.surname < I
) as T
WHERE rn = 1;

The join looks strange but it's hard to tell without knowing what the tables and their key's look like.


Outputting the results of two select queries on to a single table in Oracle SQL

Look into GROUP BY queries.

SELECT gender, sum(PRICE) / count(*)
FROM ITEM, ITEM_CLOTHING
WHERE ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID
GROUP BY gender;

Or to refactor further:

SELECT gender, AVG(Price) AveragePrice
FROM ITEM
JOIN ITEM_CLOTHING ON ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID
GROUP BY gender;


Please verify the settings for MAXDOP. Please review suggestions by Microsoft. In most cases the optimal value is the number of dedicated cores, that are available to SQL Server. But maximum value is set to '8'. In some cases that could vary and needs to be tested well to set to higher values.

I assume, that you have two six-core Processors with Hyper-Threating enabled. It is possible, that your queries don't perform very well, with Hyper-Threating enabled.
In some cases the SQL Server Performance is better using 12 'real' cores, than 24 combined with Hyper-Threating.

You should try to disable Hyper-Threating and verify the new settings.

Unfortunately you wrote, that this is your production environment. Are you able to verify settings on similar hardware and similar settings?

Furthermore you should have a look on your I/O subsystem. Are there more than one tempdb?

Additional (I can not comment another comment):

DBCC FREEPROCCACHE will effect all user.
Try using with option recompile at the SQL Query that effects the Performance issue most.


install Galera but mariadb existing on system

Try using aptitude instead of apt-get. It is more intelligent. It not only will handle downgrading conflicting packages for you, but will make a series of recommendations asking you which of many possible suggested working scenarios you would like. e.g.

sudo aptitude install rsync galera-3 mariadb-galera-server

If you don't have aptitude on your machine yet, get it with

sudo apt-get install aptitude


What's the best way to ensure referential integrity on a replicated database?

Replicated data (articles) maintain the same data and schema across the subscribers as that on the publisher. In the case of transactional replication, the data changes are applied within the same transactional boundaries as those happening at the publisher.

In short, the referential integrity will be maintained.


It takes too long to update but when I do select on that updating table its completes very fast .

If the UPDATE query is taking a long time, but the SELECT part is fast when run on its own, then one possible reason is that the UPDATE is getting blocked due to incompatible locks being held by another session.

You can check for this while UPDATE query is running by, for example, running sp_WhoIsActive:

EXEC sp_WhoIsActive;

In this screenshot, I'm trying to run an UPDATE in session_id 57, but it's blocked by another session that's holding locks that my query needs.  You can see this in the wait_info and blocking_session_id columns, which show LCK_M_X waits (which is an exclusive lock, usually required by another UPDATE query running) and 53 (the other session that is holding the lock).

  After running the below query...Query completes very fast .

Running that count(*) query will likely make sure all of the pages needed for the query are in memory (meaning it will be faster, because they won't have to be read from disk first).  This probably just masking the blocking issue, as the blocking query finishes faster when the pages are in memory.


There are valid reasons for disabling AUTO_UPDATE_STATISTICS, such as wanting to have more control over the updating process of your stats. However disabling the auto update to prevent plans from changing destroys the purpose of the optimizer.
If you know your data very well and you are confident that a given plan will lead to good results regardless of the growth in certain tables, you should just force the plan with query store or a plan guide (https://docs.microsoft.com/en-us/sql/relational-databases/performance/plan-guides?view=sql-server-ver15). However before jumping into these kind of solution you should try the more standard approach of adding indexes, rewriting queries, adding hints, etc.
Avoid "shotgun" approaches such as disabling auto_update_statistics or setting MAXDOP =1 for whole instances. Instead you should identify the specific queries that are becoming pain points and analyze their executions. Contained "patches" such as index creation, hints or even forcing plans on specific queries should always be preferred over changes in configuration that will affect your workloads all across the board.
I hope this helps.


MySQL: Split column by delimiter & replace numbers with values from another column

I got the following query:

CREATE TEMPORARY TABLE IF NOT EXISTS
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
         , (
                SELECT name FROM category
                WHERE category.id = catid
            ) AS name
         , (
                SELECT path FROM category
                WHERE category.id = catid
            ) AS path
    FROM
      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
            FROM category
            CROSS JOIN (SELECT @rownum := 0) r
        ) AS tmp
    GROUP BY catid
    ORDER BY
       n

);

SELECT * FROM t1;
DROP TEMPORARY TABLE t1;

Result is:

*nr*   *catid*   *name*   *path*
1   426   Computers   /426
2   427   Other accessories   /426/427
3   428   Laser printers   /426/427/428

This is almost good, just need to merge the name column with delimiters.

So final query is:

CREATE TEMPORARY TABLE IF NOT EXISTS
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
         , (
                SELECT name FROM category
                WHERE category.id = catid
            ) AS name
         , (
                SELECT path FROM category
                WHERE category.id = catid
            ) AS path
    FROM
      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
            FROM category
            CROSS JOIN (SELECT @rownum := 0) r
        ) AS tmp
    GROUP BY catid
    ORDER BY
       n

);

/* SELECT * FROM t1; */
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;
DROP TEMPORARY TABLE t1;

We get the following query result:

path_long
/Computers/Other accessories/Laser printers


Many transactions are getting stuck in the "cleaning up" state - what could cause that?

I have the same issue on RDS. Some of "cleaning up" transactions blocked my ALTER TABLE query. And as I understood in MySQL 5.6 there is no way to find out which transaction should be killed.

So, my solution was to kill all "cleaning up" transactions. And it helped me.
And more thing: according to AWS doc on RDS you should use CALL mysql.rds_kill(pid) instead of standard KILL.


This is just my input, but I have supported most of the major players in the 3rd party backup vendors. I have supported clients that utilize Idera, RedGate, Dell LiteSpeed, NetBackup (which does not really have compression but is 3rd party), and a few off-the-wall vendors that I can't remember anymore.

The improved compression you may or may not get with 3rd party products have a trade-off....CPU workload. I have seen Dell's (LiteSpeed) product run in SQL Server 2014 environment against a database that is right at 500GB, and using high compression kills the CPU. The result was only a backup file of around 64GB. It ended up causing SQL Server to stop providing resources to the backups since Dell's product uses the VDI API. The compression level had to be dropped down from 7 to about 3, this brought the backup size down to about 82GB. We took a native backup with compression enabled of same database, and it was right at 84GB in size. So is the trade off and cost worth it? test, test, test

I seem to recall a client I had a few years ago was using Idera's product and I had constant issues with the backups for their medical databases that were 1TB+ in size. I think some of it had to do with the disk subsystem the backups were being written to, which again is why you need to test. Overall Idera's GUI interface is not inviting to me, and could use great improvement. I would chose RedGate or Dell over Idera any day of the week.

You also have to remember that most 3rd party backup products are going to use the VDI API to SQL Server. This in and of itself can cause problems as well, frequent to often "BackupVirtualDevice" or "BackupIORequest" errors showing up in the error log. I have some of these be false positives (backup actually did occur) and some where legit failures. Make sure to read through the vendor support article or forums. RedGate and Idera have very good support systems in place and are very good about getting back with help. Dell I have not had to make any support calls with their product so far.

The one last thing I will bring up is the format of the backup taken by the 3rd party product. If you get into a DR situation are you going to have a standby server licensed with the product as well? I believe Dell allows you to install LiteSpeed without a license to do a restore, but it has to be licensed to do any backups. I think most of the vendors also include a utility to convert a vendor specific database to a SQL Server native backup file, in case of emergency.


Look at the first column TYPE, from the docs

local This record matches connection attempts using Unix-domain sockets. Without a record of this type, Unix-domain socket connections are disallowed.
host This record matches connection attempts made using TCP/IP. host records match either SSL or non-SSL connection attempts.

Your type is local, but your error message indicates you're trying to connect to 127.0.0.1 which is an IP address. You either need to connect using unix sockets without an ip-address (no -h in psql) or change the type to host.
From the docs on PgAdminIII

The host is the IP address of the machine to contact, or the fully qualified domain name. On Unix based systems, the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine, or be set to an alternate path containing a PostgreSQL socket. If a path is entered, it must begin with a “/”. The port number may also be specified.


Sql Server Full Text Search one table for text values in another table

Yes, but not directly.  You would have to build dynamic SQL strings, one row for every join set (pair of Docs and Tags rows), and then execute those strings.  That's probably not practical.

In the FROM query, if you had 1,000 records in Docs and 1,000 records in Tags, the result set would have 1,000,000 rows.  I'm assuming you knew that and just dummied up some quick T-SQL - obviously this wouldn't work in production.


Generate script to automate renaming of default constraints

Ok, couple of things.

always use EXEC when executing stored procedures; the shorthand without EXEC only works when it is the first statement in the batch (and that will not be the case here).
always use semi-colon terminators - in this case they are useful in lieu of pretty carriage returns and indentation, but they are always wise to have.
always use QUOTENAME() instead of manually applying square brackets yourself. In this case you're probably safe, but there are cases where the manual approach will break.
you can test the PRINT output but it won't necessarily be complete if your total command is > 8k (see this tip for some alternative approaches).

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'EXEC sys.sp_rename N'''
    + QUOTENAME(s.name) + '.' + QUOTENAME(d.name)
    + ''', N''DF_' + t.name + '_' + c.name + ''', ''OBJECT'';'
  FROM sys.tables AS t
  INNER JOIN sys.default_constraints AS d
     ON d.parent_object_id = t.object_id
  INNER JOIN sys.columns AS c
     ON c.object_id = t.object_id
    AND c.column_id = d.parent_column_id
  INNER JOIN sys.schemas AS s
     ON t.schema_id = s.schema_id
  WHERE d.NAME LIKE N'DF[_][_]%';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;


How to store many smallish time-series in a relational DB?

Are your queries supposed to collect data for each month/year?

You can use partitions to store your information in different physical files.  Partitions can increase the speed of SELECT statements when you only need information about a specific period. http://msdn.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx

When creating a partitioned table in Microsoft SQL Server you can also create different file groups on different physical locations and back those up separately.

With regards to your question about the database design, you may want to read about normalization here: http://en.wikipedia.org/wiki/Database_normalization


mysql... compare and match items in same column

Here is the query you need

SELECT
    Louisville.name LouisvilleServer,
    Vegas.name VegasServer
FROM
(
    SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    WHERE LEFT(name,LOCATE('0',name)-1)='LouServer'
) Louisville
INNER JOIN
(
    SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    WHERE LEFT(name,LOCATE('0',name)-1)='VgsServer'
) Vegas
ON Louisville.num = Vegas.num;

Here is a sample table

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS servers;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE servers
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20),
    ->     PRIMARY KEY (id),
    ->     KEY (name)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO servers (name) VALUES
    -> ('LouServer01'),('VgsServer01'),
    -> ('LouServer02'),('VgsServer02');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM servers;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | LouServer01 |
|  3 | LouServer02 |
|  2 | VgsServer01 |
|  4 | VgsServer02 |
+----+-------------+
4 rows in set (0.00 sec)

mysql>

Here is the query you need executed

mysql> SELECT
    ->     Louisville.name LouisvilleServer,
    ->     Vegas.name VegasServer
    -> FROM
    -> (
    ->     SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    ->     WHERE LEFT(name,LOCATE('0',name)-1)='LouServer'
    -> ) Louisville
    -> INNER JOIN
    -> (
    ->     SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    ->     WHERE LEFT(name,LOCATE('0',name)-1)='VgsServer'
    -> ) Vegas
    -> ON Louisville.num = Vegas.num;
+------------------+-------------+
| LouisvilleServer | VegasServer |
+------------------+-------------+
| LouServer01      | VgsServer01 |
| LouServer02      | VgsServer02 |
+------------------+-------------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!


It's a very open-ended question, as there is no sure-fire method to determine the server version and platform. You'll need to apply some heuristics.

In Db2 for LUW (and only LUW) since v 10.1 there's a system view dbmcfg that can return the server version:

select value from sysibmadm.dbmcfg where name = 'release'

so if it succeeds you'll be sure it's Db2 for LUW of certain release level. Similarly, since v 9.7 you can query sysibmadm.env_inst_info (or first search for it in syscat.tables).

Another indicator of Db2 for LUW is the presence of some Oracle-compatible views since v.9.7, e.g. syspublic.dual or sysibmadm.all_objects.

A good indicator of Db2 for z/OS would be the presence of mainframe-specific catalog tables, e.g. sysibm.locations or sysibm.syscopy, which go back to v9 at least.

The presence of catalog views in the QSYS and QSYS2 schemas, e.g. QSYS2.TABLES, will indicate Db2 for i (AS/400).

Neither Db2 for z/OS nor Db2 for i have the SYSCAT schema views.


Permissions for runtime user on all objects in schema (including future objects)

Yes. The key word is DEFAULT PRIVILEGES.

ALTER DEFAULT PRIVILEGES FOR ROLE owner_user IN SCHEMA owner
GRANT SELECT ON TABLES TO runtime;

Grants the specifies privileges for all specified objects created in the future, by the specified role to the other specified role.

I specified the target_role (owner_user) explicitly to avoid ambiguity. Else, quoting the manual:

  If FOR ROLE is omitted, the current role is assumed.

Related:

Grant all on a specific schema in the db to a group role in PostgreSQL
How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

And don't forget access to sequences if you have any serial columns. The first linked answer has instructions.


Difference between clustered index and partitioning column

But does the partitioning column have anything to do with order on the disk?

From Clustered Index Structures :

  Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. By default, a clustered index has a single partition. When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

From Table and Index Organization:

When a table or index uses multiple partitions, the data is partitioned horizontally so that groups of rows are mapped into individual partitions, based on a specified column. The partitions can be put on one or more filegroups in the database. The table or index is treated as a single logical entity when queries or updates are performed on the data.

The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows. The page collections for the B-tree are anchored by page pointers in the sys.system_internals_allocation_units system view.

If you want to really dig more into how data is layed out then refer to : Inside The Storage Engine: sp_AllocationMetadata

As a side note : SQL Server 2016 CTP2 has TRUNCATE TABLE ... [ WITH ( PARTITIONS ( {  |  }

For automating partitioning switching check out - SQL Server Partition Management utility


There are a number of ways to do this purely in SQL. They will, logically enough, also require dynamic SQL.

First, copy your dictionary into a table in SQL Server - I'll refer to it as Group_Def, with columns Group_Name and Where_Clause.

Option 1: replicate your logic in SQL. For each group, generate a series of SQL statements (in a variable) that will add the group name and the CAR_ID for all cars matching your criteria to a temporary table. Execute the SQL statements in the variable to populate the temp table. Then, join that temporary table to CAR_TABLE on CAR_ID, group by Group_Name, and apply your aggregates.

Option 2: Grab the aggregate values as you go. This time, instead of writing all the matching CAR_ID values into the temp table, write the actual aggregate values. Then, simply select from the temp table.

May be easier to follow if you see the code:

/*
 1           | FORD      | RED  | 100   | 4    | CAR  |
| 2           | TOYOTA    | RED  | 500   | 2    | TRK  |
| 3           | FORD      | BLACK| 10    | 4    | CAR  |
| 4           | HYUNDAI   | BLUE | 150   | 4    | VAN  |
| ...         | ...       | ...  | ...   | ...  | ...  |
| 1000        | TOYOTA    | GRN  | 200   | 8    | CAN  |
*/

IF (OBJECT_ID('tempdb..#CAR_TABLE') IS NOT NULL) DROP TABLE #CAR_TABLE;
CREATE TABLE #CAR_TABLE
 ( CAR_ID int IDENTITY(1,1)
  ,Make   varchar(50)
  ,Color  varchar(50)
  ,Miles  int
  ,Seats  int
  ,Model  varchar(50)
);

INSERT INTO #CAR_TABLE (Make, Color, Miles, Seats, Model)
VALUES ('FORD', 'RED', 100, 4, 'CAR')
      ,('TOYOTA', 'RED', 500, 2, 'TRK')
      ,('FORD', 'BLACK', 10, 4, 'CAR')
      ,('HYUNDAI', 'BLUE', 150, 4, 'VAN')
      ,('TOYOTA', 'GRN', 200, 8, 'CAN')
;

/*
Dictonary x = {GRP1, (COLOR = BLACK or Miles <50),
               GRP2, (Seats <5 AND MODEL = CAR)};

*/

IF (OBJECT_ID('tempdb..#Group_Def') IS NOT NULL) DROP TABLE #Group_Def;
CREATE TABLE #Group_Def (Group_Name nvarchar(128), Where_Clause nvarchar(2000));

INSERT INTO #Group_Def
VALUES (N'GRP1', N'(Color = ''BLACK'' OR Miles < 50)')
      ,(N'GRP2', N'(Seats < 5 AND Model = ''CAR'')')
      ,(N'Red Cars', N'(Color = ''RED'' AND Model = ''CAR'')')
      ,(N'Red Toyotas', N'(color = ''RED'' AND Make = ''TOYOTA'')')
      ,(N'All', N'(1 = 1)')
;

--OPTION 1

IF (OBJECT_ID('tempdb..#cars_by_group') IS NOT NULL) DROP TABLE #cars_by_group;
CREATE TABLE #cars_by_group (Group_Name nvarchar(128), CAR_ID int);

DECLARE @sqlcmd nvarchar(MAX);

SELECT @sqlcmd = N'INSERT INTO #cars_by_group
SELECT NULL, NULL FROM #Group_Def WHERE 1 = 0
';

SELECT @sqlcmd = @sqlcmd + N'UNION ALL
SELECT N''' + Group_Name + N''', CAR_ID FROM #CAR_TABLE WHERE ' + Where_Clause + N'
'
  FROM #Group_Def
;

EXECUTE sp_executesql @sqlcmd;

SELECT t.Group_Name
      ,SUM(c.Miles) as sum_miles
  FROM #cars_by_group t INNER JOIN #CAR_TABLE c ON (t.CAR_ID = c.CAR_ID)
 GROUP BY t.Group_Name
;

--OPTION 2:

IF (OBJECT_ID('tempdb..#group_totals') IS NOT NULL) DROP TABLE #group_totals;
CREATE TABLE #group_totals (Group_Name nvarchar(128), sum_miles int);

DECLARE @sqlcmd2 nvarchar(MAX);

SELECT @sqlcmd2 = N'INSERT INTO #group_totals
SELECT NULL, NULL FROM #Group_Def WHERE 1 = 0
';

SELECT @sqlcmd2 = @sqlcmd2 + N'UNION ALL
SELECT N''' + Group_Name + N''', SUM(Miles) FROM #CAR_TABLE WHERE ' + Where_Clause + N'
'
  FROM #Group_Def
;

EXECUTE sp_executesql @sqlcmd2;

SELECT *
  FROM #group_totals
;

Option 2 may be slightly faster, as you aren't having to calculate the aggregates in the final SELECT. Note, however, that even though I've built @sqlcmd as a single SQL statement, the UNION ALL is joining together functionally separate queries. Performance-wise, you could rewrite this so that each row in #Group_Def was generating a distinct INSERT statement, and performance would not change significantly. You're basically running one statement for each group, regardless.

I can envision a third option that would collect all the aggregates in a single step; however, it's not as scalable, and requires that all your aggregates can be expressed in terms of sums or counts, and still ultimately requires a SELECT for each group.

You would build a single SELECT statement, with columns for each value you want to record for each group. You'd want to add a version of the group name without spaces. To collect the aggregates, you would use SUM and CASE statements.  For example:

SELECT SUM(CASE WHEN (Color = 'RED' and Make = 'TOYOTA' THEN 1 ELSE 0 END) as red_toyotas_count
      ,SUM(CASE WHEN (Color = 'RED' and Make = 'TOYOTA' THEN Miles ELSE 0 END) as red_toyotas_sum_miles
...
  INTO #single_row_total
  FROM CAR_TABLE

(No GROUP BY would be necessary - all output columns would be aggregates).

Then, you build the SELECT statements to pull all the values you need out of the #single_row_total table (again, with dynamic SQL, going through the Group_Def table to build the statement), using UNION ALL to tie them together:

SELECT 'Red Toyotas' as [Group], red_toyotas_sum_miles as sum_miles, red_toyotas_sum_miles * 1.00 / red_toyotas_count as avg_miles FROM #single_row_total
UNION ALL
SELECT 'Red Cars' as [Group], red_cars_sum_miles as sum_miles, red_cars_sum_miles * 1.00 / red_cars_count as avg_miles FROM #single_row_total
UNION ALL
...

This would allow all the aggregates to be collected in a single set-based operation. As noted, breaking the single result into separate rows still requires one SELECT per group; however, these selects, being on a single row, should be quite quick.

This isn't scalable because there's an upper limit to the number of columns you can have in a single table, so a sufficient number of groups, times a sufficient number of aggregates needed per group, could lead to too many columns needed.

Also, while the query pulling the aggregates is a single SQL query, the process of evaluating each row to determine what the results of the CASE statement would be could cause the execution plan to be so hideous, that the one SELECT per group options would perform as well or better.

So, I leave the creation of Option 3 (as well as testing with much larger data sets, to determine the fastest option) up to the OP, or another interested party.

As to the dynamic SQL aspect of this: unless the groups can be defined on an ad hoc basis, you're building your SQL statements with no user-supplied values, so there's no risk of SQL injection. And (I think), if #Group_Def doesn't change, the plan generated might wind up being reusable (not sure, not planning on checking; wasn't part of the original question).


Converting spatial data from Oracle to MySQL

I took an entire 8 hour shift of reading boring Oracle and MySQL docs to figure it out but here it is.. in PHP..

(as a Github Gist)

/**
 * Convert an Oracle SYS.SDO_GEOMETRY definition (perhaps extracted from an insert
 * statement) to a MySQL Geometry column. This function only handles Polygons and
 * Multipolygons.
 *
 * Example:
 * $definition = "MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-75.01703,41.79308,-75.02978,41.7941,-75.02735,41.772,-75.02716,41.77193,-75.02697,41.77187,-75.01354,41.79051,-75.01337,41.79061,-75.0132,41.79072,-75.00949,41.79234,-75.00946,41.79254,-75.00943,41.79274,-75.00518,41.7943,-74.9995,41.79178,-74.99365,41.79788,-74.99383,41.79798,-74.99876,41.80318,-74.9988,41.80337,-74.99883,41.80349,-75.00425,41.80479,-75.00431,41.80459,-75.01239,41.79415,-75.01245,41.79412,-75.01688,41.79345,-75.01696,41.79327,-75.01703,41.79308))";
 * $sql = "INSERT INTO mytable (geom) VALUES (".OraclePolygonToMysql($definition).")";
 */
function OraclePolygonToMysql($definition){
    // Get the geometry type, our data should only be polygons and multipolygons
    $SDO_GTYPE = substr($definition, strpos($definition, "(") + 3, 2);
    switch($SDO_GTYPE){
        case "03": $type = "POLYGON"; break;
        case "07": $type = "MULTIPOLYGON"; break;
        default: die("hol up i thought we only had polygons in this bitch");
    }
    // MDSYS.SDO_ELEM_INFO_ARRAY contains info about how to split up the coordinates..
    // So the info array is some dumb ass 1-indexed triplet model where we only need every third index..
    // https://docs.oracle.com/cd/B12037_01/appdev.101/b10826/sdo_objrelschema.htm#i1006226
    $start = strpos($definition, "MDSYS.SDO_ELEM_INFO_ARRAY")+strlen("MDSYS.SDO_ELEM_INFO_ARRAY(");
    $infoArray = explode(",", substr($definition, $start, strpos($definition, ")", $start) - $start));
    // MDSYS.SDO_ORDINATE_ARRAY contains the actual ordinates that need to be grouped properly for
    // MySQL to understand them
    $start = strpos($definition, "MDSYS.SDO_ORDINATE_ARRAY") + strlen("MDSYS.SDO_ORDINATE_ARRAY");
    $ordsArray = explode(",",trim(substr($definition, $start), " ()"));
    // Get the starting index fro each group
    $groupStarts = array();
    while(count($infoArray) > 0){
        $startIndex = intval(array_shift($infoArray)) - 1; // compensate for the dumb ass 1-index
        array_shift($infoArray); array_shift($infoArray); // we don't need these... i don't think
        $groupStarts[] = $startIndex;
    }
    // Get the ending index for each group
    $groups = array();
    for($i=0; $i<count($groupStarts); $i++){
        $start = $groupStarts[$i];
        $end = isset($groupStarts[$i+1]) ? $groupStarts[$i+1] - 1 : count($ordsArray)-1;
        $groups[] = array("first"=>$start, "len"=>$end-$start+1);
    }
    // Group the indexes into their own arrays
    for($i=0; $i<count($groups); $i++)
        $groups[$i]['group'] = array_slice($ordsArray, $groups[$i]['first'], $groups[$i]['len']);
    // Create the string from the groups array
    $chunks = array();
    foreach($groups as $group){
        $chunked = array_chunk($group['group'], 2);
        foreach($chunked as $k=>$chnk) $chunked[$k] = implode(" ", $chnk);
        $c = "(".implode(",", $chunked).")";
        $chunks[] = $type === "MULTIPOLYGON" ? "($c)" : $c;
    }
    return "GeomFromText('$type(".implode(",",$chunks).")')";
}


multiple MYSQL full text indexing for innodb and memory + disk efficiency

first_name has one word, correct?  FULLTEXT(first_name) is not a use case for that type of indexing.  I recommend you use simpleINDEXes` for any simple columns.

This works very efficiently, probably faster than anything that could be done with FULLTEXT:

WHERE last = 'Muench' AND first LIKE 'C%'
INDEX(last, first)

You might still want FULLTEXT(first, last, email, phone) for handling the case where any of those might be entered.  (However, it is unclear whether this is a good use case.)

Meanwhile, don't blindly use (255) for all string columns; use sensible limits.

It is often wise to have the UI construct the WHERE clause based on what the user does/doesn't supply.  In your case, this might include switching between MATCH..AGAINST, =, and LIKE, maybe even REGEXP.

You could also look for @ to discover emails, all-digits to discover phones, etc., thereby avoiding searching lastname for foo@bar.com.

As an alternative, you could add an extra column that contains all the 'words' for name, email, etc.  Then have a single FULLTEXT index on that one column; no need for 40.  The drawback is the need to maintain this redundant info.  With a new enough MySQL or MariaDB, it could be a 'generated' column.


Get maximum, minimum values across columns

It's not going to be pretty (and I have no 2000 installation to test) but this should work.

The pk is the PRIMARY KEY of the table (or a column with UNIQUE NOT NULL constraints):

SELECT d.column1, d.column2, d.code1,
       d.qty2, d.qty3, d.qty4, d.qty5, ..., d.qty20,
       m.MinVal, m.MaxVal
FROM
    dummydata AS d
  JOIN
    ( SELECT q.pk,
             MIN(NULLIF(q.qty, 0)) AS MinVal,
             MAX(q.qty) AS MaxVal
      FROM
        ( SELECT pk, qty2 AS qty FROM dummydata UNION ALL
          SELECT pk, qty3 FROM dummydata UNION ALL
          SELECT pk, qty4 FROM dummydata UNION ALL
          SELECT pk, qty5 FROM dummydata UNION ALL
          ...
          SELECT pk, qty20
        ) AS q
      GROUP BY pk
    ) AS m
    ON m.pk = d.pk ;

Alternative is to use correlated subqueries in the SELECT list:

SELECT d.column1, d.column2, d.code1,
       d.qty2, d.qty3, d.qty4, d.qty5, ..., d.qty20,
       ( SELECT MIN(NULLIF(q.qty, 0))
         FROM
           ( SELECT d.qty2 AS qty UNION ALL
             SELECT d.qty3 UNION ALL
             ...
             SELECT d.qty20
           ) AS q
       ) AS MinVal,
       ( SELECT MAX(q.qty)
         FROM
           ( SELECT d.qty2 AS qty UNION ALL
             SELECT d.qty3 UNION ALL
             ...
             SELECT d.qty20
           ) AS q
       ) AS MaxVal
FROM
    dummydata AS d ;

Tested in rextester.com


2012 Transactional replication not delivering new articles

According to Define an Article:

  If the publication has existing subscriptions and sp_helppublication
  returns a value of 0 in the immediate_sync column, you must call
  sp_addsubscription to add the article to each existing
  subscription.

After executing sp_addarticle, execute sp_addsubscription to add the article to each existing subscription.  Then generate a new snapshot.  The Distribution Agent should then copy the schema and data for the newly added article to the Subscriber(s).


How do I design a database for a resource scheduling and allocation application?

The only possible reason that you might want to consider creating an entry for each resource and day is that it would give you the ability to use declarative constraints to enforce a business rule that each resource can only be allocated to one project (at most) per day.  You didn't say anything about this being a business rule, so I wouldn't suggest modeling it that way.  It's just more data to maintain.

You could use a schema like this:

RESOURCE:
 id (PK),
 name,
 ...

PROJECT:
 id (PK),
 name,
 ...

ALLOCATION:
 resource_id (PK/FK),
 project_id (PK/FK),
 start_date (PK),
 finish_date

This lets you assign resources to multiple projects over one or more ranges of dates.  It has the advantage of minimizing the number of records to maintain, assuming that resources are assigned to projects for more than just a day at a time.

Alternatively, if you want the allocation table to be structured so that it enforces the rule that each resource can only be allocated to one project at a time, then you could use the following schema.  Note that this requires a record per resource per day.

ALLOCATION:
  resource_id (PK/FK),
  project_id (FK),
  work_date (PK)

If you wanted to enforce this business rule through application logic instead of through declarative constraints in the database, you could use the first schema noted above instead.

Edit: Query to detect overlaps

If you use the first option, where allocations include a date range, then you need to use some application logic to enforce a business rule that says a resource can only be allocated to one project at a time.

To do this, what you want to do is this:  Before every insert or update of a record in ALLOCATION, you need to check to see if the record you are about to write would overlap in an illegal way with all of the other records that are already in the table.

A query that would find potentially overlapping records would look something like this:

select
  resource_id
, project_id
, start_date
, finish_date
from ALLOCATION
where resource_id = @ResourceOfInterest
  and start_date <= @NewFinishDate
  and finish_date >= @NewStartDate

If you are inserting and the above query returns zero rows, then you are good to go.

If there are one or more rows returned, you can insert without creating an overlap.

If there is one row and you are updating, you need to make sure that you aren't just finding the row that you plan to update.  You have to check the other column values to see if that is what you've found.

Queries to Answer Resource Allocation Questions

Using allocation tables with date ranges (instead of one record per day) makes answering some kinds of questions a little more complex.  Still, it isn't hard to answer the same questions and the time you save maintaining daily rows will more than compensate for a little extra complexity in some of your queries.  For example:

What does the resource allocation look like today?

select
  resource_id
, project_id
-- whatever else you want to join in...
from ALLOCATION
-- inner join whatever else you want...
where start_date <= @TodaysDate
  and finish_date >= @TodaysDate

Which projects have no resources assigned next Monday?

select
  A.resource_id
, A.project_id
-- whatever else you want to join in...
from ALLOCATION A
-- inner join whatever else you want...
where not exists
( select B.resource_id, B.project_id
  where B.start_date <= @NextMondaysDate
    and B.finish_date >= @NextMondaysDate
    and B.resource_id = A.resource_id
    and B.project_id = A.project_id )

How many resources were assigned to each project each day last month?

select
  A.project_id
, B.CheckDate
, COUNT(A.resource_id)
-- whatever else you want to join in...
from ALLOCATION A
-- inner join whatever else you want...
right outer join
(select DATEADD(DD,N-1,@FirstDayOfMonth) as CheckDate from NUMBER
 where N <= DATEDIFF(DD,@FirstDayOfMonth,@LastDayOfMonth)+1) B
on  A.start_date <= B.CheckDate
and A.finish_date >= B.CheckDate
group by
  A.project_id
, B.CheckDate
-- Also group by anything else you've joined in, like project name, etc.

This last one uses a Number Table which is something every database developer should have in their toolbox.


I'm guessing you are referring to a file system level backup of your data folder?

I am not familar with Rackspace for performing such a task, but have worked with other backup products that allow you to take snapshots of active or inuse files. Which it would allow you to grab a picture of the data and log files of a SQL Server database.

The problem with that is when you take a backup of those files in this manner you want to make sure the database is in a consistent state so it can be recovered, or restored. When you take a snapshot of those files while a transaction is being written to the log file, or committed to the data file, it will not necessarily have a consistent state.

When SQL Server attaches or starts a database it will read in the log file and either roll forward or back any active transactions that have not been committed to the database. If it hits an inconsistent transaction it will mark the database as suspect and you have to clean it up manually, and hope you can get it online.

Another problem that will come up with not using SQL Server native backups can depend on your recovery model. The recovery model basically dictates how your log file is managed for reuse of space. If you are in full recovery model a native full backup helps keep the log under control, taking filesystem level backups will bypass that process so you could end up with a log file out of control.

NOTE: When you determine your backup strategy for your database, you must be confident that you can use it to restore your data. No matter what suggestions you get, always try your backup plan and do a test run on your restore. If you can restore with that backup plan and it meets your recovery needs, then go with it.


What is the exact relationship between a database transaction and locking?

when I explicitly lock a row or a table, am I employing the exact same constructs that are used by the database's transaction facilities under the covers to make the transaction work properly?

Yes. If that would not be true, then your own 'locking' would only be scoped to other similar 'locking' and not interact with the engine own locking. So you would lock a row in a table so that it cannot be locked by another application in the same manner, but your lock would be ignored by the engine itself. These semantics are seldom desired. Most of the time an application locking a row means 'lock it against any means of access/modify'. Side note that locking mechanisms that are strictly application specific do exists, because they are useful. For instance SQL Server has application locks.

  it occurs to me that in order for a transaction to be atomic and isolated, it must be doing some locking.

Locking is one means to achieve this. The major alternative is versioning. Nowadays most databases support both (which also means that if you 'lock' a row in the app but another transaction uses versioning to read the row, it will read it because your locking does not block versioned reads).

You are sort of circling around a concept known in the database implementation world as 'two phase locking protocol'. the linked Wikipedia article is a good starter. If you want to read more detailed explanation about this topic I recommend head to the library and ask for a loan on Transaction Processing: Concepts and Techniques. Pretty much every database out there is, at its core, an implementation of that book.


MSSQL: Generate a 8 digit serial/Barcode number from a prefix number

DECLARE @prefix INT;
SET @prefix = 573071;
WITH
cte1 AS (SELECT 0 num
         UNION ALL
         SELECT num+1 FROM cte1 WHERE num < 9)
SELECT num,
@prefix*100+num*10+9-((@prefix/100000+(@prefix/1000)%10+(@prefix/10)%10+num)*3+((@prefix/10000)%10+(@prefix/100)%10+@prefix%10)-1)%10 barcode
FROM cte1;

fiddle

The option without variable declaration:

WITH
cte0 AS (SELECT 573071 AS prefix),
cte1 AS (SELECT 0 num
         UNION ALL
         SELECT num+1 FROM cte1 WHERE num < 9)
SELECT num,
prefix*100+num*10+9-((prefix/100000+(prefix/1000)%10+(prefix/10)%10+num)*3+((prefix/10000)%10+(prefix/100)%10+prefix%10)-1)%10 barcode
FROM cte0, cte1;


Login Error while connecting SQL Database through Power BI?

I found resolution for this error:

By default, the Encrypt connection check box is selected to signify that Power BI Desktop connects to database using an encrypted connection. If a connection to your SQL Server is not established using an encrypted connection, that Power BI Desktop prompts to connect using an unencrypted connection.

Once you click OK, it will be directly prompted to turn off encryption. Or you can go to

  File->Options and Settings -> Data Source Settings, choose your SQL
  Server data source and select Edit and uncheck "Encrypt Connections"
  option.

In addition, when we connect to SQL Server in Power BI Desktop, need to select the authentication mode to connect to the SQL Server database.

1) Windows: This is the default selection. Select this if you want to connect using Windows authentication.

2) Database: Select this if you want to connect using SQL Server authentication. After you select this, specify a user name and password to connect to your SQL Server instance.

Note: I know it's foolish and so simple but works for me anyway @sepupic Thanks for useful comment.


MySQL: How to sync on demand beetwen two database in the same server

As u told it possible to create another database(with different name) with in the server. try once in local db then you can go into production.


Need suggestion for placing foreign keys in the databases

Assuming a company can have more than one shipping address, the key would be a column added on Shipping Address table (aptly named 'customer_id' and the foreign key definition on the Shipping Address table. If you wanna avoid same address more than once, also add a unique key on shipping address table that can include (customer_id, contact_name)

On an unrelated note, make sure your created_at and updated_at use timestamps ;)


Use extra column to save an unaccented copy of a string instead of using unaccent module

You could store an unaccented version redundantly. And create index on that column to support your queries.

Or, better yet, to avoid bloating your table with redundant data, create a functional index on the original column.  You need an IMMUTABLE function for that, while unaccent() is only STABLE. Detailed instructions:

Does PostgreSQL support “accent insensitive” collations?

Then, based on the function f_unaccent() from the linked answer:

CREATE INDEX tbl_unaccent_col_idx ON tbl(public.f_unaccent(col));

Or some other index. Maybe use the full text search (FTS) infrastructure with unaccent as dictionary. Example:

Creating a case-insensitive and accent/diacritics insensitive search on a field

Or use it in a trigram index:

Queries using GIN index on f_unaccent() seem slow?
Why does a slight change in the search term slow down the query so much?


How to generate error log file while executing a .sql file in terminal

So after a little more searching I found the answer :)

Here is how this needs to be done.

mysql -uroot -xxx! -f main < sq2.sql > /var/www/log.txt &


Read only postgres table, except by trigger

The solution proposed by Ziggy will work, but if you don't want to go that deep by having to create a privileged user just to execute the trigger, there's another solution you can try: Create a validation trigger on the product_stock table that checks if the update is coming from the stock_transaction_log trigger. To do that, we can create a temporary table to serve as a "global variable".

CREATE OR REPLACE FUNCTION validate_product_stock() RETURNS trigger AS $$
DECLARE
    count_ int;
BEGIN
    BEGIN
        SELECT COUNT(1) INTO count_ FROM __inside_stl_trigger WHERE note = 'inside trigger';
    EXCEPTION WHEN undefined_table THEN
        count_ := 0;
    END;

    IF count_ = 0 THEN
        RAISE EXCEPTION 'This table is automatically updated by stock_transaction_log'
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER validate_product_stock_trigger
    BEFORE INSERT OR UPDATE ON product_stock
    FOR EACH ROW
    EXECUTE PROCEDURE validate_product_stock();

CREATE OR REPLACE FUNCTION update_product_stock() RETURNS trigger AS $$
BEGIN
    CREATE TEMPORARY TABLE __inside_stl_trigger (note text NOT NULL);
    INSERT INTO __inside_stl_trigger (note) VALUES ('inside trigger');

    -- Update the product_stock table here

    DROP TABLE __inside_stl_trigger;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_product_stock_trigger
    BEFORE INSERT OR UPDATE ON stock_transaction_log
    FOR EACH ROW
    EXECUTE PROCEDURE update_product_stock();

Obviously, one could create that temporary table by hand, insert a row on it and do the update, but at least you will make it harder.


Same server, same query, different response time

Results coming from the Query cache take about 1ms, regardless of the query.  The QC is simply a hashed lookup of SQL --> resultset.
On busy production systems, turning off the QC usually helps overall performance.
This composite index would speed up that query:
INDEX(col4, col8, col3)   -- col3 in 3rd place


How to generate the E-R Diagram of Wordpress using mySQL Workbench

The EER diagram is correct. There should be no relationships because WordPress does not have any constraints. The EER diagram shown in the codex is only for reference (it's a dummy EER diagram). You must rely on other logical operations to make sure you have no orphaned data.

Source: https://codex.wordpress.org/Database_Description

  Please note that within the standard installation of WordPress no
  integrity between the tables is enforced e.g. between posts and
  comments. If you are creating a plugin or extension that manipulates
  the WordPress database, your code should do the housekeeping so that
  no orphan records remain in the tables e.g. by removing records in
  other tables with a set of SQL commands when foreign keys are deleted
  (Don't forget to remind users to backup before such operations).


When is a result returned deterministic and when not?

Rows are not returned in any specific order in SQL (in reality they are likely to be in the same order most of the time) so without an ORDER BY any of the rows could be returned first. So it is non deterministic because this first record can change each time you run the query.

Wrapping it in a block doesn't make the answer deterministic, it just hides the warning.


What happens to a transaction if the network connection fails?

http://dev.mysql.com/doc/refman/5.6/en/innodb-implicit-commit.html says:

  If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

That means if your session disconnects for any reason, either by choice, or else because an error occurs like the network connection fails, etc., then a transaction in progress is rolled back.

If you think about it, this is the only appropriate thing for the RDBMS to do. It doesn't know if you had completed the work for your transaction only partially. For example, you might have debited one bank account, and you intended to credit another bank account to reflect a funds transfer. If the debit has occurred but the credit has not, and the connection is lost, MySQL has no way of completing the transaction or even of knowing what you wanted to do next. So it has to roll back all work so far.


mysql need the mysql.timezone* tables to be filled properly for explicit and implicit TZ conversions. They are not created and filled automatically on installation.

Install fresh timezones on your host. Usually they are provided by zoneinfo package.
Run mysql_tzinfo_to_sql /path/zoneinfo | mysql -u root mysql

Refer the mysql manual for further information: https://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html


Should I created a different table to hold a big text / varchar field in MySQL 5.5.61 with Antelope file format?

The non-inline version of text storage a slower access because its in a different page in the tablespace, however in a separate table it has to access the PK of a separate table, and then take the slower access to access the text column in a different page.

So a separate table is worse for performance.


InnoDB AUTO_INCREMENT resetting itself without MySQL restart

This can be one of known bugs: https://bugs.mysql.com/bug.php?id=77743 (fixed in 5.6.27) or https://bugs.mysql.com/bug.php?id=76872 (still not fixed)


How to remove unreferenced rows in a "Many to One" relation?

A Person and a House must be associated with a City, but a Person does not necessarily have to be associated with a House. If a Person is associated with a House, they must be associated with the same City.

Your second diagram is correct. Both Person and House are independently associated with City. You just need another table.

Simple, you create a type of intersection table to relate a Person to a House that also forces Person and House to refer to the same city.

create table PersonHouse(
    PersonID int not null,
    HouseID  int not null,
    CityID   int not null,
    constraint PK_PersonHouse primary key( PersonID ),
    constraint FK_PersonHouse_Person foreign key( PersonID, CityID )
        references Person( ID, CityID ),
    constraint FK_PersonHouse_House foreign key( HouseID, CityID )
        references House( ID, CityID )
);

PersonID is the PK so a Person cannot reside in more than one House but the same House may contain more than one Person.

When inserting a record into PersonHouse with Person 15 who (in Person table) is associated with City 37, the House ID must be contained in the House table and must also be associated with City 37. Any attempt to enter a Person and House associated with different cities will generate a foreign key violation.


Permissions not allowing Update or Select on a trigger executed function

The permissions error is to be expected when you don't have things like Cross-Database Ownership Chaining and TRUSTWORTHY enabled, and aren't logging in as sa ;-).

This very similar to the problem described (and resolved) in my answer to another question here on DBA.SE:

Permissions in triggers when using cross database certificates

However, in that case there was a Trigger in Database B and that is not the situation here. Still, this should be fairly easy to solve and will use a setup similar to what is described in that other answer.

Here are the steps you need to do for your particular issue (all of which are shown in that other answer):

Create a Certificate in Database A.
Sign the A.dbo.CallProcInB Stored Procedure using that Certificate.
Backup the Certificate, either to a File or into a temporary table (as shown in that linked answer).

Create that same Certificate in Database B (which is done by getting it from the backup file or out of the temp table, as shown in that linked answer).
Create a User from that Certificate.
Grant EXECUTE on the B.dbo.RunComponentsUpdate Stored Procedure to the new Certificate-based User.

That should be all you need.

Please be aware that any changes to the A.dbo.CallProcInB Stored Procedure will cause it to lose the signature. In those cases, just execute ADD SIGNATURE again.


SQL Query returning same value while using where 1 condition

Rewrite your query such a way:

SELECT a.*, x.cnt
FROM #temp a
CROSS APPLY (
    SELECT COUNT(VAL) AS cnt
    FROM #temp b
    WHERE a.val <= b.val
) x
--WHERE x.cnt = 1

If you uncomment the where clause you would get 333 | 1 as a result. You request a row from the outer table which doesn't have duplicates or bigger values.


Sargability question - order of variables

The short answer is that the order you put these in makes no difference to SARGability. If you want this to be as efficient as possible, you can add a RECOMPILE hint, or use dynamic SQL to generate the appropriate WHERE clause.

Since you seem to understand the concepts involved, I won't beat any dead horses here, I'll just point you to a great source on the subject: Dynamic Search Conditions in T‑SQL


How to cast an integer to a boolean in a MySQL SELECT clause?

Try using the IF function:

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, TRUE, FALSE)
FROM ...

or

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, 1, 0)
FROM ...

Even without the IF function, running

mysql> select ('rolando' = 'rolando') str_compare;
+-------------+
| str_compare |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql>

yield 0 or 1 using the mysql client

The problem is this: CAST() and CONVERT() can only accept and return the following types:

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

Since BOOLEAN is not in this list, it can never be returned by CAST or CONVERT

You could use the IF function to generate strings

SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, 'TRUE', 'FALSE')
FROM ...


Is it safe for me to switch from an 8GB server to a 4GB server? (Stats inside)

It's possible. You will potentially lose capacity to serve all data requests from in memory buffers but that depends on how large your data is.

select
ROUND(SUM(data_length+index_length/pow(1024,2))) as TotalSizeMB,
SUM(table_rows),
engine
from information_schema.tables
where table_schema not in ('mysql','information_schema')
group by engine

to see your distribution per engine. You'll be able to judge whether all data and indexes fit inside the innodb bufferpool or indexes within the myisam key buffer. Assuming you set these buffers up yourself in the my.cnf config file and you're not running with the defaults.


You have several options - all of them require some scheduling decision on your part. The one with the least impact to your source system would be, as mentioned in the comments, looking for an audit date/time stamp from your OLTP system. Many systems put a datetime stamp on rows whenever there is an insert or update, and as such can be used reliably to capture changes. This is safest if you use a >= pattern, so that you don't miss anything committed while you read. The likely small record set duplicated from the last millisecond records shouldn't cause much of an issue for standard upsert logic.

You can keep a table that tracks the latest Audit DateTime per source and use this though it may become unwieldy if you are pulling from numerous tables for a single dimension. If you know your system you may also use things like PK increments or a similar dependable data change pattern to restrict your source query.

CREATE TABLE [UTIL].[IncrementalLoadHelper]
(
     [Id] INT NOT NULL IDENTITY PRIMARY KEY
    ,[TargetTableSchema] VARCHAR(20) NOT NULL
    ,[TargetTableName] VARCHAR(100) NOT NULL
    ,[IncrementalValueInt] INT NULL
    ,[IncrementalValueDateTime] Datetime NULL
    ,[IncrementalValueVarchar] Varchar(500) NULL
    ,[IncrementalValueDecimal] Decimal NULL
    ,[SuccessfulLoadFlag] bit NOT NULL
    ,[InsertRowCount] INT NULL
    ,[UpdateRowCount] INT NULL
    ,[SSISPackageName] Varchar(500) NOT NULL
    ,[AuditInsertDate] DATETIME NOT NULL DEFAULT GETDATE()
    ,[AuditUpdateDate] DATETIME NULL
    ,[AuditUser] VARCHAR(200) NOT NULL DEFAULT SUSER_NAME()
)

.

SELECT [Columns]
FROM OLTP.SourceSystem
WHERE AuditDateTime >=
(
 SELECT MAX(AuditDateTime)
 FROM TrackingTable
 WHERE TableName = 'TblName'
)

You could also enable Change Tracking which uses built-in SQL Server functionality and doesn't require a helpful OLTP DB design. It will use less resources than Change Data Capture, which is a similar solution. Either one of these creates easily queried tables to identify changes that have occurred since a certain time and allow you to greatly reduce the total data that moves during each ETL phase.

I'd recommend dropping the linked servers and use SSIS to move your data. It will likely be easier to maintain and design. It has pre-built tasks that work with CDC. Either put SSIS on your DW machine or create a dedicated ETL processing machine and move as much of the ETL resource demand off of the DW and the OLTP.

Since you mentioned getting the facts first ensure you have the right inferred member logic to allow resolution at a later date when all data is available.

Also, consider, or make the business consider, the value of faster loads vs resource and development/maintenance costs. "Real time data" sounds great until you see it's alias: $$$$.


Does large amout of idle connections hurt performance of Mysql

Not much.
Why are there thousands?  Hundreds is reasonable.
Each connection takes some amount of RAM; if there are too many connections, you could run out of RAM, which would be bad for performance.
Also, if more than a few dozen connections suddenly get busy, they would be contending with each other for CPU, RAM, I/O, etc.
There is a "wait_timeout" that will disconnect a connection if it is idle for too long.


A range query will read from every index page that contains data within the range (plus a couple of non-leaf pages to find the start of the range) so to reduce the number of pages touched you need to make sure there are as many rows squeezed into each page as possible. This can be achieved by simply having the index not cover or include anything except that column.

This is only definitely true if only touching that column though: for queries like SELECT col1 FROM theTable WHERE col1 BETWEEN 4000 AND 9000 or SELECT COUNT(*) FROM theTable WHERE col1 BETWEEN 4000 AND 9000.

When considering anything more complex it is less clear-cut. SELECT col1, col2 FROM theTable WHERE col1 BETWEEN 4000 AND 9000 might be better served in terms of reducing page reads by an index covering col1 and col2 (or one covering col1 and INCLUDEing col2) because otherwise there are extra pages needing to be read to bring in the values from col2.

If you are selecting all, or most of, the columns from the table, and your database engine supports clustered indexes[1] or similar[2], then that will be the best way to reduce page accesses for that sort of query. This is an instance where you will deviate from the default practise of having your primary key as the clustering key, as for obvious reasons you can only have one clustered index. If clustering is not supported, or you want the one clustered index on the table for something else, then again you'd need to use a wide covering index or an index that INCLUDEs all the other columns. Though in this latter case be careful you are not wasting space, and affecting write performance, with a wide index that isn't really needed.

[1] directly called a clustered index in SQL Server, what Oracle calls an "index organized table" is the same as a table with a clustered index in SQL Server

[2] postgres allows you to reorganize a table index order with the CLUSTER command


SQL Server 2019 - Polybase -> Cosmos DB (MongoDB Mode) Error with dates

Found a solution. SQL Server recommends the wrong type for mongodb dates in the schema. Using DateTime2 resolved the issue. Found this on a polybase type mapping page in msdn.


Lock Master Log Position in Master-Master replication

You can try following method

  Flush tables with read lock on master(It will lock the master)
  show master status
  use this position accordingly in change master to command
  unlock tables(It will unlock master)

Hope it helps.


Is there any scenario where the existence of one or more Indexes in a table can prejudice it?

Well, I think you have some mixed concepts:

An index improves performance of READ OPERATIONS ( those of SELECT ) while increase the processing time of INSERT/UPDATE OPERATIONS ( So they don't improve all CRUD operations, as you've heard ). As each time you insert a new row, you should update the index, if you have too much indexes you are increasing the time of insertions, and sometimes also the updates ( if the update involves something indexed.
An index uses space, a lot of space if you have a lot of rows.
It's not a problem for the system to know which is the best index to use, I think this is not a real performance killer, but you should look for redundant indexes, as they're using space and time of inserts / updates.

For this, you should know how your DB engine works with indexes, in MySQL if you have an index over name, surname fields and other over name, this latter is redundant, as it's included in the first one ( because it appears in the same order, surname only is not included ), as an example of redundant indexes.

Also, you should test how your DB is going to interpret the query and which indexes are going to be used ( in MySQL, you can use Explain... and the query you are testing )
Finally, indexes are one of the most important features of databases, indexing can't be 'bad' by itself, and normally the problem appears when you forget to add some particular index, and not the excess of indexes, but it could happen.


Comparing Query Accomplishment Speed By Using JOIN or not

I would recommend to use JOIN for the following reasons:

It is optimised especially if Index properly designed and also if query get complicated later on.
Easy to read & understand, tidier code and one result-set
It is way the professionals do
select the only columns which are required not everything which could increase network traffic


How to generate documentation from stored procedures?

I can't for the life of me understand why this question received a flag for being off-topic. I think it is interesting and very relevant for the working DBA.

Unfortunately, as far as I can ascertain, it is impossible to do this automatically. You can run two commands

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";

and

SHOW PROCEDURE STATUS; -- will give you all procedures available.

SHOW CREATE PROCEDURE procedure_name; -- will give you details about a given procedure.

A good discussion of the topic is available here which shows the problems associated with the various commands.

Extending slightly the example given for the sample alluser() procedure as shown in the link above. I added comments with # and --.

DELIMITER //

CREATE PROCEDURE alluser()

# a proc to show all users
BEGIN
   SELECT *  -- selecting from the users' table
   FROM users;
END //
DELIMITER ;

What I found was that the comments are NOT returned using either SHOW PROCEDURE STATUS or SHOW CREATE PROCEDURE alluser; NOR the SQL statement above returns the comments.

[EDIT - I have further found that comments are not returned with SELECT * FROM Information_Schema.Routines.

I searched the system and found the field ROUTINE_COMMENT in INFORMATION_SCHEMA.ROUTINES - but it didn't return the comments either. What this field is for, I don't quite know - maybe it's not implemented yet?

I would respectfully suggest your best bet is to document as you go - put "full documentation for the procedure" as part of your testing and UAT framework.


Optimize Subquery with Windowing Function

When and if you are able to upgrade from SQL Server 2012 to SQL Server 2016, you may be able to take advantage of the much improved performance (especially for frameless window aggregates) provided by the new batch mode Window Aggregate operator.

Almost all large data processing scenarios work better with columnstore storage than rowstore. Even without changing to columnstore for your base tables, you can still gain the benefits of the new 2016 operator and batch mode execution by creating an empty nonclustered columnstore filtered index on one of the base tables, or by redundantly outer joining to a columnstore-organized table.

Using the second option, the query becomes:

-- Just to get batch mode processing and the window aggregate operator
CREATE TABLE #Dummy (a integer NOT NULL, INDEX DummyCC CLUSTERED COLUMNSTORE);

-- Identify any UniqueDates that are greater than the GroupDate within their GroupID
SELECT
    calc_maxUD.UniqueID,
    calc_maxUD.GroupID,
    calc_maxUD.GroupDate,
    calc_maxUD.UniqueDate
FROM
(
    SELECT
        E.UniqueID,
        E.GroupID,
        E.GroupDate,
        E.UniqueDate,
        maxUniqueDate = MAX(UniqueDate) OVER (
            PARTITION BY GroupID)
    FROM #Example AS E
    LEFT JOIN #Dummy AS D -- The only change to the original query
        ON 1 = 0
) AS calc_maxUD
WHERE
    calc_maxUD.maxUniqueDate > calc_maxUD.GroupDate
    AND calc_maxUD.maxUniqueDate = calc_maxUD.UniqueDate;

db<>fiddle

Note the only change to the original query is creating an empty temporary table and adding the left join. The execution plan is:

(58 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0
Table '#Example'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0

For more information and options, see Itzik Ben-Gan's excellent series, What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016 (in three parts).


Calculating Margin in CASE Statement and GROUP BY Clause

Here's one way.

select
 (select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 1) as jan
,(select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 2) as feb
,(select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 3) as mar

If using the common table expression approach and more than a month is expected or included then a sum will be needed.

;with CTE_summary as (
    select
         month(DateProcessed) as monthProcessed
        ,sum(isnull(Revenue, 0.0)) as Revenuesum
        ,sum(isnull(Cost, 0.0)) as Costsum
    from tmp
    where year(dateprocessed) = 2015
    group by month(DateProcessed)
)
select
     sum(case when monthProcessed = 1 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [jan]
    ,sum(case when monthProcessed = 2 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [feb]
    ,sum(case when monthProcessed = 3 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [mar]
    --,...
from CTE_summary


Do I need to rebuild indexes when upgrading a DB from 2008 to 2012?

I found interesting this link: http://thomaslarock.com/2011/12/7-mistakes-you-cant-afford-to-make-when-upgrading-to-sql-2012/

In my case I upgraded from sql 2000 to 2014 (with a 2008 bridge) detaching and moving files, but considerations are still good. My db whas about 1.3 terabytes.

I run

DBCC CHECKDB WITH DATA_PURITY;

But this is critical just becaming from 2000 or 2005, not your case.
Anyway: if you are moving datafile, you must check db... It is an online operation.
Also:

DBCC UPDATEUSAGE(db_name);

You can skip this.

Reindexes? I didn't find any documents about it. In my case it was even not acceptable. To much time and resources.

I updated statistics. I think this is important when you move a database. This can take long time, yes, but database is available to users. It is mandatory? No, but it makes me feel safe...


Most efficient way to get oldest row in MySQL by id

For the specific query that gets only one row from the table:

SELECT *
FROM message
WHERE conversation_id = @some_id
ORDER BY add_date ASC
LIMIT 1 ;

you only need a common (btree) index on (conversation_id, add_date). The query will need to do only an index seek (which will find the PK value) and then a seek on the table. The operations should be both very fast (O(logn)), so the size of the table will not matter much.

After you add the index (or any index), you don't need to do anything more. Whenever a query runs, if  the optimizer decides that the index is useful (for that query, which can be the specific query above or any other), then it will use the index.

Side notes: please add PRIMARY KEYs to the tables.


Unable to Start PostgreSQL. kernel's SHMMAX parameter exceeded you limit

This page in the manual has detailed instructions how to deal with SHMMAX and SHMALL. It depends on your OS.

For Linux (example from the manual, to allow 16 GB):

$ sysctl -w kernel.shmmax=17179869184
$ sysctl -w kernel.shmall=4194304

Your number is very high: 52033798400 translates to 48 GB. Do you have that much RAM available?

Also, more quotes:

  Note that SHMALL is measured in pages rather than bytes on many systems.

And:

  A page is almost always 4096 bytes except in unusual kernel configurations with "huge pages" (use getconf PAGE_SIZE to verify).

Bold emphasis mine. You applied a factor 10000 instead. The matching setting for SHMALL would be 12703564 instead of 5203379.

In addition edit the settings in /etc/sysctl.conf to persist the settings for a server restart.
Note that changing this file alone does not affect the running system.


From your previous questions you use SQL Server. So you can use the & operator.

e.g. to see if the bit for 4 is on (and assuming NULL should return NULL)

SELECT CASE number & 4 WHEN 4 THEN 1 WHEN 0 THEN 0 END


Yes, it's safe. Might take a while, though.

In future you might want to use "CREATE INDEX CONCURRENTLY", like dezso suggested, but first create new index, and then drop old one.


Difficulty understanding Postgres synchronous_commit = off and data loss

so I can simply re-run what didn't go through once it's back up, correct?

How would you know to do so?

  Say we wanted to sequentially save 3 items in our database table called numbers: [1, 2, 3] And we have a client that queries the table numbers for what was last saved, and will resume from where it left off.

So it saves all three.  It queries the database and sees all three.  Then it says "OK, I'm good" and exits.  But then the database crashes, and when it comes back up only the 1st one is there.  Unless the client is always periodically rechecking, it won't know anything has been lost in order to fix it.

Another scenario.  One client commits all three rows, and a second program immediately queries and sees all three, and uses that data to make a decision.  If the decision of the second program is recorded exclusively into the database, then you are fine.  If any of those 3 rows are lost, the recorded decision is also lost.  But if that second program sent its decision to something outside the database, then you can have a problem because the external decision survives the crash, but the data upon which the decision was based might no longer exist once the database comes back up.


what are the events recorded in the transaction log?

Every transaction in SQL Server is logged in someway or other depending on recovery model. fn_dblog and fn_dumpdblog both undocumented command would give you nice view what is present in transaction log but the result could be massive. What I do is I run whatever transaction I need to do within begin tran and commit/rollback and then only capture logs I need to using below query

BEGIN TRANSACTION TRANSACTION_NAME
SQL QUERY
COMMIT TRANSACTION OR ROLLBACK TRANSACTION

--Then use below query to filter out records related to the transaction

USE DATABASE_NAME
GO
SELECT
OPERATION,
Context,
[Transaction Name],
[AllocUnitId],
[Page ID],
Description,
[Lock Information]
FROM FN_DBLOG(NULL,NULL)
WHERE [Transaction ID] in
(
select [Transaction ID] from
fn_dblog(Null,Null)
where [Transaction Name]='TRANSACTION_NAME'
)

You can run

SELECT * FROM FN_DBLOG(NULL,NULL)

To see all column names which fn_dblog would produce

Example: I would show what all things are logged when simple update command is fired

Begin tran T1

update [dbo].[IXBUILD] set C2='XXXXXX' where C1=1

Commit

Its has one row for begin tran one for commit and one regarding update operation. You can see LOP_BEGIN_XACT for beginning of transaction and COMMIT for commit of transaction.
LOP_MODIFY_ROW says a row was modified like we did in update statement.
LCK_Clustered_index is in picture because table had CI and row must have been exclusively locked for update
Then comes hexadecimal page ID which actually says what page had row which was modified
Then last column Includes the lock Information. The lock which was taken on HoBt 72057594041860096:ACQUIRE_LOCK_IX OBJECT: 8:613577224:0 ;ACQUIRE_LOCK_IX PAGE: 8:1:344 ;ACQUIRE_LOCK_X KEY: 8:72057594041860096 (8194443284a0)

Database ID=8 file number 1 and page 334. You would also see KEY value
There is also a log sequence number for every transaction and column Log Record Length which would tell you LSN and size of log record for particular operation

This was just a small demo. Remus Rusanu has explained how to read transaction log in details in This article please read it


Report an error in merge's when-not-matched-then clause

You can do this using a trigger.

Note: This is HORRIBLE, but I can't think of another way of achieving this.

Test tables:

create table testmerge
(
col1 number primary key ,
col2 number
);

insert into testmerge values ( 1, 1 );

create table testmerge2
(
col1 number primary key,
col2 number
);

insert into testmerge2 values ( 2, 2 );

Trigger on merge table:

create or replace trigger testmergeerror_btrig
  before insert on testmerge
  for each row
begin
if :new.col1 = -99999999999 and :new.col2 = -99999999999
then
    raise_application_error(-20000,'Your error message');
end if;
end;
/

Merge:

MERGE INTO testmerge t1
    USING testmerge2 t2
    ON (t1.col1 = t2.col1)
  WHEN MATCHED THEN
    UPDATE SET t1.col2 = t2.col2
WHEN NOT MATCHED THEN
insert(col1,col2) values (-99999999999,-99999999999);

Basically, when a row doesn't match, we set some values (that your application will never use, yes I know it is a horrible way of doing it!) in the INSERT that the trigger looks for, then fires an error.

Test case:

SQL> MERGE INTO testmerge t1
  2  USING testmerge2 t2
  3  ON (t1.col1 = t2.col1)
  4  WHEN MATCHED THEN
  5  UPDATE SET t1.col2 = t2.col2
  6  WHEN NOT MATCHED THEN
  7  insert(col1,col2) values (-99999999999,-99999999999);
USING testmerge2 t2
      *
ERROR at line 2:
ORA-20000: Your error message
ORA-06512: at "PHIL.TESTMERGEERROR_BTRIG", line 4
ORA-04088: error during execution of trigger 'PHIL.TESTMERGEERROR_BTRIG'

SQL>


SQL database disaster recovery in a second datacenter using storage

With a standard edition you basically have two options, log shipping and mirroring.  Unfortunately, neither of these solutions gives you an automated or managed solution for your third requirement of migrating connections.  However, you could base connections on a set of configuration files and swap them out for a "failover" scenario.

If you can swing an Enterprise upgrade, AvailabilityGroups become a much better holistic solution for data and connections.  You have both hardware and data redundancy and connections to the database server are managed via a listener service, directing database requests to the active server.  Downtime is almost non-existent.


Calculating the percentage change from the previous row

The best way I think would be to use the LAG() or LEAD() functions:

SELECT *,
       - 100.0 * (1 - LEAD(Price) OVER (ORDER BY t.Id) / Price) AS Grown
FROM table_name AS t
ORDER BY t.Id ;

With LEAD(Price) OVER (ORDER BY t.Id) you have access to the next Price when the rows are ordered by Id. It's not clear what the order should be. Based on the data in the question, you may want (ORDER BY Price DESC).


How can I change the default storage engine in phpmyadmin?

You have to add the line default-storage-engine = InnoDB under the [mysqld] section of your mysql config file (my.cnf or my.ini depending on your operation system) and restart the mysqld service.

I don't believe you can change this through PhpMyAdmin.


MySQL: Unable To Create Generated Column With Expression That Uses DATE_ADD() Function

Mysql has done, what you ask of him.
here are more infomration

DATE_ADD(col1, INTERVAL col2 HOUR) is equal to (col1 + interval col2 hour)

And is set as Default Value for the column. so when you add a NULL you get  what ever (col1 + interval col2 hour) represents.

So you can't achieve a Nullable and generated Column at the same time.

If You need A NULL there, you have to generate the test column VALUE at runtime.


Why query with SQL_NO_CACHE runs slower on the first run?

As another answer highlighted, Query Cache is not the only cache. Its use is not even advisable in most cases - in fact, it was removed in MySQL 8.0. The reason is that it has scalability problems (it's governed by a global lock) and it invalidates data far too frequently to be useful in a normal workload.

But InnoDB buffer pool contains indexes and data accessed frequently. After your first query, some indexes and data are cached, so next time they will be read from memory. Probably some data/index pages are accessed only once per query, in which case you should be able to see a slighter difference between second and third execution (first time these pages are not cached).

How to avoid the difference between query execution times? Well, there is no way to make the query faster the very first time it runs, as it needs to read from disk. But then, if your buffer pool is big enough, your query will always be fast. Keep in mind that a big buffer pool is very important for MySQL performance. The general recommendation is to keep it 75-80% of total memory. But in reality, things are more complex:

Other caches and buffers require space. In particular, if you have many users, or if your users run many JOINs or ORDER BYs, per-session buffers should be taken into account.
It is useless to have the buffer pool bigger than your database. And it is generally a waste to have it bigger than hot data (data you access often).

Another thing is, sometimes every server is restarted. When it happens, if the buffer pool is simply emptied, your queries will be slower after restart - until hot data are cached again. But you can avoid this by setting:

innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

In this way, your buffer pool will be (partially) written to disk on shutdown and reloaded at startup, so your queries should be fast even after a restart.


All nodes in a cluster should be using the same service account for SQL Server. This is true for both an FCI instance and an Availability Group setup.

For security sake, consider using Group Managed Service Accounts.https://blogs.technet.microsoft.com/askpfeplat/2012/12/16/windows-server-2012-group-managed-service-accounts/

This will allow you to have a service account that will have an strong, automatically changing password for your SQL Servers. Auditors love this. Your risk is minimum since Active Directory is handling the password change and downtime is not required to do this.


How can you output UPDATE / INSERT query results when using MySQL -e,--execute?

Run with -vv, that will print the query being ran as well as the results you're looking for


JSON is always tricky and you have to search a lot of tutorials to get the hang of it.
But you can use normalized table instead of JSON.

CREATE TABLE tab (
  `id` INTEGER,
  `name` JSON
);

INSERT INTO tab
  (`id`, `name`)
VALUES
  ('1', '{"vals":[{"id":123,"name":""}],"additional":"text"}'),
  ('2', '{"vals":[{"id":123}],"additional":"text"}'),
  ('3', '{"vals":[{"id":123,"name":"this"}],"additional":"text"}'),
  ('4', '{"vals":[{"id":222,"name":"this"}],"additional":"text"}');

SELECT id, name
FROM tab WHERE JSON_CONTAINS(name->>'$.vals[*].id','123','$')
AND (name->>'$.vals[*].name' IS NOT NULL AND  NOT `name`->'$.vals[*].name' = JSON_ARRAY("")  )
;

id | name
-: | :------------------------------------------------------------
 3 | {"vals": [{"id": 123, "name": "this"}], "additional": "text"}

db<>fiddle here


Incompatibility between unixODBC and Firebird ODBC driver

I came to a solution, if anyone is interested:

For any reason, firebird ODBC 64-bits driver downloaded from oficial site comes with SQLLEN = 4 bytes. After some help, I managed to compile driver from source code, and the result seems to work with 8 bytes SQLLEN.

In the compile proccess, I had a BUILD_DRIVER_VERSION error, modifying line 139 (SetupAttributes.h), adding spaces between each number fixed it.


You add a row into the bridging table , when you need to connect two rows from the referenced tables.
you can always delete one row in the bridging table, when that connection isn't any longer present.
What you are searching for is Associative entity


How to determine Integer or String based SQL Injection?

Simple. Look at the value in parameter cat=1.
If the value is a number, then it's an integer.
If the value is a letter, then it's a string.
In this case, 1 is a number, and yes ... it's an integer.
To answer your question, yes this is an Integer Based SQL Injection and ' not required.


should I create a table named vector containing a pool of all the vectors used in my tables?

No

Creating such a table and referencing to it would only be helpful If you know that many, many players are going to die in the same spot. Assuming that death of players will be in random locations, creating another table will not help, and your select queries will have to carry an unnecessary overhead of joins. If your queries will mainly look like this, then this option is best for you :

SELECT * FROM player_death WHERE player_id = <playerid>

On the other hand, if your game largely depended on coordinates of places rather than death places of players, creating another table would have been relevant. For example, if you want to spawn monsters only where players have died , or if you have to paint the death place red, and some other place yellow and rest all green, these all are relevant to  on position in the map, not death place of players, and it'd be better to have a separate table.


Installation of SQL Server 2012 does not contain Virtual Interface Adapter?

The Virtual Interface Adapter has been deprecated and removed in SQL Server 2012.
Choosing a Network Protocol - SQL Server 2008 R2

The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Configure Client Protocols - SQL Server 2012

Microsoft SQL Server supports client communication with the TCP/IP network protocol and the named pipes protocol. The shared memory protocol is also available if the client is connecting to an instance of the Database Engine on the same computer. There are three common methods of selecting the protocol.
•Configure all client applications to use the same network protocol by setting the protocol order in SQL Server Configuration Manager.
•Configure a single client application to use a different network protocol by creating an alias. For more information, see Create or Delete a Server Alias for Use by a Client (SQL Server Configuration Manager).
•Some client applications, such as sqlcmd.exe, can specify the protocol as part of the connection string. For more information, see Connect to the Database Engine With sqlcmd.


MySQL Script to automate creation of audit (history) tables and triggers?

I just wrote this earlier today.  It's a select statement working off the information_schema database, which produces the schema for the audit tables and the triggers.

SET GLOBAL group_concat_max_len = 1000;

SET @dbName = "[[[your_db_name_here]]]";

SELECT concat("DROP TABLE IF EXISTS `", @dbName, "`.`", table_data.audit_table, "`;\r",
          "CREATE TABLE `", @dbName, "`.`", table_data.audit_table, "`\r",
          "(\r",
          "  `auditAction` ENUM ('INSERT', 'UPDATE', 'DELETE'),\r",
          "  `auditTimestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,\r",
          "  `auditId` INT(14) AUTO_INCREMENT,",
          column_defs, ",\r"
          "  PRIMARY KEY (`auditId`),\r",
          "  INDEX (`auditTimestamp`)\r",
          ")\r",
          "  ENGINE = InnoDB;\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.insert_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.insert_trigger, "`\r",
          "  AFTER INSERT ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('INSERT',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.update_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.update_trigger, "`\r",
          "  AFTER UPDATE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('UPDATE',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.delete_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.delete_trigger, "`\r",
          "  AFTER DELETE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('DELETE',", table_data.OLDcolumn_names, ");\r\r"
)
FROM (
   # This select builds a derived table of table names with ordered and grouped column information in different
   # formats as needed for audit table definitions and trigger definitions.
   SELECT
     table_order_key,
     table_name                                                                      AS db_table,
     concat("audit_", table_name)                                                    AS audit_table,
     concat(table_name, "_inserts")                                                  AS insert_trigger,
     concat(table_name, "_updates")                                                  AS update_trigger,
     concat(table_name, "_deletes")                                                  AS delete_trigger,
     group_concat("\r  `", column_name, "` ", column_type ORDER BY column_order_key) AS column_defs,
     group_concat("`", column_name, "`" ORDER BY column_order_key)                   AS column_names,
     group_concat("`NEW.", column_name, "`" ORDER BY column_order_key)               AS NEWcolumn_names,
     group_concat("`OLD.", column_name, "`" ORDER BY column_order_key)               AS OLDcolumn_names
   FROM
     (
       # This select builds a derived table of table names, column names and column types for
       # non-audit tables of the specified db, along with ordering keys for later order by.
       # The ordering must be done outside this select, as tables (including derived tables)
       # are by definition unordered.
       # We're only ordering so that the generated audit schema maintains a resemblance to the
       # main schema.
       SELECT
         information_schema.tables.table_name        AS table_name,
         information_schema.columns.column_name      AS column_name,
         information_schema.columns.column_type      AS column_type,
         information_schema.tables.create_time       AS table_order_key,
         information_schema.columns.ordinal_position AS column_order_key
       FROM information_schema.tables
         JOIN information_schema.columns
           ON information_schema.tables.table_name = information_schema.columns.table_name
       WHERE information_schema.tables.table_schema = @dbName
             AND information_schema.columns.table_schema = @dbName
             AND information_schema.tables.table_name NOT LIKE "audit\_%"
     ) table_column_ordering_info
   GROUP BY table_name
 ) table_data
ORDER BY table_order_key
INTO OUTFILE "[[[your_output_file]]]"


If you don't have access to window-functions, you can use a subquery:

SELECT Letter_Copy_ID
     , Letter_ID
     , ( SELECT COUNT(1)
         FROM T AS T2
         WHERE T1.Letter_ID = T2.Letter_ID ) AS Letter_Copy_Count
FROM T AS T1

If you have access to window functions:

SELECT Letter_Copy_ID
     , Letter_ID
     , COUNT(1) OVER (PARTITION BY Letter_ID) AS Letter_Copy_Count
FROM T

You should avoid space in your identifiers (I used _ in my example), but if you feel you have to use them, you can quote them with ", as in "Letter Copy Count". Note that this makes the identifier case sensitive, so you will have to use the exact spelling.


Is DBCA in Oracle for a single database or the entire system?

DBCA will be reside on Oracle home. You can create as many number of databases in system using dbca. It will create default locations based on your unique SID (and dbunique name).


Deny certain user from updating certain rows

In my opinion, if you are stuck with sql server 2012, the Row Level Security is not going to work for you.

So you have two options:
1) application layer, which was already mentioned.
2) on database level, an trigger may work