What to check in SSMS:

Right click on the database in question, click "Properties", then choose the "Files" tab.

Note all the files for the database, and the drives they are on. Note which files have auto-growth turned on, and (of those) which have a maximum file size.

Switch to the "Options" tab.

Check the recovery model (upper right corner). Is it "Full", "Simple", or "Bulk-logged"?

Repeat the first step for the system database tempdb.

Using Windows/File Explorer, check how much space is available on each of the drives you noted above.

Why you're having problems:

The index rebuild process takes up extra space in either your database itself, or in tempdb (depending on the settings for the index when it was created). In your case, it seems save to say the rebuild uses your database, not tempdb. Once the index is completely rebuilt, this space is released as available again; that's why it looks like you have free space in your data file, but you run out during the rebuild process.

Note that the database rebuild writes data to the transaction log, tracking the changes it's making, in case they have to be rolled back. The precise behavior of the transaction log depends on the recovery model of your database.

Databases using the "Simple" recovery model need to keep the details of every transaction around until the transaction is completed. Once the transaction is completed, the log space can be released to be reused. So, you can run out of space in your transaction log during a transaction, but have almost no space in use when you look at the log ten minutes later.

Databases using the "Full" recovery model (or "Bulk-logged") keep transaction information around until the transaction log is backed up. This means that a database in "Full", even with a regular full database backup, but with no transaction log backups scheduled, will eventually fill up, as transactions that completed months or even years ago must be kept in the log.

The main reason to use the "Full" recovery model is to allow for "point-in-time" recovery. You can combine your full backups with the string of transaction log backups to recover the database up to any point in time from when the full backup was taken, up to when the most recent transaction log backup was taken.

If you want the option to restore to a point more recent than your last full backup, then use the "Full" recovery model, and schedule transaction log backups to happen often enough that you don't mind losing the data that wasn't backed up. If you don't need transaction log backups (and you're sure about it), then put your DB into the "Simple" model.

A bit of advice: if you're using "Full" but haven't been backing up your transaction logs, you have the option of taking that first transaction log backup, if you have enough space to hold it. If you don't, you can basically "start over" by:

Shifting the recovery model to "Simple". This will mark all completed transactions as being OK to release. You can use the T-SQL CHECKPOINT command to ensure that the space is released immediately.
Switch back to "Full"
Immediately take a full backup of the database
Immediately after that, make sure your regular transaction log backup is set up.

This is one of the few scenarios where it actually makes sense to shrink your transaction log. If it's very large, try cutting it in half. Ideally, you don't want to shrink the file so small that it has to grow for normal operations. If you're looking for a good target size, I've heard the recommendation that you size the transaction log to twice the size of your largest index.

As noted, an index rebuild does write data to the transaction log. So, if you're using the "Full" model, and you do have transaction log backups running, you may want to run them more often when the rebuild is happening. I did this on one of my DBs a few years back - my usual schedule for transaction log backups was every 15 minutes, but I bumped that up to every 5 minutes during the rebuild process.

So, if all of that's set up fine, check to see if your drives are actually full, or if your files have an upper growth limit (or don't grow). If you still have drive space, then you can:

Manually increase the size of the file (on the afore-mentioned "Files" tab);
If auto-growth is not allowed, allow it (you can set a maximum if you like); or
If auto-growth is allowed, but a maximum is set, then increase the maximum (or remove it).

If you don't have any free disk space, and your transaction log is properly sized - then you need more disk space.


Can I check the services currently running through SSMS?

You can do this with CLR or PowerShell. From PowerShell you can use things like Get-Process or Get-Service for some of this information. With C# you could look at this answer but I don't know of any ready-made code for SQLCLR. Note that with PowerShell you'll need to run the command in the context of the remote computer, e.g.

Invoke-Command -ComputerName RemoteComputerName { Get-Service }

// or if your version of PS is modern enough:

Get-Service -ComputerName RemoteComputerName

You can also use xp_cmdshell to call things like tasklist, net start, wmic or 3rd-party things like Process Explorer (though I haven't tried to invoke that via command line):

EXEC master..xp_cmdshell 'tasklist';
EXEC master..xp_cmdshell 'net start';
EXEC master..xp_cmdshell 'wmic service get';

The latter has much more valuable information, but the output is absolutely horrible to work with.

Of course this all assumes that you and/or the SQL Server service account has sufficient privileges to call these functions.

(Adopted from my answer to a similar question on SO.)


Will Changing the Max Memory Setting Invalidate Query Plans?

Per Microsoft documentation changing the max server memory will clear the plan cache, as will changing:

access check cache bucket count
access check cache quota
clr enabled
cost threshold for parallelism
cross db ownership chaining
index create memory
max degree of parallelism
max server memory
max text repl size
max worker threads
min memory per query
min server memory
query governor cost limit
query wait
remote query timeout
user options


There are two kinds of foreign keys:  declared foreign keys and undeclared foreign keys.  Most people are familiar with declared foreign keys, but some don't realize that there is such a thing as an undeclared foreign key.

There are several advantages to declaring a foreign key.  The big advantage is rejecting invalid data at input time.  For interactive data entry, this is very valuable.

In your case, however, it's a detriment.  You probably want to store all the data you are given, even if some of it appears to be dirty data.  Later, you can go through some kind of cleansing operation where you detect certain kinds of errors, like orphaned references, and flag them for human intervention.

You would use this field in join conditions precisely the way you use foreign keys.  This is what is meant by an undeclared foreign key.  If there are any orphaned references, they will drop out of the join, and you need to program accordingly.

In a more formal situation, like a data warehouse, incoming data is loaded into a staging table at first, and only added to the warehouse data after cleansing.  That's probably overkill in your case.


First let's do a quick review of how SQL Server generates the default ordering of columns when you do a SELECT * query. There are probably a few edge cases but I believe that columns are returned in the order that they are created. If a column is dropped the ordinal ID associated with that column is not used and cannot be used by a new column.

Consider this sample table:

CREATE TABLE dbo.SEE_COLUMN_ORDER (
COL1 INT,
COL2 INT,
COL3 INT,
COL4 INT
);

We can see the column_id using this query against sys.columns:

SELECT name, column_id
FROM sys.columns
WHERE object_id = OBJECT_ID('SEE_COLUMN_ORDER');

Initial results:

╔══════╦═══════════╗
║ name ║ column_id ║
╠══════╬═══════════╣
║ COL1 ║         1 ║
║ COL2 ║         2 ║
║ COL3 ║         3 ║
║ COL4 ║         4 ║
╚══════╩═══════════╝

Now drop a column:

ALTER TABLE dbo.SEE_COLUMN_ORDER DROP COLUMN COL3;

New results:

╔══════╦═══════════╗
║ name ║ column_id ║
╠══════╬═══════════╣
║ COL1 ║         1 ║
║ COL2 ║         2 ║
║ COL4 ║         4 ║
╚══════╩═══════════╝

Now add a column:

ALTER TABLE dbo.SEE_COLUMN_ORDER ADD COL3 INT;

New results:

╔══════╦═══════════╗
║ name ║ column_id ║
╠══════╬═══════════╣
║ COL1 ║         1 ║
║ COL2 ║         2 ║
║ COL4 ║         4 ║
║ COL3 ║         5 ║
╚══════╩═══════════╝

There is no column with a column_id of 3. As far as I can tell there is no known or supported way of making COL3 have a column_id of 3. If you wanted that column to be displayed third in a SELECT * query you could define a view against the table with the name and a different schema or you could drop and recreate the entire table with the column order that you want.

Recreating an entire table sounds like a slow operation but sometimes it can be quicker than updating all of the rows for a single column. It'll depend on your system, your table structure, and the data in your table. One scenario in which the drop and create might be faster is if your transaction log writes are the bottleneck. With a recovery model of simple you can create the new table with minimal logging which will write very little data to the transaction log compared to the UPDATE. Another scenario in which the drop and recreate can be faster is if the UPDATE will lead to a lot of page splits. It's possible to construct a table and an UPDATE which splits every data page into two (an UPDATE to a column value making it NULL won't do this as far as I know). As a general rule of thumb, the same amount of data is saved to the log regardless of the recovery model unless the operation can be minimally logged. UPDATE is never minimally logged so switching to simple won't reduce the transaction log requirements for that operation.

To cover the part of the question that deals with performance, one important thing to note is that adding and dropping a column to an existing table are optimized operations that have a fixed cost that doesn't scale with the amount of data in the table. To see this in action I'm going to look at how much data is logged in the transaction log for the operations (dropping and adding columns can be rolled back in a transaction). I'm testing against SQL Server 2016.

Here is the sample data:

DROP TABLE IF EXISTS dbo.X_COLUMN_WIPE_2;
CREATE TABLE dbo.X_COLUMN_WIPE_2 (
    ID INT NOT NULL IDENTITY (1, 1),
    COL_TO_WIPE VARCHAR(1) NULL,
    FILLER VARCHAR(100) NULL,
    PRIMARY KEY (ID)
);

-- 2536 rows
INSERT INTO dbo.X_COLUMN_WIPE_2 WITH (TABLOCK)
SELECT 'A', REPLICATE('Z', 100)
FROM master..spt_values t1;

Per sys.dm_tran_database_transactions, the UPDATE writes 324752 log bytes to the transaction log:

BEGIN TRANSACTION

UPDATE dbo.X_COLUMN_WIPE_2 SET COL_TO_WIPE = NULL;

ROLLBACK;

Dropping and adding a column writes just 1992 log bytes to the log:

BEGIN TRANSACTION

ALTER TABLE dbo.X_COLUMN_WIPE_2 DROP COLUMN COL_TO_WIPE;

ALTER TABLE dbo.X_COLUMN_WIPE_2 ADD NEW_COLUMN VARCHAR(1) NULL;

ROLLBACK;

Now to test with more data:

DROP TABLE IF EXISTS dbo.X_COLUMN_WIPE_3;

CREATE TABLE dbo.X_COLUMN_WIPE_3 (
    ID INT NOT NULL IDENTITY (1, 1),
    COL_TO_WIPE VARCHAR(1) NULL,
    FILLER VARCHAR(100) NULL,
    PRIMARY KEY (ID)
);

-- 6431296 rows
INSERT INTO dbo.X_COLUMN_WIPE_3 WITH (TABLOCK)
SELECT 'A', REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

The same UPDATE now writes 721979808 bytes to the transaction log but dropping and creating a column still writes just 1992 bytes.

It's possible to dig into the internals of why this happens using the undocumented DBCC PAGE. Here's an example from when I ran the code on my system (copying and pasting the code won't work because the page numbers will be different):

DROP TABLE IF EXISTS dbo.X_COLUMN_WIPE_4;
CREATE TABLE dbo.X_COLUMN_WIPE_4 (
    ID INT NOT NULL IDENTITY (1, 1),
    COL_TO_WIPE VARCHAR(1) NULL,
    FILLER VARCHAR(100) NULL,
    PRIMARY KEY (ID)
);

INSERT INTO dbo.X_COLUMN_WIPE_4 WITH (TABLOCK)
SELECT 'A', REPLICATE('Z', 100)
FROM master..spt_values t1;

-- first first data page
DBCC IND('SE_DB',X_COLUMN_WIPE_4,-1)

-- view first data page
DBCC TRACEON(3604)
DBCC PAGE('SE_DB',1,1192232,3);

BEGIN TRANSACTION

UPDATE dbo.X_COLUMN_WIPE_4 SET COL_TO_WIPE = NULL;

DBCC PAGE('SE_DB',1,1192232,3);

ROLLBACK;

As Paul White would say I'm quite the amateur when it comes to this stuff, but I'll give my interpretation anyway. Here's a diff of what's in the page for the first record in the table before and after the update:

I underlined what I suspect are the important pages in red. Note that many of the physical lengths have changed and that the value "A" for COL_TO_WIPE is no longer present in the row data. It looks like the UPDATE changed much of the data stored in the page.

Here's a diff between the original table and after the COL_TO_WIPE column is dropped:

The number of differences is much smaller than before. None of the row data is changed. I believe that there is just a metadata operation performed on the table outside of the table pages, so all of the changes displayed by DBCC PAGE here are logical as opposed to physical.

Here's a diff between the original table and after the column is dropped and a new column is added:

As before there don't really appear to be any physical differences. The actual data looks to be the exact same. There are just column definitions with a physical length of 0.

Since the problem here appears to be related to how the date is stored row by row in pages, perhaps the results would be different for tables created as clustered columnstore indexes which are stored in column format. After all, it is reasonable to think that updating a column value should only affect the one column instead of all of the data in the table. Unfortunately for this case, the current implementation of UPDATE against CCIs is a logical delete and insert. So if you update all of the rows for a column to be NULL, SQL Server will mark all of the existing rows in the table as logically deleted and will create new rowgroups for all of the rows.

In summary, other than recreating the whole table you've already mentioned all of the "clever" ways in the question that I know of for working around your problem. It's very unlikely that there is an "instant" way to update a column to be NULL in the middle of the table without breaking the previous column order in the table.


How to insert values to a table from 2 foreign keys

You have two issues:

A typo in your GradeInfo table definition that should be a syntax error, but unfortunately sqlite is all too often too lenient about what it accepts:

FOREIGN KEY (Student_ID) REFERENCES StudentInfo (Student_ID)
FOREIGN KEY (CourseNo) REFERENCES CourseInfo (CourseNo),

Notice the lack of comma. (EDIT: This actually works even though it shouldn't! Bug in Sqlite?)
The primary key on CourseInfo is (CourseNo, Year), but your foreign key only references CourseNo. You have multiple rows in CourseInfo with the same value for CourseNo. Which one is supposed to be used as the parent to the foreign key? Add tracking the year too to the table and it works:

  CREATE TABLE GradeInfo (                    /*Created 3rd*/
    Student_ID NUMERIC AUTO_INCREMENT,
    CourseNo Text,
    Year Numeric,
    Pts NUMERIC,
    Grade TEXT,
    FOREIGN KEY (Student_ID) REFERENCES StudentInfo (Student_ID),
    FOREIGN KEY (CourseNo, Year) REFERENCES CourseInfo (CourseNo, Year),
    PRIMARY KEY (Student_ID, CourseNo, Year)
    );
INSERT INTO GradeInfo(Student_ID, CourseNo, Year, Pts, Grade)
VALUES(7007, 'INFO151', 2018, 15, 'A+');

Also, there's the double quote vs single quote issue that sticky bit mentioned already, and AUTO_INCREMENT is not a sqlite keyword - you should read this documentation about auto increment and how to use it (If you really need it; you usually don't; an INTEGER PRIMARY KEY is enough).


Creating a Windows database backup service

It seems you are trying to roll you own incremental backup script. The short answer is:

Don't.

There are so many things to consider when doing backups and even more for incremental backups that it's very difficult to be done right.
Using a simple INSERT IGNORE (which ignores many errors, not only duplicate key errors) is not going to work and give you incremental backups.

Also note that a database has a few more things besides tables. There are view definitions and events that you may want to backup as well. And that tables are created and deleted and changed (columns added, dropped, modified, triggers added or dropped, etc).

Plus a backup has to be a consistent snapshot of the database, so whatever a script does has to be either a single transaction or while the database is not modified by any other session.

I suggest you start by reading the MySQL documentation on:

Backup and Recovery

It explains the various backup types and methods that can be used. There is no solution that covers all situations so the answer for your specific case depends on what exactly is your objective (normal backups, incremental backups) and many other details like the version, edition, the tables' engine (MyISAM, InnoDB, NBD, etc.), whether the database will be accessed during the backups or not, etc.

You might find that a simple mysqldump of the 5 databases is all you need.


How to attach .mdf file to SQL Server 2005 without an .ldf file?

E.g like this:

CREATE DATABASE AdventureWorks2012 ON (FILENAME = '{drive}:\{file path}\AdventureWorks2012_Data.mdf') FOR ATTACH_REBUILD_LOG;


How to connect each SQL instance to get SQL server version updated into the temp table?

Just use dbatools - Get-DbaCmsRegServerGroup

  Gets list of Server Groups objects stored in SQL Server Central Management Server (CMS).

If you have multiple servers to connect, you can use foreach to loop through each servers and store the result into a variable.

You can even write to a database using ConvertTo-DbaDataTable and Write-DbaDataTable


How do you hide tool tips in MySQL Workbench?

According to Philip Olson on the mysql.com bug system, this is "fixed as of the upcoming MySQL Workbench 6.1.6 release".


Is compression used in RDS SQLServer database

After you have loaded the data into a table, and as long as you have signed up for a 2016 Standard Edition or above, you can use the Data Compression that is included within Sql Server:

CREATE TABLE t1
(
    ... -- whatever columns you need.
)
WITH
(
    DATA_COMPRESSION=PAGE
);

You can also create compressed indexes in a similar fashion:

CREATE INDEX idx_t1 ON t1(...) WITH (DATA_COMPRESSION=PAGE);

For more information, check out the documentation for Enabling Compression on a a Table or Index.


SQL Server Backup failing for just 1 DB ("The system cannot find the path specified.")

Ok i finally figured out the issue. It was completely random that it happened on the ReportServerTempDB, and i think that's what threw me off.
My daily maintenance runs at 3AM and includes the backups as above. I also have 30min log backups happening 24/7, also with the specified 'Y' drive and deleting it.

The Log backup was running during the normal backup, and deleting the 'Y' drive from the full backup, therefore killing the full backup as the location now no longer exists.
I have added some logic in, so that the Log backups do not remove the drive between 3AM and 6AM and that's solved the issue.
Thank you all for the assistance though.


Is it worth to separate columns into multiple tables for one-to-one relational table

Another thing to consider in deciding to split the tables or not is the width of the table if you put them all in one table. Many databases will allow you to define a table where the total length of all the fields is wider than the total record length allowed. You cannot however, put data into a record that would exceed the width. Therefore, if you are going over the limit, it might be wise to split the table. Further, most databases can store and retrieve data more efficiently in tables that are less wide. So if the fields you want to split out are not always going to be queried with the fields in the orginal table and the table will be large, it might be in your best interests to split the tables out.

Another thing to consider is if you are in a 1-1 relationship now, is it likely or possible that you will want to change to 1-many later? For instance suppose the fields you want to move are out are address fields. Maybe now you only need one address, but having them in a separate table makes it much easier later to have multiple addresses.

If you split the tables, make sure you define a unique index on the FK to preserve the one-to-one relationship.


MySQL PDO Cannot assign requested address

You could also be hitting max open files (sockets) under Linux. You can change this in /etc/security/limits.conf.


How to change results cell padding in Azure Data Studio

maybe this helps:

https://github.com/real-napster/azuredatastudio_settings

...
sqlops_settings

My Azure Data Studio Settings

Settings
{
    "sql.messagesDefaultOpen": false,
    "mssql.intelliSense.lowerCaseSuggestions": true,
    "resultsGrid.fontSize": 12,
    "resultsGrid.fontFamily": "Microsoft Sans Serif",
    "editor.fontFamily": "Fira Code, 'Courier New', monospace",
    "editor.fontSize": 14,
    "editor.minimap.enabled": true,
    "editor.minimap.maxColumn": 120,
    "resultsGrid.cellPadding": [
        5,   !!!
        10,  !!!
        15   !!!
    ],
    "editor.cursorBlinking": "phase",
    "editor.smoothScrolling": true,
    "workbench.colorTheme": "One Dark Pro",
    "workbench.iconTheme": "vscode-icons",
    "files.autoGuessEncoding": true,
    "window.titleBarStyle": "custom",
    "editor.smoothScrolling": true,
    "editor.cursorBlinking": "smooth",
    "workbench.enablePreviewFeatures": true
}

marked the Padding entries using !!!


Here's an option that uses ROW_NUMBER to create a join based on the order of the ID column from each table.  Naturally, this assumes an equal number of rows in each table.

--demo setup
Declare @T1 table (id int)
Declare @T2 table (id int)

insert into @T1(id) values(1),(2)
insert into @T2(id) values(4),(5)

--solution
select a.id, b.id from
(
select id, ROW_NUMBER() over(order by id) as rn from @T1
) a
join
(
select id, ROW_NUMBER() over(order by id) as rn from @T2
) b
on b.rn = a.rn

id  id
1   4
2   5


The reason for the truncation is quite simple. Some characters (accented ones, for example) in the WE8ISO8859P1 character set are stored as a single byte, but in AL32UTF8 they end up being stored as multiple bytes. As a result of conversion, a 4000 character string may end up actually requiring more than 4000 bytes.

By way of example, this query shows you that the Euro symbol (0x80 in WE8ISO8859P1) becomes 2 bytes in AL32UTF8:

SQL>  select length(convert(chr(128),'AL32UTF8','WE8ISO8859P1')) from dual
  2  /
LENGTH(CONVERT(CHR(128),'AL32UTF8','WE8ISO8859P1'))
---------------------------------------------------
                                                  2
SQL>

To list all characters that will be affected by the change, you can use the following query:

with n as
(
select level as c from dual
connect by level <= 255
)
select c as "WE8ISO8859P1 value",
       '"'||chr(c)||'"' as Character,
       length(convert(chr(c),'AL32UTF8','WE8ISO8859P1')) as "New length"
from n
where length(convert(chr(c),'AL32UTF8','WE8ISO8859P1'))>1;

Unfortunately the maximum length a CHAR or VARCHAR string can be in Oracle is 4000 bytes. The only option available to you if characterset conversion pushes you over this limit is to convert the columns to use the CLOB datatype, but we warned - CLOBs are difficult to deal with and can present challenges.


There is no way to do that. The only panes available in the main window are listed in the menu under View: Object browser, SQL pane, Tool bar.

You can ...

... select a passage in the query tool, then hit F5 to execute just the selection. This way you can have various queries on a page for ad-hoc calls.

... Copy SQL from the main window to query tool automatically with this option.


Execution plan issue requires reset on SQL Server 2005, how to determine cause?

Probably Parameter Sniffing. To troubleshoot this without running Profiler

1) Add a GUID into the text of the query as a comment. e.g.

/*ED7446E4-03B1-4279-AB43-6FFE800AD860*/

2) Wait for the problem to recur so that it runs slow from the application and fine from SSMS.

3) Compare the query plans for the 2 different environments (retrievable from the below)

SELECT *
FROM   sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
       CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
       CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE  t.text LIKE '%ED7446E4-03B1-4279-AB43-6FFE800AD860%'
       AND epa.attribute = 'set_options'


After lowering maxmemory, redis still has high RSS memory

I had the same problem and this answer was helpful.

  even though Redis isn’t using the memory anymore, the allocator just hasn’t released the memory back to the OS yet. Reds v4 has a MEMORY PURGE command to tell the allocator to release the memory it isn’t using, but unfortunately that isn’t available to you on v3.2.


When using blitzindex should are the reads the number of times the index was used to read or is that the op stats?

The "Usage Stats" column is from sys.dm_db_index_usage_stats.

The "Operational Stats" column is from sys.dm_db_index_operational_stats.

I won't rehash the Books Online documentation for each DMV here, but the short story is that they both measure how many times the index has been used - they just measure it in different ways.


Domain Admin Account Accessing SQL Server

You could use the following query to provide some quick details about the domain admin:

SELECT des.session_id
    , des.host_name
    , des.login_time
    , des.is_user_process
    , des.last_request_start_time
    , dest.text
FROM sys.dm_exec_sessions des
    INNER JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id
    CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
WHERE des.login_name = N'DOMAIN\AccountName'

It'll show the name of the client machine where they are connecting from, and the last statement they executed.

In general, you probably want to explicitly control who has access to the SQL Server, especially for security-sensitive accounts such as members of the sysadmin and securityadmin server roles.  The principle of least privilege applies.

This query will show you the members of each server-level role:

SELECT spr.name
    , spm.name
FROM sys.server_principals spr
    INNER JOIN sys.server_role_members srm ON spr.principal_id = srm.role_principal_id
    INNER JOIN sys.server_principals spm ON srm.member_principal_id = spm.principal_id
ORDER BY spr.name
    , spm.name;

In general, this list should be as small as possible.  Pay particular attention to the securityadmin and sysadmin roles.

As an aside, you want to limit the number of people who have access to the Domain Admins AD group since members of that group could restart your SQL Server in such a way that they can gain access to it, even if they haven't been explicitly granted access.  There are a lot of security implications to be aware of domain-wide for highly privileged groups such as the Domain Admins.


I have account_id on all of the tables in the database that belong to an account. Could this be why our database is using a lot of memory?

Welcome.

There can not be a best and perfect database design (always). It all depends upon your (project) use case, coding simplicity and expandability.

Any best database design will have its own (exclusive) disadvantages too.

Nothing negative about your described design (until you listed - though not all of your ~50 tables). But you shall think of account as a single entity where in the record details goes into the account table itself. Referring

account.first_name, account.address1 account.city

looks good unless your account holders have multiple addresses.


Retire a SQL instance without impacting the primary server?

The goal is to release as many resource to the VM pool, as practical, pending the full server retirement.

You can just turn off sql server service of the instance that you are retiring and change the startup mode to disable.

Also, make sure to allocate the memory to other instance that is running form the decommissioned instance.

In a maintenance window, you can go ahead and uninstall SQL Server instance that you offlined.


As others have said this is known as a PIVOT. There are several ways in which you can transform your data from rows into columns of data.

If you know the values ahead of time, then you can hard-code the values. Prior to the PIVOT function you would use an aggregate function with a CASE statement.

Aggregate/CASE Version:

select sitecode,
  sum(case when [month] = 'Jan' then amount else 0 end) MonthJan,
  sum(case when [month] = 'Apr' then amount else 0 end) MonthApr
from yourtable
group by sitecode;

See SQL Fiddle with Demo.

The PIVOT function was made available in SQL Server 2005, so if you are using that version or a newer one then you can apply that to your data.

Static PIVOT:

select *
from
(
  select sitecode,
    [month],
    amount
  from yourtable
) src
pivot
(
  sum(amount)
  for month in (Jan, Apr)
) piv;

See SQL Fiddle with Demo

The above two versions work great if you know the values ahead of time. If not, then you will use dynamic sql to create the result.

Dynamic PIVOT:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month)
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT sitecode,' + @cols + ' from
             (
                select sitecode, [month], amount
                from yourtable
            ) x
            pivot
            (
                sum(amount)
                for month in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

All 3 versions of this will return the same result:

| SITECODE |  JAN |  APR |
--------------------------
|       XX | 4000 | 4000 |


Can I get an equivalent query for this Not in Query

You don't select those ids that are minimum in their systemno group so you select only those that have a lower id in their group:

SELECT id FROM dbo.SomeTable st1
WHERE EXISTS
  (
    SELECT *
    FROM dbo.SomeTable st2
    WHERE st1.SystemNo = st2.SystemNo AND st2.id < st1.i
  )


What are the factors to consider or to worry when we migrate to SQL Server 2017 from 2012

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017 as a list of deprecated features for 2017.
2012 to 2017 yes. 2017 to 2012 no.
No, because you can't restore from 2017 to 2012.

The main thing to look at with your current database is what Compatibility Level is it set to in the 2012 instance? If it's below 110, and needs to stay below 110, then it won't function correctly, or at all.

The only way you'll be able to definitively determine what won't work is install a copy of SQL Server 2017 somewhere (with the appropriate licensing), restore your database to that new instance, and then start testing your application connecting to that new database.


The primary server must retain all transactions until the secondary restores them and acknowledges to the primary.
You must constantly monitor both the primary and secondary servers to avoid this situation.


How Uniqueidentifier in SQL Server is always a unique value globally?

Problem #1

  As per Microsoft documentation on UniqueIdentifier, This value is always a unique globally because it's based on network clock and CPU clock time... (emphasis added)

The main problem here is that you are confusing two different things as being two terms that refer to one thing: UNIQUEIDENTIFIER and GUIDs.

UNIQUEIDENTIFIER is a datatype. Datatypes define the nature of the data that they (i.e. columns and variables of this type) can contain (e.g. min / max values, etc) and certain behaviors of the data (e.g. how to handle comparisons). This particular datatype merely holds GUID / UUID values. But it is not data, so the concept of uniqueness does not apply to it. And the word "Unique" in the name "UniqueIdentifier" is not a promise, or even statement, regarding actual uniqueness.
GUIDs / UUIDs are actual values that can be stored as UNIQUEIDENTIFIER, but could also be stored as VARBINARY / BINARY, (N)VARCHAR / (N)CHAR, and maybe some others. While the UNIQUEIDENTIFIER datatype is the best choice (in SQL Server) for storing these values, storing the values in the other types does not make the values any more or less unique.

Problem #2

  I'm not able to come to a conclusion how UniqueIdentifier (GUIDs) can be unique globally, as Network address (Mac address) can be same on two different networks

The second problem here is that you are accepting, as fact, a technical error in the documentation that you linked to. I assume you are referring to this statement:

  A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value.

That statement is referring to functions like NEWID() in T-SQL and Guid.NewGuid() in .NET that create new GUID / UUID values, and the intention of them to always generate unique values. However, that is not reality: newly generated GUIDs are not guaranteed to be unique. As you already pointed out, MAC Addresses aren't necessarily unique (they can even be spoofed; more info in the "Related info" section below). Also, from other Microsoft documentation:

MSDN page for .NET Guid Structure states (emphasis added):

  A GUID is a 128-bit integer (16 bytes) that can be used across all computers and networks wherever a unique identifier is required. Such an identifier has a very low probability of being duplicated.

The .NET Guid.NewGuid() method (which is used to generate new GUID / UUID values) calls Win32Native.CoCreateGuid. The documentation for that function states (emphasis added):

  To a very high degree of certainty, this function returns a unique value – no other invocation, on the same or any other system (networked or not), should return the same value.

Please note that the non-SQL Server documentation doesn't even mention MAC Address. And the documentation for CoCreateGuid points to the real function that does the generation: UuidCreate. The documentation for that function states:

  For security reasons, it is often desirable to keep ethernet addresses on networks from becoming available outside a company or organization. The UuidCreate function generates a UUID that cannot be traced to the ethernet address of the computer on which it was generated. It also cannot be associated with other UUIDs created on the same computer. If you do not need this level of security, your application can use the UuidCreateSequential function, which behaves exactly as the UuidCreate function does on all other versions of the operating system.

The implication here is that MAC Address is specifically not used (unless using NEWSEQUENTIALID()). And in fact, generating a few GUIDs in SQL Server via NEWID() indicates that they are RFC 4122, Version 4 UUIDs, which are extremely likely to be unique. There is a chart here, Random UUID probability of duplicates, that shows just how unlikely it is to have duplicates. However, even a very, very low probability of duplicates is not a guarantee of uniqueness.

And so...

There is no guarantee that newly generated GUID / UUID values are unique. And, even if there was a guarantee, the UNIQUEIDENTIFIER datatype would still have nothing to do with actual uniqueness (as is shown in Brent's answer). Uniqueness, for one or more columns (i.e. data, not datatypes) can only be enforced by Unique Indexes / Constraints.

Related info:

How is the Uniqueness of MAC Addresses Enforced?
Are MAC addresses really unique?
MAC address
Globally unique identifier


Configuring physical storage for a Microsoft SQL Server installation

First and foremost, look at your SAN vendor's documentation for recommendations regarding storage for SQL Server.  This really should be your first step as the vendor hopefully has done a lot of this general analysis for you.

If the documentation doesn't mention hosting databases, do some more digging before you choose to go with Tiered Storage.  Understand how data migration between tiers works on your SAN and how frequently it occurs.  Beware of anything that will run a scheduled job that tracks how active sectors may be during a time frame (often a 24 hour period, but this if often configurable).  I've found that in this scenario when it comes time to migrate the data, that data is often slow to access during the operation.  You'll want to insure this process runs during a period of low db activity (which means don't run this during the same time you're backing up your database).  If you have a database that's active all day, I'd recommend you NOT use tiered storage at all if this is how tiering works on your SAN.  Another problem with migrating data between tiers when this type of analysis is performed is that often times data access patterns change and are not consistent throughout the day.  This can result in data living on the faster tiers that really shouldn't be there.  For instance, when you run Index Maintenance overnight the SAN may flag that data as hot and migrate it to a higher tier.  If those indexes don't get accessed during the next migration analysis window, you're now wasting I/Os on idle data.  Depending on your database usage patterns, this could happen quite often where your data is getting migrated to the faster tier only to sit there idle.

Again, look at your vendor documentation.  I would hope they clearly outline the recommended approach needed for their hardware.  Also, give SAN Storage Best Practices for SQL Server, from Brent Ozar a read.  That goes into much more depth on some best practices and is well worth the read.


SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc

Usually you'd do weekly or daily depending on your usage and maintenance windows.

You pretty much never shrink, especially not scheduled.

For rebuild/reorganise and statistics there are scripts (such as SQL Fool's one) that do a better job.
Note: reorganise and rebuild are not exclusive but work different ways. 3rd party scripts can choose the best option (based on fragmentation).

We use weekly index/DBCC and daily statistics. And never shrink.

From Simple-talk: Don't Forget to Maintain Your Indexes


Pass the unix variable to the ddl command inside a pl/sql block

Instead of the string literal VAR1 and VAR2, use the variables in your EXECUTE IMMEDIATE statement:

execute immediate 'CREATE DATABASE LINK  try_lnk CONNECT TO ' || VAR1 || ' IDENTIFIED BY ' || VAR1 || ' USING ''' || VAR2 || '''';


How to avoid log space error while executing SSIS packages?

There may be nothing you can do except allow your log file to grow further. Without the exact message we can't be sure, but there are two reasons you might be runing out of log space:

You log file is not set to a large enoguh size (or not set to auto-grow), in which case you need adjust these properties.
The partition your log file is stored on is full so even if you have log growth enabled it has no-where to grow too. In this case you need more drive space.

Everything your package does will go via the log file: this is an important part of how SQL Server manges transactions in order to keep your data consistent and it can not simply be switched off or temporarily moved. You might find that your package is taking more fully logged action than it needs to in order to acheive the goal. You give no detail of the process your package follows so we can only give general guidance:

Could the same rows be being modified many times in the process? If so you could try to merge these steps. UPDATE table1 SET fiedl1=1 WHERE ID=1; UPDATE table1 SET fiedl2=2 WHERE ID=1; is two row updates that will be logged where UPDATE table1 SET fiedl1=1, fiedl2=2 WHERE ID=1; is one. Over millions of rows this could stack up quite quickly. Likewise if you insert rows and later update them in the process.
Are you refreshing a table completely by wiping it down and refilling it? In which case use TRUNCATE TABLE <tablename> instead of DELETE <tablename>. TRUNCATE is a minimally logged operation so will use less log space (and be much faster) then wiping the table by DELETEing every row.
Better still: if you are deleteing a bunch of rows then replacing them, are many of the replacement rows identical to the ones you initially delete? If so you could update your logic to only UPDATE rows that need updating, INSERT rows that are missing and DELETE rows that are superfluous. For instance one of our apps gets a daily update feed from a client that contains state from another application: this is hundreds of thousands of rows but only a few hundred are added/changed/deleted each day so it is a lot more efficient to detect which few hundred need action instead refreshing everything.


How would I change this relational model to fit a nosql database?

"Is this realistic to do with something like MongoDB?"

If "realistic" means can be engineered to produce acceptable performance then the answer depends on whether you will do with appropriate benchmarking before putting it into production.  You can replace "MongoDB" in your question with anything from Access to Oracle and the answer remains basically the same.

The MongoDB documentation has some good information on considerations for data modeling and performance.  I would start there and lean towards a "collection" per each of your original source tables.

The important step is to benchmark, benchmark and benchmark.  I would approach this from the standpoint of proving that MongoDB can't handle the workload. There are three possible outcomes:

You prove that it won't work. Maybe it's due to technical limitations or it would require excessive HW to meet your SLA. If they proceed you can't be held responsible ("I told you so").
You fail prove that it won't work and after it's in production it doesn't work then at least you tried.  Everyone is surprised and there is a big scramble for an alternate solution.
You fail to prove that it won't work and it works great. You will sleep good at night feeling confident in the solution forced down your throat.

Start by benchmarking the "critical paths". If you can't get MongoDB to handle the incoming click traffic then it's "case closed". Start with an aggressive goal, maybe 300% of what business users project traffic will be. Figure out how many documents/sec you can load with your initial schema.  Is it reasonable?  Change the schema and benchmark again. Is it better or worse?  How does it scale with additional HW?  Try scaling UP and OUT.  Try multiple clients performing inserts. Next look at the updates.  Can you meet your performance goals?

Lastly look at reporting.  Reporting performance is typically an easy problem that is solved by additional hardware.  If the reports are ran every day or so then you should have some leeway in expected performance.

Conclusion

There is nothing special about MongoDB compared to the evaluation of any other database technology.  Start with technical requirements and benchmark, benchmark, benchmark.


Finding earliest connected value over two columns

It's a recursive problem by nature. SQL introduced recursive CTEs for that purpose quite some time ago. Since Redshift does not seem to support this, your only remaining option to solve this within the database is a user defined function (UDF). It seems like the only server-side language supported by Redshift is plpythonu.

Here is an old answer with a PL/pgSQL function and an rCTE implementing the same solution side-by-side. But it's using the default procedural language of Postgres: PL/pgSQL:

Loop in function does not work as expected

No idea why Redshift chose not to support PL/pgSQL.


join for between multiple tables without common fields

Which make of SQL are you using?  I am most familiar with Microsoft SQL Server. Syntactically, you need to move the WHERE after all of the JOIN statements.  (I appended the tablename L to its references for consistency.)

select * from L
left outer join ER on ER.childid=L.id
left outer join E on E.id=ER.parentid
left outer join M on M.id=ER.parentid
where L.xpos > 5

This is legal syntax.  The next question is what results you want.  I think possibly so, although you did not show a sample of results.  Perhaps you can play with the data a bit to decide what you need.

CREATE table E (id INT, title varchar(50), [datetime] DateTime);
CREATE table L (id INT, place varchar(50), xpos float, ypos float);
CREATE table M (id INT, title varchar(50), [datetime] DateTime, contact varchar(50), agenda varchar(50));
CREATE table ER (parentid INT, childid INT, parenttype INT, childtype INT);

INSERT INTO L VALUES (1, 'Boston',123.234, 23.234);
INSERT INTO L VALUES (3, 'Miami', 456.234, 78.234);
INSERT INTO ER VALUES (100,1, 5, 4);
INSERT INTO ER VALUES (200,2, 5, 4);
INSERT INTO ER VALUES (200,3, 5, 4);
INSERT INTO ER VALUES (100,4, 5, 4);
INSERT INTO E VALUES (100, 'Able','2014/01/02');
INSERT INTO E VALUES (2, 'Baker','2014/01/11');
INSERT INTO M VALUES (100, 'Love', '2014/04/04', 'George', 'None');
INSERT INTO M VALUES (200, 'Fury', '2014/05/05', 'Bernard', 'Something big');

SELECT * FROM L
    LEFT OUTER JOIN ER on ER.childid = L.id
    LEFT OUTER JOIN E on E.id= ER.parentid
    LEFT OUTER JOIN M on M.id= ER.parentid
    WHERE L.xpos > 5

DROP TABLE E
DROP TABLE L
DROP TABLE M
DROP TABLE ER


What indexes do I need to define for my bridging tables?

By default PRIMARY KEY is already a clustered indexed. It will also speed up queries executed against first column in an index, but not the other.

So if you index is defined on user_id, role_id (in that order), index will work also for user_id.

In your situation I'd go with default index of compound key, then, if needed, take a closer look at long running queries and benchmark if separate index on role_id is necessary.


MySQL query show times in milliseconds, not seconds?

For a test/check session you can enable the Slow Query Log and setting the sysvar long_query_time to 0

  The minimum and default values of long_query_time are 0 and 10,
  respectively.  The value can be specified to a resolution of
  microseconds.  For logging to a file, times are written including the
  microseconds part.  For logging to tables, only integer times are
  written; the microseconds part is ignored


How to find minimal cover when attributes have equal chance to be not necessary?

In a functional dependency, both the left part and the right part are sets, so the order of the attributes does not matter.

To find the canonical (or minimal) cover of a set of functional dependencies, the classical algorithm (presented in almost all books on databases) consists of three steps:

First, rewrite the dependencies so that they have a single attribute on the right part (for instance, t,w,g -> r,e is replaced by t,w,g -> r and t,w,g -> e).
Then, for each attribute on the left part, see if the closure of the remaining attributes includes the right attribute. In this case the attribute is not necessary and should be eliminated.
Finally, eliminate the superfluous dependencies, if present. A dependency is superfluous if its right part is contained in the closure of the left part, calculated with respect to all the other dependencies.

For instance, in your example, a possible minimal cover can be computed in this way.

At the end of the first step, we have:

{ e → w
  e r w → s
  g t w → e
  g t w → r
  g → s
  g → t
  r s → g
  r s → t
  s w → r }

At the end of the second step:

{ e r → s
  e → w
  g → t
  g → s
  g w → r
  g w → e
  r s → t
  r s → g
  s w → r }

Finally, a minimal cover is:

{ e → w
  e r → s
  g w → e
  g → s
  g → t
  r s → g
  s w → r }


How to make a flexible service broker receive in a stored sql procedure, how to pass "FROM" parameter into receive

Because RECEIVE is basically a DELETE and as such has a query plan, it must obey the same restrictions SELECT/INSERT/DELETE/UPDATE statements have, specifically the restrictions that the object it acts on must be known at compile time, not at execution time.

The only option is to use dynamic SQL, with all the blessings and pitfalls that follow.

You could also generate the procedure body during project deployment, have a single template and generate a specific procedure for each queue, specialized for the specific queue name. Whether this is feasible depends on many factors, first and foremost on the organization of your project and how you deploy.

On a side note I'm surprised to hear that you have many queues. In general the tendency is to have a single queue and several queue readers (activated procedures). Since SSB programing is event driven (wait for for message, process message, wait for message, process message, wait for message...) having more than one queue to wait for messages on it becomes harder, as the application now has to wait on multiple sources (eg. one thread per queue at least). Even with SSB activation, which alleviates the need for explicit wait wince it launches the code to process the message on-demand, multiple queues are harder to manage (max_queue_readers per queue adds up to perhaps too many internal activated procedures launched). Consider using a single service and queue on the RECEIVE side. Even when multiple services are needed (for whatever reason) they can be consolidated on a single queue.


When creating a database using an ERD, what do the entities and relationships become (in database terms)?

When implementing an ERD as physical database the following mappings apply:

An entity becomes a table (set of rows)
A relationship becomes a foreign key.
Associative (many to many) relationships become a table with a foreign key to each associated table.

Generally the ERD shoud be in at least third normal form.

A few notes about relationships:

The (zero or) one side of the relationship is implemented as the  primary key of the table.
The many side of the relationship is usually implemented with a foreign key index.
If the site opposite the many side range is zero to one, the foreign keys column(s) are nullable.
If the side opposite the many side range is one to one, the foreign key columns are not nullable.
If the side opposite the many side range is also zero or one to many, then a relationship table must be implemented.

Many ERD tools can be used to create the tables and indexes; or the DDL (Data Definition Language) to do so.  They may also be able to create an ERD from a database.  The best tools can update an existing database to match a modified ERD, or generate the DDL to modify the database.


The single quote character has no special significance to LIKE

You need to double them up to escape them inside any string literal.

So the two consecutive single quotes in the string you are trying not to match become four as below.

And textdata not like '%@ListNoteTypeCode=N''''%'


This module does not (currently) support prepared statements functionality MySql Todo Github


Answer is yes. :)
CREATE OR REPLACE FUNCTION create_table_type1(t_name varchar(30))
  RETURNS VOID
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('
      CREATE TABLE IF NOT EXISTS %I (
       id serial PRIMARY KEY,
       customerid int,
       daterecorded date,
       value double precision
      )', 't_' || t_name);
END
$func$;

I am using format() with %I to sanitize the table name and avoid SQL injection. Requires PostgreSQL 9.1 or above.
Be sure to use single quotes ('') for data. Double quotes ("") are for identifiers in SQL.


After Inserting , Update / Insert into another table in pl/sql trigger

Instead of materializing the count, which bears the risk of inconsistencies, I recommend you drop the my_tags table and create a view which uses aggregation to get the count.

DROP TABLE my_tags;

CREATE VIEW my_tags
AS
SELECT sr_no,
       tag,
       brand_id,
       count(*)
       FROM tag
       GROUP BY sr_no,
                tag,
                brand_id;

Like that you don't need any trigger, the count is always accurately calculated.


Storing a music playlist, best approach?

I would approach it with this structure:

This way you can have a more normalized structure, non-repeating data and simple tables.

In this method you would also be using WHERE PlaylistID=[bobs playlist id] instead.

With this structure's simplicity, you wont have to worry about any size issues for a long time. This structure will quickly handle millions of records. If you get bigger than that you can look into partitioning; that's not necessary for now.


Do the SQL Server track all the queries run so that the DMVs can access the information?

I would suggest taking a look at a free e-book Red Gate published here:
Performance Tuning with SQL Server Dynamic Management Views. It is free and a good read. It will point out those DMVs that provide cumulative data versus point-in-time (what is currently in the buffer). As long as the server has not been rebooted you are in good shape to capture some good information.

  What other sql snippets would be helpful to diagnose the problem?

You can also use sp_whoisactive that will provide much more information if you can run it during the hours noted. Returns CPU, Memory, and I/O usage of each query. You can even have it pull the execution plan of the offending query as well.

Do a basic Bing/Google search for SQL Server DMV and high CPU queries or something will likely bring up some other good scripts/blog post on researching your particular situation.

Just a note on your comment regarding moving the scheduled reports, I would also look at ad-hoc reports on those that users may run on their own. Especially if you are running SSRS on the same server as the database engine. I usually take a look at the ExecutionLog table in the SSRS database (default name ReportServer). There is a wealth of information in there on execution time and rendering time of the reports.


pgAdmin is the most recommended GUI tool for querying PostgreSQL: https://www.pgadmin.org/
Here's a list of other recommendations too: https://scalegrid.io/blog/which-is-the-best-postgresql-gui-2019-comparison/#:~:text=pgAdmin%20is%20the%20de%20facto,and%20developers%20for%20database%20administration.


Find all instances where a user has been explicitly granted permissions on an object

Check the sys.database_permissions DMV, which contains all granted and denied permissions.

The major_id will point to the object_id in sys.objects, the minor_id points to the column_id in sys.columns (if applicable).

The grantee_principal_id and grantor_principal_id columns point to the granted and granting user ID respectively, which you can find in sys.database_principals.

Remember that permissions can be assigned not just to users, but also to server or database roles, which the user can inherit. Also, granted and denied permissions can conflict, in which case the more restrictive of the two rules applies.

I wrote a blog series a while back, which goes into more details on permissions.


How can I make an Async Slave track all of the nodes in a Percona XtraDB Cluster?

You can get rid of the pager, but not if you expect >= 50% failure.

PXC (Galera), spread across 3 geographic locations, is an excellent HA configuration.

Optionally, one of them can be a "garbd" -- light weight, no data -- node only for providing a "quorum".

If any one server goes down, the other two declare that they have a quorum and continue running.

If the network goes down, and 2 servers are split from the 3rd, then the 2 declare quorum and the 1 gracefully fades away.

"Normally" you need to plan for a single point of failure.  That is what the above provides.

If you really need to survive (without a midnight page) two failures, then you need 5 (or more) nodes in the cluster -- and place them in 5 geographic locations.  (Else a tornado, earthquake, etc, could take out multiple nodes.)

I am unclear on what you mean by a "Slave tracking all nodes".  Perhaps you want to set up two 3-node clusters with async replication from one to the other?  That is possible, and it has some degree of failover.

Some people even put the 3 nodes in a single location at one end; the the other 3 at a single location at the other end.  This gives them most of the HA, but without the dreaded latency you may experience between the 3+ nodes in a WAN cluster.

Depending on where your clients are, and which node each client prefers to talk to, a WAN Cluster may be both faster and more reliable than any non-Cluster configuration.  (That is another discussion.)

As for "all but one node can go down" -- that is [probably] a theoretical impossibility, especially when you consider that "split brain" is one of the failure cases.  The Cluster depends on discovering which subset of more than 50% of the nodes are still talking to each other.


Looping with array of values in PL/pgSQL

can we use return query within this loop ...?

You can use RETURN QUERY:

CREATE OR REPLACE FUNCTION f_pivot_arrays()
  RETURNS TABLE(question text, response_id int, response text) AS
$func$
DECLARE
   QuestionList         text[] := '{Who?,When?,WHY?}';
   UserResponseID_List  int[]  := '{1,2,3}';
   UserResponseList     text[] := '{Here.,Now.,Because.}';
BEGIN
   FOR i IN 1 .. array_upper(UserResponseList, 1)
   LOOP
      RETURN QUERY SELECT QuestionList[i], UserResponseID_List[i], UserResponseList[i];
   END LOOP;
END
$func$  LANGUAGE plpgsql STABLE;

Or RETURN NEXT:

...
   FOR i IN 1 .. array_upper(UserResponseList, 1)
   LOOP
      SELECT QuestionList[i], UserResponseID_List[i], UserResponseList[i]
      INTO   question,        response_id,            response;
      RETURN NEXT;
   END LOOP;
...

But since Postgres 9.4, the simpler tool to unnest arrays in parallel is unnest():

   RETURN QUERY
   SELECT *
   FROM   unnest(QuestionList, UserResponseID_List, UserResponseList);

dbfiddle here

Related:

How do you declare a set-returning-function to only be allowed in the FROM clause?


Does daily pg_dump mess up postgres cache?

If you don't want to have backups, then sure, disable them.  Of course, then you won't have backups.  We cannot tell you if your data is valuable to you or not.  But why would you pay someone to host data you don't care about?  Maybe you can just turn them off for a day, and see if that actually fixes the problem.
I don't think that pg_dump does anything special that should drive data out of the cache.  It does access all of your table data, but PostgreSQL server has code to prevent a sequential scan of a large table from driving all other data out of cache.  This should be just as effective for pg_dump as it is for anything else.  But if you have a very large number of small tables, the cumulative effect of reading all of them might be enough to drive everything else out of cache, as I think the antispoliation code applies to each table individually, not all of them collectively.
pg_prewarm is certainly an option.  Another would be a cron job that just runs the query in question every morning.


Generally speaking with relational databases, 1000 single-row inserts will be slower than a single 1000-row insert. Batching up your data in memory and loading to the database may be faster.

As you receive data into memory, you build a queue. Then, you pop the top item from your queue and insert it into the database. Instead, pop the top N items and insert them all in a single INSERT statement. The ideal value for N isn't universal, so you'll have to experiment. Try starting at 1000, 10000, and going from there based on the throughput results.

I've also seen (and supported) architectures where high volume data is streamed to a file, then asynchronously loaded to the DB. This helps ensure that your receiving service never runs out of memory and crashes (thus losing data in memory), but it does add some complexity, and doesn't guarantee that you'll always load data faster than it comes in. Rather, it just separates it into a separate problem to solve. In this configuration, your receiving service would write data to files until they are N MB/rows in size. Then, you roll over into the next file. (Personally, I like datetime stamps on file names to preserve file order.) Asynchronously, a separate service picks up the less-than-most-recent files and loads them to the database, (using COPY or whatever Python library you find for that).


ALTER TABLE AUTO_INCREMENT number in a Trigger

In MySQL you can insert directly into AUTO_INCREMENT Column. So, take this example from MySQL manual:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Let's try this:

TRUNCATE TABLE animals;

CREATE TRIGGER increment_insert BEFORE INSERT ON animals
  FOR EACH ROW
    SET NEW.id = (SELECT MAX(id) + 2 FROM animals);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Which returns:

| ID |    NAME |
|----|---------|
|  1 |     dog |
|  3 |     cat |
|  5 | penguin |
|  7 |     lax |
|  9 |   whale |
| 11 | ostrich |

(BTW, it is not STEP of 3, it is STEP of 2 you are after)

SQL Fiddle


Do decimal separators behave differently for Money and Decimal data types?

The ToString() implementation in the SqlDecimal type in System.Data.Types contains this as the final step in the algorithm:

while (i > 0)
{
    if (i-- == (int)this.m_bScale)
    {
        array3[num2++] = '.';
    }
    array3[num2++] = array2[i];
}
return new string(array3);

Notice there is a hardcoded '.' in there? SqlMoney does not have this as it calls a specific format which uses the current cultureinfo.


I'd say that as long as you can guarantee that you (or anyone else) won't change the definitions of the '3','5','6','2' values somewhere down the road, it's not a bad idea.

You'll end up having to do a little more work with the first option to get it into a "User-friendly" view (if that's your end goal), but it seems like INT values (old_value, new_value) would be easier to store than full text values (message).  The main advantage to your second option is that it's easier to read and you don't have to link a few tables together to get the user-friendly version.

Another way I've seen it done (Particularly by Salesforce) is to select which fields you'd like tracked in an object.  Say "status" and "assistant" are your choices.  Salesforce will have a structure as follows:

id | ModifiedDate | OldValues                     | New Values
1  | 08/26/2016   | Status: Open, Assistant: John | Status: Closed, Assistant: Kyle

This is if at least one value changed.  So even if the assistant hadn't changed, it still displays all field values.

  id | ModifiedDate | OldValues                     | New Values
  1  | 08/26/2016   | Status: Open, Assistant: John | Status: Open, Assistant: Kyle

It also tracks initial "inserts" as such:

id | ModifiedDate | OldValues             | New Values
1  | 08/26/2016   | Status: , Assistant:  | Status: Open, Assistant: John

Hope that helps a bit.


Move a database (from decomisioned server) to an existing server with heavy ongoing loads

A couple of transactions running every 2-3 minutes and a 60GB database is pretty light IMHO and shouldn't add much load to an existing instance unless it's at the edge of it's capacity.

A couple of quick questions to take into account.  You are just moving the database not creating a new instance on the "heavily used" machines correct?  On the old instance is this the only database?

I would check RAM/CPU usage on each server.  Pick the lowest usage instance of the three and compare that to the usage on the old instance.  Remember that the CPU/RAM usage for an instance is more than just that for the database (even if it's the only user DB on the instance).  Also that SQL is pretty good at managing multiple databases/workloads.

In the end your best bet is going to be to use a development machine, make a copy of the database there and load test.  If everything goes well there then move to the lowest use old instance.

If you have the option to add CPU/RAM to the newer servers then that will be a nice safety net.  Move the DB and monitor. If it needs more memory add it, more CPU add that.


What options are there for rebuilds of indexes on large tables for a data warehouse?

At 4 vCPU you're on the smallest Enterprise Edition, so your cost isn't prohibitive. You will need to keep up to date with the Software Assurance if you want HA on your VM.

I would recommend adding memory to this (assuming its available in the hypervisor) and taking this VM to the maximum that edition of SQL can support (64GB for 2008 R2). That's probably your lowest cost option.

If that doesn't give what you want / need then realistically you are looking at Enterprise Edition and table partitioning to manage the data which isn't being manipulated by the ETL.

If the majority of the data isn't being changed, and it just gets reloaded time after time, then that needs to change as its not going to be sustainable. Only load the data which needs to be reloaded.


I ran into this today. I tried to connect to a SQL Server 2014 instance while using SQL Server Management Studio 2008 R2. This is exactly the same error I got:

  Cannot read property Collation.This property is not available on SQL Server 7.0. (Microsoft.SqlServer.Smo).

Installing SSMS 2014 did the trick; I can connect and add users fine.


Lock mysql table to prevent select and insert at the same time

With LOCK you probably mean locking entire table, that should not be needed in InnoDB. Check SELECT .. FOR UPDATE.

But duplicate values should be forbidden with the use of UNIQUE indexes, that way the worst thing you get is an "duplicate key" error and not a duplicated row.

If you have proper indexes in place, you may even use INSERT .. ON DUPLICATE KEY UPDATE.


MySQL - Autoincrement does not increment sequentally if last row was deleted

This is by design - all DBMS act this was with auto-increment columns.

If they did not external referential integrity could be damaged. For a simple example of this, imagine you are storing URLs for a shortening service using an auto-increment column as the key. You don't know if the shortened URL has been given out to anyone yet, and the database certainly doesn't, so reusing ID 1234 could result in someone's poor granny visiting somenastypornsite.xxx instead of loverlyknitting.org when she clicks http://shortthi.ng/1234 in an old email, instead of getting a "sorry but this link no longer exists in our records" message.

Also if you reset the increment after deleting the last item, will you also be going through all the work (or expecting the database to) of renumbering everything after the 5th item of 5 million when the 5th item is removed? complete with changes to other tables where their are foreign key constraints pointing at the increment column? Such extra work could get very expensive IO-wise.

If you do reset the increment point after deleting the last item, do be very very careful of your transaction isolation levels: You might reset it just as another transaction makes use of the value, leading to errors (or worse, silent failures), unless you make sure that your action is fully 100% isolated.

I generally recommend people working with databases read "SQL Antipatterns" which has a chapter on this issue named "Psuedo-Key Neat Freaks" (which covers the matter in a friendlier manner than the chapter title may imply to some!). Essentially, if the value has meaning beyond being a key (or at most carrying insert order information) then it probably should not be an auto-incrementing column, and if it does have no meaning beyond being a key (or at most carrying insert order information) then gaps should not matter.


Perhaps you could look for the number of records for each usageUser as well
This query will give the record count for all users per division
SELECT count(1) usageUser_Records,L.divisionName,R.usageUser
FROM records R
INNER JOIN locate L
    ON L.computerID=R.usageComputerID
WHERE R.usageWhen LIKE "2012-07-08T12:%"
GROUP BY L.divisionName,R.usageUser;

This query will give the record count for all divisions per user (reversed GROUP BY)
SELECT count(1) usageUser_Records,L.divisionName,R.usageUser
FROM records R
INNER JOIN locate L
    ON L.computerID=R.usageComputerID
WHERE R.usageWhen LIKE "2012-07-08T12:%"
GROUP BY R.usageUser,L.divisionName;

Looking back at your original query, if removing the count(distinct usageUser) gave you the same number of rows, my guess would be that 2012-07-08 has either one division or one user being retrieved. Looking back at your original post, you said you inherited this data. Chances are, there is only one division or one user throughout the data.
This may not be a full answer but just two suggested queries to get you to think. I hope this helps...
UPDATE 2013-08-06 12:48 EST
As suggested by @yercube
SELECT count(1) usageUser_Records,L.divisionName,R.usageUser
FROM records R
INNER JOIN locate L
    ON L.computerID=R.usageComputerID
WHERE (R.usageWhen >= '2012-07-08T12:00:00' AND R.usageWhen < '2012-07-08T13:00:00')
GROUP BY L.divisionName,R.usageUser;

This query will give the record count for all divisions per user (reversed GROUP BY)
SELECT count(1) usageUser_Records,L.divisionName,R.usageUser
FROM records R
INNER JOIN locate L
    ON L.computerID=R.usageComputerID
WHERE (R.usageWhen >= '2012-07-08T12:00:00' AND R.usageWhen < '2012-07-08T13:00:00')
GROUP BY R.usageUser,L.divisionName;


How can the LEFT JOIN return more results than the unjoined query?

Each row in the first table may match more than one row in the second table.

You can check this with:

SELECT l.id,count(*)
FROM list l
LEFT JOIN cardinal c ON l.id=c.id
WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink'
group by l.id
having count(*)>1;

A little demo is on SQL Fiddle here.


Is it possible to grant role privileges to a user by document (not collection) in MongoDB?

No, you cannot grant rights for document... But you can do views what shows only that public information.


Construct the tag in your script and pass it as a substitution variable. Example on Linux with bash:

$ cat BKP_FULL.rcv
run
{
  backup as compressed backupset incremental level 0 database tag '&1';
}
$ export MYTAG='BKP_FULL_'$(date +%Y%m%d%H%M%S)
$ echo $MYTAG
BKP_FULL_20191111195400
$ rman target / cmdfile=BKP_FULL.rcv using $MYTAG

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 11 19:54:14 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MIN19 (DBID=2133113873, not open)

RMAN> run
2> {
3>   backup as compressed backupset incremental level 0 database tag 'BKP_FULL_20191111195400';
4> }
5>
Starting backup at 11-NOV-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=169 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/MIN19_O71/system01.dbf
input datafile file number=00002 name=/oradata/MIN19_O71/sysaux01.dbf
input datafile file number=00003 name=/oradata/MIN19_O71/undotbs01.dbf
input datafile file number=00004 name=/oradata/MIN19_O71/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-NOV-19
channel ORA_DISK_1: finished piece 1 at 11-NOV-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/03ugkif9_1_1 tag=BKP_FULL_20191111195400 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 11-NOV-19

Starting Control File and SPFILE Autobackup at 11-NOV-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2133113873-20191111-01 comment=NONE
Finished Control File and SPFILE Autobackup at 11-NOV-19

Recovery Manager complete.
$

As you can see, the tag was set as tag=BKP_FULL_20191111195400.


Error "column does not exist" in a SELECT with JOIN and GROUP BY query

You probably want something like this:

SELECT h_v_charges.*,
       last_v.last_version
FROM hist_versions_charges h_v_charges
  JOIN (select proj_charge_id,
               max(version) as last_version
        from hist_versions_charges
        where proj_sous_projet_id = 2
        group by proj_charge_id
  ) last_v
  ON h_v_charges.version = last_v.last_version
 AND h_v_charges.proj_charge_id = last_v.proj_charge_id
ORDER BY h_v_charges.proj_charge_id ASC;

A possibly (because no join is required) faster solution would be:

select *
from (
   select hvc.*,
          row_number() over (partition by proj_charge_id order by version desc) as rn
   from hist_versions_charges as hvc
   where proj_sous_projet_id = 2
) as hv
where rn = 1
order by hv.proj_charge_id ASC;

As Colin has pointed out, this can also be written as:

with hv as (
  select hvc.*,
         row_number() over (partition by proj_charge_id order by version desc) as rn
  from hist_versions_charges as hvc
  where proj_sous_projet_id = 2
)
select *
from hv
where rn = 1
order by hv.proj_charge_id ASC;


Why MySQL does not provide stddev(distinct expression)?

Possible answer:  Standard Deviation is one of thousands of things that are so little used that it is not worth providing.

You can provide it, even in summary tables, by keeping

COUNT(*)
SUM(x)
SUM(x*x)

There are two stddev forumlas; they differ by only tiny amounts for large sets of data.  One of them can be reformulated using only those 3 aggregates.


how to automatically create the associated schema when I `pg_restore` a table?

Use pg_dump --schema option for this.

With:

$ psql
psql (9.4.19)
Type "help" for help.

mevzek=# CREATE SCHEMA test1;
CREATE SCHEMA
mevzek=# CREATE TABLE test1.atable (id SERIAL);
CREATE TABLE
mevzek=# \q

You can compare the results of the two commands:

$ pg_dump --table test1.atable > dump1.sql
$ pg_dump --schema test1 > dump2.sql
$ diff -u dump1.sql dump2.sql
--- dump1.sql   2019-07-18 12:55:53.000000000 -0500
+++ dump2.sql   2019-07-18 12:56:59.000000000 -0500
@@ -10,6 +10,15 @@
 SET check_function_bodies = false;
 SET client_min_messages = warning;

+--
+-- Name: test1; Type: SCHEMA; Schema: -; Owner: mevzek
+--
+
+CREATE SCHEMA test1;
+
+
+ALTER SCHEMA test1 OWNER TO mevzek;
+
 SET default_tablespace = '';

 SET default_with_oids = false;

If you add --table it does not seem to create the schema anymore. You could go around that with two commands: pg_dump --schema test1 --exclude-table-data '*' and then pg_dump --table test1.atable --data-only or similar.


alert when any job in a job category fails

You could create a job that checks the msdb.dbo.sysjobhistory table every minute (or however frequently you want). You might want to implement a queue table so you only ever send the message for any single instance failure once.

USE msdb;
GO

CREATE TABLE dbo.ReportServerJob_FailQueue
(
  job_id UNIQUEIDENTIFIER,
  run_date INT,
  run_time INT, -- horrible schema, just matching sysjobhistory
  sql_message_id INT,
  sent BIT NOT NULL DEFAULT 0,
  PRIMARY KEY (job_id, run_date, run_time)
);

So then your code, that you can schedule in a job, becomes:

INSERT dbo.ReportServerJob_FailQueue
  (job_id, run_date, run_time, sql_message_id)
SELECT job_id, run_date, run_time, sql_message_id
FROM msdb.dbo.sysjobhistory AS h
WHERE step_id = 0
AND run_status = 0
AND EXISTS
(
  SELECT 1 FROM msdb.dbo.sysjobs AS j
    INNER JOIN msdb.dbo.syscategories AS c
    ON j.category_id = c.category_id
    WHERE j.job_id = h.job_id
   AND c.name = 'Report Server'
)
AND NOT EXISTS
(
  SELECT 1 FROM dbo.ReportServerJob_FailQueue
    WHERE job_id = h.job_id
    AND run_date = h.run_date
    AND run_time = h.run_time
);

Now I assume you want to send an individual e-mail for each failure, so this could be part of the job as well (or part of a different job, though that isn't necessarily wise):

DECLARE
  @subject NVARCHAR(4000),
  @body NVARCHAR(4000),
  @name SYSNAME,
  @id UNIQUEIDENTIFIER,
  @date INT,
  @time INT,
  @msg INT;

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT q.job_id, q.run_date, q.run_time, q.sql_message_id, j.name
  FROM dbo.ReportServerJob_FailQueue AS q
  INNER JOIN msdb.dbo.sysjobs AS j
  ON q.job_id = j.job_id
  WHERE q.sent = 0;

OPEN c;

FETCH NEXT FROM c INTO @id, @date, @time, @msg, @name;

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @subject = 'Report Server job ' + @name + ' failed.';
  SET @body = 'Error number: ' + RTRIM(@msg);

  BEGIN TRY
    EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'default',     -- you may need to change this
      @recipients   = 'foo@bar.com', -- you will need to change this
      @subject      = @subject,
      @body         = @body;

    UPDATE dbo.ReportServerJob_FailQueue
      SET sent = 1
      WHERE job_id = @id
      AND run_date = @date
      AND run_time = @time;
  END TRY
  BEGIN CATCH
    PRINT 'Will have to try that one again later.';
  END

  FETCH NEXT FROM c INTO @id, @date, @time, @msg, @name;
END

CLOSE c; DEALLOCATE c;

There are some other options too:

pull in sysjobhistory.message
look at individual steps that failed
only send a message for any job once in n minutes/hours even if there are multiple failures
send a single e-mail with a list of all the jobs that have failed, instead of an e-mail for each failure
you might want to include the run_date and run_time in the message, since the e-mail may not be sent or received quickly enough to be an accurate measure of when the job actually failed (I did not include it here because their horrible data type choices make formatting that stuff a royal PITA)
you'll probably want to clean up old rows after some time, so a purge command may be desired as well

If Database Mail isn't already set up, please see this tutorial.

You could also use 3rd party tools (e.g. SQL Sentry) that will make a lot of this simpler. Full disclosure: I work for SQL Sentry.


There are 2 good options that you can think of implementing based on your comfort level :

Incremental Loads - using SSIS

Pros:

You can schedule SSIS package as and when required.
You can do it for selected tables.
Depending on the frequency of data transfer, the data to be transferred would be less.

Cons:

Would require a bit of learning if you are new to SSIS.

Backup Restore (sample script .. change it as per your needs) :

Pros :

You get a copy of your database as per your schedule.

Cons:

Entire database has to be restored. Requires more disk space.

Your application should use 3 part (dbname.schema.tableName).

Also, make sure that Instant file initialization is enabled to cutdown the restore time.


That's a system session.
DBCC INPUTBUFFER(37); might give a clue what it's doing, or CROSS APPLY with  sys.dm_exec_input_buffer(), but you might not be able to understand any more about what it's doing. Since it's a background task, it is unlikely to be a cause for any other perceived slowness but, being internal, I'm not sure what you could possibly do to tune it anyway...
If you're just randomly checking dm_exec_requests you should probably limit it to non-system sessions (join to sys.dm_exec_sessions where is_user_process = 1). Why it's not filtered out of the report, I have no no idea, I tend to not use built-in reports because I (and vendors) build much better versions.


PostgreSQL Correlation Between "max_connections" and "numbackends"

max_connections is the upper limit for the number of database connections to all databases in the PostgreSQL cluster.

numbackends is the number of backends connected to a certain database.

The only connection between these numbers is that the former must be greater or equal than the latter.

It seems like you don't have more than 16 connections to the database. This is not a sign to worry; I would take it as an indication that your connection pool is working.

Remark: shared_buffers is very likely set way too low, which will harm performance.


32-bit or 64-bit for development and deployment?

If you distribute your application with scripts to build the database (not a database backup or something like that), then in theory it should make no difference which version (of 32-bit and 64-bit) you develop against. Back when 64-bit was new and shiny (which was SQL Server 2000), there were some advanced features that 64-bit didn't support, but in 2005/2008 they should be functionally identical.

(Not that it should matter if you're using Compact Edition, but there are some configuration differences between 32-bit and 64-bit once you get to the point where your DB performance is an issue.)


I am unaware of a way to script the existing one out so I drop and create a new one each time.
The following script assumes a database name of DBA and a Service of DeadlockEventsService (viewable in SSMS under Service Broker Services)

/****** BEGIN Create the Event Notification object that will capture the

deadlocks ******/
USE [DBA]
GO

EXECUTE AS LOGIN = 'sa';
DECLARE @AuditServiceBrokerGuid [uniqueidentifier]
,@SQL [varchar](max);

--— Retrieving the service broker guid of CaptureDeadlockGraph database

SELECT @AuditServiceBrokerGuid = [service_broker_guid]
FROM [master].[sys].[databases]
WHERE [name] = 'DBA'

--— Building and executing dynamic SQL to create event notification object

SET @SQL = 'IF EXISTS (SELECT * FROM sys.server_event_notifications
WHERE name = ''DeadlockGraphEventNotification'')

DROP EVENT NOTIFICATION DeadlockGraphEventNotification ON SERVER

CREATE EVENT NOTIFICATION DeadlockGraphEventNotification
ON SERVER
WITH FAN_IN
FOR DEADLOCK_GRAPH
TO SERVICE ''DeadlockEventsService'', '''
+ CAST(@AuditServiceBrokerGuid AS [varchar](50)) + ''';'

EXEC (@SQL)

GO

/****** END Create the Event Notification object that will capture the deadlocks ******/


Can CPU / memory influence restore performance

Restoring involves bringing data pages into memory so yes memory can be a factor but cannot be a show stopper. Things which really can affect backup performance are the drives on which data file and backup resides and the network connectivity. Ofcourse you will see faster restore speed on SSD's as compared to SATA disks.

Read How SQL Server backup and restore operation works. This will let you know how memory is internally used while backup and restore operation

If you read this BOL document it shows how you can improve performance of SQL Server restore process


MySQL Use an In Clause With a Comma Delimited Column

col IN (1,2,34)        -- A
col IN ("1,2,34")      -- B
col IN ("1","2","34")  -- C

Binding tends to produce B, but...
A and C have 3 choices, B has only one choice (a 6-character string)
Both A and C work if col is INT; C works if col is VARCHAR.
A, B, and C, may be able to use an INDEX.
FIND_IN_SET(col, "1,2,34")  -- D

Find_in_set has these characteristics:

Easy to 'bind'
Won't use an INDEX
Works with strings or numbers.


54001 stack depth limit exceeded in recursive stable function

A function may not see the same state of the database depending on whether it's VOLATILE or not, when this state is changing during the execution of the SQL statement it's called from, either because of the statement itself or because of another transaction if the isolation level is
Read  Committed.

This is documented in https://www.postgresql.org/docs/current/xfunc-volatility.html

  For functions written in SQL or in any of the standard procedural
  languages, there is a second important property determined by the
  volatility category, namely the visibility of any data changes that
  have been made by the SQL command that is calling the function. A
  VOLATILE function will see such changes, a STABLE or IMMUTABLE
  function will not. This behavior is implemented using the snapshotting
  behavior of MVCC (see Chapter 13): STABLE and IMMUTABLE functions use
  a snapshot established as of the start of the calling query, whereas
  VOLATILE functions obtain a fresh snapshot at the start of each query
  they execute.

If your function reads the database and follows different paths depending on what it reads, it's quite possible that it goes into infinite recursion only when it's not VOLATILE. Looking at the code might be revealing.


Create archive solution on seperate server

If you're planning on using Transactional or Merge Replication, then you won't be allowed to run a TRUNCATE command on any of your published tables in the Publisher Database. Please see the Microsoft BOL Frequently Asked Questions for Replication Administrators - Database Maintenance, specifically the first section called Why can't I run TRUNCATE TABLE on a published table?:

TRUNCATE TABLE is a DDL statement that does not log individual row deletions and does not fire DML triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through DML triggers on published tables.

That being said, you can modify the properties of your published articles such that DELETE statements don't get replicated to the Subscriber Database, as detailed in this article Options to not replicate SQL Server DELETE commands. Or you can follow the second option of that article to modify the underlying stored procedure that would execute the DELETE statement, though I find the first solution cleaner.
Either of these options should help you achieve your goal.


WITH cte AS (
SELECT *, DENSE_RANK() OVER (ORDER BY ColumnA) dr
FROM source
),
cte2 AS (
SELECT DISTINCT ColumnA, NULL ColumnB, NULL ColumnC, NULL ColumnD, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL, ColumnB, NULL, NULL, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL , NULL, ColumnC, NULL, dr
FROM cte
UNION ALL
SELECT DISTINCT NULL, NULL, NULL, ColumnD, dr
FROM cte
)
SELECT ColumnA, ColumnB, ColumnC, ColumnD
FROM cte2
ORDER BY dr, ColumnA DESC, ColumnB DESC, ColumnC DESC, ColumnD DESC

fiddle

PS. Edit final ORDER BY clause (except dr first) as you need.


Change character limit of SQL Agent Operators

Just because you can does not mean you should. Please do not apply the changes below. This answer is only to show you why you should not change system tables or system procedures and what can go wrong.

msdb.dbo.sp_add_operator

When adding the operator with the ssms gui, msdb.dbo.sp_add_operator is called behind the scenes.

The email address parameter used is also nvarchar(100).

In theory, you could adapt the procedure by altering it

And then changing the @email_address parameter to nvarchar(1000)

If I then run it wih 384 characters (non gui)

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'test',
        @enabled=1,
        @pager_days=0,
        @email_address=N'emaione@example.com;emailtwo@example.com;emailthree@example.com;emaione@example.com;emailtwo@example.com;emailthree@example.com;emaione@example.com;emailtwo@example.com;emailthree@example.com;emaione@example.com;emailtwo@example.com;emailthree@example.com;emaione@example.com;emailtwo@example.com;emailthree@example.com;emaione@example.com;emailtwo@example.com;emailthree@example.com;'
GO

Getting the length:

select LEN(email_address)  as email_length
from sysoperators
where name = 'test';

Shows the 384 characters are there.

email_length
384

However, one of the things instantly breaking is the operator list when opening it via ssms:

Due to ssms running this query:

create table #tmp_sp_help_operator
(id int null, name nvarchar(128) null, enabled tinyint null, email_address nvarchar(100) null, last_email_date int null, last_email_time int null, pager_address nvarchar(100) null, last_pager_date int null, last_pager_time int null, weekday_pager_start_time int null, weekday_pager_end_time int null, saturday_pager_start_time int null, saturday_pager_end_time int null, sunday_pager_start_time int null, sunday_pager_end_time int null, pager_days tinyint null, netsend_address nvarchar(100) null, last_netsend_date int null, last_netsend_time int null, category_name nvarchar(128) null)
insert into #tmp_sp_help_operator exec msdb.dbo.sp_help_operator

SELECT
tsho.name AS [Name],
'Server[@Name=' + quotename(CAST(
        serverproperty(N'Servername')
       AS sysname),'''') + ']' + '/JobServer' + '/Operator[@Name=' + quotename(tsho.name,'''') + ']' AS [Urn],
CAST(tsho.enabled AS bit) AS [Enabled]
FROM
#tmp_sp_help_operator AS tsho
ORDER BY
[Name] ASC

drop table #tmp_sp_help_operator

Showing why we should not play around with system tables & procedures.

Who knows what will work and what will break when changing the table & the procedure.

A distribution list as vonPryz mentioned would be a much better solution.


Where can I find the first standardization of SQL, SQL-86?

HTML SQL-86 Google Groups

I found some references about an HTML version of SQL-86.

  The new URL for the HTML translation of SQL-86 is:

     http://speckle.ncsl.nist.gov/~sql/

  The reason for this move is that I am taking a job in another lab and
  I had to get it off of my PC hard drive.

  DWF

  Unfortunately this link is broken, but I believe the OP is still working at NIST >as a Computer Scientist.

  I've sent him a request via LinkedIN, and now I'm waiting for a reply.

The answer:

  To Joan and others interested in SQL-86,

  The 'HTML version' was a demonstration project that was never completed. Your best bet is to try to obtain the ANSI specification through interlibrary loan.

  V/R,
  —
  David Flater
  National Institute of Standards and Technology, U.S.A.

comp.databases Newsgroup Posting

I've found new references dated 12 Oct 89 about ANSI X3.135-1986: it's from newsgroup comp.databases where someone else was interested on this document. There is a response by Merrill Holt to the initial inquiry. Merrill Holt claims to be "a member of the X3H2 SQL and X3H2.1 RDA committees.", that seems likely as they're writing from a Sybase email address. He/she says,

  The ANSI and ISO documents are identical (ISO
  9075:1987 == ANSI X3.135-1986 and ISO 9075:1989 == ANSI X3.135-1989).
  All of these standards are known informally as SQL 1 as compared to future drafts which are referred to as SQL 2 and 3.

SQL 1   SQL 86  ANSI X3.135-1986   ISO 9075:1987
        - level 1 DML & DDL
        - level 2 DML & DDL
        SQL 89  ANSI X3.135-1989   ISO 9075:1989
        - level 1 DML & DDL
        - level 2 DML & DDL
        - IEF - new for 89

The answer points out to this company that may have the document:

Global Engineering
2805 McGaw Ave
Irvine, CA 92714

It turns out ISO does mention ISO 9075:1987 however they have it listed as obsolete and retired and are not currently selling it.

US Army Corps of Engineers

And I've found one available exemplar at:

  US Army Corps of Engineers
  Engineer Research and Development Center Library
  3909 Halls Ferry Road, Vicksburg, MS.
  erdclibrary@ask-a-librarian.info

  I'm not sure this is the original book covers.

Linda Hall Library

I've found one exemplar at Linda Hall Library

Other potential names

It seems some sources references this as ISO/TC 97/SC21/WG3 N117. However, I'm not sure how to read this or if this designation refers to the document or not. This source helps break down the acronyms.

TC refers to "Technical Committee"
SC refers to "Sub Committee"; 21 is Systems
WG is usually working group.
N117 may be a document or something published like a SQL spec.


Trying to understand how to use postgresql generate_series() for this operation

You need to join users and calendar and then cross join that with a generate_series().

insert into calendar_field (text, rating, calendar_id, week_number)
select '', 0, c.id, g.wn
from calendar c
  join users u on u.id = c.user_id
  cross join generate_series(0, u.weeks_to_live) as g(wn);

Online example


I have some theories as to why the performance is so bad.

Lets see.

  The first is that I simply have too many rows.

Probably not. There is no limit on the number of rows in a (SQL) DBMS. 80 million rows is not that much these days.

  The second is that a database is not suited for this task.

Unlikely. We don't know which DBMS you use (Postgres? MySQL? SQLite? SQL Server? There are hundreds of them) but I think any of them could handle what seems to be a simple query.

  The third is that my query is not well formulated.

No, it seems good. The query produced will look like:

SELECT id, firstid, secondid, ..., distanceValue
FROM LocationsLookupTable
WHERE firstId = ?
  AND secondId = ? ;

  So, what is causing this?

If that is all the code that is creating the table, then what is missing is indexes. Lacking them, the database optimizer/planner has to create a query plan that scans the entire table. While the table is not big, it is not very small either.

Adding an index on (firstid, secondid) will improve performance a lot.

However 300 seconds looks too long, even for a table scan, so the hardware or the DBMS settings should be examined, too.


How to fix below blocking problem on SQL server 2014

As stated by a previous user the lock type indicates that your query tries to acquire an Exclusive lock on a resource.
Is your predicate (Col1=@col1) supported by in index ?
Some options for a query referencing a specific table to acquire locks on other tables can be: triggers, fks and fk cascading.
More info is required. See this post for more ideas.
Have you tried to capture the blocked process report ?
See this post for how to run a server side trace to capture the "Blocked Process Report Event" constantly in the background so that when your issue occurs you do not miss it and have the data needed for analysis


Partial unique constraint spanning multiple tables in postgres

Not an answer for postgres, but may be useful nevertheless to someone using Oracle who stumbles across this:

Oracle allows partial uniqueness to be enforced across multiple tables using a fast refreshing materialised view. Tom Kyte describes it here. In short, if a join produces any rows on commit, it violates a constraint on the materialised view.

Untested, but in principle it should work like so:

create materialized view log
  on possession with rowid;

create materialized view log
  on toy with rowid;

create materialized view one_special_toy_per_owner
  refresh fast
  on commit
  as select p.owner_id, count(t.special) as special
     from possession p, toy t
     where p.possession_id = t.possession_id
     group by p.owner_id
     having count(t.special) > 1;

alter table one_special_toy_per_owner
  add constraint owner_has_only_one_special_toy
  check (owner_id is null and special is null);


Do fixed-width rows improve PostgreSQL read performance?

Do tables with only fixed width values perform read queries better
than those with varying widths?

Basically no. There are very minor costs when accessing columns, but you won't be able to measure any difference. Details:

Does the order of columns in a Postgres table impact performance?

In particular:

There is no difference in performance between character varying(255) and text at all. You seem to be under the impression that varchar(255) (unlike text) might be a "fixed-width" type, but that is not so. Both are variable-length types, varchar(255) just adds a maximum length check:
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

The use of varchar(255) in a table definition typically indicates a lack of understanding of the Postgres type system. The architect behind it is most probably not a native speaker - or the layout has been carried over from another RDBMS like SQL Server where this used to matter.

Your most expensive query SELECT COUNT(*) FROM articles does not even consider row data at all, only the total size matters indirectly. Counting all rows is costly in Postgres due to its MVCC model. Maybe an estimate is good enough, which can be had very cheaply?
Fast way to discover the row count of a table

(Pretend disk space isn't an issue.)

Disk space is always an issue, even if you have plenty. The size on disk (number of data pages that have to be read / processed / written) is one of the most important factors for performance.

Where can I learn more about the internals of the Postgres DB engine?

The info page for the tag postgres has the most important links to more information, including books, the Postgres Wiki and the excellent manual. The latter is my personal favorite.
Your third query has issues
SELECT * FROM articles WHERE user_id = $1 ORDER BY published_date DESC LIMIT 1;

ORDER BY published_date DESC, but published_date can be NULL (no NOT NULL constraint). That's a loaded foot-gun if there can be NULL values, unless you prefer NULL values over the latest actual published_date.
Either add a NOT NULL constraint. Always do that for columns that can't be NULL.
Or make that ORDER BY published_date DESCNULLS LAST and adapt the index accordingly.
"articles_user_id_published_date_idx" btree (user_id, published_date DESC NULLS LAST)
Details in this recent, related answer:

Extremely slow query on indexed column in Postgres

Convert published_date to an actual date
While 'published_date' is always rounded, it's effectively just a date which occupies 4 bytes instead of 8 for the timestamp. You would best move that up in the table definition to come before the two timestamp columns, so you don't lose the 4 bytes to padding:
...
body           | text
published_date | date   --     <---- here
created_at     | timestamp without time zone
updated_at     | timestamp without time zone

Smaller on-disk storage does make a difference for performance.

Configuring PostgreSQL for read performance

More importantly, your index on (user_id, published_date) would now just occupy 32 bytes per index entry instead of 40, because 2x4 bytes do not incur extra padding. And that would make a noticeable difference for performance.
Aside: this index is not relevant to the demonstrated queries. Delete unless indexes unless used elsewhere:
"index_articles_on_published_date" btree (published_date)