Are there any reasons why a table wouldn't show in sys.dba_tables? (Oracle) I have 2 queries

 SELECT *
 FROM sys.dba_indexes
 WHERE Table_Name = 'XDB$RESOURCE'

and

 SELECT *
 FROM sys.dba_tables
 WHERE Table_Name = 'XDB$RESOURCE'

Are there any reasons why a table wouldn't show in sys.dba_tables? As far as I've read all tables should show here, hence why I'm using this rather than all_tables.

Can an index in Oracle be against a deleted table?

Does sys.dba_tables show data relevant to access you have, and if so is there any table that rather brings back nothing than half the data?

Relatively new to Oracle, any explanation or advice appreciated.

You are right. XDB$RESOURCE is not in DBA_TABLES. It is in DBA_OBJECT_TABLES, because it is an object table.

Object tables are listed in {DBA,ALL,USER}_OBJECT_TABLES. All regular (non-object) tables are in {DBA,ALL,USER}_TABLES.


DB2 online restore - “SQL1035N The database is currently in use.” I am trying to do ONLINE RESTORE ROLLFORWARDING TO END OF LOGS AND COMPLETE. When I run this command I am getting error SQL1035N The database is currently in use. It does not allow any connections to it!

If I deactivate database and then run command I can restore but then my database is not available for users but it should be becuase it is live production system 24/7. How to resolve this?

[This answer was also posted to the duplicate question on Stack Overflow]

When you perform an online restore, DB2 must lock the tablespace(s) you are trying to restore. The restore process essentially overwrites the file on disk containing the tablespaces' data. This is incompatible with applications using data in the same tablespace while the restore occurs.

If your database has all data in a single tablespace, then an online restore is not particularly useful. If you have multiple tablespaces in the database, applications may be able to continue functioning while the corrupted tablespace(s) are restored, but of course this requires some planning in your application and database design.


Permission denied for view beyond its own schema There are two schemes rohdaten_fiat and staging_fiat. I have created a view in the latter one based on the first one.
CREATE VIEW staging_fiat.udz_odx_dtcs AS
SELECT * FROM rohdaten_fiat.udz_odx_relational_dtcs;

While keeping the database connection alive I can access the view, e. g.
SELECT * FROM staging_fiat.udz_odx_dtcs;

It runs under the following parameters:
SELECT current_user, current_setting('search_path'::text) AS search_path
     , relowner::regrole, relnamespace::regnamespace, relname, relacl
     , pg_get_viewdef(c.oid) AS view_definition
FROM   pg_class c
WHERE  relname = 'udz_odx_dtcs' OR relname = 'udz_odx_relational_dtcs';

See: https://pastebin.com/Hn7jAUat
However, after closing the connection and reconnect to the database, I can not:
SELECT * FROM staging_fiat.udz_odx_dtcs;

ERROR:  permission denied for relation view

The environmental parameters are the same (see https://pastebin.com/Hn7jAUat). Curiously, I can execute the query of the view manually:
SELECT * FROM rohdaten_fiat.udz_odx_relational_dtcs;

Only the view does not work. Where get the permissions lost? And why?
It's PostgreSQL 10.12. The connection including the search_path is absolutely identical.

To diagnose, inspect the output of this query before and after reconnecting in your question:
SELECT current_user, current_setting('search_path'::text) AS search_path
     , relowner::regrole, relnamespace::regnamespace, relname, relacl
     , pg_get_viewdef(c.oid) AS view_definition
FROM   pg_class c
WHERE  relname = 'view'  -- actual view name
OR    (relname = 'table' AND relnamespace = 'schema1');   -- actual table & schema name

You should get two or more rows each time. I omitted the schema of the view on purpose to see whether there might be others with the same name.
Oh, and make sure, your transaction is committed. Read the manual here.
This is not a direct answer, but it should enable you to get your answer.


Creating Users on secondary server in log shipping I have a production Server say ServerA I have setup log shipping to ServerB which is left in read-only mode. The purpose of this log shipping is to lower the load on production server for some expensive queries (painful reports).

Now if I have to create some logins using our domain accounts. I cannot do this because the secondary database is in standby mode.

I thought if I create these logins on Primary server it will be copied over to secondary server then the logs are restored there but this isnt the case.

I have done a lot of research online finding a way around to this. I found the following resources for this. I tried every method suggested in this articles but none of them seems to work.

1) Log Shipping in SQL Server 2008 R2 for set BI on replicated database

2) How to transfer logins and passwords between instances of SQL Server

3) Orphaned Users with Database Mirroring and Log Shipping

Has someone experienced the same issue? what did you do? Is there any way around for this issue? Any suggestions any pointer please.

Two options:

On your log shipping primary server, find a user in that database with the permissions you want (or create one). If you create one, log shipping will then transfer that over (since that's a database-level object). Then, to find the SID of the user you want on your primary server: select sid from sys.database_principals dp where type = 'S' and name = '<<YourUserNameHere>>'. (SID is basically a unique ID - it's completely unreadable) Then, on the secondary server, create a login with that specified SID: CREATE LOGIN [<YourLoginNameHere>>] WITH PASSWORD ='<<YourPasswordHere>>', SID = <<the SID from primary server>>. By specifying the SID, that login will have the same SID as the user in your logshipped database - you should then be able to log into that user.
Create a login with server-level permissions (i.e. sysadmin) on your log shipping destination. This is a really bad idea for a multitude of reasons (especially if you have other databases on that server).

Worked example of creating a user: I have a database called LogShippingTest and I want to give a login on my secondary server db_datareader on the logshipped copy so that Marketing can read stuff.

On the primary server:

USE [LogShippingTest];
CREATE USER [DataReaderForMarketing] WITHOUT LOGIN;
ALTER ROLE [db_datareader] ADD MEMBER [DataReaderForMarketing];
SELECT sid FROM sys.database_principals dp WHERE type = 'S' AND name = 'DataReaderForMarketing'

Let's say the SID is 0x70DD0D59ADF3FD4ABB000037826A2888 (I just made that up but we'll go from there). Now, either force log shipping to run, or wait until it runs itself and the user exists on the secondary side. Then, on the secondary:

USE [master];
CREATE LOGIN [DataReaderForMarketing] WITH
   PASSWORD = 'ThisIsAVerySecurePassword',
   SID = 0x70DD0D59ADF3FD4ABB000037826A2888

You should then be able to log in as the DataReaderForMarketing login on the secondary server and have db_datareader on the logshipped secondary database. (You can change the permissions on the primary database user and they'll logship down to apply to the secondary, too!)


How to retire a physical standby in Oracle 11.2 I have a production Oracle 11gR2 RAC database with a single node physical standby. Now it has become the time to retire the old standby and pass that role to a RAC on fresher hardware.

We don't use Data Guard Broker.

I haven't been able to find much on this particular process, so I figured maybe there wasn't all that much to it. Apparently there is at least a tad more than I have been able to think of.

After stopping the log apply on the old standby, I attempted to simply clear the standby log destination and set a new destination for log shipping. Oracle then complains that the DB_UNQUE_NAME of the new physical standby is not a part of the current Data Guard configuration.

SQL> alter system set log_archive_dest_3='';

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system set log_archive_dest_3='SERVICE=PDB10SBY LGWR ASYNC REGISTER VALID_FOR=(online_logfile,primary_role) REOPEN=60 DB_UNIQUE_NAME=PDB11SBY' scope=both sid='*';
alter system set log_archive_dest_3='SERVICE=PDB10SBY LGWR ASYNC REGISTER VALID_FOR=(online_logfile,primary_role) REOPEN=60 DB_UNIQUE_NAME=PDB10SBY' scope=both sid='*'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16053: DB_UNIQUE_NAME PDB10SBY is not in the Data Guard Configuration

A quick query to v$dataguard_config confirms that there is an active configuration containing the DB_UNIQUE_NAME of the old standby database.

SQL> select * from
  2  v$dataguard_config;

DB_UNIQUE_NAME
------------------------------
PDB10
PDB10_STBY

So then the question is, what are the steps required to retire an existing Data Guard configuration so that a new physical standby database can be deployed?

The unique names of the databases in a Data Guard configuration are listed in the log_archive_config parameter in the following form:

log_archive_config='DG_CONFIG=(PDB10,PDB10_STBY)'

You can remove the old one and add the new one as:

alter system set log_archive_config='DG_CONFIG=(PDB10,PDB10SBY)';

After this, you can set log_archive_dest_N to the new standby.


PostgreSQL failover and replication I'm evaluating PostgreSQL 9.1 and have few questions related to failover and replication details.

I have few test scenarios. First one with a Master server and few Slaves. In case Master crashes I want one of the Slaves to become a Master. After Master get back to normal state it should sync with other servers in cluster (apply all changes done while it was down) and claim back Master role or become a Slave.

The problems I see with PostgreSQL and current scenario are the following.

1) I don't see built-in tools for detecting Master server outage. I read that pgpool can handle it and create trigger file, I also read that people use Linux heartbeat or similiar tools for this. Okay, I can detect failover and assign a new Master in cluster. Will the other Slaves understand that there is a new Master and they should backup it now?

2) I don't understand the failback procedure. Master and Slave host configurations are different. So will I have two Masters after crashed Master failback? How will servers get back in sync? I saw only manual solutions like "transfer data folder to server and restart it". So what is the solution or best practice or at least key principal here?

3) How should I handle server outage on client side? When I create connection I explicitly specify server IP. Should I develop some kind of ConnectionManager which will know my Master-Slave structure, send requests to Master only and in case of connection loss will switch to backup servers and so on? I read that pgpool can be an entry point for applications and manage connections in correct way. Is pgpool the only solution here? Does it handle failover and failback well?

4) Are there any solutions (commercial also) so I could avoid manually copying the data, reconfiguring PostgreSQL instances and other stuff which should be done by hands? So kind of cluster configuration when everyone in sync, it's clear who is Master and everything switches automatically without operator attention?

According to these threads and articles

Streaming replication and failover on PostgreSQL

Automating failover in PostgreSQL 9.1

http://denishjpatel.blogspot.com/2010/11/possibility-of-graceful-switchover.html

there is no single fully automatic solution to solve these questions. Am I right?

Thanks!

slaves wont understand new master. you should manually do that.
yes they are different and you should create new ones for old master.however old standby will continue to work as a master but you should set max_wal_senders on that node. you should also set pg_hba.conf of the new master after failover.
after failover (when nodes changes roles master->slave slave->master), you should transfer new wal files to new standby folders data directory which you set in recovery.conf file. or simply you can use rsync.
may be you can use pgbouncer. this way you will just change the pgbouncer server adres to new master.
EnterpriseDB has some commercial tools. may be you can check them.

and finally yes you are right. there is no single fully automatic solution to solve these questions.


Why is my SQL server using ~4 GBs of memory? I notice that pretty often my SQL server will eat up 50% of my 8GB of memory. This usually happens after I finish running some SQL queries, not while they're running. The most common query is one that is generating millions of rows of random data. In the query I use

BEGIN TRAN
  ....
COMMIT TRAN

I'm guessing this might be the cause, but I don't know nearly enough about SQL to figure out the issue.

SQL Server uses that memory because it needs it, and typically won't release it unless there is pressure from the operating system to do so (since it assumes that you'll need that much memory again). So it is actually a good thing that it is using 4 GB of RAM, and if you don't need that memory for other things, why do you care?

You have basically two options:

Limit SQL Server to something lower (e.g. 2 GB). This will make your queries slower, but at least you'll still have all that precious RAM to sit idle.
Restart SQL Server when you're done with any high-memory operations that push it up over 4 GB. This will allow your queries to run with the memory they need, but still return that memory when finished. Not my idea of fun times, but hey...


TSQL: How to DELETE in cascade without altering table / indexes I have inserted some data in a table where I should not have. I am trying to delete the data, but it has a 1:M relationship and I need to also delete data that references this one.

I do not want to alter the tables / indexes, I just need this instruction to cascade:

-- this alters the CONSTRAINT -- not an option:
ALTER TABLE T2
ADD CONSTRAINT fk_employee
FOREIGN KEY (employeeID)
REFERENCES T1 (employeeID)
ON DELETE CASCADE;

Also I would like (read - prefer) not to change the IDE GUI settings.

Perhaps simply do it manually with two deletes, in a transaction to ensure consistency:

BEGIN TRANSACTION
DELETE T2 WHERE employeeID IN (SELECT employeeID FROM T1 WHERE <clauses_to_find_the_records_to_delete>
DELETE T1 WHERE <clauses_to_find_the_records_to_delete>
COMMIT TRANSACTION

You might want to add that FK constraint anyway, with or without the cascade options (I very much prefer without cascade, so still needing the manually ordered deletes, it is very convenient in some cases but adds dangers...), if it is correct for your data model.


Can oracle dbca template be used cross versions If you have a dbca template to create a database for version 12.1, will it also work for creating a database on 12.2 binaries?

Will it also work for creating a database on 11g binaries?

The information in templates includes database options, initialization parameters, and storage attributes. I use scripts instead, but from what I can see in $ORACLE_HOME/assistants/dbca/templates they are XML files which should translate to the creation of your CREATE DATABASE statement as well as init.ora which hasn't changed between 12.1 and 12.2 apart from a few deprecated parameters.

I would imagine it's not downward compatible to 11g, but there's no harm in trying?


Reverse connect by prior level value for arbitrarily-deep hierarchy Background

Using a menu hierarchy to drive a login process for users. Users have the ability to set their preferred menu item. When they log in, if they have a preferred menu item set, the system directs them to that item. If no preferred menu item is set, they log into the default menu item for their "most important" role.

Code

The query uses connect by prior to get the list of menus:

  SELECT
    LEVEL AS menu_level,
    t.name AS menu_name,
    t.id AS menu_id
  FROM
    jhs_menu_items t, (
      SELECT
        jmi.id
      FROM
        jhs_users ju
      JOIN jhs_user_role_grants jurg ON
        ju.id = jurg.usr_id
      LEFT OUTER JOIN user_menu_preferences ump ON
        ju.id = ump.jhs_usr_id
      LEFT OUTER JOIN default_menu_preferences dmp ON
        jurg.rle_id = dmp.jhs_rle_id
      JOIN jhs_menu_items jmi ON
        -- Retrieve the user's preferred menu item, failing to the default
        -- if no preference is set.
        jmi.id = coalesce(
          ump.jhs_menu_items_id,
          dmp.jhs_menu_items_id
        )
      WHERE
        ju.username = 'USERNAME' AND
        ROWNUM = 1
      ORDER BY
        dmp.role_priority_sort
    ) menu_preference

  -- Derive the menu hierarchy starting at the user's preference, going back to
  -- the root menu item.
  START WITH t.id = menu_preference.id
  CONNECT BY PRIOR t.mim_id = t.id

Problem

A root menu item has NULL for its parent (mim_id). The user's menu preference is a menu item leaf node, which can be found at any level in the hierarchy (the maximum depth is 3, in this case).

When the data is returned, the values for the LEVEL pseudocolumn (alias MENU_LEVEL) are in reverse order:

╔════════════╦═══════════╦══════════════╗
║ MENU_LEVEL ║ MENU_NAME ║ MENU_ITEM_ID ║
╠════════════╬═══════════╬══════════════╣
║          1 ║ MenuTab3  ║ 100436       ║
║          2 ║ MenuTab2  ║ 101322       ║
║          3 ║ MenuTab1  ║ 101115       ║
╚════════════╩═══════════╩══════════════╝

This should actually return:

╔════════════╦═══════════╦══════════════╗
║ MENU_LEVEL ║ MENU_NAME ║ MENU_ITEM_ID ║
╠════════════╬═══════════╬══════════════╣
║          3 ║ MenuTab3  ║ 100436       ║
║          2 ║ MenuTab2  ║ 101322       ║
║          1 ║ MenuTab1  ║ 101115       ║
╚════════════╩═══════════╩══════════════╝

However, since the hierarchy is connected by starting from the user's preferred menu item, and worked back up to the root menu item, it makes sense that LEVEL is counting "backwards".

Having the level reversed means we can ask, "What is the 3rd-level menu item for the user named 'USERNAME'"? Expressed in as a SQL where clause:

WHERE menu_level = 3 AND username = 'USERNAME';

Question

How would you reverse the value of LEVEL for an arbitrarily-deep hierarchy?

For example, something like:

SELECT
  LEVEL AS MENU_LEVEL_UNUSED,
  max(LEVEL) - LEVEL + 1 AS MENU_LEVEL
FROM ...

Obviously that won't work because max is an aggregate function.

Fiddle

http://sqlfiddle.com/#!4/60678/3/0

Strangely, I'm seeing different behaviours in Fiddle's 11g R2 instance than the local Oracle instance -- the ROWNUM is picking up "1" in the Fiddle when it should be picking up "3". This prevents seeing the menu hierarchy, and hence the LEVEL. Not sure why.

Ideas

We could add a column to jhs_menu_items that stores the depth. This is a bit redundant, though, because the hierarchy itself contains that information.
We could wrap the jhs_menu_items table in a view that calculates the depth. This could get computationally expensive.
Is this a good candidate for WITH?

You can't ever query for ROWNUM = 3; ROWNUM 1 gets filtered out, so the next record is now ROWNUM = 1, and so on.

I suspect being worried about controlling the computational depth is premature optimization.  Unless you have millions of options being returned for a single user, pulling the highest level out of the data set - or even pulling the highest level for each dataset should be pretty simple.  It's not as if you can't fully explore the hierarchy, so the additional work in the SQL engine of prioritizing/ranking each window of rows is pretty light on top of that.

So using aggregate/window functions seems like a natural choice here.

Your SQLFiddle doesn't return the dataset expected, but I've modified the SQL below to show how you can prioritize them, assuming I understand the requirements you're looking for.

Analytics rock.

select menu_level, menu_name, menu_id
from
(
select menu_level, menu_name, menu_id,
       row_number() over (partition by menu_name order by menu_level desc) as menu_priority
from
(
SELECT
    LEVEL AS menu_level,
    t.name AS menu_name,
    t.id AS menu_id
  FROM
    jhs_menu_items t, (
      SELECT
        jmi.id
      FROM
        jhs_users ju
      JOIN jhs_user_role_grants jurg ON
        ju.id = jurg.usr_id
      LEFT OUTER JOIN user_menu_preferences ump ON
        ju.id = ump.jhs_usr_id
      LEFT OUTER JOIN default_menu_preferences dmp ON
        jurg.rle_id = dmp.jhs_rle_id
      JOIN jhs_menu_items jmi ON
        -- Retrieve the user's preferred menu item, failing to the default
        -- if no preference is set.
        jmi.id = coalesce(
          ump.jhs_menu_items_id,
          dmp.jhs_menu_items_id
        )
      WHERE
        ju.username = 'adminname'
--      AND ROWNUM = 1
      ORDER BY
        dmp.role_priority_sort
    ) menu_preference

  -- Derive the menu hierarchy starting at the user's preference, going back to
  -- the root menu item.
  START WITH t.id = menu_preference.id
  CONNECT BY PRIOR t.mim_id = t.id
  )
)
 where menu_priority = 1
 order by menu_name

MENU_LEVEL  MENU_NAME   MENU_ID
        3   MenuTab1    1
        2   MenuTab2    2
        1   MenuTab3    3


Database Design and structure I want to make colleges search system where a user enter any keywords in a search box and we can provide them some institutes with their details like institutes names, belonging hotspots, cities, and courses etc by searching in all tables (it could be area, city, institute name, courses or anything what we have).

But the problem is I have to make joins between all the tables and it is getting too slow. Can we optimize these tables to make the database more search/filter friendly?

For now I am using MongoDB to cache these data in single collection. As MongoDB isn't a relation database system, we have to compromise with many features.

Is MongoDB the only solution for me?
Or can these be optimized?

My Query is

SELECT i.name, GROUP_CONCAT(h.hotspot_name) hotspots,
       GROUP_CONCAT(ac.accreditation) accreditations, c.city_name from institutes i
left join areas a on i.area_id = a.area_id
left JOIN districts d on a.district_id = d.district_id
LEFT JOIN cities c on c.city_id = d.city_id
LEFT JOIN institute_hotspots ih on ih.inst_id = i.inst_id
LEFT JOIN hotspots h on ih.hotspot_id = h.hotspot_id
LEFT JOIN institute_accr ia on i.inst_id = ia.inst_id
LEFT JOIN accreditations ac on ia.accr_id = ac.accreditation_id
LEFT JOIN institute_courses ic ON i.inst_id = ic.inst_id
LEFT JOIN courses co on ic.course_id = co.id
LEFT JOIN course_names cn on co.course_id = cn.id
LEFT JOIN subcourses sc on co.subcourse_id = sc.id
LEFT JOIN course_types ct on co.type_id = ct.id
LEFT JOIN course_levels cl on co.level_id = cl.id
LEFT JOIN course_streams cs on co.stream_id = cs.id
LEFT JOIN course_category_relation ccr ON co.id = ccr.course_id
LEFT JOIN course_categories cc ON ccr.category_id = cc.id
WHERE i.name LIKE '%{QUERY}%'
OR i.name LIKE '%{QUERY}%'
OR  c.city_name LIKE '%{QUERY}%'
OR  a.area_name LIKE '%{QUERY}%'
OR  d.district_name LIKE '%{QUERY}%'
OR  h.hotspot_name LIKE '%{QUERY}%'
OR  ac.accreditation LIKE '%{QUERY}%'
OR  cn.name LIKE '%{QUERY}%'
OR  sc.name LIKE '%{QUERY}%'
OR  cl.name LIKE '%{QUERY}%'
OR  ct.name LIKE '%{QUERY}%'
OR  cs.name LIKE '%{QUERY}%'
OR  cc.name LIKE '%{QUERY}%'

GROUP BY i.inst_id
limit 10

It does not even run on my local system.

While preparing the diagram, I have changed some field names.

More detailed view

34 tables is a lot.

Over-nomalization
Many-to-many mappings that should be 1:many
Excessive use of "added by"
Use minimal datatypes

Some examples:

Because of the nested nature of districts-cities-states, and the fact that city and state names "never" change, there is no need to carry that normalization past district (or maybe even area).

I suggest it is OK to use the 2-letter state abbreviations and not have the full name.  Note: that should be CHAR(2) CHARACTER SET ascii to minimize the size (2 bytes).

Don't bother 'normalizing' simple things like keywords, course types, etc.  When there is a relatively small and static number of such things, consider using ENUM for them.

An "accreditation" is for one institute, correct?  It is not a blanket for many institutes?  So that is really 1:many, not many:many.  Hence, the intermediate table is unnecessary.  (Some relations should continue to be many:many.)

For dozens of items, use TINYINT UNSIGNED (1 bytes), not INT UNSIGNED (4 bytes). For hundreds, maybe thousands, use SMALLINT UNSIGNED (2 bytes).

What is a type_id?


reverse the characters of a string without changing the position of the numeric value in T-SQL With T-SQL, I'm trying to find the easiest way to reverse the characters of a string without changing the position of the numeric value.

So for string:

abc223de11

have

edc223ba11

I am not proud of this, but you can do it in T-SQL. This inline table-valued function breaks the string into a set of characters, and applies row numbers to just the string values, so that you can flip the set upside down.

CREATE FUNCTION dbo.SortString
(
  @s varchar(64)
)
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN
  ( -- get a sequence number for every character in @s
    WITH n AS
    (
      SELECT n = 1 UNION ALL SELECT n + 1 FROM n WHERE n < LEN(@s)
    ),
    s AS
    ( -- break out each character, apply sequence number, test numeric
      SELECT n, s = SUBSTRING(@s, n, 1), isn = ISNUMERIC(SUBSTRING(@s, n, 1))
      FROM n
    ),
    s2 AS
    ( -- apply reverse match pointers, but only for strings
      SELECT n,s,
        rn1 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER
              (PARTITION BY isn ORDER BY n ASC) END,
        rn2 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER
              (PARTITION BY isn ORDER BY n DESC) END
      FROM s
    )
    SELECT s2.n, New = COALESCE(s3.s, s2.s), Original = s2.s
        FROM s2 LEFT OUTER JOIN s2 AS s3
        ON s2.rn2 = s3.rn1
  );
GO

The following call:

DECLARE @str varchar(64) = 'abc223de11';
SELECT Original, New FROM dbo.SortString(@str) ORDER BY n;

Yields the following results:

Original  New
--------  ---
a         e
b         d
c         c
2         2
2         2
3         3
d         b
e         a
1         1
1         1

In SQL Server 2017, you can pack the string back together with predictable order using STRING_AGG():

DECLARE @str varchar(64) = 'abc223de11';

SELECT
  OriginalString = @str,
  QuasiReversed = STRING_AGG(New,'') WITHIN GROUP (ORDER BY n)
FROM dbo.SortString(@str);

In older versions, you'll need to use a FOR XML hack:

DECLARE @str varchar(64) = 'abc223de11';

SELECT
  OriginalString = @str,
  QuasiReversed = (SELECT '' + New
    FROM dbo.SortString(@str)
    ORDER BY n
    FOR XML PATH, TYPE).value(N'.[1]','varchar(64)');

Results in both cases:

OriginalString  QuasiReversed
--------------  -------------
abc223de11      edc223ba11


I need to copy data from remote Postgres server that has limited disk space and limited connectivity to a server I have a remote Postgres server recording some data every second. the server has very less space like 6-7 GB, currently I write data to a remote VM server but due to poor connectivity the applications fails everyday without notice (I believe as I get error 0x80004005 which is a ODBC related error). I need to fix this and I thought if I can run a local server which store data and the VM server periodically connects>copies>confirms data>deletes the original data. So that even if the connectivity issues occurs the VM can try next time and when gets the data executes the procedure. I can fix this interval as hourly or daily to avoid the data loss.

I am a newbie on DB and really stuck with an app that needs some fix. (The original developer of the app is not available so I have need to get this fixed by myself). Someone please help.

It sounds like you don't need a local server, just a queue file that you write the data to then insert in batches into the server when it's available.

Just log to a local CSV file or something.

Alternately, you could use something like Londiste (from Skytools) to replicate the rows from a local server to the remote. It's pretty fault-tolerant.

Finally, the team I'm involved in will soon be releasing a new replication option for PostgreSQL that would meet your needs rather nicely. It's a few weeks/months off production release, though.


Does MySQL reindex on update I have a users table with various fields which are getting updated frequently.
i.e table have:

userid -  PRIMARY int
profile_views - int
ratings - int
last_active - int ( to store UNIX timestamps for sorting purpose)

I also have an index on last_active to get the last active users. When user logins or performs a certain action last_active gets updated and same with profile_views and ratings.

My question is does mysql reindex all table ro reindex index last_active when I update last_active index value or update profile_views or ratings?

table:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varbinary(16) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(80) NOT NULL,
  `salt` varchar(40) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `title` varchar(255) NOT NULL,
  `detail` varchar(500) NOT NULL,
  `genre_id` int(2) NOT NULL,
  `category` tinyint(1) NOT NULL,
  `profile_views` int(11) NOT NULL,
  `ratings ` int(11) NOT NULL,
  `last_active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `genre_id` (`genre_id`),
  KEY `last_active` (`last_active`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4059 ;

and second table to join to

CREATE TABLE IF NOT EXISTS `users_genre` (
  `userid` int(11) NOT NULL,
  `genre_id` int(11) NOT NULL,
  KEY `userid` (`userid`),
  KEY `genre_id` (`genre_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Thanks

MySQL will not serve you stale data (if that is your question).  The index is maintained and kept up to date as part of every operation (whether be INSERT, UPDATE, DELETE etc.).


Storage space requirements for a varchar(40) column to store 'abcd' with charset 'ucs2' and 'utf8mb4'? I understand that if the length is > 255 it will require 2 bytes to store the length prefix. I just need to verify my assumptions. Please tell me the length prefix and the length of string('abcd') for charsets(latin1, ucs2, utf8mb4). For a varchar(20) column.
The documentaton says:

[...] For the string 'abcd', L is 4 and the storage requirement is five bytes. If the same column is instead declared to use the ucs2 double-byte character set, the storage requirement is 10 bytes: The length of 'abcd' is eight bytes and the column requires two bytes to store lengths because the maximum length is greater than 255 (up to 510 bytes). [...]

Reference: 11.7 Data Type Storage Requirements (Oracle | Docs)
I just don't understand why for 'abcd' with charset 'ucs2' it requires 2 byte length prefix even after it being less than 255.

abcd is all English, so:  9 bytes for ucs2; 5 bytes for utf8.  But...
UCS2 uses 2 byte per character (at least for 'western' letters).  I think it takes 4 bytes for most Asian characters.
utf8 (or utf8mb4) is a variable-length encoding.  English letters take 1 byte each; most European text takes 1 or 2 bytes per character.  Asian takes 3, sometimes 4 bytes per character.
latin1 has only 1-byte characters, so it is limited to English, plus some accented European letters.
But, but...
VARCHAR(40) stores up to 40 characters (not bytes) in whatever encoding is being used.  The disk space be 1 byte for length plus up to 4*40 bytes for text.  A long example would be 40 Emoji, taking 161 bytes.
<opinion>
There is virtually no reason to use the CHARACTER SET ucs2 (or ucs4).  In particular, anything involving unicode or utf8 should use utf8mb4.
</opinion>
But But But...
Re your comment about 10 bytes -- InnoDB will sometimes use 1 byte for the length, sometimes 2.  But the decision is based on all the columns in the table.  Your "40" does not force a 2-byte length, but some other column might.
The documentation says "For example, a VARCHAR(255) column ... (up to 510 bytes)".  So it needs 2 bytes since the max byte length is >255.  Your example is with "VARCHAR(40)", which is "up to 80 bytes" (<=255).


Dates, Times and Timezones - How are they incorporated in Interval? Does interval '1 month' incorporate month differences and leap years when calculating date/datetime boundaries?  If so, how does it work?

I was asked this question, and I'm not sure how to answer it.  I am using Postgres as a reference example, I assume it has its own time offset and calculation integration.  But how does this take into account timezones, leap years, and other oddities of the calendaring?

I want to know if there's anything in a the SQL spec that defines how the behavior will occur.

Yes, if not it would cause all kinds of errors.  when doing math on a date for example 1 month ago, it does take the date to be subtract from and figures out what to do from there,  it does not just subtract 30 days.

links will answer your question
wiki interval
another site

also consider this simple query

select '03-01-2019'::date - interval '30 day',
'03-01-2019'::date -interval '1 month'


Call pl/sql function and discard result I'm calling a function from pl/sql, but I do not require the result in this case.  Is there a cleaner way to call this function without using a dummy variable?

Works:

junk := performWork();

Does not work:

performWork();         --PLS-00221: performWork is not a procedure or is undefined
EXEC performWork();    --this might work from SQL*Plus, but not from pl/sql
SELECT pkg.performWork() FROM DUAL;  --PLS-00428: INTO clause is expected in this SELECT statement

Your options are:

Assign the result to a dummy variable:

junk := perform_work;

Select the result into a dummy variable:

select perform_work into junk from dual;

Create an implicit cursor in a loop:

for i in ( select perform_work from dual ) loop
   null;
end loop;

If the function does not need to be called from SQL you could change it into a procedure with an OUT parameter, though this still requires a "junk" variable it might look very slightly cleaner?

perform_work(junk);

If you don't want to use this function though, why not just remove it from your code?

The only time I can think of there being a valid use-case for this sort of structure is when you're running identical code in two different schemas/on two different databases but this function is useless in one of them. If that's the case the function is still useful in the other and so you will want to do something with the result anyway.


Why does Inner Join introduce a hidden sort? Consider the following example:

DECLARE @suppliedNumber MONEY
SET @suppliedNumber = 245656.00

SELECT
    ah.FirstName,
    ah.LastName
FROM
    AccountHolders AS ah
JOIN
    [dbo].[Accounts] AS a
ON
    ah.Id = a.AccountHolderId
GROUP BY
    ah.FirstName, ah.LastName
HAVING SUM(a.Balance) > @suppliedNumber

As you can see there is no ordering of any kind in the query. But looking in the execution plan I see implicit sorting:

Why is this happening? What's the technical reason behind this?

I'm using Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64)   May 27 2016 15:06:08   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.3  (Build 14393: )

I don't think the join is producing the sort

The group by sum is producing the sort
See the order by in the query plan

This might be faster
It breaks if the purpose is multiple accounts with same name

SELECT ah.Id, ah.FirstName, ah.LastName
FROM
    AccountHolders AS ah
JOIN
    [dbo].[Accounts] AS a
ON
    ah.Id = a.AccountHolderId
GROUP BY
    ah.Id, ah.FirstName, ah.LastName
HAVING SUM(a.Balance) > @suppliedNumber

Or

SELECT ah.Id, ah.FirstName, ah.LastName
FROM AccountHolders AS ah
JOIN ( select AccountHolderId
       from [dbo].[Accounts] A
       GROUP BY Id
       HAVING SUM(Balance) > @suppliedNumber
     ) a
 on a.AccountHolderId = ah.ID


How can I time SQL-queries using psql? I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?

Just turn on timing by entering:

\timing


How come website be random slow? Open sleep processes? My website is randomly very slow by high MYSQL CPU, and I have no idea what the problem is. Hopefully I will come closer to the solution with this post.

If I check the mysql processes, I see a lot of open sleep commands when the website is slow. When there are none open sleep commands, there is no problem. So probably the problem lies there.

Curiously, this problem only happens sometimes. Sometimes I have no problems for 4 weeks, now the website is already 3 days in a row slow.

I already set the time_out mysql settings to 30 secs, but that does not help to solve the problem. So now I try to figure out what the problem is. Here are the alert status variables. Perhaps that gives some extra insights.

Could it be spambots/brute-force attacks?
Heavy/slow query's in the website?
Bad server settings?
Sql injections?

Other things that this problem can cause? What is the best way to tackle this problem now? Sending any direction will already help.

IMHO,reason can be one of the following

i) Inline query :It is prone to sql injection attack.but not necessarily website will be slow.It is Advisable to use parameterise proc.

ii) Unpotimize query : Yes.shortlist those page and related query/proc.

iii) CSRF Attack : your pages can be attack by malware or web crawler.There are various way to overcome it,one of them is Captcha, but not always.google it.

iv) Network/server configuration :this should point i).check with your admin.

v) check for infinite and uncessary loop in pages.Infact identiyfy pages which are slow then examine both code and query.


Decode and aggregate a BitMask in t-sql I have a table that contains a bitmask field that stores permissions, where each bit denotes whether a particular permission is granted or not.  Here's a simplified example:

DECLARE @T TABLE (id smallint identity, BitMask tinyint);
INSERT INTO @T (BitMask) VALUES
  (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT
  t.id, t.BitMask, bm.BitNum, bm.Permission
FROM @T t
OUTER APPLY (
  SELECT * FROM (VALUES
    (t.id, 0, 'Can X'),
    (t.id, 1, 'Can Y'),
    (t.id, 2, 'Can Z')
  ) bm(id, BitNum, Permission)
  WHERE t.BitMask & POWER(2, bm.BitNum) <> 0
) bm

This returns the following info:

id     BitMask BitNum      Permission
------ ------- ----------- ----------
1      0       NULL        NULL
2      1       0           Can X
3      2       1           Can Y
4      3       0           Can X
4      3       1           Can Y
5      4       2           Can Z
6      5       0           Can X
6      5       2           Can Z
7      6       1           Can Y
7      6       2           Can Z
8      7       0           Can X
8      7       1           Can Y
8      7       2           Can Z
9      8       NULL        NULL
10     9       0           Can X

(15 row(s) affected)

So far so good.  The trouble comes when I try to do an aggregate by id, so that I have all the permissions in one field.  I've tried adding the following APPLY clause to do a standard XML list-string-agg, but I get an error Invalid object name 'bm'.:

OUTER APPLY (
  SELECT
   ParamList = STUFF(
     (
       SELECT  '; ' + a.Permission
       FROM bm a WHERE a.id = b.id
       ORDER BY a.BitNum
       FOR XML PATH(''), TYPE).value('.', 'varchar(max)'
     ), 1, 2, ''
   )
  FROM bm b
  GROUP BY b.id
) q

Any ideas?

First, we need to make a few minor adjustments to the original code:

It does not make sense to have a permission value of 0 since 0 means "no permissions".
The "BitNum" is not the direct value that you use in the POWER function. If you need a "bit" value of 1, that comes from raising 2 to the power of 0. So you need to subtract 1 from the "BitNum" for use in the POWER function.

With those two changes in mind, the following changes to the original query give you the correct initial result set:

DECLARE @T TABLE (id SMALLINT IDENTITY(1, 1), BitMask TINYINT);
INSERT INTO @T (BitMask) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT  t.id, t.BitMask, bm.BitNum, bm.Permission
FROM    @T t
OUTER APPLY (
  SELECT * FROM (VALUES
    (1, 'Can Y'),
    (2, 'Can Z')
  ) bm(BitNum, Permission)
  WHERE t.BitMask & POWER(2, bm.BitNum - 1) <> 0
) bm

And that query can be further reduced / simplified to be a simple LEFT JOIN as follows:

SELECT  t.id, t.BitMask, bm.BitNum, bm.Permission
FROM    @T t
LEFT JOIN (VALUES
    (1, 'Can Y'),
    (2, 'Can Z')
          ) bm(BitNum, Permission)
  ON t.BitMask & POWER(2, bm.BitNum - 1) <> 0

Results (12 rows):

id  BitMask BitNum  Permission
1   0       NULL    NULL
2   1       1       Can Y
3   2       2       Can Z
4   3       1       Can Y
4   3       2       Can Z
5   4       NULL    NULL
6   5       1       Can Y
7   6       2       Can Z
8   7       1       Can Y
8   7       2       Can Z
9   8       NULL    NULL
10  9       1       Can Y

Next, now that we have the proper base query, you cannot simply add an APPLY since your original query had each permission as a separate row but now you want them grouped into a single row per "BitMask". Hence you need to restructure the request to be the following (or something similar):

SELECT   t.id, t.BitMask, PermissionList =
(
  SELECT PermissionList = STUFF(
     (
       SELECT  '; ' + bm.Permission
       FROM   (VALUES
                    (1, 'Can Y'),
                    (2, 'Can Z')
              ) bm(BitNum, Permission)
        WHERE  t.BitMask & POWER(2, bm.BitNum - 1) <> 0
        ORDER BY bm.BitNum
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, ''
   )
)
FROM @T t
GROUP BY t.id, t.BitMask;

Results (10 rows):

id  BitMask     PermissionList
1   0           NULL
2   1           Can Y
3   2           Can Z
4   3           Can Y; Can Z
5   4           NULL
6   5           Can Y
7   6           Can Z
8   7           Can Y; Can Z
9   8           NULL
10  9           Can Y

You also have the option of using SQLCLR to create a User-Defined Aggregate (UDA) that can do this type of String.Join() operation. And an Aggregate function such as this already exists in the SQL# library (which I am the author of, but this function is available in the Free version), though it is hard-coded to use a comma (and no spaces) as the delimiter, and returns an empty string instead of NULL if there are no matches. But, it does make for a much more readable query:

SELECT  t.id, t.BitMask, SQL#.Agg_Join(bm.Permission) AS [PermissionList]
FROM    @T t
LEFT JOIN (VALUES
    (1, 'Can Y'),
    (2, 'Can Z')
          ) bm(BitNum, Permission)
  ON t.BitMask & POWER(2, bm.BitNum - 1) <> 0
GROUP BY t.id, t.BitMask;

This is not to say that using a SQLCLR UDA is necessarily the better choice, I am just pointing out that it is a choice, and depending on the specific requirements, might be better.

OR, starting in SQL Server 2017, there is a built-in aggregate function, STRING_AGG, that can handle this.

A slightly different way of testing the Bit value against the Bitmask is to compare the bit-wise AND operation of them to the Bit value itself instead of against <> 0:

DECLARE @T TABLE (id SMALLINT IDENTITY(1, 1), BitMask TINYINT);
INSERT INTO @T (BitMask) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

 -- based on "improved" query
SELECT   t.id, t.BitMask, [PermissionList] =
(
  SELECT [PermissionList] = STUFF(
     (
       SELECT  '; ' + bm.Permission
       FROM   (VALUES
                    (0, 'Default'),
                    (1, 'Can Y'),
                    (2, 'Can Z')
              ) bm(BitNum, Permission)
        WHERE  t.BitMask & POWER(2, bm.BitNum - 1) = POWER(2, bm.BitNum - 1)
        ORDER BY bm.BitNum
        FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, ''
   )
)
FROM @T t
GROUP BY t.id, t.BitMask;

This gets you a little closer to using 0 as a value, but then you have the problem of that value being implicitly in all records. The results of the above (notice that the ON condition was changed AND I added the 0 record back in):

id  BitMask     PermissionList
1   0           Default
2   1           Default; Can Y
3   2           Default; Can Z
4   3           Default; Can Y; Can Z
5   4           Default
6   5           Default; Can Y
7   6           Default; Can Z
8   7           Default; Can Y; Can Z
9   8           Default
10  9           Default; Can Y


SQL equivalent to CouchDB for 2 way replication Are there any SQL server variants (i.e MySQL, Postgresql etc) that handle replication as well as couch db does and offers client side replication (2 way)?

I'm in the early stages of my app which is currently using PouchDB / CouchDB as a key point about the app is being able to go offline then replicate when back online. I know the servers themselves can replicate but I'm basically looking to have an offline version of the database available to the client as well (which is what PouchDB / CouchDB achieves).

The way CouchDB's replication mechanism is created and integrated is kind of unique. This has to do with the historical situation, that CouchDB should be able to live in a distributed world from the beginning. That said, "older" databases like MySQL or PostgreSQL do not have these features, because there was never a need for it.

It is very important to understand, that MySQL and PostgreSQL do offer replication but not in the way CouchDB does. There is MySQL Percona and PostgreSQL BRD but one has to go through a more time consuming setup process in comparison to CouchDB where you simply add a replication document with the source and target in the _replicator database.

So as far as I know, it will be difficult to find something comparable to the mechanisms and features you are looking for.


Can adding a columnstore index to a table affect read performance of a query that uses a rowstore index on the same table? I'm doing some testing of columnstore indexing on a single table that has about 500 million rows.
The performance gains on aggregate queries have been awesome (a query that previously took about 2 minutes to run now runs in 0 seconds to aggregate the entire table).
But I also noticed another test query that leverages seeking on an existing rowstore index on the same table is now running 4x as slow as it previously did before creating the columnstore index. I can repeatedly demonstrate when dropping the columnstore index the rowstore query runs in 5 seconds, and by adding back in the columnstore index the rowstore query runs in 20 seconds.
I'm keeping an eye on the actual execution plan for the rowstore index query, and it's almost exactly the same in both cases, regardless if the columnstore index exists. (It uses the rowstore index in both cases.)
The rowstore test query is:
SELECT *
INTO #TEMP
FROM Table1 WITH (FORCESEEK)
WHERE IntField1 = 571
    AND DateField1 >= '6/01/2020'

The rowstore index used in this query is: CREATE NONCLUSTERED INDEX IX_Table1_1 ON Table1 (IntField1, DateField1) INCLUDE (IntField2)
The columnstore test query is:
SELECT COUNT(DISTINCT IntField2) AS IntField2_UniqueCount, COUNT(1) AS RowCount
FROM Table1
WHERE IntField1 = 571 -- Some other test columnstore queries also don't use any WHERE predicates on this table
    AND DateField1 >= '1/1/2019'

The columnstore index is: CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Table1_2 ON Table1 (IntField2, IntField1, DateField1)
Here is the execution plan for the rowstore index query before I create the columnstore index:

Here is the execution plan for the rowstore index query after I create the columnstore index:

The only differences I notice between the two plans is the Sort operation's warning goes away after creating the columnstore index, and the Key Lookup and Table Insert (#TEMP) operators take significantly longer.
Here is the Sort operation's info with the warning (before creating the columnstore index):

Here's the Sort operation's info without the warning (after creating the columnstore index):

I would've thought a read query that is specifically leveraging the same rowstore index and execution plan in both cases should have roughly the same performance on every run, regardless of what other indexes exist on that table. What gives here?
Edit: Here's the TIME and IO stats before creating the index:

Here's the stats after creating the columnstore index:

Adding the nonclustered columnstore index allows for a batch mode sort in the second execution plan.  This causes all of the processing to be done on one thread - so even though the query has a parallel plan, it's essentially running serially.  You can see that by looking at the details of the different operators.
I reproduced your problem locally, here's the sort operator with per-thread counts - as you can see, everything is on thread 1:

Notice the "Actual Execution Mode" is "Batch."
Everything after the sort (the nested loops join, key lookup, etc) is essentially serial, which is what slows the query down.
See this KB article for details and possible solutions:
Adds trace flag 9358 to disable batch mode sort operations in a complex parallel query in SQL Server 2016

Batch mode sorts were introduced in SQL Server 2016 under compatibility level 130.  If a query execution plan contains parallel batch mode sorts in conjunction with directly-upstream parallel operators, you may encounter degraded performance compared to row mode sort plan equivalents.
This occurs due to a parallel batch sort outputting fully sorted data via a single thread to the upstream parallel operator (for example, a parallel merge join operator).  The performance degradation occurs when the upstream parallel operator uses single-threaded processing due to the incoming single-threaded batch mode sort operator.

For completeness, the options outlined there are either:

enable TF 9358
enable query optimizer hotfixes (through TF 4199, the QUERY_OPTIMIZER_HOTFIXES database option, or the ENABLE_QUERY_OPTIMIZER_HOTFIXES query hint)

Getting rid of the sort is another solution for this problem.  The sort is only present to try and prevent too much random I/O from the nested loops join, which is using unordered prefetch, as mentioned in this article by Craig Freedman:
Optimizing I/O Performance by Sorting – Part 1

The plan uses the non-clustered index to avoid unnecessarily touching many rows.  Yet, performing 64,000 random I/Os is still rather expensive so SQL Server adds a sort.  By sorting the rows on the clustered index key, SQL Server transforms the random I/Os into sequential I/Os.

You can get rid of the sort by:

eliminating the need for the key lookup (by selecting less columns, or creating a covering nonclustered index)
disabling nested loops prefetching by adding (undocumented, unsupported trace flag) OPTION (QUERYTRACEON 9115) to the query


Dynamic SQL To Send HTML Email I am creating a cursor to iterate the values stored in my table, then attempting to use dynamic sql to set the select statement for my html body email.  However, anytime I try to execute my statement I get an incorrect syntax error.  Is this possible to do?  SQL Server 2008

Declare @name varchar(100),@bodytext nvarchar(max), @subject varchar(200)
Create Table #info
(
    name varchar(100)
    ,itemsold varchar(100)
)
Insert Into #info Values
('ZZZZZZ', 'First'),
('CCCCCC', 'Last'),
('EEEEE', 'Green'),
('XXX', 'Blue'),
('QQQ', 'Red')

DECLARE mailcursor CURSOR FOR
SELECT DISTINCT(name) FROM #info

OPEN mailcursor
FETCH NEXT FROM mailcursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

    set @bodytext = cast( (
    select td = '' + name + '	' + itemsold + '	 '
    from (
              'Select * from #info Where name = ''' +@name + ''' '
          ) as d
    for xml path( 'tr' ), type ) as varchar(max) )

    set @bodytext
    = '
'
    + '
name	itemsold
'
    + replace( replace( @bodytext, '&lt;', '<' ), '&gt;', '>' )
    + '
name	itemsold
'
    + '
'

    Set @subject = 'Sent Through Code'

    exec msdb.dbo.sp_send_dbmail
        @profile_name = 'DatabaseMail',
        @recipients = 'foxtrotalphamale12343124@gmail.com',
        @body_format = 'HTML',
        @from_address = 'redfirebluestonesgreeneyes16@gmail.com',
        @body = @bodytext,
        @subject = @subject;

    FETCH NEXT FROM mailcursor INTO @name

END

CLOSE mailcursor
DEALLOCATE mailcursor

--Drop Table #info

And my error message is:

  Msg 102, Level 15, State 1, Line 26
  Incorrect syntax near 'Select * from #info Where name = ''.

Currently you have:

from
(
   'Select * from #info Where name = ''' +@name + ''' '
) as d

This isn't valid, because you can't say FROM ('some query'), that's like saying FROM ('something that is not a query') because it is just a string. Try:

from
(
  Select * from #info Where name = @name
) as d

...in other words, why all the string wrapping? I did not parse the code, so there may be other problems, too. But that's the immediate one.


What is the max length of oracle user passwords? I'm using the Oracle Verify Function 12c - the max password length seem to be < 30 charcters. Can I change that?

Quote from the manual

  Passwords can be at most 30 bytes long

(Emphasis mine)

So the answer to the question "Can I change that" is: no


Alternative of JOIN with BETWEEN I have two tables to JOIN based on BETWEEN condition.

Table 1 is small is small around 1500 records and Table 2 is of 40 millions records. Table1 is having only one column with the datatype bigint and Table2 with 8 columns. I need to do join between these two tables on BETWEEN condition.

I tried following but its getting slow for just 1 record in Table1 and 40 millions in Table2.

Query:

SELECT t1.cola AS [InputValue],t2.cola,t2.colb,t2.colc,t2.cold,t2.code
FROM table2 t2
INNER JOIN table1 t1 ON t1.cola BETWEEN t2.cola AND t2.colb ;

Indexing:

CREATE NONCLUSTERED INDEX NCIX_Table1_Cola ON table1(cola)
CREATE NONCLUSTERED INDEX NCIX_Table2_Col_a_b ON table2(cola,colb)

Above query took 30 seconds for just 1 record in table1 and 40 millions in table2. As said I will be getting more than 1500 records in table1 will getting more slower. Any alternative of between or proper indexing need to be done?

Edit: Added sample data.

Table1:

cola
---------------
12
145
34
90
88990
987611
55
...
..
......1500 rows

Table2:

cola    colb    colc    cold    cole
-------------------------------------
0       10      c1      d1      e1
11      20      c2      d2      e2
21      40      c3      d3      e3
41      60      c4      d4      e4
61      100     c5      d5      e5
101     1000    c6      d6      e6
1001    10000   c7      d7      e7
10001   200000  c8      d8      e8
......
......40 millions records

Expected result:

InputValue  cola    colb    colc    cold    cole
--------------------------------------------------
12          11      20      c2      d2      e2
145         101     1000    c6      d6      e6
34          21      40      c3      d3      e3
.....

SQL Fiddle : Link

I've faced similar issue - the thing is sql doesn't "know" that cola and colb is range and cola of next row will always be bigger than current row colb, so between won't help too much: when it will find first cola that matches between, it will continue checking others also.
I would suggest query that uses cross apply to find max(table2.cola) <= table1.cola and then to verify add where table2.colb >= table1.cola
Something like this

SELECT      t1.cola AS [InputValue]
           ,t2.cola
           ,t2.colb
           ,t2.colc
           ,t2.cold
           ,t2.code
FROM        table1 t1
CROSS APPLY (   SELECT   TOP 1
                         t2.cola
                        ,t2.colb
                        ,t2.colc
                        ,t2.cold
                        ,t2.code
                FROM     table2 t2
                WHERE    t1.cola >= t2.cola
                         AND t1.cola <= t2.colb
                ORDER BY t2.cola DESC ) t2;


Confusion Database System and DBMS I was reading book "Database System Concepts" (6th edition by Henry F Korth Abraham Silberschatz, S Sudharshan

There is slight confusion between database system and DBMS
Are both term same or different?

A database system is a collection of interrelated data and a set of programs that allow users to access and modify these data.

A database-management system (DBMS) is a collection of interrelated data and
a set of programs to access those data.

What I understand---
A database system (DBMS) means a set of programs to access and modify data.

A database-management system (DBMS) means a set of programs to only access data.

Often the two terms are used interchangeably, so you may find Microsoft SQL Server or Postgres being referred to as either a DBMS or a database system.

Where a distinction is maintained "database system" usually refers to the overall application or set of applications that constitute a project. So for instance in the case of a business management suite that has a few applications which uses a few on-premises SQL Server databases for online storage, and has a data warehouse in some cloud database for reporting, and utilities for maintaining the warehouse and running reports; the application suite is a "database system" that itself uses one or more instances of one or more "database management systems" as part of its construction.

A "database system" in that respect might use several different database management systems in its design. A single application suite may use OLTP style stores for the main data, OLAP optimised stores for reporting, and NoSQL stores for "soft" supplementary data (and/or perhaps caching).


how to run SSMS as a different user? I am running SSMS as a different user from my local machine and it works fine.

When I try to do the same, even using my own credentials, on server01 I get the following error:
access is denied

I am currently in the Administrators group in that server:

The way to run ssms as a different user is:

1- right clink on the ssms icon if it is already running otherwise follow from 2
2- witht eh shift key pressed right click the ssms icon and it will give you the options as shown on the picture below.

considering the password is correct and I am sysadmin what could be missing?

I noticed you are running version 130. Apparently SSMS 2016 fails to run as different user as reported below. I have experienced this myself. Quite frustrating.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1315261c-0d9e-49bf-8334-6c0195015598/ssms-2016-fails-on-windows-10-version-1607-while-trying-to-execute-it-as-a-different-domain-user?forum=sqltools

https://connect.microsoft.com/SQLServer/feedback/details/3009405/ssms-2016-fails-on-windows-10-version-1607-while-trying-to-execute-it-as-a-different-domain-user


Will table inheritance in PostgreSql suit this situation? Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL.

So my main aim is to have a Main schema in this DB, which I am going to use to control certain services with, and have multiple schema's linked to this, which is going to be the different clients data.

I have read up on table inheritance, but have never came across anyone using inheritance with an effect similar to that of DB Migrations.
What I want to achieve is basically have a default schema, with the clients schema's all inheriting from the default. This to me gives control of creating/dropping/altering objects to all schema's by just altering a single schema.

Could this be viable? Has anyone else had any experience doing something similar? Does anyone have any suggestion?

UPDATE

This is what I've done. However, I was under the impression that, if you look at the below code, schema 'client1' will be all I need to do, however I was wrong. :D

So anyway, below is the script I have done, which should take care of everything I need for now - I am going to use the script for the 'client2' schema. Good or Bad?

-- public schema --
CREATE TABLE public.Users (
   Id bigserial primary key,
   Login text unique,
   check (false) NO INHERIT
);

CREATE TABLE public.Roles (
   Id bigserial primary key,
   RoleName text unique,
   check (false) NO INHERIT
);

CREATE TABLE public.UserRoles (
   Id bigserial primary key,
   UserId bigint references users(id),
   RoleId bigint references roles(id),
   unique (UserId, RoleId),
   check (false) NO INHERIT
);

-- client1 schema --
CREATE TABLE client1.Users (
)
INHERITS(public.Users);

CREATE TABLE client1.Roles (
)
INHERITS(public.Roles);

CREATE TABLE client1.UserRoles (
)
INHERITS(public.UserRoles);

-- client2 schema --
CREATE TABLE client2.Users (
  primary key (id),
  Unique (Login)
)
INHERITS(public.Users);

CREATE TABLE client2.Roles (
  primary key (id),
  Unique (RoleName)
)
INHERITS(public.Roles);

CREATE TABLE client2.UserRoles (
  primary key (id),
  foreign key (UserId) REFERENCES client2.Users(id),
  foreign key (RoleId) REFERENCES client2.Roles(id),
  unique (UserId, RoleId)
)
INHERITS(public.UserRoles);

It is viable provided that you note that keys and indexes must be declared on each child table.

In fact this is pretty close to an ideal use for table inheritance.  Just make sure you are familiar with the gotchas, and that you pay close attention to foreign key creation in the user schemas.

One caveat is that you can't cascade CREATE statements into all the schemas.  This would have to be scripted.  You could however create plpgsql functions to do this.  Just note that such statements usually are not parameterized so you need to use quote_ident() and quote_literal as appropriate.

As per request here's an example.  In the master schema.  Note the key and unique constraints here are for documentation purposes only.

CREATE SCHEMA master;
SET SEARCH_PATH="master;

CREATE TABLE users (
   id bigserial not null unique,
   login text primary key,
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

CREATE TABLE roles (
   id bigserial not null unique,
   role_name text primary key,
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

CREATE TABLE user_roles (
   user_id bigint references users(id),
   role_id bigint references roles(id),
   primary key (user_id, role_id)
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

RESET SEARCH_PATH;

Naturally if not on PostgreSQL 9.2 drop the check constraints since they would be inherited in prior versions and then no tables could have rows.

Then in a child schema:

CREATE SCHEMA customer123;
SET SEARCH_PATH 'customer123';

CREATE TABLE users(unique (id), primary key(login)) INHERITS (master.users);
CREATE TABLE roles(unique (id), prikary key(role_name)) INHERITS (master.roles);
CREATE TABLE user_roles(
     foreign key users_id REFERENCES customer123.users(id),
     foreign key role_id REFERENCES customer123.roles(id),
     primary key (users_id, role_id)
);
RESET SEARCH_PATH;

Note in this case you must carefully control access to the master schema because:

SELECT tableoid, login from master.users;

gives you every user account of every tenant plus enough info to determine which tenant it is.


SQL Server 2008 unable to backup due to Failed to flush the commit table to disk in dbid 11 due to error 2601 I tried to backup the SQL Server 2008 database
using the following code:

BACKUP DATABASE [DataStore] TO  DISK ='E:\SQLServerDatabase\DataBasebackUpOct302012.bak'

and I get the following error:

  Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
  Cannot insert duplicate key row in object 'sys.syscommittab' with
  unique index 'si_xdes_id'.

  Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating
  abnormally. The statement has been terminated.

  Msg 3999, Level 17, State 1, Line 1 Failed to flush the commit table
  to disk in dbid 11 due to error 2601. Check the errorlog for more
  information.

Could somebody suggest a solution to this problem?

The version is Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44  (64-bit) on Windows NT 6.1  (Build 7600: )

In reply to the comments below:

Aaron Bertrand,
Thank you. I am going to look into service packs and hope that is going to resolve the issue.

SQL-Learner,

Never used the change tracking services before.But we had unexpected Windows Server shutdowns due to mishandling 2-3 times.

Looks like I have to apply a service pack.

mrdenny,

Results after using DBCC CHECKDB
Errors:

Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'.
The statement has been terminated.
Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 3999, Level 17, State 1, Line 1
Failed to flush the commit table to disk in dbid 11 due to error 2601. Check the errorlog for more information.
Msg 3999, Level 17, State 1, Line 1
Failed to flush the commit table to disk in dbid 11 due to error 2601. Check the errorlog for more information

Update 11/9/2012
I applied the Service Pack3 to SQL Server 2008 but I still could not create the back of the database. I have not rebooted the server as it didn't ask for it.
Will have to reboot and check if the patch applied.

Would appreciate any comments/ideas as to how to go about it.
Thanks.

Do you happen to use the change tracking services provided by in SQL 2008 Enterprise?  If so, it's time to update your system.  Please try to avoid running RTM anywhere.  It's very risky from a security and sometimes stability point of view. :)

The following Microsoft Knowledge Base articles may be of use to you:

FIX: Backup fails in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012 if you enable change tracking on the database

This fix was first released in Cumulative Update 7 for SQL Server 2008 Service Pack 2.

  Symptoms:

  Error: 2601, Severity: 14, State: 1.  Cannot insert duplicate key row
  in object 'sys.syscommittab' with unique index 'Name'.  Error: 3999,
  Severity: 17, State: 1. Failed to flush the commit table to disk in
  dbid due to error 2601.

FIX: The sys.syscommittab system table is not cleaned up after the retention period for change tracking in SQL Server 2008

This fix was first released in Cumulative Update 11 for SQL Server 2008 Service Pack 1.

  Symptoms:

  You enable the Change Tracking and Auto Cleanup properties for a
  database in Microsoft SQL Server 2008.

  You set the Retention Period and Retention Period Units properties.
  In this scenario, the sys.syscommittab system table for change
  tracking is not cleaned up after the specified retention period.


How is INFORMATION_SCHEMA implemented in MySQL? The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL?

When I connect to a fresh installation I see two databases: mysql and information_schema. After using SHOW CREATE TABLE statements on the information_schema database, it looks like it's not implemented as a set of views but with base tables instead. Is this assumption correct? Or there are other system tables that are hidden from the user?

The INFORMATION_SCHEMA database is made up of temporary tables using the MEMORY storage engine.

Example: Here is the table INFORMATION_SCHEMA.TABLES in MySQL 5.5.12 (Windows Version)

mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

There is no physical folder for those tables, not even .frm files. You cannot mysqldump it. You cannot drop it. You cannot add tables to it. You cannot drop tables from it. So, where are the tables ???

All tables in the INFORMATION_SCHEMA database are stored directly in memory as MEMORY storage engine tables. They are totally internal to MySQL, so the .frm mechanisms are handled in mysqld. In my answer, I first showed the table layout of INFORMATION_SCHEMA.TABLES. It is a temporary table in memory. It is manipulated using storage engine protocols. Thus, when mysqld is shutdown, all information_schema tables are dropped. When mysqld is started, all information_schema tables are created as TEMPORARY tables and repopulated with metadata for every table in the mysql instance.

The INFORMATION_SCHEMA database was first introduced in MySQL 5.0 to give you access to  metadata about tables of other storage engines. For example, you could do SHOW DATABASES to get a list of databases. You could also query for them like this:

SELECT schema_name database FROM information_schema.schemata;

You could retrieve table names in a database in two ways:

use mydb
show tables;

or

SELECT table_name from information_schema.tables WHERE table_schema = 'mydb';

Since its inception, MySQL has expanded the INFORMATION_SCHEMA database to have the processlist (as of MySQL 5.1). You can actually query the processlist looking for long running queries that are still running at least 10 minutes:

SELECT * FROM information_schema.processlist WHERE time >= 600\G

You can use the INFORMATION_SCHEMA to do every elaborate things: such as :

Get counts of all tables using specific storage engines:

SELECT COUNT(1) TableCount,IFNULL(engine,'Total') StorageEngine
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
AND engine IS NOT NULL
GROUP BY engine WITH ROLLUP;

Get the recommended MyISAM Key Buffer Size in MB

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_key_buffer_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE
engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A,
(SELECT 2 pw) B;

Get the recommended InnoDB Buffer Pool Size in GB

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 3 pw) B;

Get Disk Usage of all Databases By Storage Engine in MB

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize
FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 2 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

Believe me, there are still more wonderful uses for INFORMATION_SCHEMA that time does not permit me to discuss further.

Please keep in mind that the INFORMATION_SCHEMA is so sensitive that if mysql is running and you do the following:

cd /var/lib/mysql
mkdir junkfolder

and then go into mysql run

mysql> SHOW DATABASES;

You will see junkfolder as one of the databases.

Knowing it is very vital for DBAs and Developers. Chapter 20 (developers) and Chapter 31 (DBAs) of the book MySQL 5.0 Certification Study Guide

are there for preparing for the Developer and DBA Certification Exams. Get the book, study those chapters well, and you could do great things with MySQL's INFORMATION_SCHEMA.

The INFORMATION_SCHEMA database as of MySQL 5.5, now features plugins, global variables (status and static), session variables (status and static), storage engine status, performance metrics instrumentation, trigger map, events (programmable) and much more.

Sorry this may seem like WTMI but I am a big proponent of using the INFORMATION_SCHEMA database.


Restoring SQL DB from down SQL Server (on domain) to existing SQL server (not on domain) If and when my SQL server goes down and takes too long to get back up, can I restore my DB on another SQL server and run my software from there (I can point which SQL server to run from)?

Will all metadata and indexes and foreign keys be restored too?

Also my current SQL server is on our domain, the other one I have is on its own workgroup... Would it still be possible? Or would I need to join it to the domain first?

Restoring a SQL Server database only requires admin rights to the destination SQL Server instance.  It doesn't matter at all if the new or old machines were members of the domain.


Designing a fragmentation database? I did some research about distributed databases, but I still have some questions. I understand there is something called "fragmentation database" but I can't find any help full document for understanding and implementing that.

Compare a fragmented distributed database with a replicated distributed database.  A replicated database would contain all columns and all records on more than one database instance; whereas, the separate instances of a fragmented distributed database do not all contain the same columns and/or records.

Vertical Fragmentation:
A vertically-fragmented distributed database would consist of separate instances which do not share all columns / attributes.  For example, the database for the sales department might have a column for a customer's interests; however, the database in the accounting department would not need this column.

Horizontal Fragmentation:
A horizontally-fragmented distributed database would exist when separate database instances have a similar schema but contain different types of records.  For example, a company with two locations East and West might have two similar databases; however, the one instance would only contain customers in the East, the other only customers in the West.


XA transactions - timeout errors We have recently setup XA Transactions as per the documentation.

However, app team is facing the issues as below

Performance Test 1: XXXX Internal users [get XXXX token API ] – 3 concurrent users with three different accounts for 10 minutes duration. [04:46 - 04:57 AM EST] – No Errors

Performance Test 2: XXXX Federated users [get XXXX token API ] - 3 concurrent users with three different accounts for 5 minutes duration. [05:27 - 05:33 AM EST] – No Errors

Performance Test 3: XXXX Federated users [get XXXX token API ] - 3 concurrent users with 20 different accounts. [05:47 - 05:49:36 AM EST] – Errors were observed

and errors says as below

  {"error":"org.hibernate.exception.LockAcquisitionException: could not
  execute query"}

  {"error":": Unable to acquire lock after 10 seconds for key
  f:6038d4995878:ptuserfafn375759570511 and requestor
  GlobalTx:local:754. Lock is held by GlobalTx:local:732"}

I checked DPA Monitoring Tool and found blockings and deadlocks between 05:47 - 05:49:36 AM EST. Victim and Survivor are running the same SQL, from same machine except different Txn/Owner ID.

Checked and validated System/SQL Server error logs, with nothing unusual.

Any thoughts please?

below article has more details

https://docs.microsoft.com/en-us/archive/blogs/sqlserverfaq/sql-server-deadlocks-due-to-character-data-type-conversion-when-using-sql-server-jdbc-drivers

If sendStringParametersAsUnicode property is set to "TRUE", String parameters are sent to the server in Unicode format: http://msdn.microsoft.com/en-us/library/ms378988.aspx. That means, if this property is set to False, the data type of the parameters being passed from application will be Varchar, not nVarchar and hence there will be no implicit conversion!

And it worked. No deadlocks.


How to store schema-less data efficiently in a relational database? I'm trying to get a nice balance of searchability and speed. I'm experimenting with different methods.

Option 1:
Serialize the array as JSON/PHP serialized array and store it in a 'meta' column.

   id  |  name   | meta
1       Bob       {"city":"GoTown","birthdate":"1980\/8\/14","cat":"Felix"}
2       Alice     {"city":"Streamville","birthdate":"1986\/6\/6","dog":"Rex"}

Option 2:
Store keys and values together in a stack.

user_id  |    key   |   value
1         name       Bob
1         city       GoTown
1         birthdate  1980/8/14
1         cat        Felix
2         name       Alice
2         city       Streamville
2         birthdate  1986/6/6
2         dog        Rex

Option 3:
3 tables: entites, keys, values. Store each key name only once.

user_id  |   name
1         Bob
2         Alice

key_id   |   keyname
1         city
2         birthdate
3         cat
4         dog

user_id   |   key_id   |   value
1          1            GoTown
1          2            1980/8/14
1          3            Felix
2          1            Streamville
2          2            1986/6/6
2          4            Rex

Are there any pitfalls with using any of these strategies? I'd like to eliminate some of them if they have insurmountable disadvantages.

EDIT: Added some data to represent schema-less data

Some really nasty problems would happen with these tables (idea)

Data Redundancy (duplicated data) what you need to keep in synchronisation (and you do you check this because MySQL doesn't have an nice JSON to records function?)
You can't force correct values on the database (say goodbye to data integrity and hello to garbage-in garbage-out data) example key birthdate could have value "hello"

And you need some kind of pivot query to fetch the key/values

If you really need dynamic key values (EAV) storage there are some more options:

Maybe MySQL 5.6 NoSQL using the InnoDB memcached Plugin
XML -> query with xpath
RDF
NoSQL like MongoDB
MariaDB dynamic columns and MariaDB also supports json.


Can't open Microsoft Access Database inside a Microsoft SQL Server Filetable Folder We recently started to migrate the Data on our Fileserver to SQL Server (Version 14) Filetables and I encountered a problem with MS Access Databases (.accdb, Access 2007).

When the .accdb file is located in a "normal" Windows shared folder on the server, I can open it with no problems.
When I copy the same file into the Filetable folder, i get an exception that the Database is locked by an unknow user and can't be opened or locked.

All other filetypes I tested work fine on the Filetable share.

The users log into the Fileshare with local accounts on the server, I've added permissions for this accounts to SQL Server.
Is there something different to take account for with MS Access databases that are located in Filetables regarding permissions?

You can't work with multi-user Access databases on a non-SMB network storage (and in fact, until this bug is fixed, you can't reliably work with Access databases on SMB network storage either unless you avoid leasing).

Access databases require that many small writes to files and partial locks on files are allowed. Most network storage solutions, however, only support locking the entire file and don't support small writes or have terrible performance while supporting it, or cache data locally and cause terrible concurrency issues when multiple users are trying to use the same database.

This is very different from other types of files, where it's very usual to lock the entire file, not write to it at all until you save it, and overwrite the entire file when you save it (e.g. Excel). These kind of files do fine on a filetable.

Unfortunately, you will have to rethink your approach, as I don't think it will ever work. You might want to have a dedicated file share for Access that uses SMB with leasing disabled (or just migrate your Access data to SQL server and use linked tables and local copies of the database for every user)


Intellisense is not working since upgrade to SQL Server 2014 We recently upgraded our production server to SQL Server 2014. This server hosts ~2200 databases. When I connect to it as sa with SQL Server Management Studio 2014 and I type queries, Intellisense does not work (nothing show up). Before the upgrade, we ran SQL Server 2008 and SSMS 2008 and Intellisense was working perfectly in the same conditions. Also, Intellisense works when connected to an other server (our development server, SQL 2014 too) which holds ~10 databases.

I already made sure Intellisense is enabled
I already tried to Refresh the local cache (CTRL-Shift-R)

Am I missing something else? Or does it have something to do with the number of databases on the server? If so, why was that not happening before, with SQL Server 2008?

Well, I'm a little ashamed... SQL Server 2014 SP1 was not installed on my client machine. Doing so seems to fix the problem with SSMS Intellisense.


Always On read-intent I've recently taken over administration of an availability group comprising two nodes, synchronous commit mode.

The configuration of the group looks like this:

My understanding is that, with the Readable Secondary option set to Yes on both replicas, any connection string with applicationIntent=ReadOnly will route to Node1.

Likewise, if I change Node2's Readable Secondary option to 'Read-intent', any connection string with applicationIntent=ReadOnly will route to Node2.

Why then, does this connection string route to Node2 when both nodes are set to 'Readable Secondary = Yes':

'Data Source=redacted.domain.com; Initial Catalog= MyDatabase; ApplicationIntent=ReadOnly; User Id=User; Password=********; MultiSubnetFailover=True'

Basically, changing the 'ReadOnly' parameter to 'ReadWrite' causes the connection to go to Node1. Changing back to 'ReadOnly' causes the connection to route to Node2. How is this possible without the 'Readable Secondary= Read-intent' option in place?

EDIT: output of
SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",
rl.routing_priority as "Routing Priority",
ar2.replica_server_name as "RO Routed To",
ar.secondary_role_allow_connections_desc,
ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

For anyone else needing help understanding this, I found the following helpful:

My understanding is that, with the Readable Secondary option set to Yes on both replicas, any connection string with applicationIntent=ReadOnly will route to Node1

No it should first go to node 2 because the Node 2 is read only replica and Node 1 is primary replica which will server to read write operations. As per BOL

  Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role)

What is output of

SELECT ag.name as "Availability Group", ar.replica_server_name as "When
  Primary Replica Is",
rl.routing_priority as "Routing Priority",
ar2.replica_server_name as "RO Routed To",
ar.secondary_role_allow_connections_desc,
ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

So from output you can see when Node 1 is primary replica read only connections will go to Node 2 as first preference and vice versa


Can an article in Sql Server replication be based upon a query? I am looking to replicate the results of a query, is that possible or do I need to rep the tables themselves?

Yes its definately possible. We use it all the time for replicating warehouse data to 53 different warehouses. The query you are talking about is called a replication filter.

Now listen up!!!
Do not put your query in the filter. Use a view instead.

That means you can easily alter the view at a later date as filters cannot be changed.
So you filter will be "select cols from vwUserFilter"

If you put the query in the filter and want to change it, you'd have to drop the publication and re-create it which is very painful.

Also see this link


Upgrade production database during software development lifecycle Background

Using Oracle, there are a number of tools that help with migrating and applying development changes into the production environment (such as Embarcadero Change Manager). These tools can be configured to perform any database DDL upgrade with little to no human intervention.

Problem

I have development and production servers running PostgreSQL 9.x. After the initial deployment of the database DDL to the production server, I will continue to make changes to the development database. These changes will include bug fixes to stored procedures, changes to tables, additional sequences, new views, more types, more tables, and so forth.

Question

What are the steps to upgrade/migrate the DDL for a production PostgreSQL database application in an automatic fashion (or nearly automatically) such that it has the new changes made in development?

Related

http://apgdiff.startnet.biz/

Thank you!

Advice

Never manually apply database changes; use scripts that are in a version control system (VCS).

After initial deployment, store the "delta" scripts in the VCS. When upgrading, apply all scripts that are missing. Tracking the scripts that have been applied is a standard problem.

Solutions

There are tools to automate this task, including:

Liquibase
Flyway

Liquibase

We are pretty satisfied with Liquibase, which stores the schema definition in XML files. A commandline tool then applies a "changelog" to the database. The changelog can be applied to all environments: development, test, staging, production, continuous build, etc.

Flyway

Also consider Flyway, which uses SQL text files with a naming convention.

Other Considerations

The downside of Liquibase and Flyway is that you basically manage an incremental script. If you need to create a database from scratch, you need to run all change sequentially.

Data Modeling Tool

Document your data model using an ER Modeling tool, such as:

DbSchema
TOAD Data Modeler
Erwin

We use a macro to convert TOAD-based models into a Liquibase changelog. This means we can recreate the database "from scratch" from our model documentation, as well as incremental (Liquibase) scripts that are used to maintain existing installations.


Getting two values for a colums which is not PK I have a table with 4 columns

id,C_number,C_reopen,c_closed

Where id is the pk.
I want to pull rows which has two different values for c_closed for the same c_number. Not c_number is not pk hence can have duplicate values.

create table claim
(
      id not null number(10),
      c_number string(50),
      c_reopen date,
      c_closed(date)
);

Sample data:

insert into claim(111 , 'c101', '10/1/12015','15/1/2015');
insert into claim(112, 'c102','10/2/2015','15/2/2015');
insert into claim(113,'c101','18/1/2015','28/1/2015');

So, here for the same c_number-c101, I have two closed one is 15/1/2015 and the other 28/1/2015. I want a query to retrieve c_cumber which have more than one value for close.

SELECT c_number
FROM CLAIM
GROUP BY c_number
HAVING COUNT(DISTINCT c_closed) > 1;

As we have to retrieve the c_number where the rows have more than one unique c_closed value for every c_number , we have to do the following things:
 1. Group the rows by c_number , so we will get rows for every unique c_number.
 2. Within every group (each unique value of c_number causes a new group, 10 unique c_number = 10 groups) use HAVING CLAUSE to filter out groups where each unique c_number has only one unique c_closed value.

To count only distinct c_closed values you have to use DISTINCT.
HAVING CLAUSE it is like WHERE CLAUSE but it is used after aggregation when all rows with the same values of chosen columns collapse into one row.


mongodb wt file corrupt issue, can not query few documents I have a mongodb cluster with 3 shards and no replication
The problem is one of the shards has a bad disk and because of which some files show input/output error when trying to read

[root@mongodb2 b0]# md5sum collection-700-3655945817504191327.wt
md5sum: collection-700-3655945817504191327.wt: Input/output error

If I try to use wt to salvage the file that too fails

I know exactly which documents are corrupt , but I am helpless because I can not delete or update these documents. Any find() or remove() command on these documents causes entire mongo shard to get killed.

How can I recover this data

Ok we "fixed" the issue or atleast solved the problem of Mongo crashing

The wt collection file was corrupt and was not readable.
We run ddrescue and got rid of the bad sections in the file

ddrescue -n -e1 /database/mongocluster/shard2/b0/collection-72--3831537583791950739.wt RecoveredFiles/collection-72--3831537583791950739.wt RecoveredFiles/collection-72--3831537583791950739.wt.log

And then run wt to salvage the file

./wt  -v -h /database/mongocluster/shard2/b0 -C "extensions=[./ext/compressors/snappy/.libs/libwiredtiger_snappy.so]" -R salvage collection-72--3831537583791950739.wt

Thats it!!. The bad documents could not be recovered, but never mind I can regenerate that data from my app. Most importantly the mongo server does not crash. Hopefully mongodb will improve the logic in the newer versions. Crashing the db on any single I/O error is not very helpful


Using SSAS for retrieval data instead of using SSRS to make a report? If you work with Reporting service in order to create a report and you need to retrieve data from the relational database.

The datamodel of the relational database can be structured in a certain way that can be very difficult to retrieve right data by using join and lots of SQL. In order to save time and cost, it is recommended to use analysis service to retrieve right data that takes less time and cost and the result of the analysis service can be used in reporting service?

In other words, is it recommended to retrieve data in SSAS that will sent to SSRS instead of using SSRS to do lots of SQL coding in order to display right data in the report?

// Fullmetalboy

Depending on what type/volume of data you are reporting on, SSAS can possibly make it easier. I would not take that as a blanket statement. The decision to generate reports off of a relational or dimensional database should be made on a case by case basis.

Keep in mind that the development required to build an OLAP database around a subject area is far more time consuming than building a single report (or even several).

The benefits of building out an OLAP database are huge in return. In many cases, your users can connect directly to it with excel and generate their own reports. This eases the SSRS development requirements on IT.

At the end of the day, there are many factors that would contribute to answering your question. There is simply not a one size fits all solution.


Perfomance of CREATE INDEX vs CREATE INDEX CONCURRENTLY in PostgreSQL I know CREATE INDEX CONCURRENTLY is slower, but how many longer it will takes to finish in comparison with the traditional CREATE INDEX? 40% more or much more than that?

I am creating an index in a table with 10000 registers, and in a column with 15 chars.  It is taking 10 minutes using the traditional CREATE INDEX.

I know it is very difficulty to answer that, but there is a way to estimate?

2ndQuadrant says:

  [CREATE INDEX CONCURRENTLY] must wait for all existing transactions to finish before starting the second phase on index build. This guarantees that no new broken HOT chains are created after the second phase begins.

I've seen CREATE INDEX CONCURRENTLY take over 90 seconds (before I aborted it) when creating the same index not CONCURRENTLY took 0.5 seconds. So it can be much slower.

There was no write activity on the table in the mean time, but other transactions were open on other tables. I think 2ndQuadrant mean that all transactions must end, not just those which have already written to the table in question.


Cannot connect to mongos Following the MongoDB Sharding Documentation, I start a mongos using a configuration file with the specifics of my config server.

It seems rather straightforward, yet I am running into an issue. I start mongos on one machine (lets say its ip address is "some_ip"):

$ sudo mongos --config /etc/some_config.conf

And I try connecting to its shell from another machine, but I get connection refused error.

$ mongos some_ip:xxxxx

Here's part of my some_config.conf file:

    net:
      port: xxxxx
      bindIp: 0.0.0.0

   sharding:
      configDB: <configReplSet>/<ip_of_config>:27019

I'm using 0.0.0.0 for testing purposes. I made sure that the ip_addr and port I'm connecting to matches the ip of the host for the mongos and the port I specified in the net.port param. I also opened up this port in my firewall for ingress/ egress connections.

Did I miss anything ? (NOTE: ** I edited post for further clarification)

UPDATE

(note: i replaced the actual ip addr with "ip_of_config")
This is what my log file for my mongos says when I start mongos.

2018-05-02T05:57:48.533+0000 W NETWORK  [monitoring keys for HMAC] Failed to
connect to <ip_of_config>:27019, in(checking socket for error after poll), reason:
Connection refused
2018-05-02T05:57:48.533+0000 W NETWORK  [monitoring keys for HMAC] Unable to
reach primary for set crs0

Now my config server is up and running fine, but its log file shows that it accepts multiple connections from my mongos server, but these connections close almost immediately. Also there are a few warning logs:

W SHARDING [Balancer] Balancer settings could not be loaded and will be
retried in 10 seconds :: caused by :: ReadConcernMajorityNotAvailableYet:
Failed to refresh the balancer settings due to
ReadConcernMajorityNotAvailableYet: Read concern majority reads are currently
not possible.

W SHARDING [shard registry reload] cant reload ShardRegistry  :: caused by ::
CallbackCanceled: Callback canceled

mongos for “MongoDB Shard,” is a routing service for MongoDB shard configurations that processes queries from the application layer, and determines the location of this data in the sharded cluster, in order to complete these operations. From the perspective of the application, a mongos instance behaves identically to any other MongoDB instance.

As you have mention here mongos.conf file

 net:
      port: xxxxx
      bindIp: 0.0.0.0

   sharding:
      configDB: <configReplSet>/<ip_of_config>:27019

As here i would like to say that here as you are bindIp in mongos.conf file with 0.0.0.0 but in sharding section , you are using  with port number 27019 of any replica set.

Note : As here I am not able to see any logpath destion in your mongos.conf file.

As per MongoDB BOL documentation here Starting in MongoDB 3.6, mongos bind to localhost (127.0.0.1) by default.

Did you try with this configuration of your mongos.conf file.

sharding:
  configDB: replicasetname/ipaddress:27019 -- external ip of replica set of any node which port is 27019, as you mention in your code. which must be participate the replication.
 net:
  bindIp: localhost,ipaddress -- external ip address, if you are using
  port: xxxxx --port number of mongos, it should be different from replica set node port number
systemLog:
  destination: file
  path: -- log destination of mongos file
  logAppend: true
processManagement:
  fork: true             -- if you are using the fork