Mirroring problems after removing domain I successfully created a mirror with certificates between two servers. One of those servers was a domain controller. After removing the domain the mirror was interrupted. After hours of searching I reinstalled SQL Server hoping all possible domain removal issues would be gone, but still the mirror refuses to work. I keep getting the vague error:

  Msg 1418, Level 16, State 1, Line 1
  The server network address "TCP://..." can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

If I check the eventlog of the mirrorserver I see 2 errors:

A cryptographic operation failed. This error indicates a serious problem with SQL Server. Check the SQL Server error log and the Windows event logs for further information.
A system cryptographic call failed during a Service Broker or Database Mirroring operation: system error '5(failed to retrieve text for this error. Reason: 15105)'.

Any ideas/suggestions?

After days of searching, I found the answer 10 minutes after this post. The problem was that the admin account did not have read/write rights on c:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ . After changing that I also had to recreate the master keys and certificates to make it work.


Cassandra - save file I'm new with Cassandra, and even more - I never have such task for habitual RDMS systems, like MariaDB or Oracle.

So, how can I store file in to Cassandra's table? Preferably using Python-driver, but few examples with raw CQL (cqlengine?) also appreciated.

We have large amount of small files, less then 10MB, with currently stored in Cassandra, and I'd like to understood how it's can be done.

For example - using next table:

cqlsh:testkeyspace> desc table files_uuids;

CREATE TABLE files_uuids (
  id uuid,
  file blob,
  PRIMARY KEY ((id))
) WITH
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.100000 AND
  gc_grace_seconds=864000 AND
  index_interval=128 AND
  read_repair_chance=0.000000 AND
  replicate_on_write='true' AND
  populate_io_cache_on_flush='false' AND
  default_time_to_live=0 AND
  speculative_retry='99.0PERCENTILE' AND
  memtable_flush_period_in_ms=0 AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'LZ4Compressor'};

You (and other future readers) might also try using the COPY command of cqlsh.

For example, if you have a local file named "filedata.csv", containing e.g.:

123-45-678-9,some file data here
abc-de-fgh-1,some other file data here

where the CSV fields corresponding to the columns in your file_uuids table, then you should be able to use:

cqlsh> COPY file_uuids FROM 'filedata.csv';

Hope this helps!


SQL Server (Azure) split comma-separated string into multiple columns? I have the following data in Azure SQL Server (Microsoft SQL Azure (RTM) - 12.0.2000.8 Sep 20 2018 21:40:02 Copyright (C) 2018 Microsoft Corporation)

ID    Name
1     US_Android_100x200_blue
2     UK_iOS_100x200_green

I want to split them into a table like this:

ID  Country  Device   Size     Color
1   US       Android  100x200  blue
2   UK       iOS      100x200  green

What are the ways to do it? I looked it up and it shows to use STRING_SPLIT but I haven't been able to find a clean way to do it.

STRING_SPLIT splits a string into a set consisting of a single column, not into multiple columns in the same row.

If there are always 4 components, you can use this long thing:

DECLARE @x TABLE(ID int,    Name sysname)
INSERT @x VALUES
(1,'     US_Android_100x200_blue'),
(2,'     UK_iOS_100x200_green');

;WITH x AS
(
  SELECT x.ID, y.value, rn = ROW_NUMBER() OVER
    (PARTITION BY x.ID ORDER BY CHARINDEX('_' + value + '_', '_' + x.name + '_'))
  FROM @x AS x CROSS APPLY STRING_SPLIT(x.name, '_') AS y
)
SELECT ID, Country = [1], Device = [2], [Size] = [3], Color = [4]
FROM x PIVOT (MAX(value) FOR rn IN ([1],[2],[3],[4])) AS p
ORDER BY ID;

Or you can hack it:

;WITH x AS
(
  SELECT ID, Name = REPLACE(Name, '_', '.') FROM @x
)
SELECT ID,
  Country = PARSENAME(Name, 4),
  Device  = PARSENAME(Name, 3),
  Size    = PARSENAME(Name, 2),
  Color   = PARSENAME(Name, 1)
FROM x
ORDER BY ID;


Oracle SELECT FIRST? I have a table of data invoices

invoice | vendor | date
0       | 0      | 08/01/2018
1       | 0      | 08/02/2018
2       | 1      | 08/05/2018
3       | 2      | 08/10/2018
4       | 2      | 08/10/2018
5       | 2      | 08/09/2018

I am trying to select the latest invoice number from each vendor, when there are more than one invoice for the same day I need the largest invoice number.

This is what I'm trying, but it gives me SQL Error: ORA-00979: not a GROUP BY expression sql developer references the beginning of first_value with that error.

SELECT max(date), first_value(invoice) OVER (order by date, invoice)
FROM invoices
GROUP BY vendor

I am using max(date) instead of max(invoice) because the largest invoice isn't always the latest date.

EDIT: this is what I'm trying to select

invoice | vendor | date
1       | 0      | 08/02/2018
2       | 1      | 08/05/2018
4       | 2      | 08/10/2018

EDIT2: added another row to demonstrate that the largest invoice number isn't always the latest date.

If I understand you correctly you can enumerate the invoices per vendor and pick the last one (order by desc):

with invoices(invoice, vendor, cdate) as
      (select 0, 0, date '2018-01-08' from dual union all
       select 1, 0, date '2018-08-02' from dual union all
       select 2, 1, date '2018-08-05' from dual union all
       select 3, 2, date '2018-08-10' from dual union all
       select 4, 2, date '2018-08-10' from dual
      )
      select invoice, vendor, cdate
      from (
        select invoice, vendor, cdate
             , row_number() over (partition by vendor
                                  order by date desc, invoice desc) as rn
        from invoices
      ) t
      where rn = 1;


sql getting sum of calculated columns How can I get the sum of TotalAmount as single value for example

Product Name UnitPrice  Quantity    Discount%      Amount     SharingCost%  ShippingCost    CustomsCost  TotalAmount
pro1        250.00      12          10.00           3000.00   75             375.00         0.00             3075.00
pro2        100.00      10          10.00           1000.00   25             125.00         125.00           1025.00

from the above sample data sum of TotalAmountis 4100.00.

How can I get sum value which is 4100.00 in single column for specific Purchase_ID

Note

Parent table is Stock_Purchase

Child table is Stock_Purchase_Details

Query

SELECT
  S.Product_Name,
  SPD.*,
  Sp.Delivery_Status,
  CONVERT(decimal(10, 2), (SPD.Quantity * SPD.UnitPrice)) Amount,
  CONVERT(int, (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * 100) [Cost%],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost AS decimal(10, 2)) AS [ShippingCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost AS decimal(10, 2)) AS [CustomsCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost + (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost + SPD.Quantity * SPD.UnitPrice - (SPD.Discount / 100) * SPD.Quantity * SPD.UnitPrice AS decimal(10, 2)) AS [TotalAmount]
FROM dbo.Stock_Purchase SP
INNER JOIN dbo.Stock_Purchase_Details SPD
  ON SP.Purchase_ID = SPD.Purchase_ID
INNER JOIN dbo.Store S
  ON SPD.Pro_ID = S.Pro_ID
WHERE SPD.Purchase_ID = '1'

Try naming that query, and then using the column with PARTITION BY in an OVER clause.

WITH MyCTE AS (
SELECT
  S.Product_Name,
    SPD.*,
    Sp.Delivery_Status,
  CONVERT(decimal(10, 2), (SPD.Quantity * SPD.UnitPrice)) Amount,
  CONVERT(int, (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * 100) [Cost%],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost AS decimal(10, 2)) AS [ShippingCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost AS decimal(10, 2)) AS [CustomsCost],
  CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost + (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost + SPD.Quantity * SPD.UnitPrice - (SPD.Discount / 100) * SPD.Quantity * SPD.UnitPrice AS decimal(10, 2)) AS [TotalAmount]
FROM dbo.Stock_Purchase SP
INNER JOIN dbo.Stock_Purchase_Details SPD
  ON SP.Purchase_ID = SPD.Purchase_ID
INNER JOIN dbo.Store S
  ON SPD.Pro_ID = S.Pro_ID
)
SELECT *, SUM(TotalAmount) OVER (PARTITION BY Purchase_ID)
FROM MyCTE;

But also, you may like to consider using APPLY to make this a lot simpler. Read http://blogs.lobsterpot.com.au/2011/12/13/a-t-sql-tip-working-calculations/ for more info on this.


MYSQL parallel query performance issue Im working on a project for my company which sells huge amounts of weather data to customers through a Web API. As part of that API, there is a backend service which needs to fetch data from MYSQL database (ver 5.1.1, InnoDB). The data set can reach amount of 50 000 rows and 12 columns if interval is set to whole year, which is very common. MYSQL database runs on Red Hat Linux 64bit, 4 core server computer.

Query which calculates, for example, daily averages on set of 50000 rows executes in 3.5 sec. However, if i run 4 of those queries in parallel, execution time for each of them reaches average 8sec per query. Im also monitoring client connection on DB and I can clearly see there are 4 threads executing each query. CPU usage on server during execution goes from 0 to cca 50% usage.

My question is, why execution time rises if I run same query in parallel? Im I missing something or this is expected behaviour?

More server info: CPU: Intel Core I5-4440 @ 3.1 GHz (4 cores), Memory: 8 GB

EDIT: Increase of innodb_buffer_pool_size from 530 MB to 5.5 GB solved problem!

Upgrade to 5.5, then 5.6, then 5.7.  You will be able to run more simultaneous queries at each upgrade.  Do you really mean "5.1.1"?  That was a pre-release for 5.1 from about 2005.
Use summary tables for old, static, data.  (It is static after it is INSERTed, correct?)  Perhaps summarizing (eg AVG, MAX, MIN) daily readings can be done just after midnight?
Use InnoDB, not MyISAM.  Much better sharing of the table.  (And it gets better with each version upgrade.)
innodb_buffer_pool_size should be about 5500M for your 8G machine.  Is the dataset, including indexes, bigger than that?  If so, get more RAM and/or RAID with striping and/or SSDs.
And Summary tables are the main cure for Data Warehouse performance issues.  Further discussion .  It's not uncommon to speed up queries 10-fold, especially I/O-bound ones.

"How does one solve..." -- Most applications are not doing lots of 3.5-second queries.  They are doing 'point queries', which might be 3.5 milliseconds.


Why can a log backup not be taken without first taking a full backup? I've created a new database using the FULL recovery model. Now I am trying to take a log backup without first taking a full backup.

I'm aware that SQL Server won't allow us to do so, but I want to know why SQL Server is preventing it?

Below is the script I've used for testing purposes:

CREATE DATABASE TESTING
GO
BACKUP LOG TESTING TO DISK='G:\TESTING_LOG.TRN'

When a database is switched to FULL recovery, it behaves as if it was in SIMPLE recovery until you take a full database backup. Up to that moment, you cannot take log backups, because the log gets truncated on CHECKPOINT, as happens in SIMPLE recovery.

See this article by Paul Randal for reference.

Log backups form part of a chain that allow the whole database to be recovered. The chain starts with a full backup (complete copy), and may continue from that point with further differential or log backups.

Image taken from Complete Database Restores (Full Recovery Model)

A transaction log backup contains the information needed to:

Redo all committed changes to the database since the last full or log backup
Undo in-progress (not yet committed) changes at the time the log backup was taken

In principle, it would be possible to recreate a database from log information alone, if every log backup ever was available, including the initial creation of the database itself, but that is not the way it was implemented.

As it is, a full backup is necessary is start the recovery path.


Slave I/O thread dies very often I have MySQL Replication working between two servers in master-master mode.

Replication has been working from day 1.

The IO Thread and the SQL Thread has never died. However, the Slave SQL thread dies very, very often.

The log only says:

120913 17:58:50 [Note] Slave I/O thread killed while reading event
120913 17:58:50 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.004109',
    position 101146947
120913 17:58:50 [Note] Error reading relay log event: slave SQL thread was killed

I've Googled for the problem, but found no indication of what to look for or how to resolve the issue.

What could be causing the thread to die?

When the IO Thread dies, it usually dies for these reasons

STOP SLAVE;
STOP SLAVE IO_THREAD;
Network Issues

The first two reasons are obvious, but many have been victimized by network connectivity. For example, if there are enough dropped packets along the traceroute of the IP you are using as the Master, the IO Thread will just die off. By default, mysql attempts to reconnect to the Master every 60 seconds.

The mysqld process does not record network level issues, but a dying IO Thread is usually a dead giveaway (no pun intended). If this is happening often, go to work troubleshooting the traceroute on that IP (perhaps ifconfig on that Master IP).


'Row Chaining/Overflow' in SQL Server when adding column to table? Each row in a table can be 8060 bytes, since page size is 8KB. If a row size exceeds this, the largest column is moved to another page ROW_OVERFLOW_DATA and a pointer is created on the original page.

What happens when a new column is added to an existing table ? Is the data stored at the 'end' of the table, and pointers are created after each existing row in a page to point to this new data ? Clearly this would cause performance issues..

Or is the entire table reorganized so the new column 'fits' in the original page ?

We're running SQL 2012 (& above) Enterprise, but also interested to hear about lower versions.
I'm interested in both heaps & clustered indexes, because in either one, the existing row must be modified - to add the new column, or to add a pointer to the new column, right ?

There are three separate concepts here and I think that it's important to keep them separate.

1. Adding a column to a table

Adding a column to a table is an optimized operation designed to not take a lot of time. Most of the time it will not require all pages in the table to be modified. You can verify by this by measuring the size of the transaction required to do so or by looking at the space used by the table before and after.

Suppose that you add a column to a table with a default value of 1. The table has 100 billion rows. Does it really make sense to modify every data page to write a "1" for every row? That could take a really long time. Instead, you can interpret the data in a way to work around it. If a value for the new row hasn't been written to the page yet then you can assume that it's the default value.

Martin Smith pointed out that this optimization only apply to Enterprise edition. One rule of thumb is that SQL Server needs to modify every page if the new value for the column isn't constant, such as a computed column or a column with a default of NEWID(). There are edge cases which may result in all pages getting modified, such as this one.

2. Updating a column for an existing row

If you update a column it's possible that the full row will no longer fit on the data page. For heaps, the row will be moved to a new page with a pointer. This is known as forwarded records. For clustered indexes some of the rows will be moved to a new page. This is known as a page split and many people have written extensively about them. If a table has so many forwarded records and page splits that you're experiencing some kind of performance problem then that can be cleaned up with a REBUILD. But you probably have better things to worry about.

3. Rows larger than 8k

As of SQL Server 2008 rows can be larger than 8k. Some of the mechanics involved in doing that are explained in the link that you have in the question. Conceptually this really has very little to do with adding a column to a table.


Why does SET ROWCOUNT not reset after GO? According to the documentation for SCOPE_IDENTITY:

  A scope is a module: a stored procedure, trigger,
  function, or batch. Therefore, if two statements are in the same
  stored procedure, function, or batch, they are in the same scope.

According to SQL Server Utilities Statements - GO:

  GO is not a Transact-SQL statement; it is a command recognized by the
  sqlcmd and osql utilities and SQL Server Management Studio Code
  editor. SQL Server utilities interpret GO as a signal that they should
  send the current batch of Transact-SQL statements to an instance of
  SQL Server.

  The current batch of statements is composed of all
  statements entered since the last GO, or since the start of the ad hoc
  session or script if this is the first GO.

Armed with that information I wrote (in SMSS) this T-SQL:

SET ROWCOUNT 1    -- Limit to 1 row returned

-- Returns 1 row as expected
SELECT *
FROM MyTable

GO -- Batch ends

-- Still returns one row
SELECT *
FROM MyTable

From my understanding SET ROWCOUNT (I know it is deprecated in DML but not SELECTs) is scoped to its module (from what I have seen examining other posts at any rate).

My questions:

Is SET ROWCOUNT an exception to the 'module' rule mentioned above?
Is it NOT scoped to the module at all?
Is there a more complete definition of T-SQL scope?

I ran a query, then a stored procedure, that only sets rowcount to 4 (I picked 4 out of the blue) and then ran query again: so

SELECT * FROM MyTable
EXE MySproc
SELECT * FROM MyTable

...returns all rows then 4 rows then all rows again so it is definitely scoped to at least sprocs.

Most (all?) set statements take effect at the session level...

  provides several SET statements that change the current session handling

... with the normal set of caveats.

See SET Statements (Transact-SQL) for more details.

Quite a few of those set statements listed @ that link are obviously session-level settings, eg:

set dateformat
set fips_flagger
set indentity_insert
set statistics time/io
set nocount
set transaction isolation level

And by session-level I mean that they remain in place across batches/go statements.


Tempdb data files are growing fast in SQL server 2014? A few days back we have moved to 2014 from 2012 server and we kept the same settings as earlier like we are using 8 data files in Tempdb for better performance and all placed in single drive.

As this is data warehouse environment batch loads are running continuously and tempdb drive is getting full.Even though 98% free space in Database it could not able reuse space and throwing disk is full.

  Insufficient space in tempdb to hold row versions. Need to shrink the
  version store to free up some space in tempdb. Transaction
  (id=239368387 xsn=1273322 spid=126 elapsed_time=1590) has been marked
  as a victim and it will be rolled back if it accesses the version
  store. If the problem persists, the likely cause is improperly sized
  tempdb or long running transactions. Please refer to BOL on how to
  configure tempdb for versioning.

Question

Is there any specific settings for tempdb to avoid these issues in SQL Sever 2014?
Any recommendation for tempdb performance improvement like enabling trace flag -T1118 ?

In 2012 it was taking only 64 GB and now even 450 GB is not sufficient.

Server specification.

SQL Server detected 4 sockets with 15 cores per socket and 30 logical processors per socket, 120 total logical processors; using 120 logical processors based on SQL Server licensing. 512 GB RAM.

Before troubleshooting TempDB space, it is good to know what objects are using TempDB.

Even though you have issues only with Version Store, knowing what objects are using TempDB will give you a good idea on where to start.

Remember, TempDB is a global resource for all databases, so this will be used for all databases. At a high level these are the objects that can use TempDB:

1. User objects

User-defined tables and indexes
System tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
Tables returned in table-valued functions

2. Internal objects

These are internal objects created by SQL Server for Work Tables, Hash Joins, sorts...

3. Version Store

Triggers
MARS
Online index
Row version-based isolation levels: requires setting at the database level

Further Cursors, Database Mail, DBCC CHECKDB can also use TempDB. See this article for more details Capacity Planning for tempdb

Now that we know how TempDB  is used, we can track TempDB space utilization using DMVS provided by SQL Server..

To Track user objects which are consuming the most space:

space used by user objects:(Active Only)

   WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text,
                 ERQ.statement_start_offset / 2,
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset
                  THEN 0
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;

Space used by Internal Objects((Active Only)):

SELECT
    t1.session_id,
    t1.request_id,
    t1.task_alloc,
    t1.task_dealloc,
    t2.sql_handle,
    t2.statement_start_offset,
    t2.statement_end_offset,
    t2.plan_handle
FROM (SELECT session_id,
             request_id,
             SUM(internal_objects_alloc_page_count) AS task_alloc,
             SUM(internal_objects_dealloc_page_count) AS task_dealloc
      FROM sys.dm_db_task_space_usage
      GROUP BY session_id, request_id) AS t1,
      sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id AND
     (t1.request_id = t2.request_id)
ORDER BY t1.task_alloc DESC

Version Store:
You can find the list of transactions which are using version store. To see how much space they consume, refer to article at the end

SELECT top 2
    transaction_id,
    transaction_sequence_num,
    elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions

Space used by transaction will be cleaned automatically when the transaction has completed. If the transaction is open for long time, it will prevent Version Store cleanup and you may have to check why, by joining with sys.dm_exec_sessions or sys.sysprocesses

You also can automate this space usage collection for later analysis.

This is just to give you idea, but it may not work in your version as some columns might have changed.

Query to gather details:

CREATE PROC sp_sampleTempDbSpaceUsage AS
  Instance level tempdb FILE space usage FOR ALL files WITHIN
  -- tempdb
  INSERT tempdb_space_usage (
    scope,
    Instance_unallocated_extent_pages,
    version_store_pages,
    Instance_userobj_alloc_pages,
    Instance_internalobj_alloc_pages,
    Instance_mixed_extent_alloc_pages)
  SELECT
    'instance',
    SUM(unallocated_extent_page_count),
    SUM(version_store_reserved_page_count),
    SUM(user_object_reserved_page_count),
    SUM(internal_object_reserved_page_count),
    SUM(mixed_extent_page_count)
  FROM sys.dm_db_file_space_usage

    -- 2. tempdb space usage per session
    --
  INSERT tempdb_space_usage (
    scope,
    session_id,
    Sess_task_userobj_alloc_pages,
    Sess_task_userobj_deallocated_pages,
    Sess_task_internalobj_alloc_pages,
    Sess_task_internalobj_deallocated_pages)
  SELECT
    'session',
    session_id,
    user_objects_alloc_page_count,
    user_objects_dealloc_page_count,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count
  FROM sys.dm_db_session_space_usage
    WHERE session_id > 50
    -- 3. tempdb space usage per active task
    --
  INSERT tempdb_space_usage (
    scope,
    session_id,
    Sess_task_userobj_alloc_pages,
    Sess_task_userobj_deallocated_pages,
    Sess_task_internalobj_alloc_pages,
    Sess_task_internalobj_deallocated_pages,
    query_text)
  SELECT
    'task',
    R1.session_id,
    R1.user_objects_alloc_page_count,
    R1.user_objects_dealloc_page_count,
    R1.internal_objects_alloc_page_count,
    R1.internal_objects_dealloc_page_count,
    R3.text
  FROM sys.dm_db_task_space_usage AS R1
    LEFT OUTER JOIN
    sys.dm_exec_requests AS R2
    ON R1.session_id = R2.session_id
    OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
  WHERE R1.session_id > 50

Once you have gathered enough data, you can analyze using below queries:

Query 1: This query reports the maximum allocated space in TempDB over all the data points collected

SELECT
  CONVERT (float, (MAX(version_store_pages +
      Instance_userobj_alloc_pages +
      Instance_internalobj_alloc_pages +
      Instance_mixed_extent_alloc_pages)))/ 128.0
    AS max_tempdb_allocation_MB
FROM     tempdb_space_usage
WHERE scope = 'instance'

Query 2: This query computes the maximum allocated pages and the version store size in megabytes over all the data points collected. If the amount of TempDB space that is allocated to the version store is large, it implies that long-running transactions are generating or consuming versions.

SELECT
  MAX(version_store_pages) AS max_version_store_pages_allocated,
  MAX(version_store_pages/128.0) AS max_version_store_allocated_space_MB
FROM tempdb_space_usage
WHERE scope = 'instance'

Query 3: This query shows the top five queries that are allocating the most pages for internal objects.

SELECT top 5 MAX ((Sess_task_internalobj_alloc_pages) - (Sess_task_internalobj_deallocated_pages))
  AS Max_Sess_task_allocated_pages_delta,     query_text
FROM tempdb_space_usage
WHERE scope = 'task' and session_id > 50
GROUP BY query_text
ORDER BY Max_Sess_task_allocated_pages_delta  DESC

Below are the references i used which can help you further:

https://blogs.msdn.microsoft.com/sqlserverfaq/2010/10/13/troubleshooting-tempdb-growth-due-to-version-store-usage/
How to identify which query is filling up the tempdb transaction log?
https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/cc966545.aspx

Below is must read:

https://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx
https://technet.microsoft.com/en-us/library/cc966545.aspx


Need help about giving relationships in database design I am developing a simple blog application in which users can write posts and others can like, comment, share etc. i am facing difficulty in designing database.
What I have tried is:

user (user_id(pk), user_name)

post (post_id(pk), post_content, likes, user_id(fk))

comment(comment_id(pk), comment, post_id(fk))

PROBLEM: One user can have many posts and many users can like and comment on many posts. Whether I have to separate two tables like: user, post and userpost?

Can anyone tell me if anything wrong in this design?

Try something like this

user (user_id(pk), user_name)

post (post_id(pk),user_id(fk), post_content)

comment(comment_id(pk),post_id(fk),user_id(fk), comment)

likes(post_id(fk), user_id(fk) status)

In status column in likes table you can save it as like or unlike can be tinyint field with

NULL - Default value
True - Like and False - unlike(if you have unlike option)


MySQL InnoDB locking on combined UPDATE-JOIN statements on querying statements which contain an UPDATE combined with JOIN, we are getting the following errors:

Statements writing to a table with an auto-increment column after selecting
from another table are unsafe because the order in which rows are
retrieved determines what (if any) rows will be written.
This order cannot be predicted and may differ on master and the slave.

These statements finally turn into deadlocks and kill other statements trying to access the same table (update: it doesnt even seem the queries care about the timeout, because some deadlocks occure after 5-20 seconds).

innodb lock wait timeout = 150
binlog_format = STATEMENT

I can't believe that MySQL offers the possibility to UPDATE a table depending on another table (UPDATE x JOIN y.. SET x.val = 1 WHERE y.val = 1), but can't handle it on InnoDB-Cluster.

Any Ideas?

Without seeing your specific query and table structures, it's not possible to give a really precise explanation or to know whether MySQL is being unnecessarily cautious; however, the message you are seeing appears to be a consequence of using binlog_mode = STATEMENT which you should be able to safely change to binlog_mode = MIXED.

The problem is, if your update query isn't deterministic, and is replicated by executing the same query on the replica -- as opposed to simply sending rows changed by the query to the replica, as would likely be done in MIXED binlog format (and certainly in ROW format) -- then the execution on the replica might be optimized differently and result in your tables being different.

If that is the case -- or if MySQL thinks that is the case -- then it is correctly preventing you from running a query that might not replicate properly because of your chosen binlog_mode.

The innodb_lock_wait_timeout value is not related to deadlocks.  It is a timer for queries that block waiting for row locks, but which could eventually succeed because the lock requests aren't incompatible with any other locks.  This timer affects queries which fail this:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

A deadlock, on the other hand, is a completely different thing.

Deadlocks have no timer, because by definition, a deadlock will hang infinitely.  So, as soon as they are detected, MySQL will kill as many queries as necessary to resolve the deadlock, typically killing the one(s) that did the least amount of work so far.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlocks occur when two (or more) threads try to do mutually-incompatible things that cannot ever possibly complete, because each of them has to wait for the other one to finish before it can proceed.

http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html


Database Dump/Backup but only to specific records? This question is about MySQL Backup (or Dump) whereby i want to backup and store information from only information related to a person; SchoolA from the School Table in this example.

So, I should not be able to see any information from other schools using that backup.

I want to give this backup information to that school. However, i do not want them to see information from other schools.

Is there anyway to achieve this?

You would dump each table using the --where option. I wrote a rather gnarly post about 4 years ago : Is it possible to mysqldump a subset of a database required to reproduce a query?

In your case, you can do the following: Suppose the database is called schooldb

Dump by SchoolID

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
OPTS="--lock-all-tables"
SCHOOLID=1
WHERE="tagID in (select tagID from Ownership where studentID in"
WHERE="${WHERE} (select studentID from Students where schoolID = ${SCHOOLID}))"
mysqldump ${MYSQL_CONN} ${OPTS} --where="${WHERE}" schooldb tags > tags.sql

Dump by School Name

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
OPTS="--lock-all-tables"
SCHOOL_NAME=schoolA
WHERE="tagID in (select tagID from Ownership where studentID in"
WHERE="${WHERE} (select studentID from Students where schoolID in"
WHERE="${WHERE} (select schoolID from Schools where name='${SCHOOL_NAME}')))"
mysqldump ${MYSQL_CONN} ${OPTS} --where="${WHERE}" schooldb tags > tags.sql

GIVE IT A TRY !!!

If you are using mysql client only, you could dumping SELECT ... INTO

SELECT B.studentID,B.name,D.tagID,D.data
INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Schools A
INNER JOIN Students  B ON A.SchoolID  = B.SchoolID
INNER JOIN Ownership C ON B.studentID = C.studentID
INNER JOIN Tags      D ON C.tagID     = D.tagID
WHERE A.name = 'schoolA';

or

SELECT B.studentID,B.name,D.tagID,D.data
INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Schools A
INNER JOIN Students  B ON A.schoolID  = B.schoolID
INNER JOIN Ownership C ON B.studentID = C.studentID
INNER JOIN Tags      D ON C.tagID     = D.tagID
WHERE A.schoolID = 1;


Help with designing sales and inventory management system I need to make a sale and inventory management system. I'm stuck on the purchasing price and stock for the product.
Let's say you bought 10 t-shirt for $10 per t-shirt and selling them at $20 per t-shirt.
You sold 8 t-shirt quickly and decided to buy 10 more. Now, the price is $12 per t-shirt.
Now , you've total 12 t-shirt in stock, where 2 t-shirts purchasing price is $10 and 10 t-shirts purchasing price is $12.
How can I make a product table which will keep track of the purchasing price at different times for the product and also the total stock ?

Your Product table shouldn't store the Price at all. You need a separate table called something like Purchases which has a row for every purchase made, the Price and PurchasedDate, and a foreign key field to reference the Product table.
Also, tables like Purchases and Sales are normally broken out further into two tables each. One table having header information and the other the line information. For example, PurchaseOrders would store the header information like PurchasedDate, PurchasedBy, maybe the Company purchased from (if only one Company can be on one Order), where the PurchaseLines table would be a row for every item purchased on that PurchaseOrder, with the Quantity (per item), Price, and foreign key field to relate back to the PurchaseOrders table. Similar structure for SalesOrders and SalesLines.
PurchaseOrders are one-to-many PurchaseLines. (SalesOrders are one-to-many SalesLines.)


Bank transaction table design I’m making a little site which may, if I’m lucky, hold a little of data with regards banking transactions. This is version 2 of a previous learning project.

I will have a table with transactions. Based on the type (payment, deposit or transfer) the value will be negative or positive.

Best cases scenario, I have 10,000 users and they each have 1500 transactions per year and 8 years of data. 120,000,000. One primary bit of info stored and shown a lot is Current Balance. But also, the running total shown on multiple different types of statement and transaction screens.

So, should I break the rules and store the running total or transaction on every row, and recalculate all transactions that an insert/update may effect? I mean, that’s usually just the last weeks transactions.

Or store a separate DailyBalance table, with a date and account number as the pk with that days transactions added?

Or simply calculate on the fly?

Any advice on structure for performance as data integrity would be handy.

I’m using SQL Server 2016.
I’d have a pk on the transactionid.
And an index on accountId
As well as one on transactionDate probably.

Maybe clustered on Id, accountId?

The typical design here is to have a Transaction table and an Account table

The Account table has the current balance, as of the last transaction that has "posted".  A modern "online" system will post transactions immediately, but older systems would capture transactions online, but run "batch" to post the transactions later.

PK on the transaction table would normally be (AccountId,TransactionId), which balances the cost of insert and the cost of pulling the transactions for a single account.

I don't think there's a rule controlling whether you also store the running balance on the transaction table.  Normalization does not require you to recalculate the account balance or the running balances.  Balance is a proper attribute of Account, regardless of whether it can be derived from rows in another table.  And you don't necessarily have to perform these calculations as you insert transactions.


Joining on a M-N lookup table vs keyword searching a single column I am in the process of writing an image gallery webapp similar to that of Danbooru, and in the process, decided to take a look at danbooru's database schema for an idea about how things are structured. In doing so, I've noticed that the 'uploads' table contains a column named tag_string, which I'm guessing contains comma-separated tags. There's also a tags table which (again, by the looks of it) contains metadata about individual tags.

This got me thinking about the practicality of my initial design whereby I have a M-N lookup table specifically for mapping N individual tags to M different files. My design would use SQL Join to get a result set containing all of the images I'm looking for, while I can forsee danbooru's solution using a search on the text column containing the list of tags associated with that image.

My lookup table could concievably get very large (in terms of # of rows) if every file contains upwards of 20 or more tags, whereas the other solution only extends the size of the files table by one column.

With these differences in mind, what are the pros and cons of the different implementations?

For reference, here is an incomplete (but contains enough information regarding this question) design of my schema.

It is best to store your tags in a separate table because of the many to many relationship. With something like tagging images you can end up with a very large number of comma separated values that would need to be parsed any time you search your database which will lead to massive performance problems as your data grows.

Going that route and searching for a tag or partial tag will require you to parse through multiple strings multiple times. But if all you are doing is displaying the data to be read then comma separation is a fantastic idea and it would be much faster than joining to another table.

Here is great explanation on why, in general, comma separation is not a good practice.

--EDIT

After looking through the githuh link you have posted and the web site, I think Danbooru is doing a combination of two things. If I had to quickly guess, they are keeping the comma separated values of tags to make it easy and fast to view the text upon a mouse hover. They also seem to be inserting the tags into a well structured set of tables. It may be nice to keep that in mind for your practices but I would recommend leaving that out if you can.


Full text search through many tables in MySQL We have high traffic NEWS websites, I want to add a feature that every user can search through over all content of site, such as news, polls, comments, galleries,etc . Each of contents type has its own table(s).
I decided to create a table that holds all of contents from all types:
CREATE TABLE full_text_search
(
    master_id INT NOT NULL,
    content_text TEXT NOT NULL,
    PRIMARY KEY ( master_id )
);

I generate a unique number as master_id for each content of all types to identify each content_text in full_text_search table.
for example:
News table:
+----+-------------+---------+---------+----------+------------+
| id | news_title  | lead    | subtitle|  content | master_id  |
+----+-------------+---------+---------+----------+------------+
|  1 |  sometitle  |some lead| subtitle|content 1 |     3      |
|  2 |  some title |some lead| subtitle|content 2 |     5      |
+----+-------------+---------+---------+----------+------------+

article table:
+----+-------------+---------+------------------+---------+------------+
| id | title       | author  | short description| content | master_id  |
+----+-------------+---------+------------------+---------+------------+
|  1 |  sometitle  | someone | very short desc  |content1 |     1      |
|  2 |  some title | otherone|  some short desc |content2 |     4      |
+----+-------------+---------+------------------+---------+------------+

As you can see master_id is unique between above tables. When ever a new content from each type inserted, also I should INSERT it in to full_text_search table.
QUESTIONS

For many inserts for a day(about 3000 from all types), is it a good solution or it is anti pattern?
Is it better choice if I separate this table from my other tables, and put it in any other DB such as other RDBMS or NoSQLs?
Any other solutions?

Sure, it's fine to copy the searchable content to your full_text_search table.

MySQL supports FULLTEXT indexes only in the MyISAM storage engine (until MySQL 5.6, but fulltext in InnoDB in MySQL 5.6 still seems a little unstable).  So you can store your canonical data in InnoDB for safety, and a copy in MyISAM for indexing.  MyISAM is susceptible to data corruption, but if it's only a copy then you just need to repopulate the MyISAM table if it ever gets corrupted.

Your use of master_id as distinct from the primary key of each table is a little strange.  Why not use the primary key, and add another column to your full_text_search table for the type of content?

CREATE TABLE full_text_search
(
    id INT NOT NULL,
    content_type ENUM('news','polls','comments','galleries','articles') NOT NULL,
    content_text TEXT NOT NULL,
    PRIMARY KEY ( id, content_type )
);

full_text_search table:
+----+--------------+---------------+
| id | content_type | content_text  |
+----+--------------+---------------+
|  1 |         news |     ...       |
|  1 |     articles |     ...       |
|  2 |     articles |     ...       |
+----+--------------+---------------+

Another option is to create a fulltext search index in another specialized technology such as Sphinx Search or Apache Solr.  But the same pattern would be useful -- store the primary key field and a field for the type of content.


Postgres: check disk space taken by materialized view? I know how to check the size of indexes and tables in Postgres (I'm using version 9.4):

SELECT
   relname AS objectname,
   relkind AS objecttype,
   reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
   FROM pg_class
   WHERE relpages >= 8
   ORDER BY relpages DESC;

But this doesn't show materialized views. How can I check how much disk space they are taking up?

This is assuming that materialized views have relpages >= 8 in pg_class, which doesn't have to be the case. It can actually be empty - not populated yet, indicated by pg_class.relispopulated = FALSE. The corresponding disk file has a zero size in this case.

Try instead:

SELECT relname   AS objectname
     , relkind   AS objecttype
     , reltuples AS entries
     , pg_size_pretty(pg_table_size(oid)) AS size  -- depending - see below
FROM   pg_class
WHERE  relkind IN ('r', 'i', 'm')
ORDER  BY pg_table_size(oid) DESC;

Where the available types are:

  r = ordinary table,
  i = index,
  S = sequence,
  v = view,
  m = materialized view,
  c = composite type,
  t = TOAST table,
  f = foreign table

Use one of the database object size functions rather than building your own. Be aware that the "size of a table" can be defined in different ways. Details:

Measure the size of a PostgreSQL table row


MSSQL Master db singleuser in sys.databases but multiuser in sp_helpdb After opening SSMS, I have found that the master database on one of our production servers is in Single User mode.

Result of select * from sys.databases shows, like SSMS, that the master database has a user_access_desc set to SINGLE_USER.

However, executing sp_helpdb 'master' says it is in MULTI_USER mode.

Nothing problematic noticed on the server. I can access server and master database through multiple threads.

Any ideas?

First,
Run the

ALTER DATABASE master
SET MULTI_USER;

and confirm you receive the error Option ‘MULTI_USER’ cannot be set in database ‘master’.

Check your errorlog for any information that could explain why and when.
Then

Backup the master database
Restore as a different database, example 'master_temp'.
Switch master_temp to MULTI_USER.
Backup master_temp.
Restore master with the backup of master_temp.


Transactional Replication not sending all commands I have a transactional replication topology as follows:

NY Publication Database same server holds distributor

NY - Subscriber
LON - Subscriber
HK - Subscriber

We are using bog standard, one-way transactional replication. All machines SQL 2008 R2

The snapshot gets applied fine to all subscribers, and then starts distributing commands, as you would expect, but to the one subscriber only (London), a whole bunch 2000-4 million commands just get "stuck". You can see the commands in msrepl_commands, you can see the transactions in msrepl_transaction. Replication monitor shows that there are xxxxx undelivered commands. All commands are being delivered without issue to the other 2 subscribers.

The problem is that they just stay there. Replication monitor says that there is nothing to be replicated, and nothing happens, they just stay where they are. The only way I have found around it, it to tear down the subscription and re-initialize!?!

I am aware that there are network "blips" between NY and Lon, and I can see in msrepl_errors, that there are some communication failures. However, I would expect replication to pick them back up? The distribution agent is running fine.

How do I get around this issue, and force SQL Server to pick these commands up, and deliver them.

Examine the output of sp_helpsubscriptionerrors. One issue I have seen is with DDL commands; they are treated differently in replication so if manual schema changes are being made to the Publisher, they can sometimes fail. There may also be specific DML commands failing on the subscriber due to transactional inconsistencies. If this is the case, you can use the distribution agent profile called "Continue On Data Consistency Errors" which will ignore duplicate key and other errors.

Latency could be causing the problem (according to MS, latency and throughput are the most important factors in transactional replication); you can track that with sp_posttracertoken. Both the Log Reader and Distribution agents have parameters which can be tweaked to compensate for that. Try lowering these values, particularly on the Log Reader.

Distribution Agent (from MSDN):

MaxDeliveredTransactions
Is the maximum number of push or pull transactions applied to Subscribers in one synchronization. A value of 0 indicates that the maximum is an infinite number of transactions. Other values can be used by Subscribers to shorten the duration of a synchronization being pulled from a Publisher.

CommitBatchThreshold
Is the number of replication commands to be issued to the Subscriber before a COMMIT statement is issued. The default is 1000.

Log Reader Agent (from MSDN):

MaxCmdsInTran
Specifies the maximum number of statements grouped into a transaction as the Log Reader writes commands to the distribution database. Using this parameter allows the Log Reader Agent and Distribution Agent to divide large transactions (consisting of many commands) at the Publisher into several smaller transactions when applied at the Subscriber. Specifying this parameter can reduce contention at the Distributor and reduce latency between the Publisher and Subscriber. Because the original transaction is applied in smaller units, the Subscriber can access rows of a large logical Publisher transaction prior to the end of the original transaction, breaking strict transactional atomicity. The default is 0, which preserves the transaction boundaries of the Publisher.


postgres window functions: using CASE to partition by age group I am using postgres window functions to get a list of users taking part in a competition and their corresponding ranks based on a number of columns, all good so far....now I need to get rank based on 'age group' which are a number of pre-defined categories (eg. 0-15 years, 15+ years etc...), where age is calculated based on a timestamp column 'dob'.

I am pretty sure I can do this using CASE statements but I am unable to get the query structured correctly, I have written the query below which defines the 'age groups' in a separate FROM subquery so I can reference it in the WINDOW definition, but this doesn't work as the two FROM's are independent

SELECT
cu.* as compUser,
cus.time_in_seconds as timeInSeconds,
rank() OVER allTimes as overallRank,
u.gender as gender,
rank() OVER genderTimes as genderRank,
ageGroup as ageGroup,
rank() OVER ageGroupTimes as ageGroupRank,
FROM
competition_users cu,
(SELECT CASE WHEN usr.dob>'2000-01-01' AND usr.dob<now() THEN '0-15' ELSE '15+' END FROM users usr WHERE usr.user_id = cu.user_id) ageGroup
LEFT JOIN users u ON cu.user_id = u.user_id
FULL JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id
WHERE cu.left_competition = false
AND cu.competition_id = :compId
WINDOW
allTimes AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC),
genderTimes AS (PARTITION BY u.gender ORDER BY cus.time_in_seconds ASC),
ageGroupTimes AS (PARTITION BY ageGroup ORDER BY cus.time_in_seconds ASC)

(above produces: ERROR:  invalid reference to FROM-clause entry for table "cu"...HINT:  There is an entry for table "cu", but it cannot be referenced from this part of the query.)

Can someone point me in the right direction? (sample query above is simplified, there are many age categories)

You could use a LATERAL join for this. And no need for joining the users table twice:

FROM
    competition_users AS cu
  LEFT JOIN users AS u
    ON cu.user_id = u.user_id
  LEFT JOIN LATERAL
    ( SELECT CASE WHEN u.dob > '2000-01-01' AND u.dob < now()
                 THEN '0-15'
                 ELSE '15+'
             END AS ageGroup
    ) AS age
    ON TRUE
  LEFT JOIN current_competition_sessions AS ccs
    ON cu.competition_user_id = ccs.competition_user_id
WHERE cu.left_competition = FALSE
  AND cu.competition_id = :compId

and then the WINDOW would be left (almost) unchanged:

WINDOW
    ageGroupTimes AS (PARTITION BY age.ageGroup ORDER BY cus.time_in_seconds ASC)

Alternatively, a simple CTE or derived table with a normal LEFT JOIN could be used, without the need for the (9.3+ available only) LATERAL. With the following you'll only need to slightly modify your upper-level SELECT and WINDOW (removing table aliases mainly):

FROM
  ( SELECT
        cu.*,
        cus.time_in_seconds,
        u.gender,
        CASE WHEN u.dob > '2000-01-01' AND u.dob < now()
            THEN '0-15'
            ELSE '15+'
        END AS ageGroup
    FROM
        competition_users AS cu
      LEFT JOIN users AS u
        ON cu.user_id = u.user_id
      LEFT JOIN current_competition_sessions AS ccs
        ON cu.competition_user_id = ccs.competition_user_id
    WHERE cu.left_competition = FALSE
      AND cu.competition_id = :compId
  ) AS d


Predicate not resulting in expected scan So I have this query that I'm trying to tune and am running into an issue that I'm having trouble understanding. First of this is the query I'm using.

SELECT
    si.LoanNbr AS [LoanNumber],
    fi.[SvcClientNbr] AS ClientID,
    si.LoanMasterID,
    si.LoanSrcCode AS [LoanSourceCode],
    fi.LoanPurpCode,
    fi.[PropState] AS [Property State],
    im.ImagedocumentID AS [Image Document ID],
    -- im.requestID AS [Request ID],
    CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
    im.[PageCount],
    im.[SignatureInd]
FROM dbo.NotMybaseTable Si
INNER JOIN dbo.NotMyTableName fi
    ON si.LoanMasterID = fi.LoanMasterID
INNER JOIN [dbo].[ImagedDocument] im
    ON si.loanmasterid = im.loanmasterid
    AND im.[DocTypeCode] = '10112'
WHERE CASE WHEN si.loansrccode = 'CORE' AND  Im.[SignatureInd] IN ('Y') THEN 1
        WHEN si.FundingSysCode = 'LIS' and CASE WHEN si.loansrccode = 'CORE' THEN 0 ELSE 1 END = 1 THEN 1
    ELSE 0 END = 1
    AND [ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

When I run this query the execution plan I get looks like this.

So the query is doing a very large scan on this large table returning 30 million rows just to pare it down to 3K. The index it's using looks like this.

CREATE NONCLUSTERED INDEX [IX_ImageDoc_DocType] ON [dbo].[ImageDoc]
(
    [LoanMasterID] ASC,
    [ImageDate] ASC,
    [ImageDocType] ASC
)

I altered to query in a way that gives it much better performance by moving the date portion of the predicate outside of the initial query like so.

select
    *
from (
    SELECT
        si.LoanNbr AS [LoanNumber],
        fi.[SvcClientNbr] AS ClientID,
        si.LoanMasterID,
        si.LoanSrcCode AS [LoanSourceCode],
        fi.LoanPurpCode,
        fi.[PropState] AS [Property State],
        im.ImagedocumentID AS [Image Document ID],
        CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
        im.[PageCount],
        im.[SignatureInd]
    FROM dbo.NotMybaseTable Si
    INNER JOIN dbo.NotMyTableName fi
      ON si.LoanMasterID = fi.LoanMasterID
    INNER JOIN [dbo].[ImagedDocument] im
      ON si.loanmasterid = im.loanmasterid
      AND im.[DocTypeCode] = '10112'
    WHERE CASE WHEN si.loansrccode = 'CORE' AND  Im.[SignatureInd] IN ('Y') THEN 1
            WHEN si.FundingSysCode = 'LIS' and CASE WHEN si.loansrccode = 'CORE' THEN 0 ELSE 1 END = 1 THEN 1
        ELSE 0 END = 1
        ) as F
WHERE [ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

This resulted in much better performance and a query plan that is obviously superior.

The index it's scanning now looks like this.

CREATE NONCLUSTERED INDEX [IX_ImageLoanDoc_SystemName_ICMPDocTypeCode] ON [dbo].[ImageDoc]
(
    [SystemName] ASC,
    [ICMPDocTypeCode] ASC,
    [LoanMasterID] ASC,
    [ImageDate] ASC
)
INCLUDE (   [ImageDocumentID],
    [ImageDocType],
    [BatchName],
    [SignatureInd],
    [ICMPDocCategoryCode],
    [ICMPDocSubTypeCode])

So here's my question. With ImageDate being in this index why does having it in my query hurt performance so badly? Shouldn't having that predicate included make it easier to eliminate rows? What am I doing/thinking incorrectly?

Your query is looking for values between 2015-04-25 AND 2015-05-30.

Of which it looks like you have about 30 million.

Unfortunately there is a bug with the cardinality estimations where DATEDIFF is involved where the components get reversed.

SELECT DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, -30) ,
       DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, 0) + 5

Returns a range of 1784-05-15 to 1784-06-19. Extremely likely you have no dates in that range in the table (or statistics histogram) so SQL Server will estimate that no rows will be returned out of the scan, and certainly no need for 30 million lookups.

This is a fixed bug but requires trace flag 4199 enabled.

If that isn't possible you could try rephrasing the predicate in a different way that avoids the use of DATEDIFF or assigning the values to variables and using OPTION (RECOMPILE)


Doubts in a Glenn Berry Query ( CPU per Database ) Please, if this is not place to post questions like these, let me know and I will delete it.

Inside the Glenn berry's Diagnostic queries, there is a query to show how much CPU a database is using. This is the query:

-- Get CPU utilization by database (Query 24) (CPU Usage by Database)
WITH DB_CPU_Stats
AS
(SELECT DatabaseID,
        DB_Name(DatabaseID) AS [Database Name],
        SUM(total_worker_time) AS [CPU_Time_Ms]
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
             WHERE attribute = N'dbid'
              ) AS F_DB
 GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
       [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Rank] OPTION (RECOMPILE);

And I would like to know, if this is a query to see databases that are now using more CPU, or is this based with past information?

I'm trying to know what is causing my server to have high CPU use:

Well, in this picture the server is pretty good, but almost always we have 90%+ of CPU usage, and using sp_whoisactive I can't find nothing ( obviously I got a lot of queries, but none of them seems to be hammering the server ).And read\write is pretty low ( everytime ). And thats what i'm having problems to understand. how can a low read/write server be using so much CPU? Does I/O have nothing in common with CPU?

I'm trying to know what database is the heaviest one, to migrate it.

On first glance this looks to me to approximate CPU per database, over the history of sys.dm_exec_query_stats (so usually since the last restart), but only for plans that are currently in the cache. It also relies on a plan cache attribute, dbid, which means that was the context for the query, but not necessarily that that was the database that caused the work. For example, guess where all the CPU gets reported for this query:

USE tempdb;
GO
SELECT CONVERT(DATETIME, CONVERT(CHAR(10), CONVERT(DATE,
  CONVERT(DATETIME, o.create_date)), 120))
FROM msdb.sys.all_objects AS o
CROSS APPLY model.sys.all_columns AS c;

I'll give you a hint: It's not msdb or model.

So, it should be used as a ballpark, but there are no guarantees that it reflects 100% of reality 100% of the time. The more often you query it (e.g. have some automated job that stores snapshots of it every n minutes), the more accurate it will be, but unless you have applications that treat each database like impenetrable silos, it will still be influenced by database context rather than the actual source of queries and data.


Linking two fields from different tables (are relationships required?) For example, I have two tables.  Employees and Products.

Inside Employees I have the fields: emp_ID, emp_fname, emp_lname, emp_products
Inside Products I have the fields: pro_ID, pro_name, pro_warehouse, pro_employees

Any employee can sell any product.  I want to track which employees sell which products, and which products are sold by which employee.

If I do a lookup wizard in Employees: emp_products and allow multiple selections, and the same thing in Products: pro_employees...then in the Employee: emp_products field I select "Dodge Ram" and "Jeep Rubicon"  I want that employee added to the respective Product record in Products: pro_employees.

Is this even possible, and if so...how do I go about this?  I'm pretty new and I'm having a hard time searching the right words/phrases for this without coming up with relationship support.

You need a table to sit between Employees and Products.  It will contain the emp_Id and pro_id, though you may discover other columns later. As an employee sells a product insert the corresponding combination of IDs into this table. Remove emp_products and pro_employees from your model.


DBA Permissions on LOCAL SERVER containing SQL SERVER BACKGROUND: Infrastructure team has revoked local admin where SQL Servers resides to everyone including DBAs. Finally, they agreed to let us RDP to the SQL Servers (which they had taken out), but any of the tasks we need elevated permissions are being done via a webex where the individual with rights logs into the server and gives me control to do what I have to do. (I still have to wait for that person to be available to do this for me).

I am Database Administrator III, MCSE, MCSA, and I can't even support my servers. My coworker and I provide support to over 140+ SQL servers from 2008-2014.  95% are virtual servers (Hypervisor). I am trying to get opinions from the SQL Server experts in the industry so I can document the answers for management, and would really appreciate your comments.

QUESTION: * What is an appropriate workaround? *

These new security policies implemented (and without letting us know when they were going into effect) against all the staff in IT, and have prevented my fellow DBA and I from doing the very basic DBA tasks (which I have already documented for my manager and explained locations affected, registry keys, etc..):

Install SQL server
Install Upgrades, Hotfixes, or apply patches to SQL server
Upgrade or install features/components to SQL server
Create databases
Create data or log files and or move them to different devices
Manage files or file groups for space utilization, cleanup, and TROUBLESHOOT
Backup and Restores
Install Microsoft Assessment and planning tool.  Had to install under someone else’s login now I can’t run it or access the database in (localdb)\maptoolkit
Reboot the server after installs or to clear up an issue.- I’m ok with infrastructure doing this
Start and stop services for SQL components.- I’m ok with infrastructure doing this
Implement:

replication
high availability
SQL Server cluster
another business continuity strategy

Implement auditing
Import/export data
Start the instance of SQL Server in single-user mode
Connect to SQL Server When System Administrators Are Locked Out
Server Configuration Manager
Create jobs in the OS scheduler if needed - I’m ok with infrastructure doing this if needed, and we can provide scripts

What is an appropriate workaround?

There are a number of US government regulations that, depending on how your company interprets them, require this separation of duties.  Sarbanes–Oxley (SOX) is the big one for publicly traded companies.  HIPAA  for healthcare organizations, etc.

The first question I have is, of those tasks you outlined, how many are you doing directly from the server?  Arguably many of those tasks don't require local server access, but I understand this would be frustrating if you are used to performing this work locally on the server.

Regarding workarounds, my suggestion is to be an adopter of this policy.  Do this by making sure that your security team is properly locking down user accounts, but creates clearly defined policies for service accounts and that those accounts are setup appropriately.  I would even encourage you to look at Group Managed Service Accounts as this makes everyone's life easier if you can make them work in your environment.  Follow this up with moving whatever processes you can off of the servers.  There are real security concerns with users actively logging into servers regularly, and I would say you should avoid doing this unless absolutely necessary.  Argenis Fernandez, during one of the most entertaining PASS sessions I've attended, even showed that anyone with access to a local admin account on a server can get elevated access to the SQL Server regardless how "locked down" it is.  Honestly, you could even go so far as to request all of the servers get configured to run as Windows Server Core, which is about as secure a Windows server as you can have. Finally, document those processes that cannot be moved off or need to have exceptions created for them.  This will likely be your biggest headache in the short term, but proper documentation should allow your organization to bend the rules to fit the business needs.

Look at it this way.  If anything goes bad, such as a breach of information, and it's traced back to unauthorized access to a server, this policy may save your behind without you knowing.  I've worked for years in the healthcare and insurance industries and as much as a headache as this sort of policy may seem to be, it's really a good policy to adopt.

This process will be frustrating... at least it has been the number of times I walked a similar path, but don't make it a we vs. them issue, instead approach road blocks with the following statement "this needs to happen so the business can run, how do we accomplish that within these restrictions?"  With this approach, it's not about you or your job, it's about your company staying in business which makes it a we vs. the policy issue.


Compare Two Columns, Remove Duplicate Characters Example table:

  ID      CA      CB
  ------  ------  -----
  1       龍      龍 竜 龒
  2       齒      歯 齒
  3       黽      黽

I would like to modify column CB so that it doesn't contain any of the characters in CA.
Like this:

  ID      CA      CB
  ------  ------  -----
  1       龍       竜 龒
  2       齒      歯
  3       黽

(SQLite)

Assuming that you mean to remove the character of CA from CB in the same row and that there's always just one character in CA, you can do it like this:

UPDATE your_table
SET CB = REPLACE(CB, CA, '');


T-SQL Case functionality Is it possible to structure a CASE statement as follows?

For each Path column I want to update the Value column, but the values aren't being set and I'm not sure how to proceed. The columns are all nvarchars(255).

I've taken this code from an answer on StackOverflow; however in that case the columns listed after SET and CASE were the same. I realize that there are other methods achieve the same result, but this is the least verbose I've found.

UPDATE Database.[dbo].[Table]
SET Value =
CASE Path
    WHEN '\Folder1' THEN '0'
    WHEN '\Folder2' THEN 'xyz'
    WHEN '\Folder3' THEN '0'
    ELSE Value
END

If Value column doesn't change, then for each row only latest else of your case is true. Syntax itself is correct, and it should work as you want. I'd first do SELECT to double check that constants in CASE correspond to what you have in the table:

SELECT DISTINCT
CASE Path
WHEN '\Folder1' THEN '0'
WHEN '\Folder2' THEN 'xyz'
WHEN '\Folder3' THEN '0'
END as val
FROM Database.[dbo].[Table]

It should return up to 3 rows ('0','xyz', and NULL). If it returns 1 row only(NULL), then you don't have records where the value of Path column equals to any of '\Folder1','\Folder2','\Folder3'.

Note : the syntax of CASE you use compares value of Path column with constants using =. You might have wanted to use another logic, for instance LIKE. Then use different form of CASE :

SELECT DISTINCT
CASE
WHEN Path LIKE '\Folder1%' OR Path LIKE '\Folder3%' THEN '0'
WHEN Path LIKE '\Folder2%' THEN 'xyz'
END as val
FROM Database.[dbo].[Table]


Select a row from one table, if it doesn't exist, select from another table How can I select a row from one table, but if it doesn't exist, fall back to selecting it from a secondary table? Here's a dumbed down version of what I'm trying to achieve
create table table_1 (
  person TEXT,
  favourite_number INT
  );

  create table table_2 (
    person TEXT,
    favourite_number INT
  );

  insert into table_1 (person, favourite_number) values
  ('Bob', 1),
  ('Fred', 2)
  ;

    insert into table_2 (person, favourite_number) values
  ('Bob', 30),
  ('Alice', 70)
  ;

I want to get the following result:
| person | favourite_number |
|--------|------------------|
| Bob    | 1                |
| Alice  | 70               |
| Fred   | 2                |

Notice how it picks up Bob and Fred from the first table. Even though Bob appears in the second, because we've already got him we take him from the first table. Alice only appears in the second table.
This is what I've tried so far, but I can't quite get all 3 returning back, please help.
select
  t1.*
from table_1 t1
where t1.person not in (select person from table_2)
union
select
  t2.*
from table_2 t2
where t2.person not in (select person from table_1)
;

If it is preferable to select the rows from the first table, you should take out the filter that would remove them when the person exists in the other.
There’s also no need to distinct the rows, so use union all instead of union
select
  t1.*
from table_1 t1
union all
select
  t2.*
from table_2 t2
where t2.person not in (select person from table_1)


PowerShell Pipeline Binding examples on SQL server? I am learning PowerShell, my training machine (VM) is a Windows Server 2012 R2, with SQL 2014 and PS 4.0.

I am try get a handle on the two Pipeline Binding methods ByValue & ByPropertyName.  I am a hands on learner so I like to try things and play with them a bit to get the real feel of how they are doing what.

Some of the examples I am seeing are sub optimal for playing with

Get-Process | Stop-Process (**really bad idea, don't try this at home or work**)

Or they are focused on things not SQL (like IE)

I know how to read Get-Help x-x -Full looking for a couple of SQL centered pipelines I can play with that won't crash my server (nor be resume producing events).

Getting specific to SQL Server would require there be any cmdlets within SQLPS that actually use the ByValue or ByPropertyName binding. I only know of a few like those for the Backup or Restore, but don't really use them. I think the ones around Azure SQL might use them as well (e.g. Get-SqlInstance). So in the end this is really nothing to do with SQL Server itself, just the way PowerShell works...but still worth learning about I think.

The best way for you to see how they work is just use Trace-Command to look at the binding metadata as PowerShell does it. A simple example would be piping something to a cmdlet like Get-Date or Get-Service.

I am by no means an internal's guy when it comes to PowerShell. Up front from what I can find, ByValue is not actually used or maybe just has a lower precedence than ByPropertyName. If you look at use of this command on my local laptop:

Trace-Command -Name ParameterBinding -Expression {'mssql$number14' | Get-Service | Restart-Service} -PSHost

The output of this command shows a few things:

  DEBUG: ParameterBinding Information: 0 : BIND PIPELINE object to
  parameters: [Get-Service] DEBUG: ParameterBinding Information: 0 :
  PIPELINE object TYPE = [System.String]  DEBUG: ParameterBinding
  Information: 0 :     RESTORING pipeline parameter's original values
  DEBUG: ParameterBinding Information: 0 :     Parameter [Name] PIPELINE
  INPUT ValueFromPipeline NO COERCION  DEBUG: ParameterBinding
  Information: 0 :     BIND arg [mssql$number14] to parameter [Name]
  DEBUG: ParameterBinding Information: 0 :  Binding collection parameter
  Name: argument type [String], parameter type [System.String[]],
  collection type Array, element type [System.String], no
  coerceElementType

The one thing I get form this is that the ValueFromPipeline was not forced, PowerShell went with the ValueByPropertyName and bound the text string I passed in to the Name property.

If you want to see the full output of this:


Create an incremental number with year and month without updating the entire table using a trigger My situation and code

I'll add a number to my MS SQL Server table. It must be an unique number beginning with the year and month of the time when it's created, followed by an incremental index number. Something like this 2019.08.0001, 2019.08.0002, 2019.08.0003 etc. The ID I'll keep as internal PK for references from other tables.

For this I use this code.

create table Things (
    ID int primary key identity not null,
    Number nvarchar(20),
    Text nvarchar(max),
    CreationDate datetime not null
);

create trigger UpdateThingsNumberTrigger on Things
after insert
as
begin

    declare @month nvarchar(2);
    select @month = cast(month(getdate()) as nvarchar(2));

    declare @code nvarchar(15);
    select @code = cast(year(getdate()) as nvarchar(4)) +
        '.' +
        replicate('0', 2 - len(@month)) +
        @month +
        '.';

    declare @max nvarchar(20);
    select @max = t.ID
    from Things t
    where ID like @code + '%';

    with CTE_UPD as
    (
        select
            replicate('0',
                4 -
                len(cast(coalesce(cast(right(@max, 4) as int), 0) + row_number() over (order by ins.ID) as nvarchar(4)))) +
                cast(coalesce(cast(right(@max, 4) as int), 0) + row_number() over (order by ins.ID) as nvarchar(4)) as NextNo,
            ID
        from Things ins
    )
    update Things
    set Number = @code + NextNo
    from Things t inner join CTE_UPD ins on ins.ID = t.ID;

end;

insert into Things (Text, CreationDate)
values ('A', current_timestamp), ('B', current_timestamp), ('C', current_timestamp);

select *
from Things;

-- Output:
-- ID | Number       | Text | CreationDate
-- -- | ------------ | ---- | ----------------------
-- 1  | 2019.08.0001 | A    | 2019-08-23 08:54:15.157
-- 2  | 2019.08.0002 | B    | 2019-08-23 08:54:15.157
-- 3  | 2019.08.0003 | C    | 2019-08-23 08:54:15.157

But my trigger has a major logical flaw that has multiple effects… It never references the inserted virtual table. That means I'm updating the entire table every time when I insert a new row.

Effects of the logical flaw

When deleting a thing and inserting a new one

When I delete the row with ID 2, then the ID's after it, will change too.

delete Things
where ID = 2;

insert into Things (Text, CreationDate)
values ('D', current_timestamp);

select *
from Things;

-- Output:
-- ID | Number       | Text | CreationDate
-- -- | ------------ | ---- | -----------------------
-- 1  | 2019.08.0001 | A    | 2019-08-23 08:54:15.157
-- 3  | 2019.08.0002 | C    | 2019-08-23 08:54:15.157
-- 4  | 2019.08.0003 | D    | 2019-08-23 09:31:27.568

This isn't what I want to have. When ID 2 is removed, and a 4th is added, the numbers of things after ID 2 may not be changed. So the 4th thing must have number 2019.08.0004. Number 2019.08.0002 must be skipped. Like on table below.

ID | Number       | Text | CreationDate
-- | ------------ | ---- | -----------------------
1  | 2019.08.0001 | A    | 2019-08-23 08:54:15.157
3  | 2019.08.0003 | C    | 2019-08-23 08:54:15.157
4  | 2019.08.0004 | D    | 2019-08-23 09:31:27.568

When it's another month

Also when it's another month, all the things will get an other number when inserting a new thing. Then, the output will be this, the expected results on the last column:

ID | Number       | Text | CreationDate            | ExpectedNumber
-- | ------------ | ---- | ----------------------- | --------------
1  | 2019.09.0001 | A    | 2019-08-23 08:54:15.157 | 2019.08.0001
3  | 2019.09.0002 | C    | 2019-08-23 08:54:15.157 | 2019.08.0003
4  | 2019.09.0003 | D    | 2019-08-23 09:31:27.568 | 2019.08.0004
5  | 2019.09.0004 | E    | 2019-09-01 10:34:54.059 | 2019.09.0001

Question

How could I solve the logical flaw inside my trigger so that it has no effect to the existing things in my table?

  Note: The flaw was shown on this question for Stack Overflow.

Schematically (no syntax):

CREATE TRIGGER UpdateThingsNumberTrigger
ON Things
AFTER INSERT
AS
WITH cte AS ( SELECT LEFT(Number, 8) yyyymm, CAST(RIGHT(MAX(Number),4) AS INT) nnnn
              FROM Things
              GROUP BY LEFT(Number, 8)
            )
UPDATE Things
LEFT JOIN cte ON cte.yyyymm = YEAR(CreationDate)
                            + '.'
                            + MONTH(CreationDate)
                            + '.'
SET Number = YEAR(CreationDate)
           + '.'
           + MONTH(CreationDate)
           + '.'
           + RIGHT('0000' + CAST((  COALESCE(nnnn, 0)
                                  + ROW_NUMBER() OVER (GROUP BY YEAR(CreationDate)
                                                              + '.'
                                                              + MONTH(CreationDate)
                                                              + '.'
                                                       ORDER BY CreationDate) AS CHAR), 4)
WHERE Number IS NULL

Logic:

We update the whole table, but only those records which have no value in Number field (WHERE Number IS NULL).
In cte we obtain maximal current number for each year-month in the table divided to prefix (yyyymm) and number within group (nnnn).
For each group of records to be updated (1) we calculate new number part - we enumerate those records and add this number to nnnn part, then format in with leading zeros. If it is new year-month, no nnnn for it, and we set it to 0, so we start number part from 1.

PS. It is possible when the last record in the group by year-month was deleted - in such case deleted Number value will be re-assigned. I do not see the way to determine does this value existed previously and it was deleted, or it was not existed.


Master and slave tables with different character sets - will replication break? We have some huge tables in our MySQL 5.6 database that have latin-1 as their character set, and want to convert them all to UTF-8 (along with converting the database's global character set). The necessary ALTER TABLE statements take, between them, about an hour to run. We'd like to minimise our downtime.

Our plan is to set up replication, make the encoding changes on the slave database, let the databases sync up, and then briefly take down our webserver and point it at the slave database.

Will this work, or is there a risk of it causing data corruption in the slave?

My concern about this stems from there being two obvious possible approaches for how string values (that are being INSERTed or UPDATEed) might be stored in MySQL's binlog and sent across to the slave. Either...

The values are stored as unicode character sequences (i.e. the binlog knows what encoding they are encoded in sends that information to the slave so that it can convert the values to match the character set of the target column on the slave.)

or...
The encoded values are just stored as byte sequences, with no record of what the character set was.

The documentation doesn't shed any light on which of the above models MySQL uses.

If #1 is true, then our plan will work fine.

However, if #2 is true, then trying to replicate queries from the master to the slave is going to go wrong in some way if one of the columns involved has a different character set. For instance, once we've converted our tables from latin-1 to UTF-8 on the slave, the slave might try to store latin-1-encoded strings passed across from the master in a UTF-8-encoded column, resulting in either the stored text not being validly encoded in UTF-8 or representing the wrong characters.

The MySQL docs on Replication and Character Sets shed little light on the problem; they discuss potential issues resulting from differences in the global character set of each server, but don't touch on potential problems caused by differences in table or column character sets at all.

Is our plan sound, or are we at risk of ending up with corrupt data on the slave?

https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html describes the supported variations in table definitions.

For one, it appears that this is supported with statement-based replication:

  When using statement-based replication, a simple rule of thumb to follow is, “If the statement run on the master would also execute successfully on the slave, it should also replicate successfully”.

With row-based replication, only certain type conversions are supported, which are affected by the slave_type_conversions variable.  The key quotes according to my reading are:

  [Conversions are supported between] any of the string types CHAR, VARCHAR, and TEXT, including conversions between different widths.

However:

  [Row-based] Replication between columns using different character sets is not supported.


How to remove the duplicate records from primary filegroup after partitioning? Basically, our database is created based on primary filegroup alone.

Now, I have done partitioning for a large table which was approx 25 GB. The partitioning was done on secondary filegroups which has data files located at different drives for performance optimization. Now when I look into the database it looks like that the primary filegroup size have not been decreased.

FYI, I have partitioned an existing table based on index.

It looks like that the data has been copied records from my primary filegroup to secondary filegroups instead of moving the records.

Is there any fix for this or does the partition increases the database size ?

The table size looks to be doubled in size after partition and is that going to enhance performance occupying a lot of memory ?

Can I remove the duplicating records from primary filegroups after it has been moved to secondary filegroups ?

Once SQL Server acquires disk space from the operating system and allocates it to a database it will not voluntarily give it back.  It takes time and effort to allocate this space so it is more efficient for the database to hang onto it once it has acquired it.  If you genuinely have moved the data and not copied it (you would have to post your SQL for us to be sure) there will be a lot of free space in the files of the PRIMARY filegroup. Have a look at this post for hints how to find the free space in a file.

To force SQL Server to hand back this free space to the operating system you will have to uses a SHRINK command.  This is well documented in Books Online.  There are potential performance problems with SHRINK, so use it after proper consideration.


In which cases is a full table scan preferable to an index scan with low cardinality? Are there real-world scenarios where to perform a FTS is preferable to scanning an index with very low cardinality? Why would MySQL do so?

Doesn't an index scan always involve reading less data (pages)?

Let's imagine a large table, and the index is essentially the rows numbered.  And we want every 50th row via id IN (50,100,150,200, ...).  Further, let's say that there are about 100 rows per block.

If you do a table scan, it will read all the blocks.  That scan will read a block, pick up 2 rows, move on to the next block, etc.  By the time it finishes, it will have read each block once.  If you use the index, it will bounce between the index and the data; this is slightly worse.  Still, one block will yield 2 rows before moving on to the next block.

Now, let's change the example a little.  Let's say that the rows are ordered by something else.  Now 50 and 100 are very likely to be in different blocks, etc.  Further, let's say that the table is at least twice as big as the cache.

With this case, using the index will (usually) grab one block, extract one row from it, leave the block cached.  But, before the other row in that block is requested, the block may get bumped from cache.  That is, all the blocks will be read twice.  The table scan would be twice as fast because it would need read the blocks only once, essentially as in the first case.

(Using UUIDs, GUIDs, MD5s, or SHA1s as the PRIMARY KEY is a simple way to create this problem.)


ORA-01031: insufficient privileges, cannot login as any user I ran below command and restarted docker container.
alter system set processes = 1 scope = spfile;

after this I am not able to login to DB at all.
root@30b2f9030f89:/u01/app/oracle/product/11.2.0/xe/bin# sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 5 14:59:14 2020

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges

SQL>

I tried to resolve it with below command, tried login again, still getting same error
orapwd file=filename password=password entries=100

Also tried editting /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora file
and manually assign processes=100 and back to processes=1
this didnt help.
can someone guide here please?

Based on your comments, I have to assume that you are new to Oracle Database.

alter system set processes = 1 ...

It takes lots of processes to run an Oracle Database.
I very rarely hear of anyone reducing this parameter - usually we're having to ramp it up to cope with increasing workload.

orapwd file=filename password=password entries=100

The Oracle Password File controls who can log into the database using Operating System authentication rather than a local, database password.
It should have as few entries in it as possible (I don't think I've ever exceeded five) and then only those that *administer" the database and are trusted to know what they're doing.

Also tried editting /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora file

No, no, no.  Just ...   No.
You should consider most, if not all, of the files used / managed by Oracle Database to be in a proprietary format that you absolutely must not try to "edit".
This is Oracle database, not Microsoft's "ODBC Text" Driver.
The PFile is one of the few a text files that you can edit but their use is exceptional these days - most database instances run from an SPFile - intended for situations like this where you have to "fix" things on the fly.


Split database for security and privacy reasons Currently I have a small business where I have a main product (written in PHP and MySql), several users, several companies, etc.

The software occupies around 20 tables in its operation. The problem is that I want to expand the business and create a different program, but I want to unify the data.

A new program (different function) will use about 30 tables, but in reality there is a "core" that are the same tables, let's say 10.

Here I have two options:

Create a larger database with
20 + 30 tables, (since some were repeated) = 40 tables.
Or create different and connected databases

DB_core = 10 tables
   DB_1 = 10 tables
   DB_2 = 20 tables

Why using different databases?
I already want to develop this second software with a different team, but I don't want to give them access to some sensitive or valuable information, tables of people, companies, etc. This data will keep it in the "core" database and only in the production server.

From what I understand, users can be created with permissions by tables, but I feel that it is much easier to keep us in separate databases. Are there any significant performance implications? Is the most extensive programming worth it?

PD: Sometimes, I need the programmers team manage the production server.

A logical separation by databases is a sensible move.

There is no performance impacts of this compared to table separation.

Programming becomes referencing by database.table rather than just table.

Eventually you'll need to raise the table cache sizes in your server.

Be careful with permissions and provide guidance for your programmers. Too often they open up permissions to try to make things work.


"No Loop" Constraint for hierarchical data in postgresql I've got table like this in postgres:

id primary key
parent_id foreign key on id
some data columns

I need no a constrain which prevents cycles on the parent relations. There might be more tree in the table (root of a tree is null in parent_id).

How to achieve this goal?

Thanks

Daniel

You can't express the constraint that an edge-list must be acyclic as a CHECK constraint.

It would have to be an ASSERTION constraint, which PostgreSQL does not support.

You'll have to LOCK TABLE to prevent concurrent INSERTs or UPDATEs and check it with an AFTER trigger.

The resulting lock upgrade issues will result in frequent deadlocks. To prevent them your app will have to lock the table before inserting/updating, e.g.

BEGIN;
LOCK TABLE my_table IN EXCLUSIVE MODE;
INSERT INTO my_table (...) VALUES (...);
-- Trigger runs to sanity check table here
COMMIT;

You might find it useful to make the trigger a deferred constraint trigger, so the table is allowed to temporarily enter invalid states so long as it's valid again by the time you commit.


See the definition of a cursor From doing select * from pg_stat_activity;, I can see that some process is using a cursor.  The query column look like this:

FETCH FORWARD 50000 FROM "db"

Is there any way to see the SQL that was used to define "db"?

Use the pg_catalogs.pg_cursors view.

postgres=# BEGIN;
BEGIN
postgres=# DECLARE "db" CURSOR FOR SELECT * FROM book_lines;
DECLARE CURSOR
postgres=# SELECT * FROM pg_cursors ;
 name |                     statement                     | is_holdable | is_binary | is_scrollable |         creation_time
------+---------------------------------------------------+-------------+-----------+---------------+-------------------------------
 db   | DECLARE "db" CURSOR FOR SELECT * FROM book_lines; | f           | f         | t             | 2016-03-05 21:17:01.565456+08
(1 row)

Note that it shows the whole statement defining the cursor, not just the query that underlies the cursor. I don't know of any way to get just the cursor's query, so you'd probably have to do text processing to get that.


Create unique sequence per set of rows sharing the same foreign key value In my system, I want each user to have an own id sequence for his articles.

So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the outline of such table:

    +------+----------+---------+
    | id   | article  | user_id |
    +------+----------+---------+
    | 1    |some notes| 1       |
    +------+----------+---------+
    | 2    |some notes| 1       |
    +------+----------+---------+
    | 3    |some notes| 2       |
    +------+----------+---------+
    | 4    |some notes| 2       |
    +------+----------+---------+
    | 5    |some notes| 3       |
    +------+----------+---------+

But I want to generate the ids in this table in the following way:

    +------+----------+---------+
    | id   | article  | user_id |
    +------+----------+---------+
    | 1    |some notes| 1       |
    +------+----------+---------+
    | 2    |some notes| 1       |
    +------+----------+---------+
    | 1    |some notes| 2       |
    +------+----------+---------+
    | 2    |some notes| 2       |
    +------+----------+---------+
    | 3    |some notes| 2       |
    +------+----------+---------+
    | 1    |some notes| 3       |
    +------+----------+---------+

So, when a new user is being created in the associated user table, an id sequence for his articles will start from 1.

Is there any generally accepted approach to solve this problem?

This has been asked many times. The long and the short of it: Don't try it. You'd run into all kinds of problems with concurrency, gaps introduced by deleting rows, etc. Make id a plain serial column and create a VIEW to display a running number per user_id.

CREATE VIEW v_user_article AS
SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY id) AS per_user_id
FROM   user_article;

A column article with 'some notes' hardly makes sense for your setup. And ordering by it makes even less sense, since the numbers would change with every new article that does not happen to sort last. To get a stable sort order and stable numbers (except for deletes!) use the serial column to determine the sort order.

Details:

Serial numbers per group of rows for compound key


Does this simple database design work even if it's cyclic? I am a graduate student working on breast cancer in a veterinarian facility. I am designing a database to keep track of the health of my mouse colony.

Each day, animal care staff verify each cage for health problems. If they find something suspicious (e.g. tumour, skin ulceration), they file a health report for the veterinarian to check. Each report can have multiple observations.

Then, the vet decides whether or not it is a real case. If it is real, the vet will create a clinical case number with open/close dates. If not, they won't open a case. However, I want to keep track of these "false positives" for further study (at this point, a primary key can be automatically created).

Here is my tentative database design:

Please correct me if I am wrong, but I have created two transition tables (red) to respect the first nominal form for the observations (i.e. only one value per entry)?

Finally, I have not seen any database design like mine where the tables are connected into a circle, so I am worried that I have made an error.

I would greatly appreciate the community's feedback. Thank you!

So, just to clarify:

You want 1 Report to have Many Observations. The Observations then become either a Case or NoCase. Now, each case/nocase is linked by the ObservationID and the ReportID (or the Observation itself belongs to a ReportID), right?

It wouldn't be a circle: Report->Observations->Case/NoCase (I can see the Case and NoCase entities as one specialization).


Duplicate Same Table for Multiple locations We have an "appointments" table with following schema.

appointments(app_id,.....,app_type,location_id)

app_type - type of appointment
location_id - keeping the location for multiple location setup.

We are using MySQL and expect about 20,000-50,000 rows inserted per day around 365 days.

The expected number of operations = number of appointments * 5

For each operation in our app, we'll read this appointment table to get particular appointment details and update certain flags in the same table.

SPEED is our main concern.
My question is, performance wise is it OK to

leave this schema as it is (large number of rows and many reads across it), or
keep separate appointments tables for locations?

You are talking about less than 5 queries per second.  A well maintained database can handle 100 qps or more.

A poorly handled db will melt down even with 5 qps.

It is rarely a good idea to split a table artificially.

It is usually a good idea to normalize repeated data.  But don't normalize 'continuous' data, such as numbers, dates, floats, etc.

What will the SELECTs look like?  And SHOW CREATE TABLE.  Then we can discuss what indexes to have, and whether you have under/over-normalized, etc.


SQL Server Management Studio: how to parse stored procedure code and check if table columns exist? I'm afraid that SSMS won't parse table columns within a stored procedure.

When you click in Parse option

SSMS says that parse is completed, even when a procedure is selecting a column that does not exists in a table.

When you run your script, SSMS shows what the Parse should warn about:

Is there a way to parse a script in order to check if table fields really exist before creating or altering procedures?

I don't think there's any way to do this with a parse, but you could try running the script inside a transaction then rolling back that transaction at the end. If there's an error, it should throw it, but even if there isn't, there won't be any lasting changes.


ROR application with Mysql 5.6 - transaction-isolation with Master -Slave We are running a ROR application with Mysql 5.6 and master/slave replication. We are facing deadlock issues.

We've successfully tested changing settings from REPEATABLE-READ to READ-COMMITTED on our test servers (which are standalone; not master/slave) which has eliminated the deadlock problem:

transaction-isolation = READ-COMMITTED

When we added the same in the my.cnf file on both master and slave, not only does the replication fail but the application also fails to load.

What would be the recommended setting to accommodate this change. Is there any alternative ?

Let's look at it in a different way...

Are the transactions running longer than a second?  If so, rethink them.
Do you test for errors and rerun a transaction if there is a Deadlock?

If it is not too big, let's see the transaction and the SHOW CREATE TABLE.


Memcached plugin on MariaDB? I'd like to try new NoSQL feature in MySQL 5.6 but I am using MariaDB 10.0.2 on Debian 6 and don't fancy coming back to MySQL.

I'm wondering whether the memcached plugin has been added to MariaDB? And if not whether one can still use it as an addon?

And if not, can I use the existing Cassandra plugin of MariaDB to the same effect?

I'm wondering whether the memcached plugin has been added to MariaDB?

Not yet.  But slide 24 of mariadb-10-and-whats-new-with-the-project mentions:

 Plan is to have all important features of MySQL 5.6 by the time
 MariaDB 10.1.x is released as stable - For all practical purposes
 MariaDB 10.1.x will be a drop in replacement to MySQL 5.6"

https://kb.askmonty.org/en/what-is-mariadb-100/

New Features Backported from MySQL 5.6

New InnoDB — from MySQL 5.6.5

The thought being that the InnoDB Memcached Plugin would come along with InnoDB from 5.6.  Also see https://mariadb.com/blog/mariadb-10-vs-mysql-56-feature-comparison-update

https://jira.mariadb.org/browse/MDEV-4674 is a request for it
  https://github.com/MariaDB/server/pull/126 is a patch

The source is https://github.com/MariaDB/server , this is where it would be added.

Update, add link to related issue: https://jira.mariadb.org/browse/MDEV-12050 - "Remove unused InnoDB Memcached hooks from MariaDB Server"