Virtual Environment for learning I am a new DBA and my friend told me the best way to learn about becoming a DBA is to setup your own server (virtually or physically).

I don't have a spare machine laying around so I will need to set one up virtually.

My job uses RHEL 7.2 so I downloaded the same version of the OS.

My work uses Oracle 10, 11 and 12. Should I use one of these versions or should I install the latest?

Here's where my question

What size should I create the disk drive for this environment? (I plan on using it to practice RMAN, ASM, DataGaurd and RAC).
What should the size of the partitions be? (for education and will not be used for production)
What should my RAM, CPU config be (if it really matters). I know this stuff can be adjusted later on the fly. I am more concerned with the first one since changing partition sizes is an involved process.

If I am missing anything, please feel free to leave a comment or "answer the question".

This is a very broad question as the list you gave of what you want to practice would need multiple databases to learn dataguard and RAC.  Why do't you try Oracle's free trial of the cloud based databases?  You can make mistakes there and it will be easy to learn on the newest versions and when you have some more knowledge you can deploy Oracle Virtual Box at home for a test that will last longer.

Running an Oracle database on Virtual Box on a home PC could easily use four to six GB of memory if you are doing any thing intensive.  Storage is cheap these days so allocating at least 100Gb for the database files gets you started.

A typical enterprise disk setup is to mount separate drives

/u01 for the oracle product ~100 Gb
/u02 for datafiles ~100 Gb (but if you import data you could need up to 500 Gb)
/u03 for indexes ~100 Gb
/u04 for archive logs ~100 Gb and so on

This is probably overkill for a learning situation but, at the least, separate the oracle install from the data for easier management. Suggested disk allocations get you started but if you actually use it to store data you should ensure that you can extend the virtual disks.  In other words allocate 1 Tb of storage to the virtual machine and then you can extend the storage as needed.


How to design sql database with a subset of tables for each row in a main table? Consider a restaurant database where you have a table of restaurants (with some additional information) and for each restaurant (each row) in this table, you have a set of tables containing information (lets say menu etc..). Is it OK to have so many tables (with thousands of restaurants), or is there a better way to design such relationship?

for better imagination:

I'd go for something like:

restaurants table (stores all of your restaurants):
restaurant_id INT PRIMARY KEY
restaurant_name
etc

menus table (stores all of your menus):
menu_id INT PRIMARY KEY
menu_name

restaurant_menus table (stores which restaurants serve which menus):
restaurant_id INT, -- references restaurants.restaurant_id
menu_id       INT  -- references menus.menu_id

ingredients table (stores all available ingredients):
ingredient_id INT PRIMARY KEY
name
etc

menu_items table (stores each available menu item):
menu_item_id INT PRIMARY KEY
name
etc

menu_item_ingredients table (stores which ingredients make up a given menu item)
menu_item_id  INT, -- references menu_items.menu_item_id
ingredient_id INT, -- references ingredients.ingredient_id
name
quantity
etc

menu_item_items (stores which items are on a given menu):
menu_id      INT, -- references menus.menu_id
menu_item_id INT -- references menu_items.menu_item_id

menu_item_items and menu_items probably need better names (dishes for menu_items, perhaps?).


why is this postgresql query returning only zeros? select g.ad_id, count(1) as total,
(select count(1) from geoData g1 WHERE g1.ad_id=g.ad_id and
utc_timestamp > '2017-11-01 00:00:00' AND utc_timestamp < '2017-11-01 23:59:59') NOV1,
(select count(1) from geodata g2 where g2.ad_id=g.ad_id and
utc_timestamp > '2017-11-02 00:00:00' AND utc_timestamp < '2017-11-02 23:59:59') NOV2,
(select count(1) from geodata g3 where g3.ad_id=g.ad_id and
utc_timestamp > '2017-11-03 00:00:00' AND utc_timestamp < '2017-11-03 23:59:59') NOV3,
(select count(1) from geodata g4 where g4.ad_id=g.ad_id and
utc_timestamp > '2017-11-04 00:00:00' AND utc_timestamp < '2017-11-04 23:59:59') NOV4,
(select count(1) from geodata g5 where g5.ad_id=g.ad_id and
utc_timestamp > '2017-11-05 00:00:00' AND utc_timestamp < '2017-11-05 23:59:59') NOV5,
(select count(1) from geodata g6 where g6.ad_id=g.ad_id and
utc_timestamp > '2017-11-06 00:00:00' AND utc_timestamp < '2017-11-06 23:59:59') NOV6,
(select count(1) from geodata g7 where g7.ad_id=g.ad_id and
utc_timestamp > '2017-11-07 00:00:00' AND utc_timestamp < '2017-11-07 23:59:59') NOV7,
(select count(1) from geodata g8 where g8.ad_id=g.ad_id and
utc_timestamp > '2017-11-08 00:00:00' AND utc_timestamp < '2017-11-08 23:59:59') NOV8,
(select count(1) from geodata g9 where g9.ad_id=g.ad_id and
utc_timestamp > '2017-11-09 00:00:00' AND utc_timestamp < '2017-11-09 23:59:59') NOV9
FROM geoData g
group by g.ad_id ;

The psql says its ok, it takes almost an hour to run (it's 35gigs of data in the table) but it returns all zeros in the subqueries

You know you don't need to use all those subqueries, right?

select g.ad_id, count(1) as total,
count(case when utc_timestamp > '2017-11-01 00:00:00' AND utc_timestamp < '2017-11-01 23:59:59' then 1 end) NOV1,
count(case when utc_timestamp > '2017-11-02 00:00:00' AND utc_timestamp < '2017-11-02 23:59:59' then 1 end) NOV2,
...
FROM geoData g
group by g.ad_id ;


SQL Server permissions to create, restore, delete only some databases On MS SQL Server 2008 R2, I need to have user which can create, restore, DBCC CHECKDB and then drop databases. But he must not be able to access or delete specified databases on server (security reasons). Is this setup possible?

Yes it is possible through a couple permissions.

First - to create a database - You would need to grant the server level permission Create Any Database. This permission does just what it sounds like - the power to create a database. Note this is not giving the fixed server role of dbcreator to the login, since that fixed server role gives alter/delete permissions on any database. Create Any Database allows the login to have that power over databases they own, only. This will also give the ability to restore.

(So by doing "Create Any Database" ONLY - you are giving the ability to create a new database, but avoiding the ability to Drop or Alter ANY database that you'd get by using the dbcreator fixed server role)

To grant this "Create Any Database" permission -

use [master]
GO
GRANT CREATE ANY DATABASE TO [LoginName]
GO

For the ability to run DBCC CHECKDB and to drop databases - the fixed database role db_owner is sufficient. This grants all of what you are requesting. Please Note: You are also granting this user the ability to select, delete, truncate, update and insert with db_owner. I like to think of this as sysadmin within a database.

You only will apply this permission to the database you intend to have this ability on. This will allow you to control which databases the user can drop, restore or checkdb on. Any databases that you have not given the user these permissions on will be safe from this user doing these actions on

To do this you would first map the login to a database user:

USE [DatabaseName]
GO
CREATE USER [UserName] FOR LOGIN [LoginName]
GO

And then add that user to the db_owner role (Works in SQL Server 2012 and on):

ALTER ROLE [db_owner] ADD MEMBER [frank]
GO

For SQL Server 2008 you would have to use the system stored procedure to add a role member as a commenter below rightly pointed out:

EXEC sp_addrolemember 'db_owner', 'frank';

You can also do all of this through the GUI. The login information you would do through the security folder in SSMS at the instance level: Right click on the login -> Properties -> Securables. For the database level, you would do it through the security folder at the database level: right click and select new user -> choose the login you wish to user from your list of server logins/give the database user a name -> go to the membership tab to select the role membership.

SQL Server's help system, Books Online, is a fantastic resource for most permissions questions also - if you determine you need to assign other permissions. Simply do a search for the T-SQL command you wish to give permissions to and there is typically a Permissions section for that command in the article letting you know what permissions are required to do this action. You can look at the article for DBCC CHECKDB for an example - about 7/8 of the way down the article is the permissions section.


What are valid connection strings for SSMS login box? What are valid connection strings for SSMS login box?

Every once in a while I will have to connect to an instance of SQL Server with some specific configuration setting or in a DMZ where no host names are available or I'll have to connect to the Dedicated Admin Connection (DAC) via SSMS or SQLCMD. Because I rarely connect to a SQL Server via specific port or with the DAC, I'll tend to forget what the exact syntax options are to get a connection up and running. I'll then have to search the internet again for the umpteenth time to retrieve the exact connection strings and will regularly be frustrated that I can't find the information required.

Research

I'll read all the questions and answers previously posted on Serverfault, Stackoverflow, and DBA.

Serverfault

[1] DAC connection port SQL Server 2005 SP3
[2] Remote SQL server connection failure
[3] SQL Server 2008 R2 remote connection

Stackoverflow

[4] How to specify a port number in SQL Server connection string?

DBA

[5] DAC connection error

Microsoft

Additionally, I'll search the Microsoft site for relevant articles:

[6] How to configure SQL Server to listen on a specific port
[7] Configure the Windows Firewall to Allow SQL Server Access
[8] Surface Area Configuration
[9] How to configure SQL server to listen on different ports on different IP addresses?
[10] Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)
[11] Diagnostic Connection for Database Administrators

Brent Ozar

And Kendra Little has an article for some interesting aspects of DAC.
[12] The Dedicated Admin Connection: Why You Want It, When You Need It, and How To Tell Who’s Using It

On a side note: sometimes the DAC connection is also referred to as the ADMIN connection.

I find that in the end there is never an actual summary of simple connection strings to input into an SSMS login box, nor what to add to a SQLCMD.

This question and the following answers are intended to sum up what I found so far and to put the information into one single knowledge base article.

Actual Question

Given the following settings for a SQL Server Standard Edition:

Servername.....:    SERVERNAME
IP address.....:    123.1.2.3

1st instance (default instance):
CNAME/Alias....:    SERVERNAME-I01
Name...........:    MSSQLSERVER
Port...........:    1433 (TCP)
IP address.....:    123.1.2.3
DAC Port.......:    1434 (TCP)

2nd instance (default ip & port configuration according to [7]):
CNAME/Alias....:    SERVERNAME-I02
Name...........:    Instance2
Port...........:    dynamic (TCP)
IP address.....:    123.1.2.3
DAC port.......:    dynamic (TCP)

3rd instance (dedicated IP and port):
CNAME/Alias....:    SERVERNAME-I03
Name...........:    Instance3
Port...........:    1433 (TCP)
IP address.....:    123.1.2.4
DAC port.......:    dynamic (TCP)

Browser Service:    1434 (UDP)
Browser service:    ON

...what are valid connection strings for each instance that I can enter into the SSMS login box

...with the host name?
...an alias (CNAME)?
...with an ip address?
...for a "Dedicated Admin Connection" (DAC aka ADMIN:)?

Assumptions

The server firewall is either deactivated or configured according to the Microsoft documentation mentioned in 6, 7 or 8. This is also true for any network firewall inside the LAN (including the connection to a DMZ server).
The additional instances, apart from the default instance, have manually been configured to listen on different IP addresses according to 9 and in this example have been configured to listen on fixed ports 10.
In some cases DAC will have been enable according to 11 and 12.

There are various possibilities and I will try to answer them according to the numbered questions.

Valid connection strings for the SSMS login box are:

1st instance

Using the host name

SERVERNAME
SERVERNAME,1433

Using the CNAME/Alias

SERVERNAME-I01
SERVERNAME-I01,1433

Using the IP address

123.1.2.3
123.1.2.3,1433

For the Dedicated Admin Connection (DAC); depends on Browser Service status

a) Browser Service ON

ADMIN:SERVERNAME
ADMIN:SERVERNAME-I01
ADMIN:123.1.2.3

b) Browser Service OFF

SERVERNAME,1434
SERVERNAME-I01,1434
123.1.2.3,1434

2nd instance

Because the 2nd instance has a dynamic port for the SQL Server instance and a dynamic port for DAC, the ports have to be retrieved from the ERROR log file in advance before connecting to the SQL Server. See 7 for more information. In this example I will assume port 63390 is used for the SQL Server instance and port 63389 is used for the Dedicated Admin Connection. Microsoft recommends to fix the IP port, thus allowing you to properly configure a firewall 6.

Using the host name

SERVERNAME\Instance2 (Browser Service ON)
SERVERNAME,63390 (Browser Service OFF)

Using the CNAME\Alias

SERVERNAME-I02 (Browser Service ON)
SERVERNAME-I02,63390 (Browser Service OFF)

Using the IP address

123.1.2.3,63390 (Browser Service is ON or OFF)
Using the IP address without port, would connect you to the default instance

For the Dedicated Admin Connection (DAC); really depends on Browser Service status

a) Browser Service ON

ADMIN:SERVERNAME\Instance2
ADMIN:SERVERNAME-I02\Instance2
ADMIN:123.1.2.3\Instance2
Always requires instance name, because the CNAME or IP address references the same IP address as the default standard instance

b) Browser Service OFF

SERVERNAME,63389
SERVERNAME-I01,63389
123.1.2.3,63389

3rd instance

Using the host name

SERVERNAME\Instance3
Because this instance is using the same SQL Server port as the default instance, but a different IP address, the SERVERNAME,1433 notation would not work. You would be connected to the default instance.

Using the CNAME/Alias

SERVERNAME-I03 (Browser Service ON)
SERVERNAME-I03,1433 (Browser Service OFF)

Using the IP address

123.1.2.4
123.1.2.4,1435

For the Dedicated Admin Connection (DAC); depends on Browser Service status

a) Browser Service ON

ADMIN:SERVERNAME\Instance3
ADMIN:SERVERNAME-I03 (works because the CNAME references a dedicated IP address)
ADMIN:123.1.2.4

b) Browser Service OFF

SERVERNAME,63389 (works because the port references a dedicated port for DAC)
SERVERNAME-I01,63389 (works because the CNAME and port reference a dedicated instance/DAC)
123.1.2.4,63389

Browser Service

If the browser service is DISABLED or has not been started, then connections cannot be routed to the relevant port. In this case the DAC port has to be retrieved from the ERRORLOG file of the instance. You should find a line with something like Dedicated admin connection support was established for listening remotely on port 63389. for the DAC port. This port number is the port the DAC will listen on. You are required to supply the relevant port for the Dedicated Admin connection and the corresponding port for the SQL Server connection if your Browser Service is turned off. You do not have to add the instance name to the connection string when using the port, because the port is reserved for the instance.

In short the Browser Service is responsible for converting instance names to ports. If the Browser Service is not running, then you have to supply the port instead of the instance name.

Reference Material

Logging In to SQL Server (Microsoft | Docs | SQL Server)


MySQL keeps crashing MySQL keeps crashing randomly - recent errors in error logs says this

160626 21:50:42 [Warning] IP address '113.108.21.*' could not be resolved: Name or service not known
160626 22:42:57 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:10 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:25 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:41 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:43:54 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160626 22:44:09 [Warning] IP address '124.172.137.*' could not be resolved: Temporary failure in name resolution
160627  0:45:24 [Warning] IP address '117.79.84.*' could not be resolved: Name or service not known

160624  7:00:31 [Warning] IP address '58.221.62.*' could not be resolved: Name or service not known
160624  7:05:14 [Note] /usr/sbin/mysqld: Normal shutdown

160624  7:05:14 [Note] Event Scheduler: Purging the queue. 0 events
160624  7:05:16 [Warning] /usr/sbin/mysqld: Forcing close of thread 16955  user: 'amember'

160624  7:05:16 [Warning] /usr/sbin/mysqld: Forcing close of thread 16953  user: 'amember'

160624  7:05:16  InnoDB: Starting shutdown...
160624  7:05:22  InnoDB: Shutdown completed; log sequence number 195606046122
160624  7:05:22 [Note] /usr/sbin/mysqld: Shutdown complete

160624 07:05:22 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160624 07:05:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

In my.cnf are this lanes, which can resolve this somehow?

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M

Server have CPUs with 20cores and 258450MB of RAM

Solved with

[mysqld]
skip-host-cache
skip-name-resolve


Sql Server AG assigning wrong listener I have 2 instances of SQL Server on two Windows Servers, namely:

D-MSSQL01-DE (v.2014)
D-MSSQL02-DE\SQL2019 (v.2019)

And seperate server for AGs:

D-MSSQL02-DE (v.2014)
D-MSSQL02-DE\SQL2019 (v.2019)

Listeners:

D-AGSQL01-PP (v.2014 listener)
D-AGSQL04-PP (v.2019 listener)

I'm after setting up new AG and it's listener on SQL server 2019 with new IP:port assigned by networkteam. Now, when I connect to this newely created listener, somehow under "Always On High Availability" I see 2014 AG instance!
Why is this connecting to SQL2014 instead of 2019?

Both your 2014 & 2019 instances are running on the same hardware--just different ports.

While you don't specify which ports they are using, the 2014 instance is the default instance, so it's probably running on the default port (1433), and the named instance is running on some other port (who knows... You'd have to check SQL Server Configuration Manager). When you connect to the named instance, the SQL Browser Service helps with name/port resolution, or you can specify the port in the connection string.

It sounds like your AG Listener for your 2019 instance is potentially also using a non-default port (You'd have to check the Listener configuration in SSMS... If both listeners are on port 1433, keep reading). Unfortunately, the SQL Browser Service can't do it's magic on AG Listener names.

If you look at the related documentation, it explains that using a non default Listener port means you need to include the port in your connection string:

  You can also designate a non-standard listener port; however this means that you will also need to explicitly specify a target port in your connection string whenever connecting to the availability group listener.

SHOULD you be using a non default port? In your case, yes. You have to. In fact, if you are running both listeners on the same port, that would be the problem! The documentation continues:

  If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.

In your case, you are running multiple services (SQL 2014 & SQL 2019) on the same cluster node. This creates a conflict where both sqlservr.exe processes are both trying to listen on 1433, thus causing a port conflict. In this case, you'll need to change one of the listeners to use a different port, and also specify that port when connecting (Ex to connect to D-AGSQL04-PP on port 56789, specify server name in the format D-AGSQL04-PP,56789)


What are the risks of building a database outside IT control? I've been looking for the best StackExchange community to ask this. I hope it fits well here.

As a risk analyst in a trading business, I'm often confronted with large volumes of data, way beyond the excel-vba/MS Access scale capabilities.

However, when I requested my IT department to have a (any) SQL Database installed in my machine (or in whatever place) so I could 'play with', I was informed that it's an IT policy not to allow a mortal user like me to build a database because somehow I could threaten the infrastructure.

Assuming this reply is not due to my poor coding skills or my dubious character, can anyone put some perspective in this policy? Does anyone knows if this is a regular IT policy? Is there a way I could implement a SQL database that would eliminate such fears?

tks in advance!

There are a variety of factors that would cause IT organizations to be cautious about creating databases and giving business users the level of access to those systems that you are, presumably, asking for when you talk about wanting to "play with" the data.

First off, since you're in a company that does trading, that implies that there are dozens of laws and agencies that regulate the company.  In order to comply with those laws, your organization undoubtedly has put together dozens if not hundreds of policy documents that undoubtedly include policy statements that say things like only members of the DBA team have superuser privileges to modify data in databases, only changes that go through the official software development lifecycle are promoted to production, a separation of duties ensures that people that administer the database are different from people that code against the database which are different from people that input and maintain the data.  The first step of the many, many audits that such an organization goes through is to check to see whether the policies in those documents matches the actual practice.  Finding "rogue" databases that exist outside of these policies is a huge red flag in such an audit.  Even if your database didn't actually create any real risk, it would create an audit finding because it violated the agreed-upon policies.  Theoretically, the IT organization could work with their regulators to amend the dozens of policies to allow this sort of thing in special cases, but that would likely be a tremendous amount of work.

It is unlikely, though, that regulators would view the sort of risk analysis that you're talking about as the sort of thing that might get a pass from the burden of regulation.  The process of building risk models and analyzing risk in a trading business is about as core a concern of regulators as you're going to find.  Regulators are going to want someone to be able to explain exactly what sort of analysis is being done, what data sources are being used, how that data is being manipulated, etc.  They're going to want to know who has access to that data, how it is backed up, etc.

Second, systems like the one you describe have a tendency to grow over time and often get dumped on IT organizations once they become unwieldy or when the original owners move on.  A system that you set up today just for yourself to play with can quickly become a system that your department depends on and then a system that is integrated with all sorts of reporting and workflow processes as time goes on.  At some point, though, you'll move on to another company or the system will get slower or someone will realize that some critical bit of the business depends on a system that is running with no backups on some guy's machine and then IT will get the call to own the system.  Those projects are generally train wrecks.  These systems aren't generally designed well (risk analysts generally come up with better software designs than software developers come up with risk models but both pursuits benefit from expert attention up front), they're not properly documented, etc.  They also tend to rely on tons of manual processes that scale acceptably when one business user owns the process and understands in detail what the system is doing and why but that scales incredibly poorly when IT folks are trying to simultaneously manage many different systems that they don't understand in deep detail.  IT organizations generally do everything they can to prevent these systems from sprouting in the first place because they turn into such hassles in the end.

Third, if the system that you're building is necessary for you to do your job, then it's likely that it needs the attention of IT.  It probably needs to be backed up, for example.  It probably needs to be added to various proactive and reactive monitoring scripts/ systems that the DBAs monitor in order to ensure that sufficient disk space is available, that the workload of your system isn't crushing other systems, etc.  It probably needs to be part of the disaster recovery plan (if only to note that it's a low priority system to restore).  It needs to get added to the set of systems that the DBAs patch when security updates come out or when the enterprise upgrades to new versions of the database.  It needs to get added to the support contract so that it doesn't raise issues when the company gets audited by the vendor.  These things are generally much harder when IT doesn't own the system.  But if IT owns the system, it needs to behave like all the other IT systems-- it needs to be developed by developers, used by users, administered by administrators, it needs to have different environments (dev, test, staging, prod) to support the standard development models, etc.

In most IT organizations, if you need this sort of setup, you would need a project to create it (which would include things like assigning development resources, a project manager, etc.)  That project obviously needs to be prioritized, implemented, etc.

If you really want "free reign" to do whatever you want without any of the controls that are put on developers, it's possible that you, your manager, and IT could get together and come up with a reasonable compromise.  Perhaps you could be given slightly elevated privileges in some database/ schema somewhere along with restrictions in the types of things you're allowed to do (i.e. nothing from what you're doing can feed any other system, nothing can be used in a production process, etc.)  It's not uncommon for analysts to have a "playground" where they can create tables to support ad hoc reporting, for example, with the understanding that anything that needs to become part of a production report needs to get built following the full development lifecycle process.


Query Performance Issue I have this below query which is behaving bit weird . well Weird in the sense that I couldn't find complete explanation for this.

Version : Sql Server 2008 R2 Enterprise

No fragmentation . Statistics updated with fullscan and for all indexes.

DECLARE @t TABLE (
  subid INT )

INSERT INTO @t
VALUES     (7)--,(3)

SELECT TOP 1 t.QueueItemID
FROM   QueueTable t
WHERE  t.IsProcessed = 0
       AND t.QCode = 'USA'
       AND SubID IN (SELECT SubID
                     FROM   @t)
ORDER  BY t.QueueItemID

Little bit about schema :

Table variable (@t ) is just one column subid .

QueueTable schema is :

CREATE TABLE [dbo].[QueueTable](
    [QueueItemID] [int] IDENTITY(1,1) NOT NULL,
    [SubID] [int] NOT NULL,
    [IsProcessed] [bit] NOT NULL,
    [Qload] [varchar](max) NOT NULL,
    [QCode] [varchar](5) NOT NULL,
    [QDesc] [varchar](max) NULL,
 CONSTRAINT [PK_QueueTable] PRIMARY KEY CLUSTERED
(
    [QueueItemID] ASC
)

This table is big as obvious from varchar(max) type columns in above schema..

There are 2 NC index:

NC index NCx_1 on (isprocessed,qcode) include(queueitemid,subid)
NC index NCx_2 on (subid,isprocessed,qcode)  include(queueitemid)

Total rowcount is around 9 million rows. Group by subid is below :

SubID   RowCount
------  --------
1   68
2   8255571
3   378584
7   5350
11  5318

Rows satisfying condition  (t.IsProcessed = 0 and t.QCode = 'USA' ) are around 350k .

When I run above query it takes 1.5 sec to complete with seek on NC NCx_1 and then scan on table variable. Here is plan.

Above plan is for subid = 11 or 7 in @t table variable
Not sure why it isn't using index NCx_2 (subid,isprocessed,qcode) include(queueitemid) which matches the criteria . It is using index NCX_1 instead .

It appears as it is seeking around 350k rows to satisfy (t.IsProcessed = 0 and t.QCode = 'USA' ) and then filtering out data based on subid column.

I would expect it to first filter out data based on subid column (which would be very less) and then apply other filters which is exactly what is NCX_2 is for.

I tried couple of optimization here that improved performance but want to understand this strange behavior at least to me.

When I add merge join hint in the query then query runs very fast ( 100 ms)
When I add index hint (NCX_2) in the query then also query runs very fast (60 ms)
When I modify query to do MIN(t.QueueItemID) and remove order by query again runs very fast( 60 ms)

Not sure why optimizer not choosing it by default.

You say

  Above plan is for subid = 11 or 7 in @t table variable

I think you may be under a misapprehension here. SQL Server does not look at the contents of the table variable and choose a plan based upon the values it contains.

The statement is compiled before the table variable contains any rows at all and you will get the same plan (that assumes a single row) regardless of whether it eventually contains 2 (and would match 95.5% of the rows) or 1 (and would match only 0.0008%).

The table variable may of course also contain multiple rows but SQL Server will not take account of that except if you use the OPTION (RECOMPILE) hint and even then there are no statistics on table variables so it cannot take any account of actual values.

Some alternate plans are below

These require finding all matching rows and sorting them.

Because NCx_1 is not declared as a unique index the include(QueueItemID) is ignored (as explained in More About Nonclustered Index Keys) and QueueItemID gets added as an index key column instead. This means that SQL Server can seek on IsProcessed, QCode and the matching rows will be ordered by QueueItemID.

The plan in your question therefore avoids a sort operation but performance is entirely reliant upon how many rows in practice need to be evaluated before the first one matching the SubID IN (SELECT SubID FROM @t) predicate is found and the range seek can stop.

Of course this can vary wildly depending on how common the SubID values contained in @t are and whether there is any skew in the distribution of these values with respect to QueueItemID (You say that both around  350k rows match the seek predicate and that around  350k end up being seeked so for SubID = 7 it sounds like these are all at the end or perhaps no rows match at all - which would be the worse case for this plan).

It would be interesting to know what the estimated number of rows coming out of the seek is. Presumably this is much less than 350,000 and thus SQL Server chooses the plan you see based on this estimated cost.

If the table variable will always just have few rows you might find this rewrite works better for you.

SELECT TOP 1 QueueItemID
FROM   @t
       CROSS APPLY (SELECT TOP 1 t.QueueItemID
                    FROM   QueueTable t
                    WHERE  t.IsProcessed = 0
                           AND t.QCode = 'USA'
                           AND SubID = [@t].SubID
                    ORDER  BY t.QueueItemID) CA
ORDER BY QueueItemID

For me it gives the plan below where it seeks into the index on subid,isprocessed,qcode,queueitemid as many times as you have rows in the table variable. It is similar to the first plan shown but may be slightly more efficient as each seek stops after the first row is returned.


In Search of FILESTREAM Insider Information When the FILESTREAM feature is activated on Microsoft SQL Server 2012 then SQL Server will create a "hidden" share on the system. The share is defined as follows:
Sharename          FILESTREAM_SHARE
Path               \\?\GLOBALROOT\Device\RsFx0320\<localmachine>\FILESTREAM_SHARE
Remark             SQL Server FILESTREAM share
Maximum users      unlimited
Users Caching      Manual caching of documents
Permissions        NT-AUTHORITY\Authenticated Users, FULL

The name is the name of the share you provide when initially configuring FILESTREAM in the SQL Server Configuration Manager. But what is it for?
So far
I read through all the available FILESTREAM documentation starting at:

FILESTREAM (SQL Server)
Enable and Configure FILESTREAM
Create a FILESTREAM-Enabled Database
Create a Table for Storing FILESTREAM Data
... and everything related
FILESTREAM Compatibility with Other SQL Server Features
FILESTREAM DDL, Functions, Stored Procedures, and Views
Access FILESTREAM Data with OpenSqlFilestream
the paper SQL Server 2008 - FILESTREAM Storage in SQL Server 2008
and the article FILESTREAM (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions which references a lot of other material

...but there was no mention of the share and what it does or what it is for. You enter the name and SQL Server will create the share under-the-hood.
FILESTREAM-enabled database
When you create a FILESTREAM-enabled database, the database references a filegroup which references a directory (recommended on a separate drive) that has absolutely nothing to do with the share, which was initially created during FILESTREAM configuration.
Screenshot FILESTREAM-enabled database script

Yes, I do realise that all paths are on C:; it is just an example
The white paper by Paul Randall et al. goes on to explaing that...

FILESTREAM data is stored in the file system in a set of NTFS directories called data containers, which correspond to special filegroups in the database. Transactional access to the FILESTREAM data is controlled by SQL Server and a file system filter driver that is installed as part of enabling FILESTREAM at the Windows level. The use of a file system filter driver also allows remote access to the FILESTREAM data through a UNC path. SQL Server maintains a link of sorts from table rows to the FILESTREAM files associated with them. This means that deleting or renaming any FILESTREAM files directly through the file system will result in database corruption.

... further down the document (page 14) they carry on with ...

There is a single FILESTREAM file system filter driver for each NTFS volume that has a FILESTREAM data container, and there is also one for each version of SQL Server that has a FILESTREAM data container on the volume. Each filter driver is responsible for managing all FILESTREAM data containers for that volume, for all instances that use a particular version of SQL Server.
For example, an NTFS volume that is hosting three FILESTREAM data containers, one for each of three SQL Server 2008 instances, will have only one SQL Server 2008 FILESTREAM file system filter driver.

Questions

It's nice to know that SQL Server has everything nice and tied up, but what does that share actually do? Is it the so called "file system filter driver"?
Seeing as any authenticated user can access the "share", what are the security implications?
Is the Device RsFx0320 a predecessor to the resilient file system format that was introduced with Windows Server 2012?

If you can supply answers to my questions, then it would be nice if you could provide a source reference.

When the FILESTREAM feature is activated on Microsoft SQL Server 2012 then SQL Server will create a "hidden" share on the system.

It does not do this by default, you have to CHOOSE to enable the share. This is done via SQL Server Configuration Manager. If you deselect the Enable FILESTREAM for file I/O access the share will be removed.

  It's nice to know that SQL Server has everything nice and tied up, but what does that share actually do?

The share allows for clients (local and remote) to have a singular shared location to use the streaming windows api for access to filestream data. This works in conjunction with the SQL Server Instance level settings for filestream access of Full Access Enabled, any other access setting should not work with the streaming API.

  ... Is it the so called "file system filter driver"?

No, it is not. This is just a file share.

I was trying not to muddy the waters but you did ask for as much information as possible. In the above strikethrough text I did, in fact, say that this was not the filter driver. However that technically is a half truth. Yes, it is a shared folder but it actually shares through the filter driver. I really debated about this because it starts becoming a rabbit hole that you really can't go down without the source code (and to be honest it's of little value other than academic in my opinion).

The whole point of the filter driver is to do a few things, but the one of those things is to give transactional access to the data stored in the filestream target via a variety of interfaces; SQL Server, Transact SQL, Windows APIs. It also does a handful of other items - however the access given through the share is done via the filter driver. In fact, if you attempt to access files in a filestream and are not an administrator or SQL Server you shouldn't be able to access them.

So, yes this both is and is not the filter driver. It's half a windows fileshare that is exposed through a filter driver. You can see this is you view the path property of the share.

get-wmiobject -class Win32_share | where {$_.Description -like 'SQL Server*'} | ft name, path -autosize

  2.Seeing as any authenticated user can access the "share", what are the security implications?

You can change the permissions and requires the settings to be properly set. The security implications are that of any other file share.

  3.Is the Device RsFx0320 a predecessor to the resilient file system format that was introduced with Windows Server 2012?

No, this is the name of a specific version of the filter driver. For example, here is a system with the 2016 one loaded RsFx0410. ReFS is a file system, this is a filter driver that sits between the filesystem and the miniport driver. It's actually quite disconcerting that this is a legacy filter driver as denoted by the .10 at the end of the altitude... hmm. You'll also notice it has quite a low altitude, which is generally not acceptable for 3rd party filter drivers.

  If you can supply answers to my questions, then it would be nice if you could provide a source reference.

I have no sources for this but have backed up my information through screenshots and configuration options that change settings. Everything in this answer can be found by looking through the product itself and knowing how pieces of windows work (ex: filter drivers).


Difference between exp and expdp I am a newcomer to database administration. Can someone briefly explain to me the difference between commands exp and expdp?

The main difference compared to the architecture of Data Pump utility exp / imp and expdp / impdp are always run on the DB server and not on a client. Then the files will always on the server where and active an Oracle database.
The advantages of the data pump is performance (eg. Parallel) and the presence of new features' (eg. Migration direct SQL * Net, native compression, remap).
See this  Oracle white paper(Upgrading to Oracle Database 12c)  on page 14.
You can also see the Database Utilities manual.


Joining Three Tables by using IDs as Foreign Keys I'm starting out with learning MySQL and so far I realized I need to JOIN three tables.
The three tables are

Users

Groups

Members
There is a list of users, a list of groups that users can join, and then the members table is a many-to-many relationship between Users and Groups that lists which user joined which group.

I have a user_id auto_increment for the user table, and a group_id auto_increment for the groups table.
In my Members table, I have user_id and group_id which are both primary keys and  foreign keys to the respective tables.
Because my foreign keys are IDs (Integers), If I wanted to display a list of users in a certain group by username, I would have to use JOIN since my Members table only references ID integers...
I would appreciate a professional opinion on whether I'm on the right path and if it will be manageable in the future so I won't have to restart from scratch.
I read online that using IDs is good because sometimes using other columns like usernames are vulnerable to change and querying integers is much faster. Is this what most of you guys do? It just seems like a lot of work to have to JOIN lots of tables together.

You are on the right track. Don't let perceived complexity derail the design, as it is basically the standard way to represent many-to-many relationships. These tables are also a good place to store metadata or attributes of the entity relationship.

Your reasoning is mostly correct: there are data integrity benefits to using a DBMS-generated key in the intermediate table approach, but an integer isn't necessarily faster on key comparisons because ideally the joins would use indexes, not the column values.


Can operations in a loop cause table locks? I know a bit about locking etc, that when a transaction is working on a table, if it is altering a certain amount of data it might try and lock the entire table (I'm sure that is over simplified, but I don't think it's important in this case, feel free to correct me).

I want to know whether single row operations within a while loop could cause the same kind of lock on a table? The operations are mostly inserts, each creating a single. Can this be optimized in the background to lock the entire table?

If it can't lock the whole table, can it have the same type of effect, monopolizing the resources or operations on that table until it is completed?

We are using MS SQL Server 2008r2, and a spring batch application is calling stored procedures in the database. The while loop in question is inside that stored procedure which commits as an entire transaction (not per loop or anything like that).

EDIT: Below is a cut down version of the loop (it is a production application):

select @MinItem = min(ItemId) from Item

while @MinItem is not null
begin

    declare @x, @y, @z

    select @x = i.x
           @y = i.y
           @z = i.z
    from Info i
    where ItemId = @MinId

    insert into Sales (a,b,c)
    select x,y,z

    --two more inserts like above but to different tables, followed
    --by an update to another table

    select @MinItem = min(ItemId) from Item
    where ItemId > @MinItem

end

If you have wrapped the transaction block at the around of the insert command it would be only locking that record at a time, but if you have defined the whole loop in the transaction block, all involved records will be locked until the operation is finished.


Does mysql "order by" guarantees to give the same order for the same query every time if the sort key is equal? I'm planning to write a query that sorts the result by a value s and then paginate the result. Let's say I have ten items that match the query and all the items have the same s. In the first query, I sort the value by s and get the first five items. Then in the next query, I sort the value by s and get the sixth to tenth items. Is it possible that the items that appear on my first query will appear again in my second query, and that some items will not show up in either query?

When the field being sorted on is not unique, then the order of the results are non-deterministic, meaning there is no guarantee on their exact ordering. Specifically in your example case where the first 10 results all share the same exact value for the s field could yield indeterminate results for the first time you run the query vs the second time. It is possible you see the same result in both cases or not at all as you guessed. You would need to add another field to the ORDER BY clause that uniqifies the sort in order to always guarantee deterministic results.
For a little proof of case and further reading, here is a DBA.StackExchange question and answer where the user was experiencing unpredictable ordering of results because the field they were sorting on wasn't unique. The comments on that answer briefly mention that being the cause.


Oracle scheduler max_jobs_Slave_processes is set to null, how many will be allocated? Executing the following query against my database:

select value
from dba_scheduler_global_attribute
where attribute_name='MAX_JOB_SLAVE_PROCESSES';

yields a value of NULL.  My question is what is the maximum number of slave processes that could be allocated, for job runs, when this value is set to null?

NULL implies no limits. Anything other than NULL sets a limit.

See the documentation here.


slave replication stopped working This morning my slave stopped working..

Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: No

  Last_SQL_Error: Could not execute Update_rows event on table
  dynaccount.account; Can't find record in 'account', Error_code: 1032;
  handler error HA_ERR_KEY_NOT_FOUND; the event's master log
  mysql-bin.000025, end_log_pos 802331975

Restoring slave

- Master
# mysqldump -u root -pxxx --single-transaction --hex-blob --routines --triggers --events --quick --add-drop-database --extended-insert --databases my_db | gzip > /root/dump.sql.gz
# scp -o 'StrictHostKeyChecking no' /root/dump.sql.gz root@remote.com:/root

- Slave
# mysql -u root_slave -pxxx -e "stop slave; reset master"

# pv /root/dump.sql.gz | gunzip | mysql -u root_slave -pxxx
# mysql -u root_slave -pxxx -e "start slave"
# mysql -u root_slave -pxxx -e "show slave status\G"

After restoring the slave I keep getting the same error?!

Add --master-data to the mysqldump. That way the change of slave position to where you did the mysqldump gets executed when you load it.


Couchbase Ghost entry - Have it in view, not in data itself I have a 4 server setup.

In them I have a view like this:

function (doc, meta) {
    if(meta.type == "json") {
        if(doc.type && doc.type == "saveDocument") {
            emit(meta.id, null);
        }
    }
}

The problem is that right now I have as first reply from it record with key ""cache::1433576113.9028" but when I try to Remove it from the system all I am getting is "Key not present" error.

Please advice.

What is interested I have changed the view, to show me the data itself and the data was OK...

Well, I have Failed Over each server one by one, and made a FULL REBALANCE after the failure.
After I have finished with the last 4th server, the ghost record disappeared.


Why am i getting a truncation error when running a SSIS package on the sql server agent, but not when i manually run it I created a SSIS package that drops then creates a table, to transfer data from Sql Server 2008 r2 to an access file. When I run it in BIDS 2008r2 it runs just fine, no truncation issues at all. When I take that package and run it on the SQL Server agent, it fails saying it had a truncation error on the date field. When I manually look at the data (it’s a mere 159 records) I don’t see any funky data.

Both my account and the service account are server administrators so no difference of access on the server side, and we are both in the local administrator group on the destination file share. I also run 4 other packages that are built the same way, but this is the only one that has the data truncation error only on the agent, and not manually run in bids.

Below is the full text of the error minus the user account information.

  Executed as user:<Domain>\<sql service account>. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.6560.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.
    Started:  7:13:49 AM
  Error: 2019-08-12 07:13:50.78
     Code: 0xC0202009
     Source: Data Flow Task 1 chips dest [705]
     Description: SSIS Error Code DTS_E_OLEDBERROR.
       An OLE DB error has occurred. Error code: 0x00040EDA.
  End Error
  Error: 2019-08-12 07:13:50.78
     Code: 0xC020901C
     Source: Data Flow Task 1 chips dest [705]
     Description: There was an error with input column "DATE" (959) on input "Destination Input" (718). The column status returned was: "The value could not be converted because of a potential loss of data.".
  End Error
  Error: 2019-08-12 07:13:50.78
     Code: 0xC0209029
     Source: Data Flow Task 1 chips dest [705]
     Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
       The "input "Destination Input" (718)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (718)" specifies failure on error. An error occurred on the specified object of the specified component.
       There may be error messages posted before this with more information about the failure.
  End Error
  Error: 2019-08-12 07:13:50.78
     Code: 0xC0047022
     Source: Data Flow Task 1 SSIS.Pipeline
     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.
       The ProcessInput method on component "chips dest" (705) failed with error code 0xC0209029 while processing input "Destination Input" (718). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
       There may be error messages posted before this with more information about the failure.
  End Error
  DTExec: The package execution returned DTSER_FAILURE (1).
  Started:  7:13:49 AM
  Finished: 7:13:53 AM
  Elapsed:  4.197 seconds.
  The package execution failed.
  The step failed.

This is one of those annoying inconsistencies due to the fact that the SQL Agent may run under different settings than when you run it yourself (e.g. it might have ARITHABORT off when you have it on, so forth).

Rather than twiddling with the settings, I find it more robust to use solution that are not sensitive to different settings, which means avoid any possible precision loss. Because your target is an Access database, the date/time has a precision of one second. If you're using old datetime data type, it has a weird precision of 1/3 milliseconds, which can potentially cause the precision loss that SSIS loves to tell everyone and their dog about.

To work around that, match the precision exactly by using datetime2(0) which has the same precision (one second) which should then avoid the error, regardless of what settings you/the agent are running under.


AWS DMS creates tables with no auto_increment I am migrating my databases to Amazon's RDS using their DMS tool.  Pretty much everything looks great except that columns that were marked as auto_increment on the source database did not get marked as auto_increment on the target database.  I also noticed that my indexes and foreign key constraints were not included. I am running version 5.6 on both ends.

Is there a way to get DMS to include the full create tables for my database?

If DMS is told to create the table on the destination (the default option) it only creates the most basic structure needed to move the raw data over. So you shouldn't trust that other metadata like default values are moved over either.

You can either migrate all your changes after moving the data, or create the schema first on your destination and tell DMS not to create tables.

Just keep in mind that copying data with indexes at the same time is much slower, so if you have a lot of data you can create them afterwards or simply just wait longer for the sync.


Optimizing concurrent updates in Postgres I'm running concurrent Postgres queries like this:

UPDATE foo SET bar = bar + 1 WHERE baz = 1234

Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which the rows are updated, I end up with deadlocks. Currently I fix the problem by enforcing the order by hand, but this means I have to execute many more queries than I normally would while also raising the search complexity from O(log N + K) to O(K log N).

Is there a way to improve performance without ending up vulnerable to deadlocks? I suspect that replacing the (baz) index with the (baz, id) index might work provided that Postgres updates the rows in the same order that it have scanned them, is this an approach worth pursuing?

There is no ORDER BY in an SQL UPDATE command. Postgres updates rows in arbitrary order:

UPDATE with ORDER BY

To avoid deadlocks with absolute certainty, you could run your statements in serializable transaction isolation. But that's more expensive and you need to prepare to repeat commands on serialization failure.

Your best course of action is probably to lock explicitly with SELECT ... ORDER BY ... FOR UPDATE in a subquery or a standalone SELECT in a transaction - in default "read committed" isolation level. Quoting Tom Lane on pgsql-general:

  Should be all right --- the FOR UPDATE locking is always the last step
  in the SELECT pipeline.

This should do the job:

BEGIN;

SELECT 1
FROM   foo
WHERE  baz = 1234
ORDER  BY bar
FOR    UPDATE;

UPDATE foo
SET    bar = bar + 1
WHERE  baz = 1234;

COMMIT;

A multicolumn index on (baz, bar) might be perfect for performance. But since bar is obviously updated a lot, a single-column index on just (baz) might be even better. Depends on a couple of factors. How many rows per baz? Are HOT updates possible without the multicolumn index? ...

If baz is updated concurrently, there is still an unlikely corner case chance for conflicts (per documentation):

  It is possible for a SELECT command running at the READ COMMITTED
  transaction isolation level and using ORDER BY and a locking clause to
  return rows out of order. ...

Also, if you should have a unique constraint involving bar, consider a DEFERRABLE constraint to avoid unique violations within the same command. Related answer:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?


ora-12514 Error While Connecting local Oracle EXPRESS EDITION 11 I have recently installed Oracle XE 11 on my local machine and then installed SQL Developer. But when I try to create a new connection (for my localhost) or add from 'database detected local XE' it returns either ora-12514 or ora-12505. I know the password that I entered during the installation.

OracleXETnsListener,OracleServiceXE  service is running
Tried connecting via localhost, 127.0.0.1 and computer_name
Opened localhost tns and wrote the information there

But I am still not able to connect.

It bugs me that even a simple localhost connection is problematic when it comes to Oracle tools. It should be as easy as using Microsoft tools. I can't even connect to a localhost xe oracle.

What is wrong?

Edit: I may have found the issue.. I have installed Oracle XE 11.2 as domain user so probably there is some other configuration that needs to be done to access.?

ORA-12514 means there is no requested service registered in the listener.

ORA-12505 means there is no requested instance registered in the listener.

If the service name or instance name you provided was correct, then your database may be not started.

Oracle databases run as a service on Windows named OracleServiceXE (for Express Edition), you should check and start the service if it is not running.

On Linux, you can start the database as:

sqlplus / as sysdba
SQL> startup

After that you should see your instance and services in the output of lsnrctl status, and that is when you should try connecting again.


Block user access to certain tables DBMS allows the admin to grant table access to a user using something like:

GRANT ALL ON mydb.mytbl1, mydb.mytbl2 TO 'someuser'@'somehost';

However, is it possible to grant all tables to a user and explicitly block access to some of them? For example (The BLOCK key word is a fake one and is only used for illustration.):

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
BLOCK ALL ON mydb.mytbl3 TO 'someuser'@'somehost';

Another idea is to add a where clause to the GRANT statement. However, it seems that the GRANT statement syntax does not allow that (http://dev.mysql.com/doc/refman/5.1/en/grant.html).

This kind of access control might be convenient if the admin trust the user in most cases (e.g. 1000 tables) and only want to block access to a small number of top sensitive tables.

Could you please tell me whether such kind of mechanism exists?

For MySQL in addition to the GRANT command, there is also a REVOKE command.  However, REVOKE has limitations.

If you GRANT ALL ON mydb.* TO 'someuser'@'somehost' you cannot remove one table by running REVOKE ALL ON mydb.mytbl3 TO 'someuser'@'somehost'.

This is because REVOKE will only remove rights as they are granted. That is, you could:

REVOKE ALL ON mydb.* TO 'someuser'@'somehost'

Of course you could create scripts that would automatically GRANT access to each table individually.  Then you could have a special script that would REVOKE the table access that you need to remove from 'troublesomeuser'@'somehost'.

Note that the most recently run command, whether GRANT or REVOKE is the command that is in effect.

There is no DENY command in the MySQL syntax. This is a feature defined in some other SQL implementations. However, denying rights has its own pitfalls that many have stumbled over.


Table with too many nullable columns - is there a better design? I have a user table with around 30 columns. Most of these columns are almost always null unless user decides to share additional info about themselves. For example, weight, height, religion, is_smoking, is_drinking, star_sign, school, job, etc.

In my app, I have on option to filter users based on these values. I might be adding one or two columns like these in future.

I was just wondering if there is a better design approach for situations like these. For some reason, it doesn't feel right to have all these nullable columns in the same table with login credentials.

PS: I am using PostgreSQL 11.4 as the database management system.

You could have a table

CREATE TABLE user_info (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   user_id bigint NOT NULL REFERENCES users,
   property text NOT NULL,
   value TEXT NOT NULL
);

and store the additional properties there. That would be the relational way of doing it.

In recent PostgreSQL versions you could also add a jsonb column for all such information.

With a GIN index on it you can filter efficiently as long as you are looking for certain keys and their value. But you won't be able to do advanced things like substring or similarity matches that way.

It is a matter of taste which one you prefer.


Transaction backup plan and size I am using SQL Server 2016.
I have setup SQL Agent for Full backup (Weekly) and Transaction Log backup (Daily).
Backup transaction log into a single file using:
BACKUP LOG [XXX] TO  DISK = N'E:\SQLDB\Backup\XXX_Trans_Log.bak' WITH RETAINDAYS = 28, NOFORMAT, NOINIT,  NAME = N'XXX-Transaction Log Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

What I expected is only 28 days transaction log backups will be kept. But now I just found All transaction log backups are kept. So the file grows to very large in size.
After finding this, I change the plan to backup log in a separate file each day by:
DECLARE @Ver VARCHAR(2)
DECLARE @File VARCHAR(100)

SET @Ver = (SELECT RIGHT('0' + CAST((DATEPART(DAY,GETDATE())) AS VARCHAR(2)),2))
SET @File = 'E:\SQLDB\Backup\XXX_Trans_Log_' + @Ver + '.bak'

BACKUP LOG [XXX] TO DISK = @File WITH RETAINDAYS = 28, NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10

I am testing it on testing environment.  Basically it works but I found the log size is quite large, as nobody is using the environment.  Here are the figures:
DB file size = 500MB (38% free)
Log file Size = 865MB (38% free)
DB Backup (Weekly) = 311MB
Trans Log Backup (Daily)= 277MB
Any reason why the trans log backup so large?

I cannot say why your daily transaction log backups are large - it is usually down to user activity on the database and SQL Agent maintenance jobs such as ones that use DBCC CHECKDB and re-indexing.
WITH RETAINDAYS will not delete your previous backups, it will prevent overwrites of the backup file when using the INIT option.  You will still need to run a job to clear out the old backup files.
Here's one that Ola Hallengren's scripts add :-
    DECLARE @CleanupDate datetime
    SET @CleanupDate = DATEADD(dd,-28,GETDATE())
    EXECUTE dbo.sp_purge_jobhistory @oldest_date = @CleanupDate


Clients timing out with no apparent reason I have a SQL Server 2005 server that's been running fine most of the time.

Today the users started to get time outs when opening new connections, including myself when I tried to open another tab on SSMS.

I've looked everywhere for the problem but I haven't found anything actually wrong with the server!

It's over 10.000 seconds of PLE, >99.80 cache hit ratio, Target Memory of 10GB and using only about 4GB. The server has 16 processor and the load is well below 20%. The I/O is pretty low, most activity comes from logging and CHECKPOINTs and only rarely the Lazywriter has to write stuff to disk.

This server is always been pretty underutilized, and still today I started getting timeouts when opening new connections.

The server has been restarted and the problem persists.

My first thought would be to blame the network, but I have remote DAC setup and I've been able to connect to the server without any delay when using the DAC, so I'm not so sure it's the network.

How can I track this problem down and diagnose what's going on? What tools or techniques can help me solve this?

Have you checked the error log? In Object Explorer, expand Management, expand SQL Server Logs, right-click Current... and choose Show SQL Server Log. Hopefully there will be more details there about what is causing the timeouts. If you can post anything suspect that you find there, we can help you troubleshoot further (though it may be something obvious, like a logon trigger or resource governor).


Set non-negative floor for rolling sum, in SQL Server I need to set a floor on the rolling sum calculation. For example, with

PKID    NumValue    GroupID
----------------------------
1       -1          1
2       -2          1
3       5           1
4       -7          1
5       1           2

I would like to have:

PKID   RollingSum    GroupID
----------------------------- ## Explanation:
1      0             1        ## 0 - 1 < 0  => 0
2      0             1        ## 0 - 2 < 0  => 0
3      5             1        ## 0 + 5 > 0  => 5
4      0             1        ## 5 - 7 < 0  => 0

When adding a negative number will cause the sum to be negative, the limit will be activated to set the result as zero. Subsequent addition should be based on this adjusted value, instead of the original rolling sum.

The expected result should be achieved using addition. If the fourth number changes from -7 to -3, the fourth result should be 2 instead of 0

If a single sum can be provided rather than a few rolling numbers, it would also be acceptable. I can use stored procedures to implement a non-negative addition, but that would be too low-level.

The real life problem for this is that we record order placed as positive amount and cancelled as negative. Due to connectivity issues customers may click the cancel button more than once, which will result in multiple negative values being recorded. When calculating our revenue, "zero" need to be a boundary for sales.

This business application is absolutely stupid, but there's nothing I can do about that. For this question, please only consider solution that can be used by a DBA.

I expect fifty rows per GroupID at most.

Here's a recursive CTE example I came up with (which seems to work).  Is uses Row_Number() OVER to create a sequence number with no gaps. I don't know how well it would perform with your data, but it's something to try.

--Set up demo data
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    drop table #temp
go
create table #temp (PKID int, NumValue int, GroupID int)
insert into #temp values
(1,-1,1), (3,-2,1), (5,5,1), (7,-3,1), (9,1,2)

--here is the real code
;
with RowNumberAddedToTemp as
(
SELECT
  ROW_NUMBER() OVER(ORDER BY PKID ASC) AS rn,
  * from #temp
  )
,x
AS (
    SELECT PKID         --Anchor row
        ,NumValue
        ,RunningTotal = CASE
            WHEN NumValue < 0   --if initial value less than zero, make zero
                THEN 0
            ELSE NumValue
            END
        ,GroupID
        ,rn
    FROM RowNumberAddedToTemp
    WHERE rn = 1

    UNION ALL

    SELECT y.PKID
        ,y.NumValue
        ,CASE
            WHEN x.GroupID <> y.groupid     --did GroupId change?
                THEN CASE
                        WHEN y.NumValue < 0     --if value is less than zero, make zero
                            THEN 0
                        ELSE y.numvalue         --start new groupid totals
                        END
            WHEN x.RunningTotal + y.NumValue < 0    --If adding the current row makes the total < 0, make zero
                THEN 0
            ELSE x.RunningTotal + y.NumValue        --Add to the running total for the current groupid
            END
        ,y.Groupid
        ,y.rn
    FROM x
    INNER JOIN RowNumberAddedToTemp AS y ON y.rn = x.rn + 1
    )
SELECT PKID
    ,Numvalue
    ,RunningTotal
    ,GroupID
FROM x
ORDER BY PKID
OPTION (MAXRECURSION 10000);


MySQL Dump Restoration when FOREIGN_KEY_CHECKS=0 I currently use a SaaS DBMS. Backups of my database are provided to me in the form of a MySQL dump, and I noticed that FOREIGN_KEY_CHECKS=0. I am annoyed by this because it appears that this dump is not actually conducive to a restoration of the database(including relationships) on my end should the need arise.

Upon review of the structure I see that there are no Foreign Keys in the tables, but I see there are KEYS(indexes) that appear to correspond with what the foreign keys should be.

When performing a MySQL dump, does setting FOREIGN_KEY_CHECKS=0 mean that there are no relationships/foreign keys included in the dump?

Is it true that when restoring a MySQL dump that had FOREIGN_KEY_CHECKS=0 at the time of the dump , the foreign key relationships will not be present?

Is there a way to create foreign key relationships from the existing KEYS?

That is a default option in mysqldump. As the name states it only disables the foreign key check, which is necessary. If you don't disable the check, you'll almost certainly run into foreign keys errors, unless the restore, coincidentally, honors the parent/child relationships in the sequence that it restored the tables in. Extremely unlikely. Mysqldump doesn't account for this.

Look at it this way. If your dump restore tries to restore a child table before its parent, it will see the rows as orphan records and the check will give an error.

As ypercube said. The dump file will contain a set command at the end to turn the check back on. It's also a session based variable.

To address your concern. No. The set command does not remove foreign keys from your tables.


How to pass application user names to the database server for audit purposes? Writing an application that will require a user to login.
I would like to use a table to store my user information; however, the user's ID will not be available to me when I create audits for all my tables.
Conversely, adding the user to the database itself will give me what I need but it's going to be A LOT of users.
What is the best practice here?  I need to be able to create table audits with a column that holds the id of the user that changed it.

If I understand your question correctly, you are asking how to pass an application user name to MySQL to be recorded in an audit table.
You can use a user-defined variable, which is local to the session in which it is created. Your application code will set it to the name of the logged-in user once it is authenticated. Your audit code can then reference that variable and retrieve the user name.
dbfiddle


How fast should I expect to load data on a high performance computer? I am considering a model where I use PostgreSQL COPY to copy data from a file into a table. I was wondering what kind of performance to expect on high-end hardware. An interval in MB/s would be nice so I can start estimating. If you just know the answer on other databases, I would be interested to compare.

This is a bit hard to tell, what is high-end hardware?

Using Oracle on EMC storage 60MB/s is certainly possible.
Using that same old Oracle on exadata can be slightly faster, 300MB/s and sometimes higher, again, depending on the configuration. A full rack is faster than a half rack ...


advantages of single-leader (transactions) over multi-leader replication I am reading the excellent book "Designing Data-Intensive Applications" which I wholeheartedly recommend, but I'm confused by a section comparing multi-leader (i.e. multi-writer) replication to single-leader replication. I understand the basic difference: In multi-leader, multiple leader nodes can accept writes, each leader sends its writes to the other leaders, and you have conflict-resolution rules to decide how to merge them. Single leader solves concurrency using transactions.

The following two paragraphs describe how multi-writer can be more challenging because conflicts aren't resolved right away. My question is afterward.

  [This paragraph and the diagram are describing multi-leader.] For example, consider a wiki page that is simultaneously being edited
  by two users as shown in Figure 5-7 [copied below]. User 1 changes the title of the
  page from A to B, and user 2 changes the title from A to C at the same
  time. Each user’s change is successfully applied to their local
  leader. However, when the changes are asynchronously replicated, a
  conflict is detected. This problem does not occur in a
  single-leader database.

  In a single-leader database, the second writer will either block and
  wait for the first write to complete, or abort the second write
  transaction, forcing the user to retry the write. On the other hand,
  in a multi-leader setup, both writes are successful, and the conflict
  is only detected asynchronously at some later point in time. At that
  time, it may be too late to ask the user to resolve the conflict.

I see the difficulty with multi-writer here, but I'm skeptical that single-writer would be much better.

Consider the most likely chain of events when two people edit a Wikipedia page at roughly the same time: 1) Person 1 loads the edit page, takes 3-5 seconds to edit the title and submits. 2) Person 2 loads the edit page, takes 3-5 seconds to edit the title and submits. Each database transaction to apply the edit is only a few milliseconds, so it is far more likely that that the updates will happen one after the other, than that they will happen at the same time. Therefore if your concern is that one of these 2 people's updates will be lost, you need to address the potential for conflicts at the application level somehow; transactions won't really help you.

Furthermore in the case where the two transactions do overlap, it doesn't help the users to simply delay one of the transactions until the other is done. Once it resumes it will still overwrite the first user's data.

So my question is, is there some helpful transaction technique I'm missing that would actually be useful here? It's been a while since I tried to use transactions so my technique is rusty.

The best improvement I can think of: add AND title='A' to the end of both UPDATE statements, and add a second statement to the transaction that checks the number of affected rows, and rollback if that is equal to 0. The rollback would have no effect but it would indicate a failure to the client. But this is a bit hackish.

I don't think it would help to begin the transaction with a check (i.e. SELECT * FROM pages WHERE title='A' and make sure you get something back). Both transactions could possibly see 'A' at the beginning even though only one transaction would win out.

I came across your question while reading the same book.

I think the issue to consider is that in the single leader scenario, there is a natural total ordering as it can apply a sequence number per transaction.  It always knows "which happened first". It's hard or impossible to do this correctly in multi leader due to issues like clock skew.

So I think it boils down to, does the system even know which happened first.

Your "write if" suggestion is mentioned/used many times and I think is agnostic to single/multi leader. Write if is application level (the user probably has to enter what they think the old value is) whereas single/multi leader is probably not exposed to the user. In single leader, the write if would unambiguously avoid conflicts; the total ordering determines which operation happens first, and whichever gets there second will safely fail due to the write if.

I think the advantage of multi is performance based; the advantage of single is "correctness/complexity" based.


Can SQL Server's Page Life Expectancy be too high? I've read several articles about SQL Server's Page Life Expectancy and what it means and what kind of information you can glean from it. In most of these articles that I've read, a common, healthy PLE value is somewhere around 1000-2000 seconds. I've read that getting down to around 300 seconds can mean you're likely low on RAM.

I recently just upgraded our hardware to have 64GB of RAM, up from 14GB. On the 14GB my PLE was around 300 seconds, and I was having a lot of 5-6 memory grants pending per second. So, that was bad and I increased the RAM. Now, my PLE is much, much higher around 5000 seconds, and no more pending memory grants. I've seen it 7000 seconds, if I recall. This is much higher than anything I've read.

Can a high PLE be a bad thing? Or is it the higher the better?

EDIT: I'm sorry, my PLE wasn't 7000 seconds, it was 70,000 seconds! Although, at the moment it is down to around 7000.

The database size is around 160GB. A few tables have 5+ million rows.
max_server_memory is set to 2147483647.

No, I can't think of any situation or theory in my head that could illustrate a negative side effect for an astronomically high PLE (unless you enjoy the humming sound of the platters in a hard disk drive?).

With memory getting cheaper and cheaper, and buyers-of-hardware getting a little more generous, these bigger memory boxes we see it is quite common to have PLE in the thousands upon thousands range.  It's simply one indication that your instance may not be under memory pressure.  Take it for what it is, and only that.

Page Life Expectancy is just an estimation SQL Server makes on how long it thinks a page will last in a buffer.  This is SQL Server's way of saying it thinks it'll be "a while".

Another thing of note, take Paul Randal's advice and don't concentrate on the PLE reported by the buffer manager.  Like all averages, the buffer manager can hide differing PLEs.  You want to look at all of the individual NUMA nodes' PLEs with the Buffer Node counter:

Get-Counter -ListSet "*" |
    Select-Object -ExpandProperty Counter |
    Where-Object {$_ -like "*buffer node*page life expectancy*"} |
    Get-Counter

Page Life Expectancy is simply one color in the beautiful painting that is SQL Server memory.  Look at it along with the other "colors" (target/total server memory, available mbytes, etc.).  Too often people look at one simple metric and fear either their server is about to explode, or conversely that it'll live for centuries unmanaged.


How to fix "The OLE DB provider "MSOLAP" has not been registered"? Here's the code I'm using to register a linked server to SSAS:

USE [master];
GO

EXEC master.dbo.sp_addlinkedserver
    @server = N'ServerAlias',
    @srvproduct = N'',
    @provider= N'MSOLAP',
    @datasrc= N'ActualServerName',
    @catalog = N'DatabaseName';

When I attempt to run an MDX query using OPENQUERY, or if I use the Test Connection command in Management Studio after running the above code, I get the error:

  Msg 7403, Level 16, State 1, Line 1
  The OLE DB provider "MSOLAP" has not been registered.

This is pretty strange as this provider is installed as part of SQL Server.

It also fails on all other instances on the same box -- 5 total instances of 2008 R2 RTM and 2008 R2 SP1. I don't remember exactly, but 1 or 2 of the instances on the problem box may have been upgraded from 2008 to 2008 R2 at some point.
It succeeded on a couple of other boxes using 2008 R2, including one box that has about a dozen total instances of 2005, 2008 R2 and 2012. I tried both 32-bit and 64-bit with success.

I looked at this Microsoft KB article which describes the exact issue; however, there isn't a resolution that's applicable. Every other instance of this error I found on the internet was due to someone incorrectly defining the linked server, which also isn't applicable.

This is a development server (thankfully), so pretty much all options are on the table. Would a Repair fix this? It feels like something is awry in the registry.

(I'm asking for direction first, as I can't perform any maintenance myself due to lack of permissions.)

The SQL Server Analysis Services drivers aren't actually installed as a part of the normal SQL Server installation unless you install SSAS on the server.

You can download the MSOLAP driver from the SQL Server feature pack and install it.  Then you can create the linked server.  Don't forget to install the 64bit install if you have a 64bit SQL Server or the 32bit if you have a 32bit SQL Server.


USING construct in JOIN clause can introduce optimization barriers in certain cases? It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.

I mean this key word:

SELECT *
FROM   a
JOIN   b USING (a_id)

Just in more complex cases.

Context: this comment to this question.

I use this a lot and have never noticed anything so far. I would be very interested in a test case demonstrating the effect or any links to further information. My search efforts came up empty.

The perfect answer would be a test case to show USING (a_id) with inferior performance when compared to the alternative join clause ON a.a_id = b.a_id - if that can actually happen.

Erwin:  I would concur with the idea that USING causing rigid ordering could well create many edge cases where optimal plans would be ruled out.  I recently helped someone out who had something like this in his query:

LEFT JOIN (
     a
     JOIN b ON a.id = b.a_id
     JOIN c ON b.c_id = c.id
) ON a.id = something.a_id
LEFT JOIN (
     table1 t1
     JOIN table2 t2 ON t1.some_field = t2.other_field
     JOIN talbe3 t3 ON t2.yafield = t3.something_else
) ON ....
repeat a few more times

In his case the worst of these join blocks was causing a nested loop join through some 200k rows, about 20k times (do the math), and since keys couldn't be pushed to indexes, it was a sequential scan.  This meant that the overall query took about 3 hours to run due to cascading plan changes.  By distributing the left join, the keys could be pushed down and the query ran in a matter of seconds.  Of course this isn't exactly equivalent which is why the planner can't treat them as equivalent and so it was left figuring out that plan as a hash join and then doing a nested loop in, which was painfully slow.

Any time you rigidly force the joins to go through in a certain order you introduce cases where key filter information may not be available yet in the execution of the plan, and so what might be possible to do later in a quick index scan/hash join might be have to be done much slower in a nested loop/sequential scan and so while the above fragment is not immediately equivalent, it shows the same problem.


Are iSeries AS/400 (DB2?), Lotus 1-2-3, and Excel date/time formats the same? Are the formats for dates/times from AS/400, to Lotus 1-2-3 that carried over into excel...with the date being the number of days since 1900-1-1 when mistaking 1900 for a leap year as an integer, and the decimal portion being the time.

Are they all the same format?

No.

Db2 for i (aka AS/400) dates are stored internally as a Scaliger number (aka Julian Day number)

The number of days since noon on Monday, January 1, 4713 BC.

select hex(current_date)
from sysibm.sysdummy1

returns 002583EC as of about 3pm MDT April 30,2019

The decimal value is 2,458,604

Which agrees which the current value on the web page linked to above


SELECT CAST(YEAR(date) AS VARCHAR(4)) + '-' + CAST(MONTH(date) AS VARCHAR(2)) AS Date
    , [0-19000] = COUNT(CASE WHEN Value<=19000 THEN 1 END)
    , [19000-20000] = COUNT(CASE WHEN Value > 19000 and Value<=20000 THEN 1 END)
    , [20000-21000] = COUNT(CASE WHEN Value > 20000 and Value<=21000 THEN 1 END)
    , [21000-22000] = COUNT(CASE WHEN Value > 21000 and Value<=22000 THEN 1 END)
    , [22000-23000] = COUNT(CASE WHEN Value > 22000 and Value<=23000 THEN 1 END)
    , [23000-24000] = COUNT(CASE WHEN Value > 23000 and Value<=24000 THEN 1 END)
FROM [SomeTable]
GROUP BY CAST(YEAR(date) AS VARCHAR(4)) + '-' + CAST(MONTH(date) AS VARCHAR(2))


CHECK constraint to limit certain rows I have the task to create an EAV table with some constraints.

Attributes are foreign keys and some values are also from other tables (not realized as foreign key, but as a CHECK constraint).

On this table are some constraints to be realized, one of which is that some object/attribute-combinations must be unique, while others don't.

Example: A photo can have multiple persons in it but only have one person who took it.

So I wrote a function to limit object/value-combination to max. 1, if a certain boolean is set for that attribute. (As I said, the attributes come as foreign keys from a different table.)

-- Create function (for use in CHECK constraint) that checks if
-- an objects get two value on the same attribute,
-- unless it's explicitly allowed in attributes table
CREATE OR REPLACE FUNCTION isNoDisallowedDoublette(_object text, _attribute text)
RETURNS boolean AS
$BODY$
  DECLARE
    singular    boolean;
    cnt         integer;
    stmt        text;
  BEGIN

    -- determine if attribute needs to be unique for object
    EXECUTE 'SELECT singular FROM attributes'
      ||  'WHERE attribute = '''
      ||  _attribute
      ||  ''';'
    INTO singular;

    -- if there are multiple values allowed, continue
    -- Note: if attributes.singular is not set,
    -- allow only one value to avoid conflicts
    IF singular IS FALSE THEN
      RETURN true;
    END IF;

    -- count entries identical to _row
    EXECUTE 'SELECT count(*) FROM objects '
        ||  'WHERE object = '''
        ||  _object
        ||  ''' AND attribute = '''
        ||  _attribute
        || ''';'
    INTO cnt;

    RETURN cnt = 0;
  END
$BODY$
LANGUAGE plpgsql;

This functions checks, if a particular object/attribute-combination is not yet present in the table (cnt = 0).

This works fine against inserting unwanted doublets.

The problem however is, that it doesn't allow updating such row.
The updated row seems to be considered a doublet.

Is there a way to fix this or is it a conceptual problem CHECK constraints just don't cover?

Robin

The problem however is, that it doesn't allow updating such row. The updated row seems to be considered a doublet.

That's one of the problems.

  Is there a way to fix this or is it a conceptual problem CHECK constraints just don't cover?

Yes, use "on insert" and "on update" triggers,
and yes "check" constraints are unsuited.

you'll also need an "on update" trigger on "attribute" so that changes
of singular are verified against the existing data.

A different approach would be to have partial unique indices generated by a trigger that tracks inserts, deletes, and updates on attribute.

Eg. if you have a attribute day_of_birth which is singular you would do

CREATE UNIQUE INDEX "object_day_of_birth_unique" ON objects(object) WHERE attribute='day_of_birth';

This leverages something that the database engine is good at, and so will give a slight performance boost, but as you're using EAV I suspect perfromance is not important.

when writing such a trigger be sure to use quote_literal and quote_ident where apropriate in execute expressions.


How to replicate MySQL table in different servers? I am searching for some solution for the following problem:

I have two MySQL Database servers in different locations.
I need copy a specific table from Server A into the same table of the Server B (duplicate table).
This is a live table and it receives 3-6 insert/updates operations per minute (on average)
I need that the information in both tables is synchronized in real time, each change in the table of Server A should be made in the table of Server B.
The table of Server B will be used as only read table for a dashboard tool, so the goal is that dashboard tools does not query the original table and original server for security reasons.

Added requirement:

As described above, the goal is to have a replica of the table of Server A into Server B, so to publish it and this could be read for a dashboard tool.
This table has confidential information so some columns of the table must not be replicated, for instance, columns for client names, national ids, phones,...

My first idea is do this through triggers but these do not work with different servers.

Replica master-slave could mark the way, but I don't know if it's possible to alter schemas in the destiny table.

Can this be done with native tools? If there is some commercial solution, buying it is not a problem.

Start by splitting your confidential info out of the table.  Make a 'parallel' table ('vertical partitioning').  The new table would have the same PRIMARY KEY as the original, so they can easily be JOINed together.

To avoid replicating the new table, either use replicate_ignore_wild=... on the slave or put the table in a different database and use binlog_ignore_db=... on the master.

Or you could let it replicate, but change permissions so that most people do not have even SELECT privilege to that table/database.

That would be a minimal first step toward securing the data.  I say it is only a fist step because if the Master is hacked into or stolen, there is nothing to stop them from getting the confidential info.


Clustered index compression vs table compression - are they the same thing? If I set compression (either page or row) on the clustered index of a table, is that the same as setting compression on the table?

SQL Server provides options for doing both, which suggests that they are different, but I was under the impression that a clustered index and a table were essentially the same thing, and my mental model of how clustered indexes work tells me that compressing the clustered index must also compress the table.

If I set compression (either page or row) on the clustered index of a table, is that the same as setting compression on the table?

Yes.

From MSDN:

  Use the REBUILD WITH syntax to rebuild an entire table including all the partitions in a partitioned table. If the table has a clustered index, the REBUILD option rebuilds the clustered index.

  SQL Server provides options for doing both, which suggests that they are different

The reason why both syntaxes exist is because a table doesn't necessarily have a clustered index. In other words, ALTER INDEX ALL ON ... REBUILD does not affect a heap (though it will touch all nonclustereds), so the other route serves that purpose.

Also, while the documentation doesn't explicitly say so, the ALTER TABLE ... REBUILD syntax does not enable/disable compression of all nonclustered indexes on the table. It only affects the heap or the clustered index.

Finally, if you're testing this through SSMS, be aware that you may get errors when disabling compression -- script things out so you can see what's really going on.


Should I use bigint or varchar(20) as primary key, performance-wise, for Postgres? I have 18 character long IDs I get from an API, and I currently store them as varchar(20). They are used as primary and foreign keys. Is it better in my case, to use bigint instead of a string type, performance-wise?

Since JavaScript cannot deal with bigints, they are converted by default to string, but I could set columns to be bigint, if that will help.

Definitely store as a bigint: this uses 8 bytes, while an 18 character string uses 19 bytes.


Performance tuning for MySQL database (read only) We are developing a reporting tool that is connected to MySQL database of size 500 GB size.  Reporting tool will be fetching the millions of rows from the database.  The tool is used only for reports.

It won't be updating, inserting or deleting any data.
There won't be any join we need to use, at a time tool will be interacting with a single table exclusively.
Tool will use only "order by", "where", "limit (for paging)" clause on the the database.
All the table will be having a column "rowno" as a primary key.  There is no FK relationship between the tables.

Edit:

MySQL version: '5.7.16-log'

Database Engine: MyISAM

Tool Used: MySQL Workbech

What's best way of performance tuning for this database (like using specific row_format etc.)?

MyISAM is going to be deprecated (Bill Karwin is a big hitter on stackexchange and previously worked for Percona!) - you should change MyISAM to InnoDB NOW. You can do that by following the instructions here or here

AIUI, even the system tables in MySQL have4/are been/being moved to InnoDB, see here and here. The latter reference appears to indicate that most system tables have moved to InnoDB in the upcoming 8 release.

You can read some of the pros/cons of MyISAM/InnoDB here.

Even if you're not using the capabilities of the InnoDB engine now, you may need to use them in the future.

To answer the question, you have two performance tuning options:

you can set your InnoDB TRANSACTIONs to READ ONLY from here,

or you can

set the entire server to be READ ONLY as per here.

You may want to test these, but it is my belief that any small performance penalty is worth paying for the advantages that the InnoDB engine brings!

  It won't be updating, inserting or deleting any data.

This is good for performance - there's no need for the engine to maintain linked lists of pointers to new records and/or other transactions in this case. You should set your transactions to read only as indicated above.

  There won't be any join we need to use, at a time tool will be
     interacting with a single table exclusively.

Hmmm... a database without joins - always been a puzzling concept to me. You do realise that the optimiser can make better decisions about which tables to process in which order if there are explicit joins between the tables - those who argue that FOREIGN KEYs incur a performance penalty don't understand RDBMSs.

  Tool will use only "order by", "where", "limit (for paging)" clause
     on the the database.

And GROUP BY surely? Otherwise, why not simply use a spreadsheet? In any case, presenting a human user with thousands/millions of lines of data (or in this case apparently, records) is pretty pointless - humans will just scan and skip through pages of numbers.

  All the table will be having a column "rowno" as a primary key. There    is no FK relationship between the tables.

See above about FOREGIN KEYs. No problem with having a simple integer PRIMARY KEY as your surrogate KEY.


SQL Server 2008 R2: Problems after computer name change I'm having a confusing problem after changing the computer name of a remote server hosting a local SQL Server instance.

Basically, a remote server was moved from one site to another. In order to facilitate this, I backed up and restored the old database to a new database name, clearing out the data so it could be used as a fresh database for the client software. I also changed the computer name, as we always do so to identify each server by its site number.

The database can be connected to by the client software just fine, and I can log in directly to SQL Server fine. However, one of my SQL Server Agent jobs fails, with an error in the event log:

SQL Server Scheduled Job 'Nightly Reset' (0x4F76FDFFF6DFFE4EA0DE4A70252AD3BD) - Status: Failed - Invoked on: 2012-02-07 08:10:05 - Message: The job failed. Unable to determine if the owner (Site-19\Admin) of job Nightly Reset has server access (reason: Could not obtain information about Windows NT group/user 'Site-19\Admin', error code 0x534. [SQLSTATE 42000] (Error 15404)).

Now, 'Site-19' is the old computer name, which has been changed, and the server has been reset. I connect manually using 'Site-28', the new site number, and it shows me as being connected to the SQL Server with Site-28\Admin. However, when I look at the properties of the Agent job, it shows the owner as being Site-19\Admin, and when I attempt to browse for users to change it, Site-28\Admin doesn't show up as an option, only Site-19\Admin. If I script out a new job from this one and manually change the owner to 'Site-28\Admin', the new job is created with the owner 'Site-19\Admin'.

Looking in sys.servers (or via sp_helpserver), I only have one entry: the current computer name. However, SELECT @@SERVERNAME returns the original development machine name (two name changes ago).

In short, I can't run this important SQL Server Agent job because it belongs to a user that no longer exists, and I can't figure out how to change it or create it as the correct user.

When you added the new server name using sp_addserver, did you remember to include the "local" designation.  It is that tag that updates the metadata for @@SERVERNAME.  More information.

sp_addserver 'servername', local


Transfer Data from One Table to Another or else Update the Data Good Afternoon. I have two Tables and they are

Table: GeneralInventory

  WareHouseID | ItemCode | Qty
  Null          Null       Null

Note: as of now it is empty

and Table: Receiving

   RINo     |  ItemCode | ReceivedQty
    RI805ZFJ   FBMURG5WR   1.00
    RI467RGR   FBMURG5WR   1.00

As you can see this formats on Table are most likely you will see in Inventory System, My Table GeneralInventory is the Main Inventory where all Items that you will received will transfer here and that Table is Receiving as what you see in the table Receiving there 2 Data and they are.

Please be focused on the RINo field they are Different right? now here is what I will do and also my Question (I will convert my Question in Step by Step procedure of the Program)

The Program will Select 1(One) Data from Table Receiving
I will click a button named Post and the Program will do the Following

-Check if the ItemCode of the Selected RINo already Exist in the Table                              GeneralInventory and If the ItemCode  is already in there then ReceivedQty of table Receiving and Qty of Table GeneralInventory will sum up or else add the Data.

I hope you get my point but if not I will show you further Example.

In General: If the Data Exist then Sum it Up or Else add the New Data.

I am using this Code.

INSERT INTO GeneralInventory (ItemCode, Qty)
SELECT RE.ItemCode, RE.ReceivedQty
FROM Receiving RE LEFT JOIN
     GeneralInventory GI
ON   GI.ItemCode = RE.ItemCode AND GI.Qty = RE.ReceivedQty
WHERE RE.RINo = 'Data of RINo'

UPDATE GeneralInventory GI
INNER JOIN receiving RE
    ON GI.ItemCode = RE.ItemCode AND GI.Qty = RE.ReceivedQty
SET GI.Qty = GI.Qty + RE.ReceivedQty RE.RINo = 'Data of RINo'

TY for future Help

You can use INSERT ON DUPLICATE KEY UPDATE. For this it is necessary to have a unique index on GeneralInventory.ItemCode, so that duplicates can be detected!

Your statement would look like the following:

INSERT INTO GeneralInventory (ItemCode, Qty)
SELECT RE.ItemCode, RE.ReceivedQty
FROM Receiving RE
WHERE RE.RINo = 'Data of RINo'
ON DUPLICATE KEY UPDATE
SET Qty = Qty + VALUES(Qty);

A quote from the above linked manual page to explain the VALUES() function:

  You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.