How to make SQL Server 2008 compatible to SQL Server 2000 I have to run the backup of SQL Server 2000 in SQL Server 2008.

While restoring the database from the .bak file, I got the error

  specified cast is invalid

After doing google I feel there is compatibility issue. Therefore I want to make the database compatibile to SQL Server 2000.

And run the below query

ALTER DATABASE  DBNAME
SET COMPATIBILITY_LEVEL = 80

but nothing help. Any help will be appreciated.
The complete error message is :

Specified cast is not valid. (SqlManagerUI)

------------------------------
Program Location:

  at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.PopulateGridWithBackupSetsFromDevices()

   at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.GetBackupSetsFromDevices()

   at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.textDeviceSelected_TextChanged(Object sender, EventArgs e)

   at System.Windows.Forms.Control.OnTextChanged(EventArgs e)

   at System.Windows.Forms.TextBoxBase.OnTextChanged(EventArgs e)

   at System.Windows.Forms.Control.set_Text(String value)

   at System.Windows.Forms.TextBoxBase.set_Text(String value)

   at System.Windows.Forms.TextBox.set_Text(String value)

   at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.bu
ttonSelectDevice_Click(Object sender, EventArgs e)

   at System.Windows.Forms.Control.OnClick(EventArgs e)

   at System.Windows.Forms.Button.OnClick(EventArgs e)

   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button,Int32 clicks)

   at System.Windows.Forms.Control.WndProc(Message& m)

   at System.Windows.Forms.ButtonBase.WndProc(Message& m)

   at System.Windows.Forms.Button.WndProc(Message& m)

   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
<IntPtr wparam, IntPtr lparam)

According to the following link there is a step missing:

http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx

You must first update your SQL Server 2000 by upgrading to SP4 before making a backup to restore onto SQL Server 2008.

The TechNet link refers to moving from SQL Server 2000 to SQL Server 2012, but the first step is moving from SQL Server 2000 SP4 to SQL Server 2008.

If going all the way to a current version of SQL Server you will have to make the migration in two steps:

Step 1: Make a first migration from SQL Server 2000 to SQL Server 2008 for instance. You need to be on SQL Server 2000 SP4.
Step 2: Make a second migration from SQL Server 2008 to 2012.


Best way to store physical stock data in a SQL db I work for a retail company and we are in the process of building a new data warehouse. One element we need to store is physical stock data as to how many units of any sku are available at a certain location at a certain time. the variables we want to look at are, therefore: skus, sites, day, hour and quantity.

The problem is that there are approximately: 100k skus and 200 stores. If I store data for every hour I would get 100k x 200 x 24 = 480,000,000 lines a day. I understand that not every sku has a quantity > 0 at every site. Let's assume sparsity is as high as 80% and when the quantity is 0 we don't store it so we remain with 96,000,000 rows a day. Moreover, even though we have websites and sales won't stop at night we may assume that night hours will have very little changes in quantities and that not every sku is sold every hour. Let's assume that on average a sku is sold only on 3 different hours a day. This brings us down to 12,000,000.

This is 2.5% of what we initially looked at, however, it is still a considerable amount. 12,000,000 rows every day is going to occupy a lot of space after a few months. A partial solution to the problem is what Kimball suggests in The Data Warehouse Toolkit which is to store data for each hour for the last month or so and then only daily for the last n months and then remove older records. Considering that we are already assuming that on average we sell a sku only on 3 different hours per day switching to daily data from hourly data will reduce only by two thirds. Let's assume I want to save stock data for the last six month. One month at hourly level and five months at daily level (let's also assume 30 days months to ease the math) this is going to be: (12,000,000 X 30) + (4,000,000 X 150) = 960,000,000 rows.

I really can't think of a better way to do this, had anyone had to work with physical stock data before? Any useful lesson you learned? Is there a more efficient way to store this data?

Thanks,

One thing you can do is create a separate table to store the quality and the changes for your stock so you can limit the width of the table. For example you could have a table with the following columns.

Store
SKU
Current Stock
Change In Stock
Change Source (Sales/Restock/Other Change Sources)

When this table is updated it will only include records that had a quantity change so that items that have the same stock level will not update. While you will still get a lot of records the disk usage should be minimized.

Another possibility for performance is to use a partition in the table design so you can more quickly get to the data you need.


Authenticating to Oracle REST data services as admin i'm working on an android app connected to a database, the database has more data than any tablet can handle, so after some research i found many solutions, most of them consisted of implementing a webservice between the app and the database itself, i found this and this, i just installed the latest version of ords, had a couple issues to get the web interface to work but now that it's working, i cannot login, i did find a couple solutions but none of them seems to work , any ideas ? what are the default identifiers supposed to be ? i'm lost here with like 50 tabs

thank you

https://imagizer.imageshack.us/v2/867x679q90/571/22qo.png

https://imagizer.imageshack.us/v2/753x796q90/855/u9b3.png

ps : i checked the apex_public_user and the apex_040200 users and they were 'expired' is that normal ?

if you need any more information please let me know

ok, so i found a solution, seing how apparently my default admin password got messed up

i ran a ' find /oracle/ -iname 'apxchpwd.sql' Search command

once i got the location of the file, i logged in sqlplus as sysdba, then ran @[complete_apxchpwd.sql_path] got prompted for the new password, typed it in, and voila


making a where clause optional in a sql statement Here is my current query:

`SELECT `performers`.`hash`, `performers`.`alias`,
  `performers`.`date_updated`, `performers`.`status`,
  IF(`performers`.`status` = 'active', 'deleted','active') AS `statususe`,
  `images`.`image_hash_file`
FROM `performers`
LEFT JOIN `images` ON `images`.`asset_id` = `performers`.`id`
WHERE (`images`.`asset_type` = 'performer')
ORDER BY `alias` ASC
LIMIT 12`

In it, there is a WHERE clause WHERE (images.asset_type = performer)

I'd like it to be optional, such that if there where clause doesn't fit it still shows the records from the performers table that do not have a join to the fulfilling images records.

You may add the WHERE clause in the LEFT JOIN so not joining rows are still in the result set.

SELECT `performers`.`hash`, `performers`.`alias`,
  `performers`.`date_updated`, `performers`.`status`,
  IF(`performers`.`status` = 'active', 'deleted','active') AS `statususe`,
  `images`.`image_hash_file`
FROM `performers`
LEFT JOIN `images` ON `images`.`asset_id` = `performers`.`id` AND `images`.`asset_type` = 'performer'
ORDER BY `alias` ASC
LIMIT 12`


MySQL row-based replication - Delete Fails We have a Master - Master replication setup, MySQL 5.6.
Only one Master is used, the other is for backup and failover (we'll call that the slave).
The binlog_format is set to ROW, autoincrement settings are made to avoid conflicts.

The problem: Slave is halted due to Duplicate key errors.

We debugged the cause to be that bulk deletes made by cron jobs on the Master did not run (completely?!?) on slave. We are talking about tens of thousands of records that are NOT deleted from the slave. We did not find errors in the MySQL error log.

That leads to unsyncronized replica and errors when inserts are made using PK that should have been deleted on the Slave.

The tables are MyISAM.

Any idea why the bulk delete doesn't propagate properly on the replica?

In the absence of any obvious, logical reason why this might be happening, I'm going to shamelessly invoke the MyISAM boogey-man.

This question reminds me of one from a few years back.  It's not a duplicate question, but the underlying mechanisms could be similar.  The workaround I provided in that case was specifically based on the premise of duplicate "unique" values existing in a table.

I suspect you have latent, undetected defects in your MyISAM tables, where there are actually -- via an unknown (to me) mechanism -- duplicate primary (or unique) key values hiding below the surface.

When MyISAM selects or deletes a row by primary key, it won't see these duplicates, because as soon as it finds one row, it stops looking, because there "can't" be more to find... yet, if you SELECT * you'll get the duplicates... and deleting one of the rows would then unmask the other.

One way to test this that might work would be this:

mysql> CREATE TABLE test_table LIKE real_table;
mysql> INSERT INTO test_table SELECT * FROM real_table;

If this is indeed at the root of what's going on, you might get "lucky" and get a duplicate key error, which would prove the theory... because a duplicate key error should be impossible if the original table's data is intact.

You could, of course, review the binary logs using mysqlbinlog, to confirm that the deletions were logged... but I suspect you will find that the deletes did get logged correctly... but after the rows were deleted, the phantom rows were then visible, and caused the subsequent replication error.


sp_blitzindex Index Evaluation Results Need some help with the results from the sp_blitzindex. Below is one of the suggestion but not able to understand the comment.

http://BrentOzar.com/go/AbnormalPsychology

  dbo.ACCT_RECV_ADDRESS has 18 out of 20 columns in one or more
  publications.

Could anyone clarify what does it mean..? Does table participating in replication has any effect on indexes..?

That one is just an FYI and not something that you should take action on. Some of the rows returned will be FYI-type things, some will be things that you should look into more, some will be things that you should take action on. Like I ignore the "addicted to strings" and "addicted to nulls" rows. Those are just FYIs. I take action on the missing indexes section, though not all of them, depends on the details.


SQL Server: Collation of the resource database I'm trying to use stopwords so that some words are skipped by the Full-Text Indexer. I can add those stopwords in the table sys.fulltext_stopwords.

When trying to get a list of stopwords and system-stopwords in SQL Server 2012 an error encountered. I'm executing the following (simplified) query:

SELECT sys.fulltext_stopwords.stopword
FROM sys.fulltext_stopwords

UNION

SELECT sys.fulltext_system_stopwords.stopword
FROM sys.fulltext_system_stopwords;

The error message I get is:

  "Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the UNION operation."

The database collation for the first SELECT statement in my query is Latin1_General_CI_AS. The same is true for the master, model, msdb and tempdb databases.

Where does the Latin1_General_BIN collation come from? It looks like the sys.fulltext_system_stopwords table has a different collation, but why?

EDIT:

I can 'solve' my error by using COLLATE in my query, like this:

SELECT  sys.fulltext_stopwords.stopword COLLATE DATABASE_DEFAULT
FROM sys.fulltext_stopwords

UNION

SELECT sys.fulltext_system_stopwords.stopword COLLATE DATABASE_DEFAULT
FROM sys.fulltext_system_stopwords

I see that the system-stopwords are stored in the resource database, which can explain the difference in collation. The next question would be: why is the collation from the resource database different that the default?

The database Collation for the first SELECT statement in my query is Latin1_General_CI_AS.

Well, not exactly. There are a few problems with this statement:

The Database's default Collation only matters in a query when using string literals, variables, and return values from UDFs. AND, that default Collation only matters if there is no column or COLLATE keyword being used.
SELECT statements / queries, as a whole, do not use Collations. Collation is assigned per each string field, and it can be different for each field in a query.
The column in the first / top query, stopword, is not using the Latin1_General_CI_AS Collation (more on this in a moment).

  I see that the system-stopwords are stored in the resource database, which can explain the difference in collation.

A column (or expression) coming from a different Database does not necessarily explain a difference in Collation. As stated above, Collation is set per each field of a query, whether that field comes from a column in a table or is an expression. Collation is usually derived naturally from Collation Precedence: Column Collation overrides literals and variables, and the COLLATE keyword overrides both. When there is a conflict, then you need the COLLATE keyword.

The main point here, however, is that if the stopword column in sys.fulltext_system_stopwords comes from a column in a Table in the Resource Database (i.e. mssqlsystemresource), OR if it comes from an expression in a View in the Resource Database that has its Collation set via the COLLATE keyword, then the default Collation of the Resource Database is irrelevant.

  I can 'solve' my error by using COLLATE in my query

Yes, the COLLATE keyword is the way to go. However, to fix this conflict, you only need to specify the COLLATE keyword in one query, though it does not matter which query.

For example, if I run the query with no COLLATE keyword, I get:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw

UNION

SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

  Msg 468, Level 16, State 9, Line 4
  Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the UNION operation.

So, assuming that the conflict is in the second (i.e. bottom) SELECT statement, I could fix it by applying the current DB's default Collation:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.fulltext_system_stopwords ftssw;

And that works. BUT, what if we try putting COLLATE on the first / top SELECT:

SELECT ftsw.stopword COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

That also works. And in fact, both of the following also work:

SELECT ftsw.stopword COLLATE Hebrew_100_CI_AS
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword
FROM sys.fulltext_system_stopwords ftssw;

-- and:

SELECT ftsw.stopword
FROM sys.fulltext_stopwords ftsw
UNION
SELECT ftssw.stopword COLLATE Hebrew_100_CI_AS
FROM sys.fulltext_system_stopwords ftssw;

Also, rather than use the DATABASE_DEFAULT option, which here equates to Latin1_General_CI_AS, I would use Latin1_General_BIN (or better yet: Latin1_General_100_BIN2, which is newer and better) in this particular case because it will ensure that different strings that can be normalized into the same string via the "distinct" behavior of the UNION (without the ALL) and the case-insensitivity of the Latin1_General_CI_AS Collation show up as different rows.

  Where does the Latin1_General_BIN collation come from? It looks like the sys.fulltext_system_stopwords table has a different collation, but why?

That Collation comes from an unlikely source. Let's look at the system catalog views in the query:

EXEC sys.sp_help N'sys.fulltext_stopwords';

The results indicate that this is a View (as expected) and that the stopword column has a Collation of Latin1_General_BIN (not expected). But wait, if the Latin1_General_BIN Collation is coming from sys.fulltext_stopwords, then what about sys.fulltext_system_stopwords and where is the other Collation coming from? Let's look:

EXEC sys.sp_help N'sys.fulltext_system_stopwords';

The results indicate that this is a View (as expected) and that the stopword column has a Collation of SQL_Latin1_General_CP1_CI_AS (not expected).

We can now dig a little deeper into the definition of each of those system catalog views:

EXEC sys.sp_helptext N'sys.fulltext_stopwords';

Returns (simplified):

SELECT
    fts.stopword,
FROM sys.sysftstops fts

And then:

EXEC sys.sp_help N'sys.sysftstops';

The results indicate that this is a system table, and that the stopword column indeed has a Collation of Latin1_General_BIN.

Next we can move on to the other system catalog view:

EXEC sys.sp_helptext N'sys.fulltext_system_stopwords';

Returns (simplified):

SELECT convert(nvarchar(64), stopword) as stopword, language_id
FROM OpenRowset(TABLE FTSYSSTPWD)

And FTSYSSTPWD comes from the Resource Database, so there isn't much more we can do at this point.

Still, there is one last thing we can do to be clear about the Collation of the data coming from the Resource Database -- sys.fulltext_system_stopwords.stopword:

CREATE DATABASE [FullTextCollationTest] COLLATE SQL_EBCDIC277_CP1_CS_AS;
GO
USE [FullTextCollationTest];

EXEC sys.sp_help N'sys.fulltext_stopwords';
-- Collation for [stopword] column: Latin1_General_BIN      (same as before)

EXEC sys.sp_help N'sys.fulltext_system_stopwords';
-- Collation for [stopword] column: SQL_EBCDIC277_CP1_CS_AS (same as DB's default Collation)

GO
USE [master];
DROP DATABASE [FullTextCollationTest];


How To Grant A User Just To Create A Table In Existing Database But Not To See Other Existing Tables In It We have dealed with an outsource company to install an application for use of our accounting department. Now, they want to access our existing database to install their application but i don't want them to see our current database inside. I mean like tables, stored procedures etc.. How to grant them just to create a table but not to see other structures of our database?

This should do the work. I tested it in my test environment, works for me.

USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TutorialDB]
GO
CREATE USER [TestLogin] FOR LOGIN [TestLogin]
GO
GRANT CREATE TABLE TO [TestLogin];
GO

Create a Login on the server with public role.
Then Create a user on the database that they need to access.
Grant create table to that user.


Is database normalization dead? I've been brought up old school - where we learned to design the database schema BEFORE the application's business layer (or using OOAD for everything else). I've been pretty good with designing schemas (IMHO :) and normalized only to remove unnecessary redundancy but not where it impacted speed i.e. if joins were a performance hit, the redundancy was left in place. But mostly it wasn't.

With the advent of some ORM frameworks like Ruby's ActiveRecord or ActiveJDBC (and a few others I can't remember, but I'm sure there are plenty) it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright. Okay, I understand not too much, but it gets on my nerves (almost) when some of these ORMs (or programmers) don't acknowledge 1-1 or 1-0|1 (i.e. 1 to 0 or 1). They stipulate that it's just better to have everything as one big table no matter if it has a ton of nulls "todays systems can handle it" is the comment I've heard more often.

I agree that memory constraints did bear a direct correlation to normalization (there are other benefits too :) but in today's time with cheap memory and quad-core machines is the concept of DB normalization just left to the texts? As DBAs do you still practice normalization to 3NF (if not BCNF :)? Does it matter? Is "dirty schema" design good for production systems? Just how should one make the case "for" normalization if it's still relevant.

(Note: I'm not talking about datawarehouse's star/snowflake schemas which have redundancy as a part/need of the design but commercial systems with a backend database like StackExchange for example)

One reason for normalisation is to remove data modification anomalies
ORMs usually do not support this.

I have many examples of Hibernate-designed databases that break this principle:

bloated (string repeated over 100s of millions of rows)
no lookup tables (see above)
no DRI (constraints, keys)
varchar clustered indexes
unnecessary link tables (eg enforcing 1..0:1 when a nullable FK column would suffice)

The worst I've seen is a 1TB MySQL database that was perhaps 75-80% too big because of these

I'd also suggest that the statement "todays systems can handle it" is true for most Mickey Mouse systems. As you scale, today's systems won't.

In my example above, there was no traction to refactor or change keys or fix data: just complain about database growth rates and inability to build a meaningful DW on top of it


Memory Consumption on Database Machine My Database server is consuming 71% - 75% percent of RAM memory, I can see from perfMonitor logs that SQL_Service is eating up 65% - 69% of RAM memory.

We carried out different operations to lower the memory consumption like:

We purged most of the databases to lower the size on disk.
We restart different application services that were in active state
with SQL Server.

Server RAM memory is 65 GB in total.

Looking forward for an expert opinion. How to Lower the memory consumption ?

It seems like you are running SQL Server together with an application on one server.

SQL Server will consume all the memory it can by design. (The default MAX memory setting in SQL Server being 2147483647 MB.)

If you changed the default MAX memory setting to something else, then SQL Server will start consuming memory after a reboot for data cache, query plan cache, and for other SQL Server relevant components.

At some point in time the SQL Server will pass the MIN memory setting. SQL Server will not release memory back to the OS past this setting.

Later on the SQL Server instance will possibly reach the MAX memory setting. SQL Server will not pass this line for data cache and plan cache. However, the SQL Server process can use more than the MAX memory server setting, because some objects run outside of the MAX memory setting (depending on version) and SQL Server can overcommit memory. This is discussed in the following article

Memory configuration and sizing considerations in SQL Server 2012 and later versions

Your SQL Server will now consume memory between the MIN and MAX memory setting until you reboot the SQL Server instance.

  CAUTION: Reducing the MAX memory setting of a running SQL Server instance will purge the data cache in memory and result in a reduction in performance.

If your SQL Server instance is running on an application server and not on its own, then you might have to reduce the MAX memory setting for SQL Server to allow the application to have some share of the memory.

If your SQL Server instance is running on its own, then try configuring the MAX memory setting according to the following recommendation:

  ...reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM...

Reference: How much memory does my SQL Server actually need? (SQLSkills.com)

There are some other good articles out their that explain how SQL Server uses RAM.

A Sysadmin’s Guide to Microsoft SQL Server Memory (BrentOzar.com)
Server Memory Server Configuration Options (Microsoft.com)
How much memory does my SQL Server actually need? (SQLSkills.com)


New transactions during a Sybase "dump database" command I have a Sybase 15.5 database which is backed up every night using the DUMP DATABASE command.  The process takes about 30 minutes.

What happens to the transactions that occur within those 30 minutes?  Are they included in the backup or ignored?

The dump process happens in 3 phases:

All data and log pages are dumped.
Data pages modified during phase 1 by non-logged operations are dumped.
Log pages are dumped again.

Everything happening until end of phase 2 are contained in the dump. But the changes performed during phase 3 will be missing. Of course phase 3 is pretty fast, so the chances are that you'll not be missing anything. You can check the state of your transaction log after the dump. If anthing is in there, then you know these transactions are not in the dump.

But of course transactions happening afterwards will not be in the dump either so I guess it's not much of an issue.

If your concern is that you dump multiple databases and the dumps might be out of sync, you should consider using quiesce database.


Conditional Join in DB2 2 tables are given

Table T_PRICE

P_Price    P_Type    P_Route
15         red       long
10                   long
5                    small

Table T_ROUTE

R_Type     R_Route
blue       long
red        long
black      small

I need to display all Routes with prices.
When there is no price for type, price should be from table T_PRICE without type.

My approach:

Select * from T_ROUTE
left outer join T_PRICE on P_Route = R_Route
and case when P_Type <> R_Type then '' else R_Type end = P_Type

Wrong Result:

R_Type R_Route  P_Price P_Type P_Route
blue   long     NULL    NULL   NULL
red    long     15      red    long
black  small    NULL    NULL   NULL

Right result should be:

R_Type R_Route  P_Price P_Type P_Route
blue   long     10             long
red    long     15      red    long
black  small    5              small

How do I get the conditional join to work?
Thank you!..

You need two joins here, one with the "matching" type set, another with "default" type.

I'm making two assumptions:

"Missing" p_type is an empty string, not null, as follows from your query.
There is at most one row in t_price for each p_route and empty p_type.

:

select r_route, r_type, coalesce(match.p_price, dflt.p_price)
from t_route
left outer join t_price match
  on (r_route, r_type) = (match.p_route, match.p_type)
left outer join t_price dflt
  on r_route = dflt.p_route and dflt.p_type = ''

dbfiddle link (It would be nice if you'd included it yourself).


MySQL on SSD - what are the disadvantages? Recently, I heard that I should not host my MySQL database on an SSD drive because there is a big chance of disk failure and data lost.
Given that 20% of all queries in my database are INSERTs and UPDATEs, here are my questions:
QUESTIONS

What are the disadvantages of using MySQL on SSD ?
What solutions or alternatives are available for MySQL/SSD usage ?

Modern SSDs are a lot more reliable than people tend to think in these discussions if you buy decent drives. If drive failure is a concern then RAID the drives as you would with spinning metal based drives.

One concern with SSDs is write lifetime, but for all but pathological cases if you have good drives the reliability shouldn't be much (if any) worse than traditional drives. The other issue is random write performance which can be a problem on some drives under heavy write loads (this is mitigated somewhat with drives that have a larger "over allocation" factor, and you'll not find it slower than spinning metal manages). Neither of these issues is going to be significant unless you have write-heavy loads.

"20% of operations are inserts or updates" isn't particularly meaningful as they could be affecting one row each or many millions. From a performance PoV the best thing to do is test your app and run realistic benchmarks for it. From a reliability PoV with good drives you should be as safe as with spinning metal, though of course use RAID1 (or 10) and backups to cover all the bases as you would normally.

Any recommendation more specific than that is going to be very opinion rather than fact based. For my own PoV: these days I put SSDs anywhere where I can afford the larger $/Gb and cope with the smaller individual device sizes, but I don't trust a single SSD any more than a single spinning-metal drive so anything important gets both RAIDed and (more importantly) regularly backed up and those backups regularly tested (a step that too often gets skipped).


Expecting Postgresql WAL to shrink in size but it's not We set our max_wal_size to 24 GB recently (from the 1 GB default), did some testing, and then set it to 12 GB followed by a server restart. When I query the size of the WAL on the filesystem (total size of files in pg_xlog directory), it still shows as about 20 GB. I have issued a manual checkpoint and restarted the server, but the WAL hasn't shrunk back down to 12 GB. This is a pretty simple implementation -- no replication nor archiving in place and there are no long-running transactions that exist. Am I misunderstanding how this is supposed to work? Does it not delete the old WAL files when you lower the max_wal_size value and when all transactions have been been completed and the server restarts?

Postgres version: 9.6.15

OS: Linux Ubuntu 16.04.6 LTS

Settings:

max_wal_size = 12GB

min_wal_size = 80MB

wal_keep_segments = 0

checkpoint_timeout = 15min

checkpoint_completion_target = 0.87

wal_compression = off

archive_mode = off

wal_level = minimal

It looks at "max_wal_size" to decide if WAL freed up by the just-finished checkpoint should be recycled or deleted.  But WAL files that have already been recycled (renamed in anticipation of future re-use) in prior checkpoints while "max_wal_size" was high will not get deleted when "max_wal_size" is lowered.  They will eventually work their way out of the system as they get reused and then deleted (rather then recycled again).

If you can't wait for that to happen, you will have to delete them manually, which is a rather perilous thing to do--if you mess it up you will destroy your database.


Get related row based on two MAX Values I bumped into a use case where I wanted to write a SQL query to retrieve a row based on 2 MAX values of the table. I tried a couple of combinations but no luck as of yet. Here's a sample table and the requirement:

Index   cardCode    updateDate  updateTS    unitValue
1       sf-001      2018/1/1    1122        $12
2       sf-001      2018/2/19   7788        $4
3       sf-002      2018/1/2    2233        $56
4       sf-003      2018/3/14   3344        $77
5       sf-003      2018/3/14   4455        $51
6       sf-003      2018/3/2    6677        $88
7       sf-004      2018/11/11  8880        $73

The SQL query should return the rows with MAX updateDate per cardCode. E.g. for "sf-001" it should return row 2 (unitValue of $4). If it has two or more identical updateDates then it should check for the MAX updateTS. E.g. for "sf-003" it should return row 5 (unitValue of $51). So the query should result in the following output:

Index   cardCode    updateDate  updateTS    unitValue
1       sf-001      2018/2/19   7788        $4
2       sf-002      2018/1/2    2233        $56
3       sf-003      2018/3/14   4455        $51
4       sf-004      2018/11/11  8880        $73

This is a clear case to use cte and window functions, please read about them a little. I assume you do not have too much data, so this code is efficient

;with cte
    as (select Index, cardCode, updateDate, updateTS, unitValue,
        ROW_NUMBER() OVER(PARTITION BY cardCode ORDER BY updateDate DESC, updateTS DESC) as rn
    from YourTable)
    select Index, cardCode, updateDate, updateTS, unitValue
    from cte
    where rn = 1


How to drop database that I can't get out of single user mode? I've got a database in single user mode that I can't get out of single user mode. I've tried resetting the server, and I've even tried resetting my computer but with every attempt I make to do anything to it SQL Server insists that there is some connection already open to it somewhere.

I don't really understand how it's possible for there to be a connection open to it after I've just reset my computer and have not opened a connection to it.  I am using SSMS and I made a connection to the server using the object explorer after which I tried to delete the database from there directly by using the delete key (i.e. I don't have any query tabs open so definitely no connections there).

Unfortunately, the application I'm using doesn't allow me to specify a database name which means I've got to figure out how to get rid of this stupid database so I can restore another one.

I've been googling and trying different things but nothing seems to work... even stuff that supposedly should work in this situation.

So here I am looking for any more ideas???

EDIT:

My latest attempt has been to use SP_WHO to find which process is connected and then KILL it.  But every time I KILL it, it comes back under another number immediately.  Don't understand what in the world could possibly be causing such behavior.

EDIT2:

I just tried select * from master..sysprocesses where spid = <spid> to see the kpid which, if I understand correctly, correlates to the PID column in details tab on the task manager.  Only problem is that the value is 0, which would mean it's the system idle process.  Maybe I'm misunderstanding something here though.

Stopping the server and disabling the sa account seems like overkill to drop a database. Next time this happens, the following should be sufficient:

USE [master];
GO

ALTER DATABASE [dbname] SET OFFLINE
    WITH ROLLBACK IMMEDIATE;
GO

DROP DATABASE [dbname];

The connection that could be sneaking in could be from anything - SQL Server Agent, a linked server, IntelliSense in your query window, etc. Setting the database offline is much more direct approach than even moving into single user mode, never mind unnecessarily tracking down some rogue session in deprecated compatibility views...


How to drop all of my functions in PostgreSQL? Right now I have to use query to get the command in text file.
Then remove double quote from them. And finally, run that file in psql shell.

How can drop all of my functions in PostgreSQL in a single step?

To drop all functions (including aggregates) in a given schema (be careful with this!):
Postgres 11 or later

Procedures have been added.
The system catalog pg_proc slightly changed: prokind replaces proisagg and proiswindow - and also tags functions and the new procedures

DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          string_agg(format('DROP %s %s;'
                          , CASE prokind
                              WHEN 'f' THEN 'FUNCTION'
                              WHEN 'a' THEN 'AGGREGATE'
                              WHEN 'p' THEN 'PROCEDURE'
                              WHEN 'w' THEN 'FUNCTION'  -- window function (rarely applicable)
                              -- ELSE NULL              -- not possible in pg 11
                            END
                          , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'public'::regnamespace  -- schema name here!
   -- AND    prokind = ANY ('{f,a,p,w}')         -- optionally filter kinds
   ;

   IF _sql IS NOT NULL THEN
      RAISE NOTICE '%', _sql;  -- debug / check first
      -- EXECUTE _sql;         -- uncomment payload once you are sure
   ELSE
      RAISE NOTICE 'No fuctions found in schema %', quote_ident(_schema);
   END IF;
END
$do$;

The schema name is case sensitive in this context.
The executing role needs to have the necessary privileges of course.
You might add CASCADE like demonstrated by mehmet but that will also drop depending objects, recursively - not just functions. Makes it even more dangerous. You better know exactly what you are doing.
Related, with more explanation:

How to drop function from all schemas
DROP FUNCTION without knowing the number/type of parameters?

Postgres 10 or older
DO
$do$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql
          string_agg(format('DROP %s %s;'
                          , CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
                          , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'public'::regnamespace;  -- schema name here!

   IF _sql IS NOT NULL THEN
      RAISE NOTICE '%', _sql;  -- debug / check first
      -- EXECUTE _sql;         -- uncomment payload once you are sure
   ELSE
      RAISE NOTICE 'No fuctions found in schema %', quote_ident(_schema);
   END IF;
END
$do$;


How do you hide tool tips in MySQL Workbench? Is there a way to hide the tool tips that popup when you hover over objects? Especially when you hover over tables, I do not want to see the breakdown of what objects are in that table.

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


Can a Z/OS mainframe application or DB2 read a SQL Server database/table directly? Is there anything similar to ODBC that would allow DB2 on a Z/OS mainframe platform to read a Microsoft SQL Server database/table.

Define read...

You can certainly run a Java application on z/OS that uses JDBC to connect and transfer data from MS SQL Server into DB2 for z/OS.

Otherwise, IBM's distributed database protocol is known as Distributed Relational Database Architecture (DRDA).

Both IBM and Microsoft offer products that allow MS SQL server to be seen as a remote DB2 server.
Microsoft Service for DRDA
IBM InfoSphere Federation Server

With the IBM product, you'd need a DB2 LUW server in the middle.

On IBM's midrange platform, IBM i (aka AS/400), there exists an open-source DRDA-JDBC gateway, ArdGate.  I don't know if something similar exists for z/OS or if you could port the project or build one yourself.


error 15466 An error during decryption I have a stored procedure that runs xp_cmdshell with a user with sysadmin right.(it was created by someone else long time ago). For security purposes I need to remove the sysadmin right from this user.
So I created a proxy account and gave execute privilege to this user on the xp_cmdshell (practically followed the steps how to run xp_cmdshell with a non sysadmin user ).
But when I run the stored procedure I am getting the below error:

  error number 15466    severity 16  errorline 1    An error occurred during decryption.

I use SQL Server 2008

What could be the problem?

This is most likely occurring as the old user ( the one with sysdmin rights) had access to the SQL Server Symmetric Keys which are used to encrypt/decrypt keys which the new user does not.

The Grant Symmetric key article provides details and the links at the end provide some good reading.


Using multiple Active Directory accounts to connect to SQL Server I have web applications (Classic ASP and .NET) that connect to SQL Server using SQL authentication. Each one has their own login. I want to convert these to Windows Authentication. The new AD users (1 per application) have already been established for each application.

What is the most secure way to use these AD users? Should I configure each application's application pool in IIS to run under the new AD user account, and then just use the "integrated security" option in the connection string from each application?

Yes, the most secure means to for authentication is Windows Authentication.  Read more about this topic and the differences here.

As for different applications having different logins, that is going to be the most secure method.  The reason behind this is because each application will most likely require a different set of permissions (I'm assuming a bit here), and instead of having one server principal with a handful of permissions to suit numerous applications, it will be more secure to have each application have a login with permissions tailored to its own needs.  It's all about the Principle of Least Privilege.


Why is "Lookup" colored as a function reserved word in SQL Server? In SSMS 2208, the identifier "Lookup" is colored hot pink as if it were a function (same color as, say, "Power" or "Convert"). Why?

I cannot find it in the official list of reserved words. Searches on the web seem useless as there are an awful lot of "lookup" terms out there that have nothing to do with my question.

At first I thought it came from Sybase (which is where SQL Server originated of course), which has a lookup function, but this is PowerBuilder-related. And then I checked SQL Server 2000 and it doesn't light up in pink in Query Analyzer...

...if it was legacy from Sybase I would have expected it to be in the list of color-coded words all along. It's possible, I suppose, that the grammar file was updated and that it was omitted "by mistake" in 2000, but I doubt it. It is much more likely that it is colored because it is listed in the T-SQL Language Service as a future compatibility word, or it was thrown into the Language Service in anticipation of being used. (I am waiting on official confirmation of this, and I will share what I can.)

Some other fun examples (I complained about a few of these on Connect back in 2008, but it was closed as Won't Fix) of inappropriate highlighting of words that are also not on the list you cite:

Domains lights up in green
Description lights up in blue
Server lights up in blue
Instead lights up in blue
RC2 and RC4 light up in blue

At the time I didn't capture the Lookup or Instead examples, and I'm sure there are some others as well. Though I'm guessing the document you're looking at is not as up to date as it could be either; at the very least, INSTEAD should be on that list since it is a part of T-SQL now (since INSTEAD OF triggers were introduced). I bet there are at least 20 other keywords that have been added for SQL Server 2012 but aren't on that list, too. Quickly scanning there are some notable exclusions that should be there: OFFSET, IIF, FORMAT, etc.

Another example you might find interesting; try putting a word like INSTEAD in a string but on its own line. This runs fine but it doesn't look like it will:

SELECT 'foo
INSTEAD
';

(This one courtesy of a bug filed by @JonSeigel.)

I've probably filed and commented on a couple of dozen other bugs against Management Studio's syntax highlighting; it certainly isn't perfect. I appreciate that you want to know why but we may not ultimately find out. As you can see from a lot of these Connect items, they usually ignore/defer them, or fix them without much explanation.


Postgres suggests "Truncate table ... at the same time" When running:
TRUNCATE TABLE YYYYY RESTART IDENTITY

I am seeing this error message:
ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "XXXXX" references "YYYYY".
HINT:  Truncate table "XXXXX" at the same time, or use TRUNCATE ... CASCADE.

Of the two suggestions in HINT:

Using TRUNCATE ... CASCADE makes sense, and works, but is less explicit because one must inspect YYYYY to see where the cascade goes. That makes me want to try the other option:

Truncate table "XXXXX" at the same time, but my question:
What does it mean to truncate a table at the same time?

I tried adding a TRUNCATE XXXXX... (and wrapping them both in a BEGIN / COMMIT), but that yields the same error.

What does it mean to truncate a table at the same time?

It means with the same statement. You can truncate more than one tables:

TRUNCATE xxxxx, yyyyy RESTART IDENTITY ;

More details in Postgres docs: TRUNCATE.


Is it possible to keep track of which rows were selected and fetched last in MySQL? I am working on a small website using Java where the user posts are stored in MySQL, I am getting the posts 5 at a time (5 rows using LIMIT 5) by most recent one (using timestamp DESC) and send it to the front-end .

The problem I am trying to solve is to be able to fetch the next 5 that are not the same rows that were fetched first...and so on. So I need to find away to keep track of which rows were selected and fetched in the last query and avoid getting them again.

The idea is that when the user scrolls down to the end, AJAX will send a request to servlet and the servlet will get the next 5 rows from MySQL.

I've been trying to find away by experimenting with the database as well as some online search, could not find a way so far.

Although you can track which rows were selected last using MySQL Enterprise Audit, this would be a little like using a Saturn V rocket to go to the corner store... The cost would be ridiculously high for such a small function.

Since you are ordering rows by timestamp, I am assuming your timestamp value is unique. Wouldn't adding a select clause like where <mytimestampcolumn> < [smallest timestamp column value from last result set]; take care of the requirement?

This would have the additional benefit of making your application scale better relative to other alternatives, such as retrieving the complete result into a cursor and having the servlet fetch the result set 5 rows at a time.


How I can monitor SQL Server 2012 databases to determine the security privileges that are called by different processes Is there any tool that can identify the SQL privileges or roles that are being called on query by query basis. We need to know which ones are being used at each time on a Microsoft SQL Server 2012.

You can use SQL Activity Monitor. This will show the current queries executing in your SQL Server instance with below details

SPID
Login
Database Name
Task State
Command
Application
Wait time
Host Name and many more under PROCESSES tab

Also you can have a look on

Resource waits
Data File I/O
Recent Expensive Queries


Why does MongoDB give me the cannot use 'w' > 1 when a host is not replicated Error message? Here is the information about my development environment: •Microsoft Visual Studio Community 2015 •.NET Framework 4.6 •ASP.NET MVC assembly System.Web.Mvc Version=5.2.3.0 •MongoDB.Driver 2.0.1.27 •Mongodb 3.0.6

From MongoDB client using DOS command prompt, I got the following:

C:\Program Files\MongoDB\Server\3.0\bin>mongo
 MongoDB shell version: 3.0.6
 connecting to: test
 > use foo
 switched to db foo
  > db.runCommand( { getLastError: 1, w: 2, wtimeout:5000 } )
 {
       "connectionId" : 6,
       "n" : 0,
        "badGLE" : {
                "getLastError" : 1,
                "w" : 2,
                "wtimeout" : 5000
               },
           "ok" : 0,
            "errmsg" : "cannot use 'w' > 1 when a host is not replicated",
           "code" : 2
       }
       >

-Is there something wrong with the way I configured mongoDB database on my development computer? -When I read the error message: "cannot use 'w' > 1 when a host is not replicated" it seems to me that mongoDB by default forces developers to have replica set. Is that correct?

I'm only using MongoDB for development on my local computer. I don't want to be forced to use a replica set. Is there any way to resolve the "cannot use 'w' > 1 when a host is not replicated" error message?

You shouldn't specify w: 2 in your query. You're asking to write to two nodes. See the docs here about write concern


SQL Server 2008 R2 Memory Utilization I am facing below issue with my Server. Does this directly indicate that I have to increase my RAM immediately?

Current Setting SQL Server memory values, out of total physical RAM of 128GB:

Min Server Memory - 0
Max Server Memory - 120 GB

I have mentioned the perfmon counters too below. Kindy advise.

We are getting this alert at least every 2 days alternatively.

  SQL Server:Buffer Manager Page writes/sec has exceeded the threshold
  of 90

Perfmon counter Values :

Majorly FULL Backup job runs the time we get this alerts.DBs which are close to 1.2 TB

Memory allocated to SQL Server is 120 G and backup running is for 1.2TB + database and in such scenario of course a high page write would be done. IF this message is only coming when backup is running you might  ignore it. In your exact previous thread, I asked you to ignore it as well.

I also asked you to collect perfmon counter values using performance data collector set and this is much needed because to answer your question whether the alert actually should be considered or can be ignored depends on that. You have to have performance/system benchmark to which you can compare other results.

NOTE: Seeing the database size I can say with confidence that adding more RAM would always help. Ideally if you take my advise RAM allocated to SQL Server should be 1/3rd of total database size. RAM's are cheaper those days instead of wasting too much effort in solving an alert I suggest you to go ahead and add more RAM, that would really be worth.

If you want me to analyze the data collector result personally my website has my mail id send me the details I can help you with the analysis.


MySQL COUNT Query taking too long time to execute I have below query that takes 7 secs to run

SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`
  LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory`
    ON (`Business`.`id` = `BusinessesCategory`.`business_id`)
  LEFT JOIN `yuldi`.`categories` AS `Category`
    ON (`BusinessesCategory`.`category_id` = `Category`.`id`)
WHERE `Category`.`slug` = 'building-construction'

when i run it second time it takes 160 ms. what wrong why it takes so long on 1st run on everytime

EXPLAIN:

| id | select_type | table              | type   | possible_keys                                                                                | key                                          | key_len | ref      | rows  | Extra                    |
|  1 | SIMPLE      | Category           | const  | PRIMARY,UNIQUE_SLUG,index_lug                                                                   | UNIQUE_SLUG                                  | 302     | const    |     1 | Using index              |
|  1 | SIMPLE      | BusinessesCategory | ref    | PRIMARY,fk_businesses_has_categories_categories1_idx,fk_businesses_has_categorie_businesses_idx | fk_businesses_has_categories_categories1_idx | 4       | const    | 49630 | Using where; Using index |
|  1 | SIMPLE      | Business           | eq_ref | PRIMARY                                                                                | PRIMARY                                      | 4       | yuldi.BusinessesCategory.business_id |     1 | Using index              |

There are three aspects you need to rework
ASPECT #1 : THE QUERY
Please look at the Query
SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`
LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory`
    ON (`Business`.`id` = `BusinessesCategory`.`business_id`)
LEFT JOIN `yuldi`.`categories` AS `Category`
    ON (`BusinessesCategory`.`category_id` = `Category`.`id`)
WHERE `Category`.`slug` = 'building-construction';

You are doing LEFT JOINs. That is not necessary for doing a COUNT. Why ?
If you are counting the number of businesses, you only need to count from one table
SELECT COUNT(*) AS `count`
FROM `yuldi`.`businesses` AS `Business`;

Since you have a WHERE clause on Category.slug, the JOINs are needed. You should switch from LEFT JOINs to INNER JOINs. That will produce a smaller internal temp table. That temp table will be searched for the slug 'building-construction'.
ASPECT #2 : INDEXES
From the EXPLAIN plan, I see two indexes similarly named

fk_businesses_has_categories_categories1_idx
fk_businesses_has_categories_businesses_idx

You need to look at the columns for them and make sure the column lists are not identical.
You may want to make two indexes
ALTER TABLE BusinessesCategory
    ADD INDEX bus_cat_ndx (business_id,category_id),
    ADD INDEX cat_bus_ndx (category_id,business_id)
;

Having compound indexes could mean less culling through tables for JOIN information.
Keep in mind, this suggestion is a blind one since I do not know the key distribution of your data.
ASPECT #3 : CACHING
Gathering index statistics is the sake of query evaluation does not immediately translate into a slow query. That slowness is manifested by the real culprit : CACHING. The reason your query runs faster the second time around has to do with the data you read the first time. The data you needed was most likely not available in MySQL's caches when you issued the query.
The two main caches for MySQL are the InnoDB Buffer Pool (for queries hitting InnoDB tables) and the MyISAM key cache (for queries hitting MyISAM tables).
InnoDB caches data and index pages, while MyISAM caches only index pages.
When retrieving data for comparison, mysqld tries to determine if the data it needs is in RAM first. There are server status variables to monitor for this:
InnoDB Caching

Innodb_buffer_pool_read_requests : The number of logical read requests.
Innodb_buffer_pool_reads : The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.

MyISAM Caching

Key_read_requests : The number of requests to read a key block from the MyISAM key cache.
Key_reads : The number of physical reads of a key block from disk into the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

If your query was slow the first time, it simply means the data you need for the query was not in a cache. This would have been indicated by either your Innodb_buffer_pool_reads or Key_reads incrementing.
The second time, your data and/or index pages for the same query would be in RAM and more available for your queries and queries from others.
I suggest you look over the sizes of your InnoDB Buffer Pool and/or MyISAM key cache.


BFS Traversal In MSSQL I have been trying to write queries so that I can get the BFS & DFS traversal of the tree. I am getting the DFS preorder traversal right but unfortunately, I am not able to get the BFS part right.
CREATE TABLE GenericTree (
    NodeID INT NOT NULL,
    ParentNodeID INT NULL,
    CONSTRAINT PK_GenericTree PRIMARY KEY (NodeID),
    CONSTRAINT FK_GenericTree_Parent FOREIGN KEY (ParentNodeID) REFERENCES GenericTree (NodeID)
);
GO

INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (1, NULL);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (2, 1);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (3, 1);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (4, 1);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (5, 2);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (6, 2);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (7, 3);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (8, 3);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (9, 3);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (10, 6);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (11, 6);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (12, 6);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (13, 6);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (14, 7);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (15, 8);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (16, 8);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (17, 11);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (18, 11);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (19, 16);
INSERT INTO GenericTree (NodeID, ParentNodeID) VALUES (20, 16);
GO

CREATE PROCEDURE usp_DFS_PreOrder
@NodeID INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    /*
        EXECUTE usp_DFS_PreOrder;
    */

    DECLARE @v_NodeID INT;
    DECLARE c_CUR CURSOR FAST_FORWARD LOCAL FOR
        SELECT NodeID
            FROM GenericTree
        WHERE ISNULL(ParentNodeID, 0) = ISNULL(@NodeID, 0)
        ORDER BY NodeID;
    OPEN c_CUR;
    FETCH FROM c_CUR INTO @v_NodeID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @v_NodeID;
        EXECUTE usp_DFS_PreOrder @v_NodeID;
        FETCH FROM c_CUR INTO @v_NodeID;
    END;
    CLOSE c_CUR;
    DEALLOCATE c_CUR;
END;
GO

This is my DFS part of the query. I kept everything same except the DFS preorder part of the query. I changed the DFS_Preorder part but I am not getting the correct result for the BFS part. Here is my query for the BFS:-
CREATE PROCEDURE BFS
@NodeID INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    /*
        EXECUTE BFS;
    */

    DECLARE @v_ParentNodeID INT;
    DECLARE c_CUR CURSOR FAST_FORWARD LOCAL FOR
        SELECT ParentNodeID
            FROM GenericTree
        WHERE ISNULL(ParentNodeID, 0) = ISNULL(@NodeID, 0)
        ORDER BY ParentNodeID;
    OPEN c_CUR;
    FETCH FROM c_CUR INTO @v_ParentNodeID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @v_ParentNodeID;
        EXECUTE BFS @v_ParentNodeID;
        FETCH FROM c_CUR INTO @v_ParentNodeID;
    END;
    CLOSE c_CUR;
    DEALLOCATE c_CUR;
END;
GO

Any thoughts where I am doing wrong?

You will get better performance using a recursive common table expression although the syntax is (some would say) a bit more complicated, fortunately I don't have to explain it as there is an excellent article by Itzik where he handles both breadth first and depth first hierarchy evaluation.
https://sqlperformance.com/2020/09/t-sql-queries/fundamentals-of-table-expressions-part-6-recursive-ctes


Why does my query search datetime not match? select *
from A
where posted_date >= '2015-07-27 00:00:00.000'
  and posted_date  <= '2015-07-27 23:59:59.999'

But the result contains a record that has posted_date today: 2015-07-28.
My database server is not in my country.
What is the problem ?

As several others have mentioned in comments and other answers to your question the core issue is 2015-07-27 23:59:59.999 is being rounded to 2015-07-28 00:00:00.000 by SQL Server. Per the documentation for DATETIME:

  Time range - 00:00:00 through 23:59:59.997

Note that the time range can never be .999. Further down in the documentation it specifies the rounding rules that SQL Server uses for the least significant digit.

Notice that the least significant digit can only have one of three potential values: "0", "3", or "7".

There are several solutions/workarounds for this that you can use.

-- Option 1
SELECT
    *
FROM A
WHERE posted_date >= '2015-07-27 00:00:00.000'
  AND posted_date <  '2015-07-28 00:00:00.000' --Round up and remove equality

-- Option 2
SELECT
    *
FROM A
WHERE posted_date >= '2015-07-27 00:00:00.000'
  AND posted_date <=  '2015-07-27 23:59:59.997' --Round down and keep equality

-- Option 3
SELECT
    *
FROM A
WHERE CAST(posted_date AS DATE) = '2015-07-27' -- Use different data type

-- Option 4
SELECT
    *
FROM A
WHERE CONVERT(CHAR(8), DateColumn, 112) = '20150727' -- Cast to string stripping off time

-- Option 5
SELECT
    *
FROM A
WHERE posted_date BETWEEN '2015-07-27 00:00:00.000'
  AND '2015-07-27 23:59:59.997' --Use between

Out of the five options I've presented above I would consider options 1 and 3 the only viable options.  They convey your intent clearly, and aren't going to break if you update data types.  If you are using SQL Server 2008 or newer I think option 3 should be your preferred approach.  That is especially true if you can change away from using the DATETIME datatype to a DATE data type for your posted_date column.

Regarding option 3, a very good explanation about some issues can be found here: Cast to date is sargable but is it a good idea?

I don't like options 2 and 5 because the .997 fractional seconds is going to be just another magic number that people are going to want to "fix." For some more reasons why BETWEEN isn't widely embraced you might want to checkout this post.

I don't like option 4 because converting data types to a string for comparison purposes feels dirty to me.  A more qualitative reason to avoid it in SQL Server is it impacts sargability aka you can't perform an index seek and that will frequently result in poorer performance.

For more information on the right way and wrong way to handle date range queries checkout this post by Aaron Bertrand.

In parting you would be able to keep your original query and it would behave as desired if you change your your posted_date column from a DATETIME to a DATETIME2(3).  That would save storage space on the server, give you greater accuracy at the same precision, be more standards compliant/portable, and allow you to easily adjust the accuracy/precision if your needs change in the future. However, this is only an option if you are using SQL Server 2008 or newer.

As a bit of trivia the 1/300 of a second accuracy with DATETIME seems to be a hold over from UNIX per this StackOverflow answer. Sybase which has a shared heritage has a similar 1/300 of a second accuracy in their DATETIME and TIME data types but their least significant digits are a touch different at "0", "3", and "6". In my opinion the 1/300 of a second and/or 3.33ms accuracy is an unfortunate architectural decision since the 4 byte block for the time in SQL Server's DATETIME data type could have easily supported 1ms accuracy.


Difference between SID and Database name all.

I've searched this topic in Google and there are several information, but it makes me confused more..

At first, I thought SID is the unique key for "database" not "instance", and must be matched with "DB_NAME".

However, In a post, it says SID doesn't need to match DB NAME, global name, unique name, and instance name.. with some practical configuration. In another post, it says SID is combination of database name + instance number..

In my short knowledge,

Instance is server ORACLE processes are running, which contains SGA.

Database is just storage that user applications' data is stored. When instance runs, it access to DB, mount the control files, open the data files and redo logs.

If the above comment is right, I think SID must be Database, and.. could be DB_NAME..

Now I'm confusing Database, Instance, SID, and DB_NAME.

Thank you for reading..

Database is just storage that user applications' data is stored.

Yes. It is a collection of the files (datafile, controlfile, redo log, tempfile, block change tracking file, etc.) on the disk.

  Instance is server ORACLE processes are running, which contains SGA.

Yes. Instance is the collection of processes + SGA.

Typically one instance belongs to one database, but a RAC database can have multiple instances on multiple nodes.

db_name is the name of the database. It is stored in the database files as and pfile/spfile as well.

SID identifies the instance by the name. It can be changed by setting the instance_name parameter. It is not stored in the database files, only in the pfile/spfile. (Note that it is not a strictly unique identifier, multiple instances can exist on the same server with the same name, running from different Oracle Homes).

SID (instance_name) defaults to db_name.

In case of a cluster database, SID (instance_name) defaults to db_name + instance_number.

On top of the above, SID (instance_name) can be changed by setting instance_name to a custom value.


Can a SELECT statement block an ALTER TABLE? I see in Postgres docs:

  Two transactions cannot hold locks of conflicting modes on the same table at the same time

During maintenance (schema migration) we saw cases when we can't ALTER TABLE because of hanging SELECT in transaction (pg_locks + pg_stat_activity).

In order to proceed migration we revoke CONNECT from "rogue" users and kicked all their sessions.

Is that correct that:

BEGIN;
SELECT ...;

will block ALTER TABLE until COMMIT or ROLLBACK?

You got it right.

This is how PostgreSQL prevents conflicts: you cannot drop or modify a table while someone is using it.


Does ALTER INDEX ALL REBUILD use more transaction log space with simple recovery model than rebuilding each index individually? An "ALTER INDEX ALL REBUILD" operation on SQL Server 2012 failed because the transaction log ran out of space. The indexes have never been reorganized or rebuilt, so fragmentation is over 80% on nearly all of them.

The DB uses simple recovery model. I assumed that following each index operation performed by the "ALL" form of the command, the transaction log data would be flushed prior to the next index rebuild. Is that how it actually works, or are the index rebuilds logged as if they are part of a single transaction?

In other words, could I reduce transaction log growth by writing a script to perform each rebuild individually? Are there any other factors to consider?

I assumed that following each index operation performed by the "ALL" form of the command, the transaction log data would be flushed prior to the next index rebuild. Is that how it actually works, or are the index rebuilds logged as if they are part of a single transaction?

1) Log flushing: the SIMPLE recovery model does not clear the log after every transaction, but at checkpoints. (link for more info)

2a) REBUILD ALL: yes, REBUILD ALL works as a single transaction. The index rebuilds within have their own transactions, but the overall operation isn't fully committed until the end. So yes, you might limit log file growth by rebuilding individual indexes (and possibly issuing CHECKPOINT commands).

2b) Proof! Here, have a demo script. (Built in 2016 dev)
First, set up a test db, with table and indexes:

USE master
GO

CREATE DATABASE Test_RebuildLog
GO

ALTER DATABASE Test_RebuildLog
SET RECOVERY SIMPLE
GO

USE Test_RebuildLog
GO

CREATE TABLE IndexTest
(ID int identity(1,1),
a char(1),
b char(1))

CREATE CLUSTERED INDEX CIX_IndexTest_ID ON IndexTest(ID)
CREATE INDEX IX_IndexTest_a ON IndexTest(a)
CREATE INDEX IX_IndexTest_b ON IndexTest(b)

INSERT IndexTest
(a,b)
VALUES ('a','b'),('z','y'),('s','r')

Now you can compare log activity between REBUILD ALL and rebuilding individually

CHECKPOINT
GO
ALTER INDEX ALL ON IndexTest REBUILD

SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation = 'LOP_COMMIT_XACT'
OR Operation = 'LOP_BEGIN_XACT'
GO

CHECKPOINT
GO
ALTER INDEX CIX_IndexTest_ID ON IndexTest REBUILD
ALTER INDEX IX_IndexTest_a ON IndexTest REBUILD
ALTER INDEX IX_IndexTest_b ON IndexTest REBUILD

SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation = 'LOP_COMMIT_XACT'
OR Operation = 'LOP_BEGIN_XACT'
GO

Note how the first open transaction (Transaction ID 0000:000002fa for me) isn't committed until the end of the REBUILD ALL, but for the index-by-index rebuilds, they are successively committed.


"Syntax error or access violation" error with nested square brackets in column alias When trying to create sp_BlitzIndex I got the error "Syntax error or access violation"

I narrowed this down to column alias names like:

SELECT
       ...,
       index_definition AS [Definition: [Property]] ColumnName {datatype maxbytes}],
       ...

Removing the nested square brackets solved this, but the "]]" quoting looks fine to me :(

I checked my version-control repository and the previous version also had the nested Square Brackets, so presumably it ran OK for me previously.

Not sure what has changed here that prevents this running now, but the sp_BlitzIndex scripts starts with a long list of SET options (they appear unchanged from previous version) so doesn't seem like it would be any of them

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Here's a trivial use-case which also fails in Query Analyser

SELECT 'xxx' AS [xxx xxx]] {xxx} xxx]
SELECT 'xxx' AS []]{}]

The double close square brackets AND the open and close curly-brackets are ALL required, if any single element of those is left out then QA works OK. As does changing the order - e.g. this works OK:

SELECT 'xxx' AS [{}]]]

My guess is that it's a bug in Query Analyser. The frustration is that the "Syntax error or access violation" error gives NO line number, so it is hard to find the cause (I located it by trying to execute the first half of the code block and then doing a binary-chop to narrow down the location of the culprit)

Ah ... found the answer.  I was running this in an old version of Query Analyser (and got the error above), but it runs fine in SSMS, so error is (falsely) generated by a parsing error in Query Analyser.


TimeZone problem with Oracle SQL Developer for Windows on MySQL server I am trying to connect to a MySQL database using Oracle SQL Developer, but I get this error: Status: fail, test failed:

  Estado: Fallo:Fallo de la prueba: The server time zone value 'PDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

According to some solutions I should use a connection string such as the following, to override the server TimeZone definition.

jdbc:mysql://mysql.server:3306/db?serverTimezone=UTC

But I cannot find where to define a connection string on Oracle SQL Developer. I only get this screen when configuring (or changing configuration):

(In my test it says localhost but I am doing an SSH tunnel to a remote server.)

There is no way to add extra parameters such as serverTimezone from the configuration box.

Some other solutions propose to create a map of timezones (v.g. mapping PDT to Americas/Los_Angeles).

I cannot find a Java control. It is supposed to be integrated within Oracle SQL Developer. I just cannot find any place I can add any extra configuration. I don't have administrative privileges in the server. I might change to some other MySQL client (I can actually work fine with PHPMyAdmin), but I also need to test on Oracle Servers.

I found the following solution on a forum (https://community.oracle.com/thread/4144569?start=0)

You have to just insert a string in the port value
PORT_NUM?SERVEROPTIONS&dummyparam= and leave the database field empty

if you need to specify the database name, you just have to use this other string (I suppose, not tested)
PORT_NUM/DB_NAME?SERVEROPTIONS&dummyparam=

Hope this is still relevant to you or maybe someone


MySql Cluster for educational use I am student and I am writing Master of Science paper about in-memory databases. I want to use MySql Cluster for benchmarks in my work.
Can I use MySql Cluster free of charge for educational purposes or do I need to pay for it?

From the downloads page:

  It is available as both open source and commercial editions. The Community (OSS) Edition is available under the free software/opensource GNU General Public License (commonly known as the "GPL").

If GPL is ok for you, then you're fine. The Carrier Grade Edition is a commercial offering though. You'll need to contact Oracle to see if they have special pricing or licensing for educational purposes.


How to Natively Return Output when using Invoke-Sqlcmd? How do I natively log output from the PowerShell Invoke-Sqlcmd?

I am using Invoke-Sqlcmd to loop through a large (50,000-90,000 SQL files w/ 1-10 inserts in each) set of small SQL insert statements. Most of the insert statements run correctly, but a few of the strings are too long and I get the "string or binary data would be truncated" error.

Right now I am using Start-Transcript to capture the output and identify the problematic insert statements, but Start-Transcript captures everything. I'd like to only catch the errors -or- just catch the Invoke_Sqlcmd statement output.

If this worked (as I believe it should), I would be all set:

Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true | Out-File -FilePath "C:\Temp\Errorlog.txt" -Append

Or if this worked:

Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true | Tee-Object -FilePath "C:\Temp\Errorlog.txt" -Append

But neither of those work.

I have also tried this monstrosity but I don't get any errors at all from it:

$sqls = (Get-ChildItem -Path "I:\2019_06_24\_SQL"-Filter *.sql | Sort-Object -Property CreationTime).FullName
        foreach ($sql in $sqls)
            {
            $Error.Clear()
            try
                {
                Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true
                }
            catch
                {
                'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' | Out-File "C:\Temp\Errorlog.txt" -Append
                $sql | out-file -filepath "C:\Temp\Errorlog.txt" -Append
                $Error | out-file -filepath "C:\Temp\Errorlog.txt" -Append
                }
            }

How do I log directly from the Invoke-Sqlcmd statement?

This code is based on your "monstrosity" example above.

At the start of each loop it clears the $error variable. Then if the Invoke-Sqlcmd generates an error the IF statement picks it up. It will then add the FullName ($sql) to the log file and then add the error on the next line.

$sqls = (Get-ChildItem -Path "I:\2019_06_24\_SQL" -Filter *.sql |
    Sort-Object -Property CreationTime).FullName
foreach ($sql in $sqls)
{
$error.clear()
Invoke-Sqlcmd -InputFile $sql -ServerInstance LocalHost -Database Test_DB -Credential $pwd -Verbose -OutputSqlErrors $true
if ($error -ne $null)
    {
    $sql + '
' + $error | Out-File -FilePath "C:\Temp\ErrorLog.txt" -Append
    }
}

As it is, the ErrorLog.txt will look something similar to this:

I:\2019_06_24\_SQL\Test_File_5.sql
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 30, Procedure , Line 3.
I:\2019_06_24\_SQL\Test_File_303.sql
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 30, Procedure , Line 3.


pg_basebackup failing from another server I want to take backup of my PostgreSQL database cluster from another server. I'm using below command ,from another server:

[root@shiwangini2 pgsql]# sudo -u postgres  pg_basebackup -h 192.168.XX.XX  -p 5432  -Ft -D /var/lib/pgsql/db_file_backup

However, I'm getting below message after running this:

[root@shiwangini2 pgsql]# sudo -u postgres  pg_basebackup -h
192.168.XX.XX  -p 5432  -Ft -D /var/lib/pgsql/db_file_backup
Password:

  pg_basebackup: could not get write-ahead log end position from server:
  ERROR:  could not open file "./pg_hba.conf": Permission denied
  pg_basebackup: removing contents of data directory
  "/var/lib/pgsql/db_file_backup"

In order to fix this,after googling I updated my server's pg_hba.conf file like below,to allow connections from outside:

host    all             all              0.0.0.0/0
md5
host    all             all              ::/0                            md5

After that I restarted PotgreSQL services. And again, when I'm trying to backup it from another server, I'm still getting the same error. Please let me know if anything wrong I'm making here. Or any other way to take backup from another server.

What are the permissions set on the file pg_hba.conf?

Does the postgres user have the ability to read it?

Here is my configuration:

-rw-------. 1 postgres postgres  4334 Apr  4 22:14 ./pg_hba.conf


Not all variables bound ORA-01008: not all variables bound error I am using Oracle SQL developer. I am running a query on employees table which is by default, having columns like
first_name, last_name, employee_id, email, phone_no, hire_date, job_id, salary, commission_pct, manager_id, department_id.

I am writting a report that displays the last name and salary of employees who earn more than an amount that the user specifies after a prompt.
If you enter 12000, it should display all employees earning more than 12000.
Eg: Salary_value: 12000.

My report is like this

select last_name, salary
from employees
where employees.salary>&sal;

But I am getting an error like this

ORA-01008: not all variables bound
01008. 00000 - "not all variables bound"
*Cause:
*Action:
Vendor code 1008

Clearly there is nothing in the cause section. I need help please. Thanks in advance.

BUT I can succesfully run it from Sql plus command line utility as well as I can run it as a *.sql file. But It is not working in case of a report. Why is this happening?

Maybe you have set define to off.

set define off
select last_name, salary from employees where employees.salary>&sal;

Error starting at line : 2 in command -
select last_name, salary from employees where employees.salary>&sal
Error at Command Line : 2 Column : 31
Error report -
SQL Error: ORA-01008: not all variables bound
01008. 00000 -  "not all variables bound"
*Cause:
*Action:

Set it to on (which is the default value by the way):

set define on
select last_name, salary from employees where employees.salary>&sal;

With define set to on, SQL Developer will prompt for a value for the variable &sal.


Using SQL Server Snapshot Isolation is it possible to determine a row being read is not the latest? I understand that starting a transaction under the SNAPSHOT isolation level means that any subsequent SELECT statement will provide data as it was when the transaction was started.
The following statement, for example, will return data from the table without blocking writers as well as ignoring subsequent changes:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSATION

SELECT  col1, col2, [something here to tell me the row is out of date]
FROM    dbo.SomeTable st
WHERE   (st.SomeForeignKey = @SomeValue)

COMMIT TRANSATION

Is there a performant way of knowing whether a record retrieved from this SELECT statement has been modified since the transaction was started (without running the entire thing again)?

As far as I know there is no way to do this, you can see how many rows are in tempdb using a query like the below
SELECT  version_ghost_record_count
  FROM  sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.sometable', N'U'),
        1,
        NULL,
        N'DETAILED'
    )
  WHERE index_level = 0

but this will only tell you at a table level (or partition if you are using those) & you want to get down to row level, more info here :-
https://www.red-gate.com/simple-talk/sql/performance/read-committed-snapshot-isolation-high-version_ghost_record_count/


Incremental backup in postgresql I want to migrate a production database from one linux server to other.
I am not concern about upgrading my database version, just I want to move the database from one server to other with minimum down time.

The version of my database is 8.3. Its of size 160 GB.

It takes 12 Hours to take the dump and 8 hours to restore it.

Over here the downtime is of 20 Hours, which I cannot afford.
I can afford a down time of 4 hours at max.

So how will I refresh the delta changes that happened in 20 Hours into the production database with the database over the other server.

Please help.

OK - if you can't dump + restore, and don't want to understand your system well enough to set up replication then you've got only one option left.

You will need to run identical setups at each end (version of PostgreSQL, 32/64-bit, compilation options, file paths etc).

Then, scp the entire data directory - including all the WAL files (pg_xlog) and all other sub-directories.

During a quiet period, halt your primary server and use rsync to bring your copy up-to-date with respect to the original. Then, start the copied server and off you go.

Whether you can do this in less than 4 hours, I don't know. It will depend on how efficient rsync really is, the latency between both systems, how many updates you have had etc.

It might be that running rsync before halting the primary server will make the real run go faster. Difficult to say.


postgresql: how to know when a function was modified Scenario:
Someone creates a function named "foo" in a PostgreSQL database.
Next, someone modifies the inner workings of function "foo".

Question:
Does PostgreSQL offer a means to know who modified function "foo" and when "foo" was modified?

Sure, you can use EVENT TRIGGERS and you can create them on any of these DDL events listed here

CREATE OR REPLACE FUNCTION abort_any_command()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION '[%] user % attempt command % which disabled',
    current_timestamp,
    current_user,
    tg_tag;
END;
$$;

CREATE EVENT TRIGGER abort_ddl ON ddl_command_start
  WHEN TAG IN ('DROP FUNCTION', 'CREATE FUNCTION')
  EXECUTE PROCEDURE abort_any_command();

You can create the EVENT TRIGGER on ddl_command_end if you want an event to trigger ONLY after the operation has completed successfully. After you create an EVENT TRIGGER, you can try to drop the function and you'll see.

DROP FUNCTION abort_any_command();
ERROR:  [2017-01-09 20:27:33.843529-06] user ecarroll attempt command DROP FUNCTION which disabled

If you just want it in the logs, rather than to raise the exception then use a different level than RAISE EXCEPTION. You should also easily be able to make this INSERT into a table if you'd like.


Grant all the privileges to a user over all other user's objects in Oracle I would like to know how to grant all the privileges to a user over all other user's objects in oracle. Greetings and many thanks

Which privileges?  Of course they are granted by using the appropriate GRANT command, but I suspect that is not what you are asking.  You could use the ANY option (like GRANT SELECT ANY TABLE ...) but I strongly recommend against it as it violates the principle of granting least privilege necessary.  I suspect what you really need is something like this:

set echo off feedback off header off pagesize 0
spool doit.sql
select 'grant select on table '||table_name||' to someuser;'
from dba_tables
where <whatever condition to filter the list of tables>
;
spool off

Then do a sanity check on the spooled file 'doit.sql', edit as necessary, then execute it.  Some do it all in one pop with a PL/SQL loop, but I'd rather capture the commands first and be able to do the sanity check on it.


What can be represented in a relational database that can't be represented in a graph database? I read Blockeel's article that is comparing different paradigms how to represent data. He mentions following advantages of the relational paradigm in comparison to graph paradigm:

They can naturally represent non-binary relationships.
There is a standard way of associating information with each individual or relationship.
They naturally distinguish between different types of individuals, each type possibly having a different kind of information associated with it.

Can you agree on this list of limitations of the graph paradigm in comparison to relational paradigm?

And can you agree, that in the regard of expressiveness of the paradigms it holds: logic ⊃ relational ⊃ graph
?

Disclaimer: I am not a computer science academic. There are undoubtedly nuances and implied knowledge in that paper of which I am ignorant. I do, however, have some small practical exposure to these technologies.

To say that graph is a subset of relational would mean there is something that can be done in relational that cannot be done in graph. I would disagree with this. Data can be stored equally in either of these systems. Any graph can be represented relationally by a Node table and an Edge table (perhaps with additional NodeAttribute and EdgeAttribute tables) 1. Any relational database can be stored in graph form with one node per row, one attribute per column and one edge per foreign key. Non-binary relationships can be represented by inserting a junction node to which the multiple edges connect.

I know little of Logic stores (which I believe are also known as triple stores) but they seem to have a very direct translation to graphs and, hence, to a relational model.

As for standards, they only exist because some people decided to sit down and write them. There is nothing inherent in the structure of the universe that caused SQL to come into being. An equal effort could produce standards for graph processing. Languages like Gremlin and Cypher seems to be gaining broad acceptance and may, eventually, lead to a ISO standard graph language. The maths that underlies graph is just as valid as set theory.

The relational model has concepts like schema, primary key and referential integrity which are not part of the graph model. There is no reason why an actual graph DBMS could not implement these features, however. Indeed there are many databases hosted in relational software which are unnormalised, lack primary keys and have no DRI!

Graphs allow traversals of indeterminate length. Some RDBMs allow recursive queries which, although more cumbersome, can accomplish the same effect.

It seems to me these systems differ not so much in the data they can store, but in the ease with which their underlying structures can answer different classes of questions. Relational database engines are optimised to process sets of similarly-structured items (tables). Graphs are optimised for traversing links from node to node.

1 SQL Server 2017 has implemented graph processing with precisely this form. It has added extensions to identify a table as containing nodes or edges.


VARCHAR primary key - MySQL Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column.

This seems like the most obvious approach. However, alarm bells are ringing in my head because I am using a VARCHAR column for a primary key, which might slow down operations when querying the table.

I could introduce a third column called cat_id INT AUTO_INCREMENT and set that to be the PK, but it would introduce a new column which have no meaning.

Apart from which one would be faster, what other considerations should be taken?

N.B. I predict there will be at most 1000 categories or so so the number of rows are not very high. However, the categories PK column will be a reference column for many foreign keys, in other tables.

Should I use (unique) usernames as PK's also?

VARCHAR column as Primary Key is not a good choice as normally we create Cluster Index on same column.

Cluster Index on VARCHAR columns is a bad choice because of expected high fragmentation rate. Every new inserted key value will try to find its place somewhere between existing keys and normally cause page split and high index fragmentation. As a result poor performance and extra index rebuild/reorganise cost.

Secondly while using varchar key column as Foreign Key will take extra space as compared to Pseudo key auto-incremented column.

BUT

Clustered index on  auto-incremented column may create "hot spot". Read this carefully Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

Though hot spot could be a problem, when to many users are trying to insert values but still in your case i would like to go for auto-incremented column as compare to varchar.


Wordpress query on MyISAM tables in MariaDB on ZFS takes too long to execute I use a pretty common setup, some wordpress sites running on Debian 10 (Buster) with mariadb 10.3.27 on ZFS, on a decent server with SSDs and lots of RAM, pretty smooth operation with low CPU utilization. There is this query wordpress makes,
SELECT DISTINCT meta_key
FROM wp_postmeta
INNER JOIN  wp_posts ON post_id = ID
WHERE post_type <> 'product'
  AND post_type <> 'product_variation';

For some reason I cannot possibly imagine, on database A (InnoDB tables) it runs in about 3 seconds, and on database B (MyISAM tables) it takes over 2 minutes. wp_postmeta has 198938 records and wp_posts 25189 in database B, where on database A they both have many more records. I've tried dumping, deleting the database and recreating it to no avail. If I move the database to another similar server with EXT4, the query on MyISAM tables finishes in about 4 seconds. If I alter the tables to InnoDB, query execution finishes in about 4 seconds. Also, when running the query on MyISAM tables on ZFS I noticed the disk reads spike to about 50MB/s and up to 80MB/s while the query is running. 2 minutes of reads at 50MB/s is 6GB of data, in a database with a total size less than 500MB.
This difference in execution time between MyISAM and InnoDB was noticed on Debian systems with ZFS 0.6 and 0.7. The exact same databases on a FreeBSD/ZFS server display no such difference, with queries executed within a a few seconds for both MyISAM and InnoDB tables.
Does anybody know what could make such a huge difference between MyISAM and InnoDB on ZFS on Linux, and why ZFS on FreeBSD does not show this behavior?
The tables created as:
CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
  `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT 0,
  `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=MyISAM AUTO_INCREMENT=33318 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT 0,
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM AUTO_INCREMENT=279035 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Short answer

ZFS, etc, are not the main problems.  (See Comments for a rebuttal on ZFS.)

Use InnoDB, not MyISAM.

Fix the indexes in postmeta, as discussed here:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Avoid index prefixing.  (The above link discusses that, too.)

(If you must use MyISAM, then the indexes recommended there are not adequate.  Let me know if you need more help.)
Long Answer
Several dimensions to this question:

Version -- MariaDB 10.3 versus other versions of MySQL/MariaDB.  This is probably not significant.

OS -- Linux vs FreeBSD vs Windows -- The OS is just a middleman; all are quite mature and have mostly caught up with each other.

HDD vs SDD -- SDD drives are inherently faster for any access.  Plus they are faster for random accesses due to the lack of a physical "seek".

FileSystem -- ZFS versus many others.  "Journaling" and other advanced techniques will have some impact on the effective performance.

RAID, and especially "battery-backed write cache" has some impact on reads and possibly a big impact on writes.  This applies across all disk operations in all applications.

InnoDB vs MyISAM -- These differ significantly in locking, caching, the organization of data.  Indexes differ a lot.  In MyISAM, fetching a block of data uses the FS's blocksize and caches it in the OS.  A MyISAM index block is 1KB, which is likely to be smaller than the FS block slide, thereby leading perhaps to an extra level of caching.  For InnoDB, all blocks are 16KB.  The interactions between these MariaDB block size and the FS block is hard to predict.  Peformance at many levels depends on whether the data or index needs to be scanned linearly or probed randomly.

The INDEXes -- Above, I focused on this topic because it is where the main improvement can be had.  In the situation here (accessing WP's postmeta), will have a significant and far-reaching performance impact.

Impact of INDEX

My recommendations include having this composite index: (post_id, meta_key) which is optimal for your slow query.  Furthermore...
When using a secondary or primary index, MyISAM reaches into the data file with a random seek and fetches a "block", hence performance is dependent on FS and HDD/SSD.
With InnoDB, the PRIMARY KEY is "clustered" with the data, thereby eliminating the extra random fetch.  Some of my recommendations take advantage of this efficiency.
With InnoDB, a secondary index requires a second lookup via the PK.
So, in general, it is beneficial to lay out the data in some clustered manner to improve performance.  Changing the PK to start with post_id, helps in this respect.  Furthermore, InnoDB avoids the random seek that MyISAM requires.
"Index prefixing" (eg, "meta_key(191)") is virtually useless.  So, part of the performance enhance involves any of about 5 techniques to eliminate prefixing.  I'm pretty sure MariaDB 10.2.2  has already fixed the issue; this would let you remove the (191) from any indexes having it.  Second best is if you can assure that all keys will always be under 191 characters, then changing the definition of meta_key is a simple answer.
All of the above add up to decreasing the 6GB of disk activity, probably significantly.  Sorry, but I cannot say specifically if ZFS has any impact.

The specific Query

Scan the entire posts table.  There is no getting around this.  This will be a linear scan through the "data".  The engines do this task quite differently, but neither engine has a strong advantage here.
For each post row, reach into postmeta.  This is where the engine and indexes make a significant difference.
Dedup the list of meta_key values -- This may be done in RAM or on disk, at the whim of the Optimizer.  You could change a setting to influence that decision, but it would have the side effect of potentially impacting the performance of all other queries, so I won't discuss it.


Postgres compound index not delivering expected improvements I have a table with a definition something like this….

CREATE TABLE foo (
  A CHAR(6),
  B TIMESTAMP,
  Lots more fields……
)

It contains about 600 million records.

Originally it had these two indexes on it….

CREATE INDEX i1 ON foo (A);
CREATE INDEX i2 ON foo (B);

Most of the queries I run look something like this…

SELECT * FROM foo
WHERE A = ‘ABCDEF’
AND B > ‘some date’
AND B < ‘some other date’

I wanted to improve the performance so ( following advice on this forum) I added this compound index (previous two indexes still remain)….

CREATE INDEX i3 ON foo (A,B);

When I do an EXPLAIN it’s apparent that the new index isn’t being used, but index 'B' is being used.

This leads to some questions…
1)  Why isn’t the new index being used?
2)  Can I disable my first two indexes to try and force the compound index to be used? (I don’t want to DROP the indexes because they’re likely to days to re-create them)
3)  Does the order of the WHERE clauses make any difference? Would a compound index on A,B be any different from one on B,A?
4) how can I improve performance on this query?

Maybe the compound index isn't used because it thinks the single-column index is selective enough that there is no point in adding more selectivity.  The way to find out is by seeing the results of EXPLAIN (ANALYZE, BUFFERS) SELECT ...

You can disable the indexes by dropping them inside a transaction you later rollback:

BEGIN;
DROP index i1,i2;
EXPLAIN (ANALYZE, BUFFERS) SELECT....;
ROLLBACK;

Rolling back the index-drop will be nearly instantaneous, it doesn't have to rebuild them.  If you run it in production, be aware that the table will be AccessExclusive locked for the duration of the transaction.

You also don't have to experiment with the full 600 million rows.  6 millions is probably plenty to get some valid insight:

create table foo_exp as select * from foo where random()<0.01;
vacuum analyze foo_exp
create index...;
explain (analyze, buffers) ...;

The order in the WHERE clause doesn't matter, but the order in the btree index does.  The equality-constrained column should be the leading column in the index, with the range-constrained column after.

For asking performance questions, see https://dba.stackexchange.com/tags/postgresql-performance/info


Timeout in SSIS package running DBCC CHECKDB I have created an SSIS package to run DBCC CHECKDB on all the databases on the server and send error messages by email.

But for several of the databases, I get an exception running my script:

  Timeout expired.
  The timeout period elapsed prior to completion of the operation or the server is not responding.

The only timeout I can find in the project/package is set to 0, which I presume means no timeout.

DBCC CHECKDB runs from SSMS just fine.

As it turns out, there is a CommandTimeout property on the SqlCommand object.