How to upgrade a SQL 2000 Database to SQL 2005 - need confirmation?

Since you are using the Backup/Restore method, you don't need to copy the database (mdf) and log file (ldf) to the new server, just the backup files. You do not need to recreate the database either. The database, database files and log file will all be created during the restore process.  The upgrade process is done by SQL Server when restoring your database backup.

Here is the method I use:

On SQL Server 2000

Perform a full backup of the database
Perform a log backup of the database
"captures records on the transaction log that were written since the last transaction log backup".  Consider using the WITH NORECOVERY option, if you don't want people accessing the database after you're done.
Copy the full database and log backup files to the server that has SQL Server 2005

On SQL Server 2005

Restore the full backup with NORECOVERY
Make sure you change the directories as mentionned in your post.
Restore the log backup with RECOVERY

Once the database is restored on the new server.  I run the following commands in a new query window

USE [mydb]
sp_updatestats -- updates statistics
This is because statistics are not automatically updated during the upgrade process.
DBCC CHECKDB WITH DATA_PURITY

Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range.  This is important when upgrading from SQL Server 2000 to 2005 or 2008.

Associating database users with Logins

Now that your database has been restored, you need to associate its orphaned users with an actual login.

Method A: Fixing orphaned users

EXEC sp_change_users_login 'Report' -- find orphaned users
if the login exists already, you can asssociate the orphaned user with the login. Otherwise, you can create the login

-- Login exists

EXEC sp_change_users_login 'Auto_Fix', 'user'

-- Login doesn't exist

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Method B: sp_help_revlogin

You also have an alternative called sp_help_revlogin which can help you to restore not only the login but the original password that went with it.

http://support.microsoft.com/kb/246133/en-us

It essentially entails creating a stored procedure on your old server to generate a script that will output all of your existing logins.  You then copy the output of that stored procedure to your new server and restore the logins that are associated with the database you restored.


Table optimize command crashed MySQL server

Is there a way to drop/delete the temporary table that was created during the optimize process without having the mysql server running?

Unfortunately, no.

InnoDB is a very complex beast, and there's a lot of interplay between ibdata,.ibd, ib_logfile, and .frm files that means manual tinkering is, at best, extremely dangerous.

Before you try anything, make a complete copy of all MySQL files to a remote server or another directory if at all possible. When your situation is as delicate is this, it's easy to make a change that breaks the database irreparably.

Your best bet (as Boris mentioned above) is to try and salvage some space somewhere - anywhere. Compress or truncate some log files. Move some non-critical MyISAM tables (if you have any) off of the server until you have things at least partially under control. Move a few .myd files to another directory and symlink them if you need to (be really careful doing this to InnoDB files).

If you're not using using innodb_file_per_table, then you're  going to end up with a giant ibdata file and no free disk space. Rolando has given an extensive answer about steps to resolve this issue, and you should follow that rather than the rest of these steps.

If you are using innodb_file_per_table:

Once you have started MySQL successfully, you can check to see if the temporary table still exists (the easiest way to do this is to try desc `#sql-420a_65fb`, but there are also more complex ways).

If it still exists, you can drop it with:

drop table #mysql50##sql-420a_65fb

With innodb_file_per_table on, this will remove the temp table's .frm and .ibd files, and you will get some of your disk space back.

  I went into the console and ran the optimize command and since it's an InnoDB database it cloned the table to a temporary one (is that accurate?).

Yes, that's accurate. MySQL will create a temporary table with a name that looks something like #sql-12a_345b, and copy the data across it so that it's fresh and non-fragmented. Once it's complete, it would normally swap the tables around and then drop the old (fragmented) table).


Why shouldn't I create a new user on Oracle SYSTEM Tabespace?

The Oracle Documentation states:

  The default setting for the default tablespaces of all users is the
  SYSTEM tablespace. If a user does not create objects, and has no
  privileges to do so, then this default setting is fine. However, if a
  user is likely to create any type of object, then you should
  specifically assign the user a default tablespace, such as the USERS
  tablespace. Using a tablespace other than SYSTEM reduces contention
  between data dictionary objects and user objects for the same data
  files. In general, do not store user data in the SYSTEM tablespace.

The documentation also explains how to create an alternative tablespace etc.


Why is my Azure SQL (SQL Server) database getting overloaded with data IO for periods at a time?

Given that during the spike(s) your log activity is minimal we can assume there isn't any (or much) DUI going on.

You mention at one point that the spike doesn't affect performance, and at another that it does. Which is it?

You also mention that this goes away after a scale operation. This makes sense as it's analogous to an on-premise restart which will effectively kill all processes etc.

Do I assume correctly in guessing that this database is being accessed from the application-tier? If so, I suspect that your connections are not being closed properly. The garbage collector is supposed to take care of these eventually (which shouldn't be relied upon), but I've seen this exact situation occur because of unclosed connections from the app-tier. In our case, the application was so busy that we eventually received concurrent connection errors which is what lead us to the problem.

Try the following query during the spike:

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    s.status,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
ORDER BY c.connect_time ASC

If I'm correct, you'll find a whole bunch of records returned with the status of Sleeping, or worse Running. If that's the case you have even bigger problems in the app-tier.

We can further debug this by copying the database, using the following query (using basic tier to avoid excessive costs), and monitoring for this behaviour.

CREATE DATABASE Database1_copy AS COPY OF Database1 ( EDITION = 'basic' );


A database in MySQL is a schema in Oracle : I do not understand this statement

It's simple.

MySQL has a single daemon that runs the database server. Within the server you can create any number of databases - these databases have no direct mapping to users.

Oracle has a single database. When you create a user in an Oracle database, it also creates a Schema with the same name as the user that created it. This is equivalent to a database in MySQL.

Having seen the edit to your question, you really need to forget about mysql while you learn Oracle. DDL and RDBMS concepts are completely different.

Start with the Oracle Server Concepts guide (link). The Oracle documentation home is here: http://www.oracle.com/pls/db112/homepage.

To create a user, the syntax would be:

create user newbie identified by yourpassword;

You'll then need to grant appropriate privileges to allow the user to connect:

grant connect to newbie;


How to update mysql my.cnf without getting error?

You mentioned these four options

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_file_size
innodb_log_buffer_size

The one that worries me is innodb_log_file_size

Upon initial startup, innodb_log_file_size is 5MB. You need to make new InnoDB log files. There are two InnoDB log files that got created 5MB each:

/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

All you have to do is the following:

service mysql stop
rm -f ib_logfile*
service mysql start

The two log files will be regenerated as 1GB files. I wrote about this last year.

Give it a Try !!!

BTW DO NOT TOUCH /var/lib/mysql/ibata1 !!!


Yeah, you need that .ldf file.  In the future, don't delete that.

If your database is running in full recovery, you need to run log backups regularly.  "Regularly" is a term that depends on your particular environment, but hourly during business hours is a common choice.  Basically, it only purges the transactions out of the log file when it's backed up.  If it's never backed up, the log grows until it consumes the entire disk, as you've seen.

In the future, set the database to simple recovery, shrink the transaction log, and then back it up immediately, as NathanC suggested.

As for now:  I don't know what kind of backups you have beyond the one you restored from, but I agree with NathanC that you need to let it sit and wait for the restore to finish.  It's chugging through that log file.  Once it finishes, truncate the log and set it to simple like your other databases, or implement log backups.


Best way to allow update exceptions on a cell level

This sounds like the classic use-case for a SQL Server-based cursor.  Yes, I just said the "c" word.

Take this code, which uses your sample data:

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.tExclude', N'U') IS NOT NULL
DROP TABLE dbo.tExclude;
CREATE TABLE dbo.tExclude
(
    ID int NOT NULL
    , ColName sysname NOT NULL
);

IF OBJECT_ID(N'dbo.tBase', N'U') IS NOT NULL
DROP TABLE dbo.tBase;
CREATE TABLE dbo.tBase
(
    ID int NOT NULL
    , item_name varchar(30)
    , item_type varchar(30)
    , color varchar(30)
    , size varchar(30)
);

IF OBJECT_ID(N'dbo.tUpdate', N'U') IS NOT NULL
DROP TABLE dbo.tUpdate;
CREATE TABLE dbo.tUpdate
(
    ID int NOT NULL
    , item_name varchar(30)
    , item_type varchar(30)
    , color varchar(30)
    , size varchar(30)
);

Sample data:

INSERT INTO dbo.tExclude (ID, ColName)
VALUES (1, 'item_name')
    , (1, 'size');

INSERT INTO dbo.tBase (ID, item_name, item_type, color, size)
VALUES (1, 'A', 'Z', 'red', 'big')
    , (2, 'B', 'Y', 'blue', 'tiny')
    , (3, 'C', 'X', 'red', 'tiny')
    , (4, 'D', NULL, NULL, NULL);

INSERT INTO dbo.tUpdate(ID, item_name, item_type, color, size)
VALUES (1, 'A', 'Z', 'red', 'big')
    , (2, 'B', 'Y', 'blue', 'tiny')
    , (3, 'C', 'X', 'red', 'tiny')
    , (4, 'D', 'W', 'gold', 'big')
    , (5, 'E', 'Y', 'grey', 'cat');

At this point, the dbo.tBase table looks like:

╔════╦═══════════╦═══════════╦═══════╦══════╗
║ ID ║ item_name ║ item_type ║ color ║ size ║
╠════╬═══════════╬═══════════╬═══════╬══════╣
║  1 ║ A         ║ Z         ║ red   ║ big  ║
║  2 ║ B         ║ Y         ║ blue  ║ tiny ║
║  3 ║ C         ║ X         ║ red   ║ tiny ║
║  4 ║ D         ║ NULL      ║ NULL  ║ NULL ║
╚════╩═══════════╩═══════════╩═══════╩══════╝

Code to do the desired updates and inserts:

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t
(
    cmd nvarchar(max) NOT NULL
);

DECLARE @cmd nvarchar(max);
DECLARE @ID int;
DECLARE @Type varchar(6);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT tu.ID
    , CASE WHEN tb.ID IS NULL THEN 'INSERT' ELSE 'UPDATE' END
FROM dbo.tUpdate tu
    LEFT JOIN dbo.tBase tb ON tu.ID = tb.ID;
OPEN cur;
FETCH NEXT FROM cur INTO @ID, @Type;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Type = 'UPDATE'
    BEGIN
        SET @cmd = N'
WITH src AS
(
    SELECT c = QUOTENAME(c.name) + '' = '' + ''tu.'' + QUOTENAME(c.name)
    FROM sys.columns c
        INNER JOIN sys.tables t ON c.object_id = t.object_id
    WHERE t.name = ''tBase''
        AND c.name <> ''ID''
    EXCEPT
    SELECT QUOTENAME(e.ColName) + '' = '' + ''tu.'' + QUOTENAME(e.ColName)
    FROM dbo.tExclude e
    WHERE ID = ' + CONVERT(nvarchar(max), @ID) + N'
)
SELECT ''UPDATE dbo.tBase
SET '' + (STUFF((SELECT '', '' + src.c FROM src FOR XML PATH ('''')), 1, 2, '''')) + ''
FROM dbo.tBase tb
    INNER JOIN dbo.tUpdate tu ON tb.ID = tu.ID
WHERE tb.ID = ' + CONVERT(nvarchar(max), @ID) + N';''';
        INSERT INTO #t
        EXEC sys.sp_executesql @cmd;
    END
    ELSE
    BEGIN
        SET @cmd = N'INSERT INTO dbo.tBase (ID, item_name, item_type, color, size)
SELECT ID, item_name, item_type, color, size
FROM dbo.tUpdate tu
WHERE tu.ID = ' + CONVERT(nvarchar(max), @ID) + N';';
        INSERT INTO #t (cmd)
        SELECT @cmd;
    END
    FETCH NEXT FROM cur INTO @ID, @Type;
END
CLOSE cur;
DEALLOCATE cur;

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT t.cmd
FROM #t t;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    EXEC sys.sp_executesql @cmd;
    PRINT N'';
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The code above uses a couple of cursors to create and execute INSERT or UPDATE statements as required.  The UPDATE statements never update columns for rows included in the dbo.tExclude table.

The generated INSERT/UPDATE statements look like:

UPDATE dbo.tBase
SET [color] = tu.[color], [item_type] = tu.[item_type]
FROM dbo.tBase tb
    INNER JOIN dbo.tUpdate tu ON tb.ID = tu.ID
WHERE tb.ID = 1;

UPDATE dbo.tBase
SET [color] = tu.[color], [item_name] = tu.[item_name], [item_type] = tu.[item_type], [size] = tu.[size]
FROM dbo.tBase tb
    INNER JOIN dbo.tUpdate tu ON tb.ID = tu.ID
WHERE tb.ID = 2;

UPDATE dbo.tBase
SET [color] = tu.[color], [item_name] = tu.[item_name], [item_type] = tu.[item_type], [size] = tu.[size]
FROM dbo.tBase tb
    INNER JOIN dbo.tUpdate tu ON tb.ID = tu.ID
WHERE tb.ID = 3;

UPDATE dbo.tBase
SET [color] = tu.[color], [item_name] = tu.[item_name], [item_type] = tu.[item_type], [size] = tu.[size]
FROM dbo.tBase tb
    INNER JOIN dbo.tUpdate tu ON tb.ID = tu.ID
WHERE tb.ID = 4;

INSERT INTO dbo.tBase (ID, item_name, item_type, color, size)
SELECT ID, item_name, item_type, color, size
FROM dbo.tUpdate tu
WHERE tu.ID = 5;

And the dbo.tBase table now looks like:

SELECT *
FROM dbo.tBase;

╔════╦═══════════╦═══════════╦═══════╦══════╗
║ ID ║ item_name ║ item_type ║ color ║ size ║
╠════╬═══════════╬═══════════╬═══════╬══════╣
║  1 ║ A         ║ Z         ║ red   ║ big  ║
║  2 ║ B         ║ Y         ║ blue  ║ tiny ║
║  3 ║ C         ║ X         ║ red   ║ tiny ║
║  4 ║ D         ║ W         ║ gold  ║ big  ║
║  5 ║ E         ║ Y         ║ grey  ║ cat  ║
╚════╩═══════════╩═══════════╩═══════╩══════╝


The issue appears to have been limited to printing from report builder. When I print from SSRS directly, the labels print out as intended. Not sure what the problem is with Report Builder but I'm content the problem has been fixed.


Is it possible to format SQL on output into associative array?

As far as I know, no SQL has yet been designed to return associative arrays, but I could be wrong!


How to reduce fragmentation of nonclustered index on date partitioned log table

I'm wondering what the best way would be to keep a nonclustered index for looking by accountId without all the fragmentation

You've got the partition key in the nonclustered index, so it's on the partition scheme (by default), and only the head partition is going to get inserts and additional fragmentation.
So you can rebuild the older partitions rarely and they won't get fragmented, and rebuild the index for just the head partition on a more frequent basis.
But for an index with insertions spread across the sort order you'll always generate fragmentation.  And fragmentation doesn't always matter.  It really depends on the storage design and the workload.
Also you're keeping two complete copies of this table, one sorted by ChangedAt and one sorted by (AccountId,InventoryId,ChangedAt).  You can probably just make the (AccountId,InventoryId,ChangedAt) index the clustered index, and only store the table one time.


First problem is that you "override the LEFT join by using the WHERE clause referencing that table, so no "NULL" row can show in the results.

Second problem is that you are using an aggregate (count()) but you forgot to add a proper GROUP BY.

Fixing these the query looks like this:

SELECT class, count(ship) as sunks
FROM (
  (
   SELECT classes.class, outcomes.ship
   FROM classes
   LEFT JOIN outcomes ON classes.class=outcomes.ship AND outcomes.result='sunk'
  )
  UNION
  (
   SELECT ships.class, outcomes.ship
   FROM ships
   LEFT JOIN outcomes ON ships.name=outcomes.ship AND outcomes.result='sunk'
  )
) AS tmp
GROUP BY class

There is a fiddle based on your script to see it in action http://sqlfiddle.com/#!9/4840f6/8


Today PostgreSQL released new minor versions.
In these versions timezone datas updated to 2016h.

Released versions;

9.6.1
9.5.5
9.4.10
9.3.15
9.2.19

Also you don't have to upgrade your installation.

You can move new timezone datas to your installation and restart the service.
It's all done.
It's worth the wait.


postgresql.conf file found in both /usr/share/postgresql and /etc/postgresql/

Files below /usr/share/postgresql/ are supposed to be data files that are not meant to be edited. They are not live configuration files.
The FHS spec at https://wiki.debian.org/FilesystemHierarchyStandard
defines /usr as:

/usr/
Secondary hierarchy for shareable, read-only data (formerly from UNIX source repository, now from UNIX system resources)
(files that are not-required to boot or rescue the system)

[...]
/usr/share/ : Architecture-independent (shared) data
[...]

On the other hand, files under /etc/postgresql/X.Y/main are the live configuration files for a PostgreSQL cluster named main with version X.Y, such as created by the command pg_createcluster. Each cluster (=each postgres instance) has its own postgresql.conf that is generated with it, as the other writable configuration and data files of that cluster.
Personally, on my Ubuntu 14.04, I don't find a postgresql.conf under /usr.
There is however a template file at /usr/share/postgresql/9.3/postgresql.conf.sample, but note the .sample suffix.
That file belongs to the postgresql-9.3 package, as revealed by dpkg -S:
$ dpkg -S /usr/share/postgresql/9.3/postgresql.conf.sample
postgresql-9.3: /usr/share/postgresql/9.3/postgresql.conf.sample

It's also consistent with
http://packages.ubuntu.com/trusty/amd64/postgresql-9.3/filelist
You may try dpkg -S postgresql.conf on your system and check if /usr/share/postgresql/9.3/postgresql.conf is part of the results, and if yes, to what package it belongs, and then what is the origin of that package. Maybe for some reason it's the same as the sample postgresql.conf, except it doesn't have the .sample suffix.


Need to count records and group count by date on Oracle

Unless I am missing something, your query would be something like this:

select created, count(*) CreatedCount
from yourtable
group by created
order by created;

See SQL Fiddle with Demo

Or if you have a time associated with the date, you can use TRUNC:

select trunc(created), count(*) CreatedCount
from yourtable
group by trunc(created)
order by trunc(created);

See SQL Fiddle with Demo


How to find tempdb reside in drive location with drive size and tempdb usage details in single query in sql server?

This information can be found using the management DMVs sys.master_files and sys.dm_db_file_space_usage, and the system function sys.dm_os_volume_stats:

SELECT vs.volume_mount_point AS [Drive],
  mf.[name] AS [File],
  mf.physical_name AS [Filename],
  mf.size*8/1024 AS [Size (MB)],
  unallocated_extent_page_count*8/1024 AS [Free Space (MB)]
FROM sys.master_files mf
INNER JOIN sys.dm_db_file_space_usage fsu ON fsu.database_id = mf.database_id AND fsu.file_id = mf.file_id
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
WHERE DB_NAME(mf.database_id) = 'tempdb'


Usually people just want to kick all users out right away, so they use:

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

In your case, if you want all existing activity to continue until you get your turn, this will wait indefinitely:

ALTER DATABASE MyDB SET SINGLE_USER;
-- alter filegroup code here
ALTER DATABASE MyDB SET MULTI_USER;

If you want to give some time but not wait forever, you can say something like:

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK AFTER 300 SECONDS;

Which will wait 5 minutes and then roll back any remaining transactions. RESTRICTED_USER might be an option for you as well, in case SINGLE_USER allows agent or other background threads from taking your connection.

I don't know of any way to set the state of the database so that existing transactions are allowed to finish and no new connections (or new transactions from existing connections) are possible.

Some information here:

Set a Database to Single-user Mode (MSDN)
ALTER DATABASE SET Options (MSDN)


Scape single quote in dynamic query Oracle

You can use another method to quote strings in Oracle which makes things a lot clearer.
Example:

x := q'[This is John's address]';

y := q'[He said "What's your name ?"]';

You can have as many quote marks as you like so long as they are between the '[]'.
Your code may look something like this

SELECT REPLACE(ISR.STREET_NAME1, q'[']',q'['']')
       || CHR(3) || isr.agency_code || CHR(3) || ''
  FROM isr, isr_udf
 WHERE isr.isr_no = isr_udf.isr_no
   AND isr.isr_no = 'XXX-20160718-00022'

Link: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#CBJJDDCG


DBCC UPDATEUSAGE (dbname) runs successfully but does not fix the "disk usage report"

These numbers on the disk usage report look correct. It's important to note that the Total Space Reserved is displayed in GB, using the conversion 1024 MB = 1 GB

The formula used by the above report is straightforward:

Data File Space Reserved + Transaction Log Space Reserved = Total Disk Space Reserved

279,860.56 MB + 90.63 MB = 279,951.19 MB

279,951.19 MB / 1024 = 273.39 GB


How do I remove first line from text and only the first?

You're almost there. I think what you need is:

SELECT substring(value, LOCATE('\n', value)+1)

This means:

find the first newline in the string
then add one (to go to the next character after the newline)
and return everything from that position to the end of the string


Database design. And get last modified time with each column in PostgreSQL

I was wondering should I separate table like below, or not?

Well, one immediately obvious downside of the table design you posted is that there are no constraints on user_information or user_role ensuring that you don't have more than one such row in each of these tables for each user. And even if you remember to add that, you will still have the problem that it will be difficult or impossible to enforce the rule "every user must have a role (or other critical attribute)".
Where it typically makes sense to break out metadata like this is when you have either many-to-one relationship of this metadata (e.g. a single user may have multiple roles), or you have a lot (i.e. many columns) of optional metadata that is unwieldy to cram into a single table.

For now I only can imagine I might want to know each column last modified time?
So if I want to know each column last modified time, is there any original build method in PostgreSQL? or I have to add for each column like email_last_modified_date, username_last_modified_date ...

You'll have to create individual columns (e.g. "email_last_modified_date") and have a trigger function enforce that the column is set correctly to achieve this. A more flexible and useful approach is usually constructing an audit table, with a trigger on the "user" table saving the changed columns for every UPDATE, or the contents of the whole row for every DELETE. This is where json, jsonb, or hstore types come in handy, if you'd like a single audit table capable of dealing with several tables, or capable of dealing with many columns from a single user table without needing to know the structure of the user table.


Table size and table partitioning on a single drive server

Kendra Little just released an excellent article on the topic: How To Decide if You Should Use Table Partitioning.

  This is usually the first question people have:  “Is my table big enough?”  My response is: “Let’s talk about why
  you’re interested in table partitioning. What is the problem you’re
  experiencing?”

Go over the article linked, is very good. It even has pictures of Unicorns! Hopefully it will shed some light for you.


About Apex SQL Refactor and SSMS keyboard shortcuts being broken

Just got feedback from the Apex support team.

Apparently this is a known issue and they are working on it. I asked them to contact me so I get notified when a workaround/patch gets released that addresses this problem.


Displaying number before decimal points in oracle

Example:
SQL> select to_char((2 * 2.5) / 100) from dual;

TO_
---
.05

SQL> select to_char((2 * 2.5) / 100, '99990D99') from dual;

TO_CHAR((
---------
     0.05

SQL>

Update:
Number example:
SQL> select (2 * 2.5) / 100 as mynum from dual;

     MYNUM
----------
       .05

SQL> col mynum format 99990D99
SQL> select (2 * 2.5) / 100 as mynum from dual;

    MYNUM
---------
     0.05

SQL>

Number Format Models


Finding by ID in postgresql very slow on big table

Honestly I have never seen a single value btree lookup take this long.  I don't think your problem is simply in your query.  I think it is elsewhere.

First you say you have a lot of writes.  This probably means you are constantly pushing a lot of stuff out of the buffers and doing a lot of random disk I/O.  I would not be surprised if a lot of the bottleneck is in the general I/O and your lack of RAM.

For 18 million rows, these don't look very large.  They may all fit in memory.

At any rate the first thing to do if had access to the command line on the server would be to take CPU I/O wait vs user vs system time (you may be able to do that elsewhere in your code).  Also run explain with (ANALYSE, BUFFERS, VERBOSE) set so that you can see much more about what is going on.  These should give you an idea if your problem is underwhelming CPU activity, slow RAM, or lots of disk I/O but as it is, there is very little that can be done.

Another thing you can do is this:

VACUUM ANALYSE VERBOSE;

See if that speeds things up.


Create SQL Server user with SQL Server Authentification

Hope these links help to fix your problem ---

https://stackoverflow.com/questions/6643720/add-login-and-connect-to-sql-with-sql-server-authentication

https://movabletype.org/documentation/mt5/advanced/creating-a-sql-server-database-2.html

The message you are receiving basically means "I can't connect to SQL Server". It can be due to a number of issues:

The SQL Server service is not running
The SQL Server instance is not
configured to use TCP/IP to communicate

The database is using windows authentication and the user is not authorized orThe database is using mixed-mode authentication and the user/pass is incorrect  As for the usernames you are given, I would assume that the "administrator" username is local windows administrator of the machine SQL server is running on, and that the "sa" user is the default database administrator, that is installed when setting SQL Server to use mixed-mode authentication.  So, I would recommend to try connecting to the database using Mixed-mode authentication, with the  "sa" username the "anotherpassword" password.


You can do that using a CASE expression:

select a,b,c,d,
       case (a + b * 2 + c * 4 + d * 8)
          when 0 then 'none'
          when 1 then 'a'
          when 2 then 'b'
          when 4 then 'c'
          when 8 then 'd'
          when 3 then 'a-b'
          when 7 then 'a-b-c'
          when 5 then 'a-c'
          when 9 then 'a-d'
          when 6 then 'b-c'
          when 10 then 'b-d'
          when 14 then 'b-c-d'
          when 12 then 'c-d'
          when 15 then 'a-b-c-d'
          else 'error'
        end as present
from the_table;

This creates a "bitset" with one bit for each column, then tests for the values that uniquely identify each combination.

This assumes that there are no NULL values in those columns.

Online example on db<>fiddle

Another option might be to concat expressions:

select concat(case when a = 1 then 'a-' end,
              case when b = 1 then 'b-' end,
              case when c = 1 then 'c-' end,
              case when d = 1 then 'd-' end)
from the_table;

That leaves an ugly dangling - at the end, but you could probably remove that with a combination of substring() and/or replace() calls.


PostgreSQL socket not listening on the right one

Try the following command:

createuser -h 127.0.0.1 -s new_role

And if you want to connect your db by using psql interface:

psql -h 127.0.0.1 db_name role_name


That's because of the set linesize 1000 clause.
That one is making all your lines in the spool file to be 1000 rows long, even if there is no such data.

Try this from a clean SQL sqlplus session:

set linesize 1000
spool test.txt
select * from dual;
spool off
exit

Then, go back to shell and open the test.txt with any text editor, move to the lines belonging to the SQL query results (not the lines with the prompt where you wrote the query) and check it's length.

Those 3 lines will be 1000 bytes long.


SQL Server 2014 (12.0.4439.1) to SP2 (12.5000) service start failure

We have SSISDB installed and it's in an AG. I ran the upgrade package on the secondary and received an error

Secondary user databases in an availability group are not available to be written to (readable at best). Thus the upgrade failed as the database is part of the AG and not able to be written to.

  How do I go about bringing this thing back up? Do I actually need to restore the master DB?

I haven't tested it but you should be able to recover using -m, remove the SSISDB from the AG, then restart as normal.

Going forward you'll want to remove the SSISDB from the AG before running the upgrade and bringing it back in after the upgrade. Is it a pain? Yep. Will it stop you from having issues? Yep.


Which way should be selected when move from mysql to mariadb?

We can't do anything without a problem except point you to the official information,

How to migrate from MySQL to MariaDB on Linux in five steps
Migrating to MariaDB

Generally the further you are from an original fork the more complexities you'll encounter in the migration. Therefore the earlier the version of MySQL the fewer features of MySQL that are MySQL-specific that you'll have to contend with. Upgrading to a newer of MySQL before the migration to MariaDB is likely to make your job harder.


detect what columns to select depending on a column value of the same table

Sounds like you need to use CASE WHEN expression, which is well documented. So case when x = y then select this else select that etc.


Those errors are corrupt pages in the actual table themselves.  You will need to restore the table.  There's no way to repair the issue without loosing data as the corruption is in the base table.


#1044 - Access denied for user '#username'@'10.%' to database '#database'

have you tried using a password for your user also, when granting privileges?

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'IP-of-DBServer' identified by 'password for mySQL';


Need help with reducing cost of view's group by

I can't see your indexes, but something worth trying is to grab the copy count in a subquery or CROSS APPLY:

SELECT Product.title, CONCAT(Author.firstName, ' ', Author.lastName) as Author_name, ProductType.pType as Product_type, Product.lendable, BC.Copy_count
FROM Product
CROSS APPLY (
  SELECT COUNT(*)
  FROM BookCopy
  WHERE BookCopy.bisbn = Product.isbn
  AND BookCopy.Available = 'true'
  ) AS BC(Copy_count)
INNER JOIN Author on Product.authorId = Author.id
LEFT JOIN ProductType on ProductType.id = Product.typeId
WHERE BC.Copy_count>0;


How to connect to SQL Server using sqlcmd on Linux?

With the help of Dan Guzman's comment I got the connection working using a port.

I had a Windows machine with SQL Management Studio connected to the database in question, and used:

netstat -abn

Then I searched for ssms.exe for the connection details:

TCP    192.168.0.31:50777     192.168.0.78:49399     ESTABLISHED [Ssms.exe]

Alternatively log into the database server and look at the ports used by the instance in SQL Server Configuration Manager (SQL Server Network Configuration > Protocols for INSTANCENAME > TCP/IP > Properties > IPAll TCP Dynamic Ports).

The following then both worked:

sqlcmd -S 192.168.0.78,49399 -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"
sqlcmd -S SERVERNAME,49399 -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"


How to take SQL Server databases backup using .bat script file with time stamp

I'd recommend you look at some of the freely available backup code. Here are two possible options I would recommend, as both work on SQL Server Express Edition.

1) Ola Hallengren's backup code.

You can install Ola's code in a database on your server (ex, in a "DBA" utility database), and then backing up the databases will be done by executing a stored procedure. The stored procedure takes care of putting date/time stamps in the file name, and can also take care of cleaning up old backups (and more).

Then, you can use a PowerShell (or cmd file) to call Invoke-SqlCmd (or sqlcmd) to simply call the procedure with the proper parameters.

PowerShell:

Invoke-SqlCmd -ServerInstance SERVER02 -Database DBA -Query "EXEC dbo.BackupDatabase @Databases='testdb'"

cmd:

SqlCmd -S SERVER02 -Q "EXEC DBA.dbo.BackupDatabase @Databases='testdb'"

2) The Dbatools PowerShell Module

This module includes a simple Backup-DbaDatabase command that will perform the backup and include the date/time stamp in the file name. This has fewer bells & whistles than Ola's procedure, but if you don't need the bells & whistles, this simple solution might be just what you need.

Backup-DbaDatabase -SqlInstance SERVER02 -Databases testdb -Type Full -BackupDirectory \server2\backups


Aws RDS cpu Spike to 100% because of running set isolation command and set

Those are probably bogus.

If you have more than a few dozen connections, they will be stumbling over each other and everyone will progress very slowly.  Keep the number of threads to a realistic number.

autocommit=0 should be outlawed.  It begs the programmer to forget to issue COMMIT.  Without COMMIT, a disconnect (or certain other things) will lead to a ROLLBACK.  Meanwhile, the connections may be blocking each other unnecessarily.

To discuss this further, let's see more details about the "performance test".


Is it ok to let users add new columns and tables to your database?

Database design is both an art and a science. End users usually understand neither the art nor the science.  I don't let end users add tables or alter tables; I've never worked anywhere that allowed anything like that.

End users will almost invariably build things that look like either Word documents or Excel spreadsheets. They will only rarely build things that look like databases.

  The point is to create a management system for information within the
  company.

That's what a database is for. If your database is basically a table, as you seem to be saying, I think your users would be better off with a database.

As a database designer, I find these features to be indispensable. I'll give you the SQL keywords and concepts; you can look them up with Google if you like.

CHECK constraints
GRANT and REVOKE
Updatable views
Materialized views
Stored procedures
Foreign key constraints
"Exotic" indexes (function-based indexes, partial indexes, clustered indexes)
PRIMARY KEY and UNIQUE constraints

If you're not going to provide similar features, you should think hard about what might happen. If you don't provide something similar to GRANT and REVOKE, somebody will drop a table or delete rows they shouldn't touch sooner or later.

If you are going to provide similar features, you're reinventing the wheel.


How to get snapshot of changed data and store them on daily basis

Enable Change Data Capture on the tables of interest. Copy the rows thus flagged to another table that matches the definition of the first, but with an additional datetime column to show when it was written. Essentially a slowly changing dimension, type 2 in the Kimball nomenclature.

SQL Server 2016 introduced temporal tables. These combine the features of CDC and an audit table I mention above. Once defined, the system itself copies row versions to a history table as data changes. All history is retained until explicitly deleted. Each row has a time interval recorded showing when it was valid.


Updating values in table based on ranges defined in another table

One of two solutions

solution 1

If you are able to split the work in two update statements, first update all the matching revisions

UPDATE h
set h.majorVersion = d.majorVersion
FROM dbo.history h
INNER JOIN
dbo.definitions d
ON d.id = h.id
and d.revision = h.revision;

Then use a CTE and windowing functions to update the majorVersion's that are still 0 on the updated table

;WITH CTE
AS
(
SELECT ID,revision, MAX(majorVersion) OVER (PARTITION BY Value2) as majorVersionUpdated,majorVersion
FROM
(
    SELECT ID, majorVersion,revision
        ,COUNT(case when majorVersion = 0 then NULL else majorVersion END) OVER (ORDER BY ID DESC,revision desc) AS Value2
    FROM dbo.history
) a
)

UPDATE CTE
SET majorVersion = majorVersionUpdated
WHERE majorVersion = 0;

SELECT * FROM dbo.history;

An index like this could help

CREATE INDEX IX_id_revision
on dbo.history(id,revision)
include(majorVersion)

Result

SELECT * FROM dbo.history;

id                                 revision majorVersion
9F717823-B9CA-4C7B-97F9-7770AAAFB468    1   1
9F717823-B9CA-4C7B-97F9-7770AAAFB468    2   1
9F717823-B9CA-4C7B-97F9-7770AAAFB468    3   1
9F717823-B9CA-4C7B-97F9-7770AAAFB468    4   2
9F717823-B9CA-4C7B-97F9-7770AAAFB468    5   2
9F717823-B9CA-4C7B-97F9-7770AAAFB468    6   3
9F717823-B9CA-4C7B-97F9-7770AAAFB468    7   3
546EF185-54AC-4AF8-82C6-61EFA3202353    1   1
546EF185-54AC-4AF8-82C6-61EFA3202353    2   2
546EF185-54AC-4AF8-82C6-61EFA3202353    3   2

SQL Fiddle

solution 2,  still two updates but with a CROSS APPLY and a self join. The self join is not ideal.

First update all the matching revisions

UPDATE h
set h.majorVersion = d.majorVersion
FROM dbo.history h
INNER JOIN
dbo.definitions d
ON d.id = h.id
and d.revision = h.revision;

Then do a self join to update the other majorVersions to the next one that is not 0

UPDATE H
SET h.majorVersion = h2.majorVersion
FROM dbo.history h
CROSS APPLY(
SELECT MIN(CASE WHEN h2.majorVersion = 0
           THEN NULL
           ELSE h2.majorVersion END) as majorVersion
FROM
dbo.history h2
WHERE h2.id = h.id
AND h2.revision >= h.revision
) h2
WHERE h.majorVersion = 0;

SELECT * from dbo.history;

SQLFiddle

You would have to add BEGIN TRANSACTION ... COMMIT TRANSACTION to the updates if they have to be executed in one batch. The fastest way will depend on additional factors, such as your indexes & data, more than an example can show. YMMV


re-write active record query to PostgreSQL

I'm not sure about all your condition stuff but going on your textual description of the problem..

  Reason of writing this query, I have another table camera_shares which is related to cameras table with camera_id, I also want to get the count of camera_shares against a camera_id, How could that be possible to geth the similar results in PostgreSQL

You'll want something like this...

SELECT camera_id, count(*)
FROM cameras AS c
JOIN camera_shares AS cs
  USING (camera_id)
GROUP BY camera_id;


This is an ugly solution that may introduce other issues.  It is offered as a means to eliminate the lock you are experiencing.  Auto_increment is preferable to managing numbers in this manner.

The number could be managed in its own table incremented as needed.  This table could be locked at no detriment to the main table.  When write locked, other sessions are prevented from using the table, the locking session can then increment the number (and store it in a variable - something meaningful like @next_position) then releasing the lock immediately after the update.  The number is then used in your main query - no join, simply "LQ.initialPosition = @next_position".

You may still have contention on the position number table - but these locks shouldn't be held too long - only during the incrementation to ensure only one process increments the number at a time.

The locking scheme could be eliminated, but this would require knowing the current number first which would then be tested in the update's where clause to eliminate race conditions with other sessions.  A loop will be necessary to try again if the update doesn't work (another session made the update first).  This complicates the use of the table but reduces the potential for trouble.


How frequently you install Service Packs and/or Cumulative Updates will vary wildly based on your individual scenario. There is no objective one-size-fits-all answer.

The best general advice I could offer would be to follow Microsoft's advice. As of January 2016, Microsoft's advice is:

  we now recommend ongoing, proactive installation of CU’s as they become available.


Date a backup was taken even if restored to another server

The problem with using MSDB and Master is that those reference the server they reside on. So if you run those commands, it will refer to restores on the server you are querying and not the restores on the server you are referring to in this circumstance.

The backup files itself should contain the information you need however.

RESTORE HEADERONLY
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.bak' ;
GO

The above example is doing a check on a backup file and in there, I can see the BackupStartDate shows '2014-07-17 16:18:18.000' and BackupFinishDate shows '2014-07-17 16:18:22.000'.

When I use the query you had linked in the example referenced by Marc_c on Stack Exchange, I can see the same backup I stated above. The Create_Date is '2018-05-17 13:32:39.700' and the Restore_Date is '2018-05-17 13:32:39.357'. This illustrates my point that the Master and MSDB only refer to the local server, not the backup metadata information.

Query posted from Marc_c for preservation:

;WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

See the MSDN on HEADERONLY.

See the MSDN on Result Sets from HEADERONLY

I would create a procedure that would loop through the files you need and have it capture the output of the RESTORE HEADERONLY and then log that to the table for your reference later to meet your requirements.

Martin Smith on Stack Exchange with an OPEN ROWSET solution to logging the data.


Can I use a single stored procedure to operate on different schemas based on the executing user

You could probably use dynamic SQL,

CREATE PROCEDURE [StoredProcedures].[Insert_Table1]
AS

DECLARE @sql nvarchar(max)=N'
INSERT INTO table1 VALUES (NEWID());
INSERT INTO table1 SELECT col1 FROM StaticTable;
';

EXECUTE sys.sp_executesql @sql;

However, you should be aware that this is problematic for several reasons. The most obvious ones are:

If you're duplicating the entire schema for every user, there's probably something seriously wrong with your database design. Instead of giving users their own schemas, you should design permissions into the table (with a user column and row-level security),
It breaks ownership chaining (the user will now need permissions to the base tables, as opposed to inheriting rights from the stored procedure),
You need to manage SQL injection if your stored procedure accepts parameters that go into the SQL statement (preferably using parameterization),
Dynamic SQL has some other effects with regards to parameterization and performance that you should be aware of if you're dealing with larger OLTP-style loads.


Is there a way to tell SQLCMD not to output rows to text or a file?

This isn't an answer for SQLCMD specifically (I don't know of an option to discard results), but an option for testing the performance of a query and ignoring the network overhead or client-side rendering is to use the free SentryOne Plan Explorer. By design we drop the results at the server and don't bring them across, allowing you to focus on the actual performance on the query rather than the performance of your network or the power of your desktop.


Downsides of switching Master to Simple recovery?

It does not really make sense to have the master database in FULL recovory. The installation process leaves the database in SIMPLE recovery, so this means that somebody changed it afterwards because they could.

I do not think that you perform operations on the master database that require point-in-time recovery and if you do I strongly suggest that you rethink this strategy. Also, take into account that a database in FULL recovery needs to back up its log regularly in order to avoid filling the log files. Is this happening? How are you planning to restore these log backups in case?

That said, I would change it back to SIMPLE without worrying too much about performance hits.


Triggers lost when changing table property in SQL Server Management Studio designer

You shouldn't be doing those changes in the GUI anyway but in T-SQL it's the only way you can reliably publish changes from dev > test > prod and it's the only way you can have source control on your database structure.

That being said, this happens when you change things that require the table to be recreated and is not a bug but documented behaviour.

Please see Error message when you try to save a table in SQL Server: "Saving changes is not permitted" which clearly states (emphasis mine):

  This problem occurs when the Prevent saving changes that require the
  table re-creation option is enabled, and you make one or more of the
  following changes to the table:

  You change the Allow Nulls setting for a column.
  You reorder columns in the table.
  You change the column data type.
  You add a new column.

  When you change a table so that you alter the metadata structure of
  the table, and then you save the table, the table must be re-created
  based on these changes. This may result in the loss of metadata and in
  a direct loss of data during the re-creation of the table. If you
  enable the Prevent saving changes that require the table re-creation
  option in the Designer section of the SQL Server Management Studio
  (SSMS) Options window, you receive the error message that is mentioned
  in the "Symptoms" section.

The work-around in that article is exactly what you should be doing:

  To work around this problem, use Transact-SQL statements to make the
  changes to the metadata structure of a table.


Here are my answers:

Define relationships based on your surrogate keys and not your business keys. This is because this is what the SQL engine will use to optimize queries and this is the reason you have surrogate keys. Since surrogate keys are often integers, this simplifies the joins from using different data types such as strings which are less efficient or even using composite keys. It might be worth defining nonclustered indexes on your business keys for lookups during your ETL process, but queries against the data warehouse should utilize your surrogate keys.
Here's how I would approach your fact tables. If the different grades are the same granularity and have most if not all of the same dimensions in common, I would put them in the same fact table. Otherwise, I would put them in separate fact tables. You can always define cubes with multiple measure groups or put a view in place that combines the different fact tables into one that makes sense down the road.
You definitely can link one dimension to multiple fact tables. This is referred to as a conformed dimension and is usually more common that linking a dimension to a single fact table.
Typically, I create a date dimension that is at a single day granularity and link it to any fact table that has a date field in it. However, in an education context, you might have the need for a different granularity such as just assigning a fact to a quarter dimension instead. If that's the case, you could build out a separate dimension table at the desired granularity. However, for your example of an exam, you would just link that exam record to the date dimension based on that specific day. Furthermore, you should build out your date dimension to have fields such as quarter, school year, etc. One other thing to consider in your example is that it might be useful to calculate the duration of the exam based on the start and end dates if they are in a date time format and you want to know how long it took student to complete a specific exam and to see if duration correlates with results in some way.

Let me know if you have any follow up questions!


Tuning performance of finding entities with fully matching attribute values

Right now the query that you have looks pretty good. It seems like performance scales with the number of attributes on the entity that you're checking against. For example, if I test with the following parameters:

DECLARE @search_entity_id INT = 2896182;
DECLARE @current_count INTEGER = 8;

The query that you have with MAXDOP 1 takes about 188 ms CPU time and 188 ms elapsed time on my desktop. If I change the parameters to these:

DECLARE @search_entity_id INT = 1324226;
DECLARE @current_count INTEGER = 4;

Then CPU time drops to 93 ms CPU and elapsed time drops to 90 ms.

One trick that can improve performance is to take advantage of batch mode execution. Batch mode execution processes rows up in batches up to 900 rows instead of row by row to cut down on metadata and access costs. It was designed to work with CCIs but can also work with data from rowstore tables. There are a few different tricks to get batch mode with rowstore queries. Here, I'll just create an empty NCCI against the temp table:

CREATE NONCLUSTERED COLUMNSTORE INDEX X_CCI ON #base (entity_id)
WHERE entity_id = -1 AND entity_id = -2;

As of SQL Server 2016, batch mode processing is supported for MAXDOP 1 queries and for the sort operator. Both of those enhancements are helpful for this query. After creating the index we can see the difference in the query plan:

The previous 8 row example now executes in about 110 ms of CPU time and 115 ms of elapsed time. The 4 row example executes in about 62 ms of CPU time and 60 ms of elapsed time. I got a savings of around 33% of my machine, but I don't know if that's good enough for your workload's requirements.


You didn't specify the LOGIN right:

ALTER ROLE portal WITH LOGIN;

If you use CREATE USER instead of CREATE ROLE the LOGIN right is granted automatically; otherwise you must specify it in the WITH clause of the CREATE statement.

Superusers do not inherently have the LOGIN right; a permissions check is still performed for superuser for this right. The reason for this is so that you can have a superuser role that you can SET ROLE to, but cannot be connected as directly.


How create a table inside the selected database in PostgreSQL?

But this does not add the mytable inside the mydb. How to add the table inside the database mydb?

You have to connect to the database mydb,

psql -d mydb

Then run the command


You can just remove the WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd') part:

DECLARE @XML XML

SET @XML =
'<NodeA>
<NodeB verzePis="">
    <NodeC1 attrA="Hello" />
    <NodeC2 attrA="World" />
  </NodeB>
</NodeA>
'

;--WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd')

    SELECT
        r.value('fn:local-name(.)', 'nvarchar(50)') as SectionName,
        r.value('@attrA','NVARCHAR(250)') attrA
    FROM    @XML.nodes('/NodeA/NodeB/*') AS t(r);

produces

+-------------+-------+
| SectionName | attrA |
+-------------+-------+
| NodeC1      | Hello |
| NodeC2      | World |
+-------------+-------+

Please note, WITH XMLNAMESPACES does not create a common table expression, it just declares an optional namespace


Database diagrams have been re-added as of 18.1 in SSMS.


Please check the link below for the answer.

https://dba.stackexchange.com/a/36043/20734

Thank You


Is there a reliable way to determine when you should run DBCC CLEANTABLE to reclaim space?

The solution I'd think of for this problem is to run weekly a job that will run sp_spaceused for all tables in a database and save this data in a table. If there are differences in size for each table greater than..let's say..10%, I would run the dbcc cleantable.

My code to loop through table sizes looks like that:

if OBJECT_ID ('tempdb.dbo.#temp') is not null
    drop table #temp;

if OBJECT_ID ('dbo.BigTables') is not null
    drop table dbo.BigTables;
go

CREATE TABLE [dbo].[BigTables] (
    [table_name] [sysname] NOT NULL,
    [row_count] [int] NULL,
    [col_count] [int] NULL,
    [data_size] [varchar](50) NULL,
    [Date] [datetime] NOT NULL,
    [DBName] [nvarchar](128) NULL
);
GO

CREATE TABLE #temp (
    table_name sysname ,
    row_count int,
    reserved_size varchar(50),
    data_size varchar(50),
    index_size varchar(50),
    unused_size varchar(50)
);
go

INSERT     #temp
EXEC       sp_msforeachtable 'sp_spaceused ''?'''

insert into dbo.BigTables
SELECT     a.table_name,
           a.row_count,
           count(*) as col_count,
           a.data_size,
           getdate() as [Date],
    'MY DB' as DBName
FROM       #temp a
INNER JOIN information_schema.columns b
           ON a.table_name collate database_default
                = b.table_name collate database_default
GROUP BY   a.table_name, a.row_count, a.data_size
ORDER BY   CAST(Replace(a.data_size, ' KB', '') as integer) desc

DROP TABLE #temp;

Select * from dbo.BigTables;

Now you only need to build the logic that will verify what would be the size change through the week and schedule it.


Postgres 9.1 query plan not using check_constraints on partitions after large data load

A couple comments given the answer here.  First PostgreSQL currently does not check the where clause functions to see if all equality constraints can be met properly on all tables.  So you have to specify the condition for the partition often manually in the where clause.

A major point I would make here would be that if you are not doing a lot of frequent bulk operations, you may find that partitioning is not a net win here.  You may find that partial indexes get you what you want in a more flexible manner.  For example you could have a series of partial indexes:

CREATE INDEX markets_0_idx markets (id) WHERE zip between '00000' AND '09999';

and so forth.  However for a single zip-code lookup you are probably best to have just an index on zip.  Premature optimization is the root of all evil.


In a traditional Inventory Warehouse System or Warehouse Management System (WMS) whenever a new Item is put on hand (i.e. created or purchased, and therefore added to Stock) a unique identifier must be created, usually a serial number, before you can enter it into Inventory and adjust your quantity on hand. In the context of a Car Showroom, I would imagine this would the be the VIN instead. (I'm not an expert on cars but I believe that's a unique identifier.)
Even when an item is received in batch quantities, that many unique identifiers need to be added to your Inventory before you can add the quantity to your quantity on hand. Therefore if your Purchase table has a one-to-many relationship with how many cars a Purchase can buy, because of batches, then you can't directly relate it to your Inventory table (yet). Rather you should have a separate table such as PurchaseLines which is many-to-one with your Purchase table, and has a row with the unique VIN for each car that was purchased. (This is even a simplification, as a real WMS actually normalizes this further into at least 2 more tables for ledger purposes, but I think that's outside the scope here, and this should be sufficient to what you need.) Your InventoryDetails table would then contain the VIN as well and directly relate back to the PurchaseLine it came from.
Additionally you should have just an Items table that defines the criteria of your cars, so you can generically refer to your quantity on hand by Item. As I'm sure you don't want to track the individual quantity of a VIN which will always be 1 (since my changes above will result in that). Instead if you have an Items table with fields like Model, Description, Color, MFYear, etc then your InventoryDetails table becomes more normalized and only stores an ItemId field instead (one-to-one with the Items table). Then your Inventory table can now track quantity for an ItemId instead which would be the relationship between InventoryDetails.
Finally, the concept of an ItemId becomes universal throughout the Warehouse Management System. It is like your core unit of measure. It should be part of your Purchases (in PurchaseLines) and your Sales (in an equivalent SalesLines table as well), and is a way to establish what item was transacted on.

Additional Resources:

Example ER Diagram of an Inventory Management System


SQL - referential integrity - Foreign key and Check constraint

To answer the question of whether they are equivalent, you need to declare K. Assuming:

create table K (x int not null primary key, w int, unique(w));
insert into K (x) values (1);
insert into K (x) values (2);

The following would satisfy the CHECK constraint:

insert into R1 (a,b) values (1,1);

because:

CHECK (b in (SELECT w FROM K))

evaluates to:

CHECK (b in (null, null)) <=> CHECK (null)

The rule is that the constraint must not evaluate to false. I don't know any DBMS that support subqueries in CHECK constraints, so I can't verify that.

I tested the foreign key variant against PostgreSQL 11, and the insert is rejected

 ERROR:  insert or update on table "r1" violates foreign key constraint "r1_b_fkey" DETAIL:  Key (b)=(1) is not present in table "k".


Use pgadmin (Open source)
https://www.pgadmin.org/

It has the same comofort as phpmyadmin, mysql  workbench ssms, and with some kings.


How to set current sequence while converting primary key serial(integer) to bigserial(bigint)

PgAdmin4 has no option to modify graphically but as per PostgreSQL Document, we can alter the sequence data type to smallint, integer, and bigint

Alter table column & sequence data type and set max value for the sequence

ALTER SEQUENCE public.case_audit_case_audit_uid_seq AS bigint;
ALTER SEQUENCE public.case_audit_case_audit_uid_seq MAXVALUE 9223372036854775807;
ALTER TABLE public.case_audit ALTER COLUMN case_audit_uid TYPE bigint;


Your query is equivalent to this one:

(SELECT * FROM memtable1)
UNION ALL
(SELECT * FROM table2)
LIMIT 1000 ;

The query asks for all the rows from memtable1, without any limit. Then the UNION is applied and the LIMIT 1000 is applied to the result of the UNION. It seems that the execution plan is not very efficient, and getting the rows of both tables in memory first (so 100 thousand rows) and then applying the limit.

If you want only 1000 rows from each table, try this:

(SELECT * FROM memtable1 LIMIT 1000)
UNION ALL
(SELECT * FROM table2 LIMIT 1000) ;

And if you want up to 1000 rows from both tables:

(SELECT * FROM memtable1 LIMIT 1000)
UNION ALL
(SELECT * FROM table2 LIMIT 1000)
LIMIT 1000 ;


You can create several test types with sysbench, but if you look at the typical OLTP tests for SELECTS and INSERTS you will see that the queries executed are similar in pattern, but with random values for each query (not identical every time).

If they were identical (something that you could test if you wanted), structures like the query cache and buffer pool would be very effective, invalidating the reason for the stress test.


MySql command line cyrillic Iinsert result is corrupted

By default MySQL server is using latin1 character set for each incoming connection. Latin1, as you might know, does not support cyrillic symbols.

The simplest solution is to switch so called 'connection character set' by running SET NAMES 'utf8'; in the beginning of each connection.

For example, this query should work:

SET NAMES 'utf8';

CREATE TABLE `category` (
  `catName` varchar(40) NOT NULL DEFAULT '',
  PRIMARY KEY (`catName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into category(catName) values ('привет русско-говорящему серверу');

To learn about character sets in MySQL and how to make the above change permament see here.


Is replication a suitable method to move a SQL Server database to new hardware?

Another option (similar to what happens in replication but more manual but less setup) is to do a FULL backup, restore over the network (with NORECOVERY) and continuously do Transactio Log backups with automatic restores. This would cut your downtime to only the last Tranlog Restore...


Does .bak file also contain backup of ldf file?

A full backup will contain all used pages in both files (you can convince yourself of this by restoring a full backup: the new files end up the same sizes with the same amount of content, which means the backup must have contained the information required to achieve that). Both files, especially the transaction log, will contain block that are allocated from the filesystem but not currently in allocated to an object in the database so this will be smaller than the combined size of the actual files.

To get an indication of the size of a full backup, run EXEC sp_spaceused on that database. For example on one of my databases this returns database_size = 70015.81 MB and unallocated_space = 2768.83 MB so a full backup will be approximately 67247 MB in size. With a log the size of your example I expect that unless you've not taken any backups recently there will be a log of space unallocated in the log files that therefore won't affect the backup size.

If you are using SQL Server 2008R2 or later (or 2008 Enterprise Edition) then you can compress the backup with the WITH COMPRESSION option which will generally make it much smaller. This will take CPU time of course, but may actually make the process of taking the backup faster by reducing the amount of IO involved.


MySQL InnoDB table accepts Non-Existent Foreign Key

Please check that the FOREIGN KEY is in the table design of your table B.

EXAMPLE

CREATE TABLE projects (
    StaffID INT NOT NULL,
    ProjectName VARCHAR(20) NOT NULL,
    Allocation INT NOT NULL,
    PRIMARY KEY (StaffID, ProjectName) ,
    FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
);

Facts

Table A is the table Staff
Table B is the table projects
FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE requires StaffID in projects to reference a row in Staff with the same StaffID

Your Original Question

CREATE TABLE `competicao` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 `CompeticaoGeral_ID` int(10) unsigned NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

You need a foreign key reference

CREATE TABLE `competicao` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 `CompeticaoGeral_ID` int(10) unsigned NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`),
 FOREIGN KEY (CompeticaoGeral_ID) REFERENCES competicaogeral(ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The SQL to do this would be

ALTER TABLE competicao ADD FOREIGN KEY (CompeticaoGeral_ID)
REFERENCES competicaogeral(ID) ON DELETE CASCADE;

GIVE IT A TRY !!!


Deny insert/delete for all users in SQL Server 2005

It would be confusing if you granted someone db_datawriter and then denied them delete rights.  So if you can, remove them from the db_datawriter and then grant them the rights they need:

create role role_MyApp
grant select, insert on Table1 to role_MyApp
...
exec sp_adduserrole 'role_MyApp, 'User1'


How safe are PostgreSQL CONCURRENTLY option

Yes, that is safe, meaning that it cannot damage your database and that the index will be correct once the statement succeeds.

Creating an index uses I/O and CPU resources, that is unavoidable.

If CREATE INDEX CONCURRENTLY fails, it will leave behind an invalid index that you should drop, since it uses space.

CREATE INDEX CONCURRENTLY will require an ACCESS EXCLUSIVE lock on the table, but differently from a normal CREATE INDEX which locks the table for the whole duration of the operation, it takes only a very short lock at the very end. This is normally not a problem, but it can be a problem if you have long running transactions that use the table: CREATE INDEX CONCURRENTLY will have to queue behind these transactions, and all later statements will have to queue behind it.

So make sure you have no long running transactions.


Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

Restarting the server should be enough - those worktables should clear out. But I'd probably start it up in single user mode (-m) to prevent other processes from creating worktables before you successfully remove those files. Then redefine the files required for tempdb; perhaps deleting unnecessary files, changing sizes, etc. You should also ensure you have an even number of files, that they're all set to the same size, and that they all have the same autogrowth settings (in MB, not %). And it might be a good time to consider TF 1117 and TF 1118 as well (starting point).

I'd be very wary about the suggestion to just delete the files from the file system before starting SQL Server again - it might not start at all.

(I'd also be curious about what the actual problem is, though. Having too many files doesn't hurt you, really.)


First you should clarify the relationships between your entities by asking these questions:

Can a facilitator of a group be a user that is not a member of that group?
Or a facilitator must always be a member of that group?
Do groups always have a facilitator? (your design indicates: yes)
Does every group have one facilitator only? (your design indicates: yes)
Or some groups may have more than one?

If the answer to the first question is "Yes", then the foreign key constraint should be referencing User (and you have no circular path, only multiple paths in your design.)

If the answer is "No", then the foreign key constraint should be referencing WorkGroupMember, with a composite foreign key (and then you have a circular path):

FOREIGN KEY (GroupID, GroupFacilitatorID)
  REFERENCES WorkGroupMember (GroupID, UserID)

The circular design makes it difficult to be implemented in many DBMS (because the constaints need to be DEFERRABLE and that is not implemented in many of them.)

Another solution would be (as proposed by Steve Pettifer in his answer), to add a IsGroupFacilitator column in the WorkGroupMember and a partial (filtered) unique constraint, to enforce that each group have at most one facilitator:

UNIQUE (GroupID)
  WHERE (IsGroupFacilitator = 1)

This way however, you cannot enforce that all groups have a facilitator (not with constraints at least, you can still enforce it by taking care of it in the INSERT/UPDATE/DELETE statements)


Use the performance counters Transactions/sec and Write Transactions/sec documented here:

https://msdn.microsoft.com/en-us/library/ms189883.aspx


Installing MySQL from a noinstall Zip Archive - Should I Overwrite System Tables and Data?

After talking to some MySQL admins, I came to a conclusion on how to go about this. We can do the below steps:

Take a MySQL dump of all the databases
Overwrite all the installation folders, including the database folders and files
Re-apply the MySQL dump backup
Run mysql_upgrade

However, you may notice that there's no conceptual difference between the steps above, and simply not copying over the data file as part of the installation.

If you avoid copying over the data file, the steps become the following:

Overwrite all the installation folders, excluding the data folder.
Run mysql_upgrade


Super simple mysql query taking too long

Create a compound index as follows:

ALTER TABLE autorepair ADD INDEX specialty_zipcode_ndx (specialty,zipcode);

and drop the single column specialty index

ALTER TABLE autorepair DROP INDEX specialty;


too many entries for one single session and serila# in V$Session_longops

you should review your query,  missing s.sid = sl.sid.  Besides, wher yout lokk at v$session_longops and find many rows, they all meet the other conditions - sl.elapsed_seconds > 25*60 and sofar<>totalwork ?


Unfortunately as mustaccio mentioned, we don't answer licensing questions, partly because it can be situational. Your best bet is to talk to Microsoft directly or work with a Microsoft licensed VAR. That being said, in general what you said is true if you're doing Core (not CAL) licensing, you would need a 12 core license (licenses are sold by 2-core increments).


Grant usage partially on schema to user on Postgres

I suggest you create a dedicated schema for this user (if it does not exist already), typically with the same name as the name of the role.

CREATE SCHEMA this_user;
REVOKE ALL ON SCHEMA this_user FROM public;
GRANT USAGE ON SCHEMA this_user TO this_user;

The default search_path in typical Postgres installations is "$user",public. So the new schema is automatically the "current" schema for this_user. If your installation is different, consider setting it explicitly:

ALTER ROLE this_user SET search_path=`"$user",public`;

More about the search_path in this related answer on SO:

How to create table inside specific schema by default in Postgres?

Create a VIEW in this schema:

CREATE VIEW this_user.the_table AS
TABLE other_schema.the_table;  -- shorthand for "SELECT * FROM"

GRANT SELECT, INSERT ON this_user.the_table TO this_user;

You may need privileges on the SEQUENCE additionally, if a serial column is involved. See:

Explicitly granting permissions to update the sequence for a serial column necessary?

The same is not necessary for IDENTITY columns in Postgres 10 or later.

Since Postgres 9.3 simple views like the above are automatically updatable. Per documentation:

  Simple views are automatically updatable: the system will allow
  INSERT, UPDATE and DELETE statements to be used on the view in the
  same way as on a regular table. A view is automatically updatable if
  it satisfies all of the following conditions:

Then REVOKE all privileges from public, that should stay hidden from the public (including this_user).

More about default privileges and the basics:

Why can a new user select from any table?


Confused about the Oracle CONTROL_FILES initialization parameter

When you see Modifiable you should read MEMORY Modifiable.  For ALTER SYSTEM then this would mean can the parameter be modified in memory for the system.  The Database Reference document you site includes part of this information:

  Modifiable Specifies whether the parameter can be changed for the current session (by an ALTER SESSION statement) or for all
  sessions in the current instance (by an ALTER SYSTEM statement):

  ALTER SYSTEM can be used to change the value in the server parameter file (SPFILE) of any initialization parameter. Such a change takes effect only in subsequent instances. The parameter descriptions indicate only those parameters that can be modified for the current instance.

To understand what will happen in entirety you have to understand the SCOPE parameter and what happens when no SCOPE is specified.  From the SQL Language Reference we can see that the ALTER SYSTEM statement can have three different SCOPE specifiers or none at all.

  SCOPE The SCOPE clause lets you specify when the change takes effect.
  Scope depends on whether you started up the database using a
  traditional plain-text parameter file (pfile) or server parameter file
  (spfile).

  MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you
  started up the database using a parameter file (pfile), then this is
  the only scope you can specify.

  SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down
  and started up again. You must specify SPFILE when changing the value
  of a static parameter that is described as not modifiable in Oracle
  Database Reference.

  BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists
  after the database is shut down and started up again.

  If a server parameter file was used to start up the database, then
  BOTH is the default. If a parameter file was used to start up the
  database, then MEMORY is the default, as well as the only scope you
  can specify.

So, since you were able to specify a scope of SPFILE, we can infer that you are using an SPFILE, so the default for the first ALTER SYSTEM when you did not specify a scope was BOTH which would fail because the parameter is not MEMORY modifiable.  You can prove this by repeating your first ALTER SYSTEM and adding SCOPE=BOTH or SCOPE=MEMORY both of which will also fail.


Fulltext match() against() returning empty set

I found the solution.  Thanks Anyways

See https://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html:

The search result is empty because the “search word” is present in at least 50% of the row entries, and so is effectively treated as a stopword.

my solution was to add more db entries so that the search term is less than 50% of the of the rows values.


Sometimes, the lack of an uint type in SQL Server can come in handy. If Order-id is defined as identity(1,1) in DB2, you likely do not have any negative Order-ids.

Consider importing all the legacy DB1..Orders.Order-id fields as the same number, but multiplied by -1. If there's a FK to Order-id in DB1, you would use the same multiple-by-negative-one conversion when those records are loaded into their corresponding tables in DB2.

With this method, you also have the benefit of easily identifying the legacy orders originally from DB1 because their Order-id is always negative (just be sure to document this.)

Edit - added an example

-- Assumptions:
-- DB1 and DB2 are on the same server
-- The table Orders is in the dbo schema
-- Order-id is an integer-type in DB1 and DB2
-- Order-id >= 0 in DB1 and DB2
-- Order-id is an identity(a, b), where a and b >=0 in DB2

-- Overview: Insert records from DB1 into DB2, but reverse the
-- sign of Order-id (i.e., positive to negative).

Use DB2
Go

Set Identity_Insert dbo.Orders On;

Insert dbo.Orders
(Order-id, col2, col3)

Select (-1 * Order-id), col2, col3
From DB1.dbo.Orders;

Set Identity_Insert dbo.Orders Off;
Go

-- Have related records? Insert them here.


Size of Log Backup is not reducing from past three days

Thank you markp, this was spot-on. There was a hanging transaction from past 3 days and that was the cause of this increment in log-size.

After investigation, we found out that there is flaw in application wherein application doesn't disconnect itself if user doesn't close them. We called user and once he closed his session, everything started smoothly and logisze came down from 9GB to 300MB.

I am posting this here as an answer, it could helps others in case they face similar issues. Thanks once again.


8k row overflow error when updating row of size 5k

The update fails for broadly similar reasons to those I explained in answer to your previous question.

In this case, because you are potentially updating multiple rows where a key column of a unique index is changed*, SQL Server builds a plan that includes Split, Sort, and Collapse operators to avoid intermediate unique key violations (see this article for details).

The Sort operator thus introduced encounters an intermediate row (including internal overheads) of a width that exceeds the limit, so an error is raised. Adding an OPTION (ROBUST PLAN) hint to the update query shows this is unavoidable:

  Msg 8619, Level 16, State 2, Line 681
  The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. Resubmit your query without the ROBUST PLAN hint.

The source/target data relationships are unclear to me from a brief look, but if you can guarantee that each update operation will affect at most one row, you can avoid the need for the Split/Sort/Collapse by adding TOP (1) to the update statement:

UPDATE TOP (1) [TBL_BM_HSD_SUBJECT_AN_148_REPRO_TARGET]
SET ...

This is a bit of a hack, though. Ideally, the update statement construction and indexes should provide enough information to the optimizer so it can see that at most one row will be updated. In particular, it is best practice to write update statements that are deterministic.

Given the odd design and lack of clarity in the question, I'm not even go to try to decipher the data relationships, or query & index changes that would be necessary to achieve this in detail.

* As Martin Smith pointed out in a comment, this would not be a problem in this particular situation if the table were not partitioned. Where the update sets the key to the same deterministic value in every row, Split/Sort/Collapse is not required, unless the table is also partitioned on that key. So, an alternative solution for this query is to not partition the table on sampletime.


I think you should try this change:
INSERT INTO sbm_sys.sy_version
    (mod_id, sub_mod, version, remark, update_date, `file`)
VALUES
    ('CS', 'sbm_sl.exe', '2015.11.07.1', 'IBS Sales App', '2015-11-10 11:34:13', NULL)
ON DUPLICATE KEY UPDATE
    `file` = IF(VALUES(version) > version, NULL, `file`),    -- first this column
    version = VALUES(version),                               -- then this one
    remark = VALUES(remark),
    update_date = VALUES(update_date) ;

The order of the columns matters, rather unfortunately and in contrast with the SQL standard. The MySQL UPDATE documentation states:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
Single-table UPDATE assignments are generally evaluated from left to right. ...


SQL SERVER 2016, Restore database get 15517

Can you check what login was mapped to the dbo user in the original SQL Server instance (where the database came from)?
My guess is that the dbo user points to a login that doesn't exists, and that is wnat is setting of this situation.
If that dbo (again, check on the original server) points to a Windows login, then just create that login. If it points to a SQL Server login, then create it using the same SID ad on the originating server. You can for instance use sp_help_revlogin on the originating server to script out the CREATE LOGIN command.
Where did you change the owner by the way? On the originating server? If so, then the login for the dbo user should definitely exist!
Perhaps it is because you who do the restore isn't a sysadmin?


Not in a T-SQL function, but executing stored procedures can be done in SQLCLR functions.

An example of using a .NET / C# SQLCLR user-defined function to execute a stored procedure is shown in the following article (which I wrote):

Stairway to SQLCLR Level 2: Sample Stored Procedure and Function


INSERT [TableName] SELECT * FROM [TableName]

That SPID is already killed and you will have to wait for the rollback process to finish. The estimated time is usually wildly inaccurate: don't worry about it. It will probably take from 1 to 2 times the execution time of the INSERT until the stage when it was killed.

If this happened in production, the answer would not be any different: wait for the rollback process to end. The database would not be consistent if the rollback operation was forcibly closed.


Have you used DBCC CHECKTABLE to see what the problem is? Check the REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD options to potentially repair the table. Database must be set in single user mode.


How to filter table 2 before joining to table 1 so that the result still includes all of table 1, even if no match in table 2?

If you want to reduce the number of rows you simply make a subselect and join that.
See following query:

you should check the subquery if it returns all the wanted rows.

SELECT p.post_title AS 'Download title', p.ID AS 'Download ID', MAX(l.download_date) AS 'Last download'
FROM wp_posts p
LEFT JOIN (SELECT *
           FROM wp_download_log
           WHERE user_agent NOT LIKE '%bot%'
             AND user_agent NOT LIKE '%crawl%'
             AND user_agent NOT LIKE '%panscient%'
             AND user_agent NOT LIKE '%scrapy%'
             AND user_agent NOT LIKE '%bubing%'
             AND user_agent NOT LIKE 'Go-http-client/1.1'
             AND user_agent NOT LIKE '%guzzle%'
             AND user_agent NOT LIKE '%jetty%'
             AND user_agent NOT LIKE '%resty%') l ON p.ID = l.download_id
WHERE p.post_type = 'dlm_download'
AND p.post_status = 'publish'
GROUP BY p.ID


Are there any extension/add-on tools to improve report-writing in SSRS?

SQL Server Data Tools is typically used to author SSRS reports when Report Builder is insufficient. You can download and install SSDT as a standalone tool (runs a cut down version of Visual Studio) or if you have VS installed already it installs as an extension.

Make sure you download the correct SSDT version for the version of Visual Studio you have, then you can create and deploy advanced SSRS reports/projects from Visual Studio.

SQL Server Data Tools

Older versions for previous VS releases


Is possible to insert an array inside another array in postgresql?

create table test (id int [])

✓

insert into test values ( ARRAY [1,2,3] )

1 rows affected

insert into test values ( ARRAY [ARRAY [1,2,3],ARRAY [1,2,4],ARRAY [1,2,5]] )

1 rows affected

select * from test

| id                        |
| :------------------------ |
| {1,2,3}                   |
| {{1,2,3},{1,2,4},{1,2,5}} |

db<>fiddle here

Really - multi-dimentional array is formed.

  I want to know if it is possible to insert an array of arrays in a table in postgresql, like {{1,2,3},{1,3,5},{9,10}} in a single cell of a table in postgresql?

No. 3rd element have 2 elements whereas first two have 3 elements. The error message

  ERROR:  multidimensional arrays must have array expressions with matching dimensions

will be produced.

But different dimensions in different records are allowed.


How to return a varchar type in a select of a free string. example: Select 'Test' as field from table

Maybe you just need to cast it.

 SELECT CAST( 'Text' as CHARACTER VARYING(99999) ) as FIELD FROM TABLE;

or using an alternative pg-specific casting syntax.

 SELECT 'Text'::varchar as FIELD FROM TABLE;

If you select a string constant and don't cast it to something it has type unknown.  but I'm guessing your connection library converts this to the preferred postgres string type 'text'


SQL Server Concurrent Insert/Update and Storing Data

The updated scripts in your question are equally safe.

Expanding on the answer to your other question, you could use the natural key in both WHERE clauses and assign the @OrderLineID variable in the UPDATE statement. Perform the conditional insert first if that is the most common case, otherwise perform the update first. This method will eliminate the SELECT query.

BEGIN TRANSACTION;

DECLARE @OrderLineId int;
INSERT INTO Orderline (...)
    SELECT ...
    WHERE NOT EXISTS (
        SELECT *
        FROM Orderline WITH (UPDLOCK, SERIALIZABLE)
        WHERE LastName = @LastName
            AND FirstName = @FirstName
            AND WebLoginDate = @WebLoginDate
            AND ProductName = @ProductName
    );
IF @@ROWCOUNT > 0
BEGIN
    SET @OrderLineId = SCOPE_IDENTITY();
END
ELSE
BEGIN
    UPDATE Orderline
    SET
         ...
         , @OrderLineId = OrderLineId
    WHERE LastName = @LastName
        AND FirstName = @FirstName
        AND WebLoginDate = @WebLoginDate
        AND ProductName = @ProductName
        AND LastModified<@CreatedTime;
END;

COMMIT TRANSACTION;

Be aware that when you mix using the natural key and surrogate key to locate rows, deadlock likelihood is increased.


Catch event of auto_shrink in SQL Server Log?

Pls see this link :

To summarize (and this worked for me) :

Find out the location of your default trace file using :

SELECT * FROM fn_trace_getinfo(default);

Then use that to query your default trace file :

SELECT top 10
    TextData,
    HostName,
    ApplicationName,
    LoginName,
    StartTime
 FROM
 [fn_trace_gettable]('C:\MSSQL\Log\log_1110.trc', DEFAULT)
 WHERE TextData LIKE '%SHRINK%';

You can see entries there for operations like DBCC SHRINKDATABASE etc.


How to get lexemes array from Postgresql ts_vector, sorted by lexems positional information?

This function seems to work, Please, suggest if there is a better way.

CREATE OR REPLACE FUNCTION public.tokenize_orig(
    p_string text,
    p_dictionary regconfig)
  RETURNS text[] AS
$BODY$
/* This function turns documents into array of lexemes, keeping original order of lexemes. */

select array_agg(lexeme)
from
    (
    select (arr).lexeme as lexeme
    from
        (select unnest(tsv) arr
        from to_tsvector($2, $1) as tsv) arr
        where array_length((arr).positions, 1) > 0
        order by (arr).positions
    ) as qry

$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;


Count two different columns from two different tables and throw into a single variable

I found myself a solution. Here is the query:

select i.item_cat, count(*) total, sum(case when i.item_cat = c.cat_id then 1 else 0 end) SumCat FROM `items` i left join `cats` c on (i.item_cat = c.cat_id) group by i.item_cat

It outputs:

item_cat | total | SumCat
    1    |   1   |   1
    2    |   2   |   2
    3    |   3   |   3

=========

EDIT: Found a better and (IMO) more efficient solution, which also outputs an empty categories (where no items are assigned to). Firstly, I have added a two more categories: "games" (id=4) and "cooking" (id=5), but did only insert a single item into a "cooking" category, leaving "games" category with no items assigned. Then, the query below did a really nice piece of work:

select c.cat_id, sum(case when i.item_cat = c.cat_id then 1 else 0 end) total FROM `cats` c left join `items` i on (c.cat_id = i.item_cat) group by cat_id

This will output:

item_cat | total
    1    |   1
    2    |   2
    3    |   3
    4    |   0
    5    |   1

So the solution above suits my needs completely and does exactly what I want it to do.


Debit Credit Balance calculating from previous records with Pagination MYSQL

As you are using the user-defined variables to iterate and so calculating the balnace, you have to set the initial variable ti the right amount

SET @variable = 0;
SELECT
-SUM(debit)  + SUM(credit) INTO @variable
FROM billing_ledger where date < '2021-01-01';
SELECT id, date, particulars, debit, credit
, @variable := @variable + (COALESCE(`credit`,0) - COALESCE(`debit`,0)) `balance`
FROM billing_ledger where date >= '2021-01-01' ORDER BY id asc ;

✓

✓

 id | date       | particulars           | debit | credit | balance
--: | :--------- | :-------------------- | ----: | -----: | ------:
112 | 2021-01-01 | Purchased Item: Eggs  |   200 |   null |    2700
113 | 2021-01-02 | Purchased Item: Onion |  2300 |   null |     400


How to find the actual space being consumed by indexes on a table in Oracle?

SELECT idx.index_name, SUM(bytes)
  FROM dba_segments seg,
       dba_indexes  idx
 WHERE idx.table_owner = <<owner of table>>
   AND idx.table_name  = <<name of table>>
   AND idx.owner       = seg.owner
   AND idx.index_name  = seg.segment_name
 GROUP BY idx.index_name

will show you the amount of space actually consumed by each index.  I'm not clear if that's exactly what sorts of overhead you're trying to account for and how you are distinguishing "used" and "allocated" in the context of an index.  If you want to account for free space in the index, you can use the DBMS_SPACE.SPACE_USAGE procedure to determine how many partially empty blocks are in the index.


I am using the security documentation to answer your questions:

No, a minimum password life cannot be specified. Only a PASSWORD_LIFE_TIME (maximum life time) can be specified. Depending on what you want to accomplish, PASSWORD_LOCK_TIME might be what you're looking for. (Source)
Yes, this can be configured. You can expire a password, the user will then be prompted that he needs to change his password on first login. Look into the PASSWORD EXPIRE mechanism. (Source)


Combining data from multiple large tables

You need to have information for all surveys, even the ones that have no answers, right?

;with surveys as
    (
        select SurveyID, Region, Gender from Table1
    )
, responses as
    (
        select t1.SurveyID, t2.AnswerNumber, t2.Rating, t3.AnswerNumber, t3.Expediture -- add other columns from the other tables
        from Table1 t1 left join Table2 t2 on t1.SurveyId = t2.SurveyID
                   left join Table3 t3 on t1.SurveyId = t3.SurveyID
                   -- add other tables up to the 25th
    )
select s.SurveyId, s.Region, s.Gender, r.*
from surveys s left join responses r on s.SurveyId = r.SurveyID


is there a max number of rows in a SQL Server 2008/2012 and MySQL table?

Microsoft has excellent documentation on the limitations of SQL Server

MySQL has a similar resource (warning - PDF) on their site.

As it stands, your question is far to broad and will likely be put on hold until you can narrow down your requirements.


SELECT on column value = X but if no rows available then any column value

Obtain non-'col=X' only if 'col=x' not exists:

SELECT TOP 1000 *
FROM table
WHERE col = CASE WHEN EXISTS (SELECT 1
                              FROM table
                              WHERE col = X)
                 THEN X
                 ELSE col
            END

Obtain 1000 records, 'col=X' first:

SELECT TOP 1000 *
FROM table
ORDER BY CASE WHEN col = X
              THEN 0
              ELSE 1 END


This may be the most efficient solution:

SELECT A, t1.B, t2.C
FROM   table1 t1
LEFT   JOIN table2 t2 USING (A)
ORDER  BY t2.C NULLS FIRST;

NULL values are sorted last by default. You can reverse that with NULLS FIRST. See:

PostgreSQL sort by datetime asc, null first?


SSIS 2016 conditional split with multiple columns in conditions

I was close, but no cigar.  If I moved the "upsert the production table" task outside of the ForEach container (but population the staging table with the merge join inside the ForEach container), everything works as expected.


what is "local block" in pg_stat_statements?

From https://www.postgresql.org/docs/9.4/sql-explain.html:

  Shared blocks contain data from regular tables and indexes; local blocks contain data from temporary tables and indexes; while temp blocks contain short-term working data used in sorts, hashes, Materialize plan nodes, and similar cases.


Sort name by Thai character is not working

You need to specify that it's a UTF-8 collation:

SELECT name
FROM product_categ
ORDER BY name COLLATE "th_TH.UTF-8";


Log Shipping failover when Primary is not accessible

The issues and complications you mention are ones I've wrestled with myself.  Quite a while back, I did my best to script out a solution to help make it easier to fail over and also to fail back.  I would suggest going to codeproject.com and searching for "Streamline Log Shipping Failovers" to look at the quick article I wrote there.  You can download the SQL script and see if it's helpful for your situation.  Essentially, it is a SQL script that you run on the Secondary and it dynamically generates the scripts you need to fail over and later fail back.

I have used it many times in a Production environment to perform DR tests.  I have also used it once in a true Production down situation and it really helps streamline the process.


Error: Relation ["table name"] does not exist

You try to create relations (constraint) before create parent objects, for example:

order #2:

CREATE TABLE Driving
(
Id           CHAR(11)         REFERENCES Driver ON DELETE CASCADE,
BusId        INT              REFERENCES Bus    ON DELETE CASCADE,

PRIMARY KEY  (Id, BusId)
);

but table Bus at this moment not created.

2 possible solutions:

Create tables in order - from parent to child (not always possible)
Create all tables without constraints and add all of them after


SQL Select taking too much time to execute

I think in a million records query, you have to avoid things like OUTER JOINS. I suggest you use UNION ALL Instead of LEFT JOIN.
As long as I think CROSS APPLY is more efficient than sub-query in the select clause I will modify the query written by Conard Frix, which I think is correct.

now: when I started to modify your query I noticed that you have a WHERE clause saying:  JoinedTable.WhereColumn IN  (1, 3). in this case, if the field is null the condition will become false. then why are you using LEFT JOIN while you are filtering null valued rows?
just replace LEFT JOIN With INNER JOIN, I guarantee that it will become faster.

about INDEX:

please note that when you have an index on a table, say

table1(a int, b nvarchar)

and your index is :

nonclustered index ix1 on table1(a)

and you want to do something like this:

select a,b from table1
where a < 10

in your index you have not included the column b so what happens?

if sql-server uses your index, it will have to search in the index, called "Index Seek" and then refer to main table to get column b, called "Look Up". This procedure might take much longer than scanning the table itself: "Table Scan".

but based on the statistics that sql-server has, in such situations, it might not use your index at all.

so first of all check the Execution Plan to see if the index is used at all.

if yes or no both, alter your index to include all columns that you are selecting. say like:

nonclustered index ix1 on table1(a) include(b)

in this case Look Up will not be needed, and your query will execute so much faster.


createdb needs to connect to a database to issue a CREATE DATABASE SQL statement, and by default it will use:

a Unix socket domain connection
the current username from the shell as the database user (so postgres in your case)
template1 as the database

When it does that, in your case you get this error message:

  createdb: could not connect to database template1: FATAL:  no
  pg_hba.conf entry for host "[local]", user "postgres", database
  "template1", SSL off"

It indicates that postgres as a database user does not have the permission to connect through a Unix domain socket (that's what [local] means).

This is unusual, since in general, postgres installers on Unix tend to install a pg_hba.conf starting with this rule:

local   all       postgres       peer

Meaning that the postgres Unix user has the right to connect locally to any database as the postgres database user, which suits the needs of administrating databases, including creating new ones.

In fact your pg_hba.conf does not look like a default one. The simplest way to proceed to immediately solve the problem of database creation would be to edit it,  add the above line as the first rule, and reload the postgres service.


As Lennart has suggested, use a minimal example and see what happens.

create table accounts(account_id int primary key, sales_rep_id int);

create table orders(order_id int primary key, account_id int);

insert into accounts values
(1, 321500),
(2, null),
(3, 2000),
(4, 3000);

insert into orders values
(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 2),
(6, 3);

There is a LEFT JOIN involved, that means it returns always all rows of orders table, plus those rows of accounts table with the same account_id. But, the WHERE clause filters final result to those with sales_rep_id = 321500.

order_id | account_id | account_id | sales_rep_id
-------: | ---------: | ---------: | -----------:
       1 |          1 |          1 |       321500
       2 |          1 |          1 |       321500

However the second query returns all rows of orders table, but only accounts with the same account_id and sales_rep_id = 321500.

order_id | account_id | account_id | sales_rep_id
-------: | ---------: | ---------: | -----------:
       1 |          1 |          1 |       321500
       2 |          1 |          1 |       321500
       3 |          2 |       null |         null
       4 |          2 |       null |         null
       5 |          2 |       null |         null
       6 |          3 |       null |         null

db<>fiddle here


repmgr: Can I replicate databases from two different masters to a third slave?

PostgreSQL's built-in replication (as used by repmgr) replicates entire PostgreSQL instances - with all databases. The transaction log is shared between all databases.

PostgreSQL can't merge transaction logs from another instance. Nor can it replicate only a subset of databases. You have to replicate exactly one instance to exactly one other instance.

If you want something other than that, there are a variety of other replication options such as Londiste, Slony-I, BDR, etc.


MySQL: reducing ibdata file size for MyISAM tables

MyISAM tables do not use ibdata files, so they won't affect the size of ibdata files.

From the MySQL Docs, here is how MyISAM stores data on disk:

  Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.


From the post here (user mapping disappearing):

"The only time I have seen something like this happen, was when the application DBAs where restoring test scenario DB backups over the existing databases, without telling us SQL Server SysAdmins what they were doing. This would of course wipe out all of our later user mappings to those databases. So, you might want to check for something like that... "

And in the same thread, a pointer to a script for knowing when the database was last restored.