You probably want a cluster such as MySQL InnoDB Cluster (from Oracle) or MariaDB Galera Cluster or Percona XtraDB Cluster in each region for high availability. You can write to any of the nodes in the cluster and the transaction will be automatically replicated to the other nodes. Note that both Percona XtraDB Cluster and MariaDB Galera Cluster are based on the Galera cluster technology. Galera also provides libraries for regular MySQL.

For an automatic failover solution, you may want to use one of these solutions:

A library in your application that detects whether a node is down, and if so, attempts to use another node in the cluster. This can be e.g. MariaDB's JDBC driver - MariaDB connector/J which I believe also works with Percona and MySQL. You need to configure the connector to use a cluster.
A database proxy for each cluster/region, such as ProxySQL (GPL) or MaxScale (BSL, but older versions automatically become GPL - currently the newest GPL version is 2.1). Database proxies are database-aware: If there's a problem with any of the nodes, it will mark that node as unavailable until the problem has been resolved.

And then you probably want to have master-master asynchronous replication between those clusters. Master-master asynchronous replication simply means that you set up asynchronous master-slave replication from node A to node B and at the same time also asynchronous master-slave replication from node B to node A.

Note that asynchronous master-master replication is notoriously problematic if there is a risk that the two nodes will write to the same rows. Many tutorials on the topic also fail to mention how to avoid many collision problems by setting different values for auto_increment_increment and auto_increment_offset for each node.

Also note that geo-distributed nodes within a cluster is probably not a good idea due to latency and unreliable networks, although Galera doesn't seem to be concerned about this issue in their documentation.

Personally, I wouldn't want to use MySQL 8.0 because of inherent stability issues with the new development and release model: Major new features are now introduced in patch releases (8.0.x). Everyone obviously wants to use the latest patch release because it contains bug & security fixes of previous versions, but since the patch releases also contain major new features, there is a serious risk that new bugs will also be introduced. I'm also not sure what free options you have for non-intrusive backup with MySQL 8.0. (There are paid options, of course, if you go with a MySQL Enterprise license.) You may be able to use Percona Xtrabackup.

It's also possible there is a cloud DBaaS solution for this use-case such as AWS RDS or MariaDB Platform Managed Database Service. I'm unsure how this compares in terms of cost and features, but it might be considerably easier to configure and manage than what I have outlined above.


Are there any known benchmarks/statistics on UnQLite?

I have found some information and pointers here. Extracts from that thread:

  ...retrieved records from a given collection a parsed only once (FastJSON) and cached in-memory for fast retrieval. The cache is a O(1) hashtable so performance shouldn't be a problem.

  ...only the first retrieval is a little bit slower than the others since it involve a disk access (In case you are working with an on-disk DB) and a preprocessing phase (FastJSON decoding) while later fetches are in-memory operations only with no decoding overhead.

  My Observations about document storage in UnQlite :

  In memory: Store faster, search slower ;But both slower than SQLite!
  In File: Store slower, search faster

  e.g:- For same data, In UnQLite:

  In mem: store: 901.9 ms, search: 2586 ms
  In Local disk: store: 15223.3 ms , search: 114.2 ms

  SQLite:
  In mem: Store 9507 ms , Search : 105 ms

  Honestly, I haven't benchmarked SQLite indexes with UnQLite doc store. But, keep in mind that UnQLite is shipped with a disk KV storage engine that support nearly O(1) record retrieval.

The information given lacks specifics and context - nothing how the tests were conducted. Yet I believe it's enough to construct myself a vague opinion.

To me it seem that UnQLite has faster write but slower read performance, in relation to SQLite. It is probably a general trend when comparing SQL with NoSQL database systems.


How to concatenate these two statements (RIGHT and LEFT)? I am trying to trim two characters off both the beginning and end of cell

3 is the first position after the first 2 chars are removed. The length is reduced by 2 chars in the beginning + 2 characters at the end.

select MID(wp_postmeta.meta_value, 3, LENGTH(wp_postmeta.meta_value) -4 ) ...

simplied fiddle


Need help to design model with multiple rights

Since my question I have now a great concept.
Security, rights and access are handled by an ownership table.
Each row contains :

booleans access for create, delete, read & update
Link to table Element wich contains all manageable elements in my website
Link to table OwnershipType wich tells me if it's an access on ownership, for all ressources or only a specific ressource ID
Link to table User
An ID in case OwnershipType is only a specific ressource

To manage groups, I didn't want to create more tables, what I found clever was to consider a group as a user and assign it some ownerships. To add a user to a group, I have a Parent table wich link a user to another (in this case that user is in fact a 'group'). It explains why I have a UserType table. It tells me if it's a group or a normal user.
In my code I just have to check if a user has a parent and if so add its ownership to the user's ones.
That concept let me having some global ownership on many user who are linked to a group and still have really specific ownership on certain user.
If people are interested I can explain how I implemented that concept in my website, it's quite simple and clean.
My model :


Postgres-XL adding GTM Proxy seems to do nothing

This is known bug/feature: pgxc_ctl does not know which proxy should be used by each coordinator / datanode (it does not assume that it is supposed to use a local one).

GTM proxy does not 'pick' the config; you need to instruct others to use the proxy.

You can fix it manually by editing postgresql.conf located in data directories for your datanodes and coordinators.

Find gtm_host and gtm_port and replace real GTM values with IP and port of your proxy. Or you can add these lines to the end of the file if this makes your scripts easier. Reload datanodes and coordinators to update their config (pg_ctl reload -D /data/directory/path)

Ideally you should have a proxy configured on every physical host, and instruct your datanode and coordinators to use the local proxy.


You can create a function-based index.  If the table is named TRANSACTION

CREATE UNIQUE INDEX only_one_purchase
    ON transaction( (case when transaction_type = 'PURCHASE'
                          then order_id
                          else null
                      end) );

This leverages the fact that Oracle doesn't store completely NULL rows in the index so only the order_id values that are part of PURCHASE transactions will be stored in the index.


Transpose two-dimensional array from n rows to 2 columns

Here is a possible solution:

WITH data AS (
    SELECT '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[] AS arr
)
SELECT
    arr[i][1] AS aspect,
    arr[i][2] AS preference
FROM
    data,
    generate_subscripts((SELECT arr FROM data), 1) i
;

The WITH part is there for centralizing data input.  Otherwise it just takes all sub-arrays and selects the elements of those.  Check this on SQLFiddle.


Why indexes are not speeding up multiple join query

@Jehad Keriaki provided the answer. The lastContacts table's primary key column, ContactID, was defined as a string while the same column in each of the other tables was an integer. Joining tables on that column caused implicit conversion and resulted in poor performance.


You (and other future readers) might also try using the COPY command of cqlsh.

For example, if you have a local file named "filedata.csv", containing e.g.:

123-45-678-9,some file data here
abc-de-fgh-1,some other file data here

where the CSV fields corresponding to the columns in your file_uuids table, then you should be able to use:

cqlsh> COPY file_uuids FROM 'filedata.csv';

Hope this helps!


Yes, because it is possible to manually insert data into this field. You wouldn't want anyone intentionally or accidentally adding a record that placed a NULL in your identity field.

On the flip side, if you are not using the field as an artificial key but for some other purpose, and having a null value is ok, then sure leave it off.


How to combine three table to find Avg,High,Low value in mysql?

try use union all + avg + max + min

select avg(`net_wpm`) `avg`,max(`net_wpm`) High,min(`net_wpm`) Low from (
  select * from prac_test union all
  select * from multi_test union all
  select * from shadow_test
) T
group by `UID`

Demo Link

Result:

| UID |     avg | High | Low |
|-----|---------|------|-----|
|   5 | 65.8333 |   97 |   8 |


How to organize MySQL DB to allow an arbitrary counter for clicks, visits, etc in past 24 hours?

3 suggestions...

Don't store all of "http://count.io/vb/product37/1468038731", it wastes space and time.  Trim it down to just the timestamp.

Don't store the time, bump a counter for the day (or hour, if you prefer).

Don't store anything, parse the web server logs later.

Now let me approach it a different way... What is the minimal information you want?  Exact times of all hits?  Running counter of hits?  Daily counts?  Keep in mind what information can be derived from what other info.  Hourly counts --> daily counts.  Running counter --> daily counts (if you grab the count every midnight).  Etc.


Which Oracle directory takes up the most space and why?

In a long-running, stable Oracle database instance, configured the "recommended" way, it's probably going to be the Fast Recovery Area.
That's because it holds all sorts of weird and wonderful things, including (but not limited to):

Archived Redo Logs
Database backups, of which there may be several
Flashback Database logs

Not that you can do much about it.  Like most things about Oracle, it will take up as much space as it wants/needs to.  Your job in Capacity Planning your database instance(s), is basically to make sure it never runs out!


How can I get the whole execution log of stored procedure?

But how these values are Calculated? Is there any way to know how
  these values get calculated?

Answer to this can be refer from MSDN stating below

sys.dm_exec_procedure_stats

  Returns aggregate performance statistics for cached stored procedures.
  The view returns one row for each cached stored procedure plan, and
  the lifetime of the row is as long as the stored procedure remains
  cached. When a stored procedure is removed from the cache, the
  corresponding row is eliminated from this view. At that time, a
  Performance Statistics SQL trace event is raised similar to
  sys.dm_exec_query_stats

You can run something like below code to collect the data from this dmv as per the load to analyse the usage of resources during the execution of that SP:

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
ORDER BY AVG_LOGICAL_READS DESC

You can modify the query above and gather the data to collect the metrics for the SP by scheduling it via SQL agent job. After a certain interval you will have sufficient data to make analysis. An example of such is illustrated here.

Hope this helps!


PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED

The -1 option to psql causes it to wrap a file specified by -f in a BEGIN..COMMIT block, making it a transaction.

Otherwise, add the BEGIN and COMMIT commands to your script so that it becomes a single transaction.


Massive random reads on a huge mongodb database but no cache misses?

My guess is that YCSB isn’t fetching the whole document. Like a covered query, so if all the data that it needs is delivered by the data in the index, you don’t have to get anything from disk, so no cache misses. Another factor is that any page evicted from WT cache would be in the FS cache, since WT on MongoDB only supports buffered I/O.

YCSB doesn’t really do a good job testing MongoDB performance. If you’re using the default workloads, e.g. workloada(50%query/50%updates) or workloadc(95%query/5%updates), as soon as your indexes are loaded in memory no data you will accessed on disk. By default YCSB is querying on _id and getting only one document per request. You could force YCSB to bring create a cursor with [x]thousand documents and make it iterate until the cursor is exhausted.  Here is the command if you wanna try it:

./YCSB/bin/ycsb -s -P ./YCSB/workloads/workloadc -p mongodb.url=“mongodb://192.168.13.101:27017,192.168.13.102:27017,192.168.13.103:27017/ycsb1” -p operationcount=120000000 -p maxexecutiontime=1800 -p readproportion=0 -p scanproportion=1 -p maxscanlength=8000 -threads 16


Run time fetch the value from a table and iterate these values and sent to another procedure

If your goal is to execute the Test stored procedure for all stock rows, I'd recommend using a CURSOR to read each row from the stock table and pass the data into the Test stored procedure.  Here is an example.

CREATE PROCEDURE USAGE
AS
BEGIN
    --Declare variables
    DECLARE @SiteId INT;
    DECLARE @StockId INT;
    DECLARE @ProductId INT;

    --Declare cursor to select rows from stock table
    DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT SiteId
        ,StockId
        ,ProductId
    FROM stock;

    --open the cursor
    OPEN _CURSOR;

    --fetch stock row into variables
    FETCH NEXT
    FROM _CURSOR
    INTO @SiteId
        ,@StockId
        ,@Productid;

    --for each stock row in the cursor, execute the Test stored procedure
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC Test @SiteId
            ,@StockId
            ,@ProductId;

        --fetch the next stock row in the cursor
        FETCH NEXT
        FROM _CURSOR
        INTO @SiteId
            ,@StockId
            ,@Productid;
    END --End While

    --close the cursor after processing all stock rows
    CLOSE _CURSOR

    --deallocate the cursor
    DEALLOCATE _CURSOR
END


Take 1 row of data from MySQL and Split the Results into a Table

Using Union, you can get the results as you want. But I'm not sure whether it is a good solution.

SELECT `proj_id`,`job1_name` AS 'Name',`job1_type` AS 'Type',`job1_time` AS 'Time'
FROM `job`
WHERE 1
UNION
SELECT `proj_id`,`job2_name` AS 'Name',`job2_type` AS 'Type',`job2_time` AS 'Time'
FROM `job`
WHERE 1

Why you are having 5 jobs data in different fields. You can add job_id for the reference and other fields as common.


mySQL subqueries calculating winning percentage

SELECT player_id,
       COUNT(result) total,
       ROUND(100 * SUM(result = 'WIN') / COUNT(result)) winning_percent,
       ROUND(100 * SUM(result = 'LOSE') / COUNT(result)) loosing_percent
FROM game
GROUP BY player_id;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a04d576b918c392f90c1d835fd1a6f51


Is it dangerous to expose MongoDB's db.collection.find to users?

The real answer is it depends, but most of the time this probably isn't a good idea. SELECT while it is less nefarious than other the other CRUD commands still carries risk.
Why this is probably a bad plan

Most applications with databases have some concept of an authenticated user vs a non-authenticated user. If I have unrestricted SELECT only access to the database I can find your hashed passwords and brute force the password offline.
I can mine your database for your user's contact information to let them know about my buddy who is an African prince with a problem accessing his funds.
I can DDoS your server by crafting long running queries and repeating them every timeout.
I can create lots of memory intensive queries to drain resources as well
I can scrape your data and use it for my own profit.
I can increase your bandwidth costs by repeatedly requesting large amounts of data
And many more things

It is important to note that even sites like Have I been pwned? which offer unauthenticated searches of large semi-public data sets don't allow unrestricted SELECT capabilities.


Database design for a lot of rows with data archive

A sliding time scale is very efficient by using PARTITIONs.  Implement 14 hourly partitions using PARTITION BY RANGE(..).  Use REORGANIZE PARTITION to create a new partition every hour.  Use "transportable tablespaces" (assuming you are using 5.6, or preferably 5.7, and InnoDB) to move the 'old' partition away from the main table to the archive table.

More on timed partitions (including code).

More thoughts:

Although TRIGGERs may be functional, I worry that they are costly in your situation.  Can you elaborate.  Perhaps partitioning can serve a purpose there, too.
I hope the Query cache is completely turned off:  query_cache_type = 0 and query_cache_size = 0; otherwise it could be causing some of the slowdown.
How do you do the millions(?) of INSERTs per day?  One row at a time?  Or batched (much better)?
For the 'data analysis', do you build and maintain "Summary tables"?  With such, you may not need the triggers, archive, etc.  Reporting from a summary table is easily 10x faster, and does not touch the main table.
Have you looked into Master-Slave as a way of (1) archiving the data, and (2) providing a Reporting platform separate from the main table.  This, too, could eliminate the need for triggers/archive/etc.

Could you provide more numbers -- how many users?  inserts/sec?


How to grant privileges to use DBMS_OUTPUT in Oracle?

connect system / [PASSWORD] as sysdba;
grant create session to [USER];
grant execute on DBMS_OUTPUT  to [USER] with grant option;

I can solved with it.


If you just updating the interest id and name, use UPDATE

UPDATE user_likes SET
`interest_id` = $interest_id,
`interest_name` = '$interest_name'
WHERE `ID` = $id;

If you want to mechanically do a DELETE followed by an UPDATE, use REPLACE

REPLACE INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name);

Please test these out on a Dev DB Server to make sure this is what you want


Update a column using LOAD_FILE function

Now i want to update 'ravi' with 'ravikumar'

LOAD FILE loads an entire table, not one column, not one cell.

Instead,

UPDATE lfile
    SET name = 'ravikumar'
    WHERE name = 'ravi';


Cannot connect to 19c with SQL Developer though database is mounted and opened, error ORA-01034

You have to restart your DB instances while listener's are up and don't restart the listener after that. When DB's are started: try to connect to them locally (just with username/password) and locally through tnsnames (username/password@tnsname) If listeners are shown correct status and you are able to connect locally with tnsnames: make sure the sam tnsnames file is on your client and there is no firewall between DB and client.


Ola Hallengren's index rebuiuld script - how to exclude more than one object?

Yes that looks like the correct syntax (except you have two '--' for your last index name? :-) )

Also, when you are writing your script for these indexes only, you do not need the '-' before the index name.

more info here: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


SQL Server Internal Inconsistency on GROUP BY

Basically, you have to restore. Do you have a backup?

While this is restoring, read "Misconceptions around database repair" and the rest of Paul Randal's site.

Harsh, but true.


SQL Server Mirroring disconnected for 1 of 5 mirroried DBs after initial setup completes

You may not have enough available threads.
See how many are available with sys.dm_os_sys_info.  See how many are being used with sys.dm_os_schedulers (sum of rows in column current_workers_count)

Database mirroring has the following requirements for worker threads
Principal server: 1 global thread and 2 threads per each of the mirrored databases.
Mirror server: 1 global thread, 2 threads per mirrored databases and one additional thread for each mirrored database for every 4 processor cores.
On a 4 CPU server it requires 1 global thread+ (2+ 1) threads /mirrored db.
On a 6-8 CPU server it requires 1 global thread +(2+2) threads /mirrored db.
https://support.microsoft.com/en-us/kb/2001270

You may not have enough RAM.  You may not have enough network bandwidth. Your disk subsystem may be overwhelmed. Your servers may have too heavy of a load to support another mirrored database.


mongod.service: Failed with result 'exit-code'

Looks familiar... If you check your mongod.service file (/lib/systemd/system/mongod.service), there should be line what tells where mongod.pid file is located (PIDFile=/tmp/mongod.pid). That same file (with path) should be found from your /etc/mongod.conf file.
processManagement:
  pidFilePath: /tmp/mongod.pid

Problem is that, after 5 second when you have said 'sudo systemctl start mongod.service', system will check "is there mongod.pid" and because it cannot find it, it will KILL that running mongod-process.


Reset search_path to the global, cluster default

Factory defaults

  How can I reset it back to whatever the default that a pristine,
  unaltered database on this cluster would have?

You can read boot_val from pg_settings, but that's actually the compiled-in factory default, not the setting in postgresql.conf.

SELECT boot_val
FROM   pg_settings
WHERE  name LIKE 'search_path';

You aren't overlooking the simple RESET?

To just reset the setting (remove it), you can simply:

ALTER DATABASE my_db RESET search_path;

But that doesn't necessarily give you what "a pristine, unaltered database on this cluster would have". It just removes the custom setting for the database. There are several ways to set the search path:

How does the search_path influence identifier resolution and the “current schema”

And it does not change the search_path of your current session. The effect (if any) is visible in your next session.

Related:

What is the search_path for a given database and user?

Connect to template1

You could get the current setting from a connection to template1, like @Abelisto suggested. But you probably want to stay within your current session. You could use dblink for that:

SELECT * FROM dblink('hostaddr=127.0.0.1 dbname=template1 user=postgres password=you_wish'
                         , $$SELECT boot_val, source FROM pg_settings
                             WHERE name LIKE 'search_path'$$
                          ) AS t(search_path text, source text);

search_path    | source
---------------+---------
"$user",public | default

This has a couple of flaws:

You need the additional module dblink installed. That's simple:

Persistent inserts in a UDF even if the function aborts

You need the privilege to connect to template1. And you need to supply the user pw.
Even in template1 the setting might stem from settings to the role or database (or another instance) as well (even though they probably don't). To make sure check the columns source. If it says 'default', you got it. If it says 'database' or 'user' you are back to square 1 ...

Read actual setting in postgresql.conf

.. usingpg_read_file(), but this is also subject to some important restrictions. The manual:

  Use of these functions is restricted to superusers.

And:

  Only files within the database cluster directory and the log_directory can be accessed.

Bold emphasis mine.
If your config files are somewhere else (like in default installations on Debian and friends) you would have to create a symbolic link in the file system in your data or log directory to your actual config file.

SELECT substring(txt, $$\nsearch_path\s*=\s*'([^']+)'$$) AS search_path
FROM   pg_read_file((SELECT setting FROM pg_settings WHERE name = 'config_file')
                  , 0, 10000000) AS txt;  -- arbitrary 10 MB max.

search_path
------------
public

Note how I fetch the path to the config file dynamically with:

SELECT setting FROM pg_settings WHERE name = 'config_file'

Then I use a regular expression with substring() to extract the setting:

\nsearch_path\s*=\s*'([^']+)'

That should work, I didn't spend much time to make the regexp bullet-proof, though. Might be fooled by a commented setting that matches first or something. You might want to test some more.

Finally, since Postgres 9.4, there is also the ALTER SYSTEM command to override settings in a postgresql.auto.conf file. You'll have to check that, too.


date_trunc truncates the date to the specified part. You need extract

select extract('month' from dateofwork) from randomdate group by 1 order by 1;

+-------------+
|   date_part |
|-------------|
|           1 |
|           2 |
+-------------+
SELECT 2
Time: 0.005s


How should I model my database - cannot decide on multiple options

Design 2 two is needed when here are multiple producers per article, you need a bridge table between article and producer, so a producer can have multiple articles and an article can have multiple producers
your first is only valid for 1 to 1 reöationships.
so what you you t have 1:1 or m:n
Thet will determine, what you need


MS SQL: How to get Employee Status from two seperate tables

create table #Table1
( EmpID CHAR(4),
  Co_Code CHAR(2),
  Co_ShortName CHAR(3),
  Emp_Status CHAR(2)
 )
INSERT INTO #Table1
    ([EmpID], [Co_Code], [Co_ShortName], [Emp_Status])
VALUES
    ('WC11', '01', 'AA1', 'FT'),
    ('KC22', '02', 'BB1', 'PT'),
    ('MA33', '03', 'CC1', 'FT');

CREATE TABLE #Table2
    (EmpID CHAR(4),
     Co_Code CHAR(2),
     Co_ShortName CHAR(3),
     Emp_Status CHAR(2));

INSERT INTO #Table2
    ([EmpID], [Co_Code], [Co_ShortName], [Emp_Status])
VALUES
    ('WC11', '04', 'DD1', 'FT'),
    ('KC22', '05', 'EE1', 'PT'),
    ('MA33', '06', 'FF1', 'FT');
GO

Version1:

DECLARE @c_EmpID CHAR(4) = '';
DECLARE @c_Emp_Status CHAR(2) = '';

SET @c_EmpID = 'WC11'
--SET @c_EmpID = 'KC22'

SELECT
        CASE WHEN T1.Emp_Status = 'FT' THEN T1.Co_Code
             WHEN T1.Emp_Status = 'PT' THEN T2.Co_Code
             ELSE 'N/A'
        END AS Co_Code,
        CASE WHEN T1.Emp_Status = 'FT' THEN T1.Co_Shortname
            WHEN T1.Emp_Status = 'PT' THEN T2.Co_Shortname
            ELSE 'N/A'
        END AS Co_Shortname
    FROM #Table1 AS T1
        LEFT JOIN #Table2 AS T2
            ON T1.EmpID = T2.EmpID
            --AND T2.Emp_Status = 'PT'
    --WHERE T1.EmpID = @c_EmpID

output:

Co_Code Co_Shortname
01      AA1

Version2: You can put the logic inside stored procedures:

CREATE PROCEDURE #usp_Co_FT
(
  @i_c_EmpID CHAR(4)
)
AS
BEGIN
        SELECT
           T1.Co_Code ,T1.Co_Shortname
        FROM #Table1 AS T1
        WHERE
            T1.EmpID = @i_c_EmpID
            AND T1.Emp_Status = 'FT'
END
GO
CREATE PROCEDURE #usp_Co_PT
(
  @i_c_EmpID CHAR(4)
)
AS
BEGIN
        SELECT
           T2.Co_Code , T2.Co_Shortname
        FROM #Table2 AS T2
        WHERE
            T2.EmpID = @i_c_EmpID
            AND T2.Emp_Status ='PT'
END
GO

DECLARE @c_EmpID CHAR(4) = '';
DECLARE @c_Emp_Status CHAR(2) = '';

SET @c_EmpID = 'WC11'
--SET @c_EmpID = 'KC22'

SELECT @c_Emp_Status =  T1.Emp_Status FROM #Table1 AS T1 WHERE T1.EmpID = @c_EmpID

IF @c_Emp_Status = 'FT'
BEGIN
    EXEC  #usp_Co_FT @i_c_EmpID = @c_EmpID
END
ELSE IF @c_Emp_Status = 'PT'
    BEGIN
        EXEC  #usp_Co_PT @i_c_EmpID = @c_EmpID
    END
GO

Version 3 with UNION ALL

DECLARE @c_EmpID CHAR(4) = 'WC11';
--SET @c_EmpID = 'KC22'

SELECT
   T1.Co_Code ,T1.Co_Shortname
FROM #Table1 AS T1
WHERE
    T1.EmpID = @c_EmpID
    AND T1.Emp_Status = 'FT'

UNION ALL

SELECT
   T2.Co_Code , T2.Co_Shortname
FROM #Table2 AS T2
WHERE
    T2.EmpID = @c_EmpID
    AND T2.Emp_Status ='PT'

dbfiddle here


That is not how you load a BLOB field. BFILENAME returns a BFILE locator, and BFILE data is not stored in the database, but in files outside of but accessible to the database.

The above command would not even succeed if BLOBCOL is really of BLOB type, you would get the below error:

ORA-00932: inconsistent datatypes: expected BLOB got FILE

Another mistake is that BFILENAME expects the name of a directory database object, not a directory path on the filesystem.

If you want to update a BLOB field with the contents of a file, this is how you do that:

CREATE DIRECTORY MY_DIR AS '/directory/subdirectory';

DECLARE
  src_bfile BFILE := BFILENAME('MY_DIR', 'filename.txt');
  dest_blob BLOB;
BEGIN
  SELECT BLOBCOL into dest_blob FROM BLOBTABLE WHERE ROWID = 'ROWIDVALUE';

  DBMS_LOB.OPEN(src_bfile, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                         SRC_LOB  => src_bfile,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
  DBMS_LOB.CLOSE(src_lob);

  COMMIT;
END;
/

As to what caused the Value Error message, we do not know your table structure nor the exact method how it was loaded, but it was done with an inappropriate method, I think.

Finally, if your file really is a plain text file (just guessing because of .txt), then you could store that in a CLOB instead of BLOB.


The problem is that you should not allow free-form values for the units, but have a second table of units and reference that with a foreign key:

CREATE TABLE unit (
   unit_id bigint PRIMARY KEY,
   name text NOT NULL
);

CREATE TABLE measurement (
   measurement_id bigint PRIMARY KEY,
   unit_id bigint NOT NULL REFERENCES unit,
   value double precision NOT NULL
);

That guarantees that you can only store existing units in your measurement table, which is good for data quality (and it is not much extra effort).

Otherwise you can end up with entries like cm, centimeter, centimeters and Zentimeter all meaning the same thing.


I know cross references between schemas/databases are commonly used in Oracle and now in MS-SQL as well :)

DBmaestro TeamWork (where I work) provides database enforced change management which consists of:

Database enforced version control – enforcing the version control process on the database objects.

Database baseline impact analysis – generating delta script (between environments) utilizing version control labels

and it does support cross references between schemas (Oracle) and databases (MS-SQL).

For more information on database enforced change management you can read our whitepaper


Looping through string, adding all numbers e.g. '123' = 1+2+3, demo with working loop included

You need to set @Total to a default value, otherwise it is NULL and NULL + anything always returns NULL.

Changing this line returns 21 for me:

declare @Total int

to

declare @Total int = 0

That being said, this is not a great thing to try to do on a large number of rows.  If you need to do this on scalar values like you are using there, you may want to consider using a scalar udf for it.  This will NOT scale well though so if you use it against a table be prepared to wait a while.


The Profiler writes column is documented as

  Number of physical disk writes performed by the server on behalf of
  the event.

Before the transaction is committed the log must be physically written to disc up to that point but those writes don't get included in the write stats reported Profiler.

For many inserts no physical writes may occur before the statement completes as the page is modified in the buffer cache and not written out to disc until later (e.g. by the checkpoint or lazy writer process).

For bulk inserts there is an eager writer so the pages may be written out to disc before . See Writing Pages in BOL for more.

However the above doesn't seem relevant as even when no pages are written to disc the physical writes can be reported as non zero.

In the test below Profiler reports 5 writes for the insert but monitoring the file writes with process monitor and sys.dm_io_virtual_file_stats shows that none actually occurred. From which I conclude that the physical writes column actually shows the number of pages made dirty (on the grounds that these will need to be written out to disc later)

  So based on that assumption SQL Profiler shows 0 Writes in your test
  because you are inserting to a page that is already dirty.

TRUNCATE TABLE Foo

CHECKPOINT;

DBCC DROPCLEANBUFFERS;

/*Returns 0 rows*/
SELECT *
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = DB_ID()
       AND is_modified = 1

SELECT 'Before Insert', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1)

INSERT INTO Foo
            (ForeignID,
             FileExtension,
             MimeType)
VALUES      ( 1,
              'FOO',
              'Application/Foo')

/*Nothing Written. Figures same as previous query*/
SELECT 'After Insert', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1)

/*Returns 5 rows*/
SELECT *
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = DB_ID()
       AND is_modified = 1

CHECKPOINT;

/*Shows physical writes*/
SELECT 'After Checkpoint', num_of_writes, num_of_bytes_written
FROM   sys.dm_io_virtual_file_stats(db_id(), 1)

Profiler Trace

Process Monitor Writes after CHECKPOINT

So the CheckPoint actually physically wrote 8 pages (6 single pages and 1 double page) but shows up in Profiler as being responsible for only 4.

From the offsets in the Process Monitor screenshot it is possible to see exactly what pages were written to

+-----+---------------+-----------+
|  57 | syscolpars    | DATA_PAGE |
|  86 | sysrscols     | DATA_PAGE |
|  93 | sysrowsets    | DATA_PAGE |
| 143 | sysallocunits | DATA_PAGE |
| 168 | Foo           | DATA_PAGE |
| 169 | Foo           | IAM_PAGE  |
|   1 |               | PFS_PAGE  |
|   9 |               | BOOT_PAGE |
+-----+---------------+-----------+

The three additional pages not shown in the output from dm_os_buffer_descriptors are the database boot page and sysrscols, sysrowsets.

The write of the database boot page is correlated with the CHECKPOINT itself as the dbi_checkptLSN field gets updated.

It would be expected that the insert would cause the other two pages to be updated (The rcmodified and rcrows columns respectively) but this does not appear to happen immediately and I see no  LOP_COUNT_DELTA entries in the log file either so I assume this must happen periodically rather than after every modification.

I'm not sure how to account for 9 writes being reported but only 8 occurring. Perhaps one page was double counted.


Is a @CATALOG argument required in linked server to connect to READ-ONLY secondary?

Am I configuring the linked server incorrectly or is the @CATALOG argument required?

Regardless of the style in which you connect (SSMS, Linked Server, SQLCMD, JDBC Client, Whatever), there are a few things required in the connection string in order for Read Only Routing to properly work:

TCP/IP must be the protocol used
The listener name must be used (depending on version)
A database belonging to the AG must be in the connection string
Application Intent must be set to ReadOnly

Any deviation from the above will immediately result in read only routing not working. Thus, to answer your question, you need to somehow specify the default database as part of the connection string.


Does the hash join operator always pull from the build side?

If you post the actual query plan for your specific query, we might be able to comment on that more directly.

But in general, I believe (and have always observed) that the hash join operator does always runs as you expected:

Create build-side hash table.
Match all probe rows against it.

This generally makes sense given that SQL Server will place the smaller (estimated) rowset on the build side of the join. The probe side is expected to be larger than the build side (and therefore to contain at least some rows).

In addition, SQL Server has a potential bitmap optimization that it can apply to the probe side of a hash join in some cases, but this optimization requires that the build side be processed first.

An example

For a simple query where we force a large build side of the hash join and an empty probe side, the actual execution plan shows that all rows are processed on the build side of the join.

-- Fully processes the build side of the hash join
SELECT SUM(b.number*p.number) AS meaningless
FROM (
    SELECT 1.0 * v1.number * v2.number AS number
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
) b
INNER HASH JOIN (
    SELECT v3.number
    FROM master..spt_values v3
    WHERE number < -100000
) p
    ON p.number = b.number

Can the build side ever be skipped?

There is at least one example where the build side of the hash join can be skipped: In some cases, the entire join can be eliminated. For example, here SQL Server is able to prove prior to execution that the probe side will have 0 rows. The final query plan is therefore a constant scan and there is no hash join.

-- No hash join at all; the query optimizer realizes no rows are guaranteed
SELECT SUM(b.number*p.number) AS meaningless
FROM (
    SELECT 1.0 * v1.number * v2.number AS number
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
) b
INNER HASH JOIN (
    SELECT v3.number
    FROM master..spt_values v3
    WHERE 0=1
) p
    ON p.number = b.number

What about batch mode?

Based on a quick test, it seems that the batch mode hash join operator will also fully process the build side of the hash join.

-- The batch mode hash join also fully processes the build side
-- even if there are 0 rows on the probe side
SELECT number
INTO #spt_values
FROM master..spt_values
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_#spt_values ON #spt_values
GO
SELECT SUM(b.number*p.number) AS meaningless
FROM (
    SELECT 1.0 * v1.number * v2.number AS number
    FROM #spt_values v1
    CROSS JOIN #spt_values v2
) b
INNER HASH JOIN (
    SELECT v3.number
    FROM #spt_values v3
    WHERE number < -100000
) p
    ON p.number = b.number
GO


MySQL: How to perform aggregate functions on rows where a column is duplicate, for all values of said column

It seems like your query needs to be something like:

SELECT
  column_with_duplicates
  , AVG(column_to_aggregate) AS column_to_aggregate -- AVG does the math, AS gives the name
FROM <table name>
GROUP BY
  column_with_duplicates;

More information on creating aliases (the AS keyword).

More information on GROUP BY

More information on the AVG function with a handy example that would have served you well.

Since you had a question about the GROUP BY clause in the comments below...

As I understand it MySQL has some quirks with it's GROUP BY rules. Those quirks not withstanding, I think of a simple GROUP BY clause, like the one in the query above, as sorting the result set into buckets. So in our query above there would be one bucket for each unique value in the column_with_duplicates column. So each value in the column_to_aggregate would get dropped into the proper bucket. Once all the rows/values are sorted into their buckets then an average is taken of all the values in each bucket individually.

Of course this is just a conceptual model, but it should give you a basic understanding of the role of a GROUP BY clause.


How to split table records into aggregate and array in PostgreSQL

The first part is pretty easy:

select values,count(*) from max_values group by values order by 2 desc limit 1

The second part you could the ARRAY function to transform rows for all values that aren't equal to the one above into one long array, though that is honestly a little tough to do in one SQL statement. Does it really have to be one long array like that? What are you going to do with it?  There is an example of ARRAY() here:

https://blog.lerner.co.il/turning-postgresql-rows-arrays-array/


If you are restoring a CDC-enabled database onto a different instance, you must ensure the server principal that "owns" the database in question is present on the target instance prior to performing the restore.  You must also ensure you use the KEEP_CDC option when restoring the database.
From the MSDN page on restore options:

KEEP_CDC should be used to prevent change data capture settings from being removed when a database backup or log backup is restored on another server and the database is recovered. Specifying this option when restoring a backup with the NORECOVERY option is not permitted.

Restoring the database with KEEP_CDC will not create the change data capture jobs. To extract changes from the log after restoring the database, recreate the capture process job and the cleanup job for the restored database. For information, see sys.sp_cdc_add_job (Transact-SQL).

For information about using change data capture with database mirroring, see Change Data Capture and Other SQL Server Features.

I just restored a test CDC-enabled database onto my SQL Server 2012 instance that was created on my test SQL Server 2008R2 instance, without creating the login that owns the database on the 2012 instance.  An error was shown during the restore:

Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262
Could not update the metadata that indicates database CDCTest is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_MScdc_ddl_database triggers] 'drop''. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.

The login must also be a member of the sysadmin server role prior to restoring the CDC-enabled database.
You will need to re-create the owner of the database in the target instance using the same SID if the owner is a SQL login (if the owner is a Windows login, the SID will naturally be the same if the Windows login is a domain-member).
You'll need to perform something like the following to restore your database:
/*
    obtain the identify of the login that "owns" the database by looking at the
    UserName column in the output from the following command:
*/
RESTORE HEADERONLY FROM DISK = 'D:\SQLServer\Temp\CDCTest.bak'

/*
    This login is the owner of the database
*/
CREATE LOGIN CDCTestLogin
WITH PASSWORD = 'LozierPituophisUnconsciousShelduck4'
    , SID = 0x2ECDACB721D7E84E8A28DCFE1C758799;

/*
    Ensure the login is a member of the 'sysadmin' server-level fixed role.
*/
EXEC sp_addsrvrolemember @loginame = 'CDCTestLogin', @rolename = 'sysadmin';
GO

/*
    Restore the database, with the KEEP_CDC option
*/
RESTORE DATABASE CDCTest FROM DISK = 'D:\SQLServer\Temp\CDCTest.bak'
WITH MOVE 'CDCTest' TO 'D:\SQLServer\MV2012\Data\CDCTest.mdf'
    , MOVE 'CDCTest_log' TO 'D:\SQLServer\MV2012\Logs\CDCTest_log.LDF'
    , REPLACE
    , KEEP_CDC;


Select last 2 records each time based on the record previously accessed records from the in Sql

As far as I understood it would work as a pagination, and the params you would control on the app side anyways. I'd would the following:

@SQL2012:

SELECT [ID]
     , [message]
     , DateCreated
FROM [message]
ORDER BY ID DESC
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY


How to replace the string which has passed in procedure while using EXECUTE IMMEDIATE in Oracle?

create or replace procedure Sample(rowsize number)
is
    v_result varchar2(100);
    v_str varchar2(200);
begin
    v_str := 'Select rtrim(legl_ent_name) || '' | '' ||rtrim(legl_ent_prd_grp) as heirarchy from legl_ent_regn where rownum < :1';
    EXECUTE IMMEDIATE v_str USING rowsize;
    DBMS_OUTPUT.PUT_LINE(rowsize);
end;


Need help figuring out correct permissions for SQL Server Users

With (1) you should be good giving them db_datareader and db_datawriter.

On (2) I would probably create a role and then grant that role execute permissions on all the stored procedures, in place of granting it to each individual user. Then just add those users to the role. If you have all the stored procedures under a schema name you should be able to grant execute to that schema. That will save time and not have to worry about new procedures being added to the database.


how to force oracle 12c to create histograms

A friend of mine told me today, that using the following command

begin
  DBMS_STATS.GATHER_TABLE_STATS (
    ownname => USER,
    tabname => 'H_LINEITEM',
    estimate_percent => 100,
    method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 SYS_STU3ZM$X#P01D3JFWNOWKQ7DD$'
  );
end;
/

where SYS_STU3ZM$X#P01D3JFWNOWKQ7DD$ is the automatically generated name of the column group,

one can manually create a histogram for a given column / column group.

A description can be found here.


How can I monitor the call count per stored procedure per timespan?

You can get this (and more) from Dynamic Management Views (DMVs). To get statistics for a particular stored procedure, try the following query.

SELECT
    OBJECT_NAME(qt.objectid)
  , qs.execution_count AS [Execution Count]
  , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
  , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
  , qs.total_worker_time AS [TotalWorkerTime]
  , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
  , qs.max_logical_reads
  , qs.max_logical_writes
  , qs.total_physical_reads
  , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    qt.[dbid] = DB_ID()
AND qt.objectid = OBJECT_ID('StoredProcedureName')
OPTION (RECOMPILE);

To look at the most frequently executed procedures:

SELECT
    OBJECT_NAME(qt.objectid)
  , qs.execution_count AS [Execution Count]
  , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
  , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
  , qs.total_worker_time AS [TotalWorkerTime]
  , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
  , qs.max_logical_reads
  , qs.max_logical_writes
  , qs.total_physical_reads
  , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    qt.[dbid] = DB_ID()
ORDER BY
    qs.execution_count DESC
OPTION (RECOMPILE);

The values reported are cumulative since the last restart. If you want to measure over a fixed period, use the command below to reset wait stats.

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

If you wanted to measure fixed timespans across the day, you could feed the query output to a table via an agent job and either a) calculate the values between two runs or b) issue the wait stats reset as the last step in the agent job.

Alternatively, capture a profiler trace and run it through Clear Trace.


How to delete large amount of data from a sql table?

The impact will depend on a number of things including your dependencies and indexes on the table. If there are few dependencies (i.e. foreign keys) and few indexes then the delete will go faster. In any case it is best to do this type of operation "off peak" hours when there are the fewest number of users on the system.

If you're in the pinch right now then you'll have to perform the delete now. The system will be a bit slower than usual while the delete is taking place.

You should also understand that due to the way database and log files grow in SQL Server you will not suddenly have a bunch of disk space to work with. To free up more disk space you need to look into other things (in the short term) such as log truncation and db shrinking. Then, figure out a better long-term disk solution.


How do I retrieve data in SQL from a previous date in the pass 12 months in a WHERE CLAUSE using the date and time functions?

Both of these should work depending on what you need.

For any date range:

WHERE CLM_FROM_DT BETWEEN '20160331' AND '20170331'

If you are looking for 1 year back from current date:

WHERE CLM_FROM_DT>DATEADD(year,-1,GETDATE())

If you are looking for 1 year back from specific date (2017-3-31) as per your comment:

WHERE CLM_FROM_DT>DATEADD(year,-1, '20170331')


Can you create a primary xml index in the CREATE TABLE statement (SQL Server)?

I just tried using the XML index creation commands in-lined with a table creation, and it didn't work.
I stumbled upon this article that makes it look like trying to create non-relational indexes (XML, columnstore, or spatial) doesn't work with in-lining the index creation statement in the table definition.


The lock manager is a such super-hot critical code path (probably the most hot critical code path) that it if it would have to wait on a memory allocation for each lock performance would tank. It probably allocates large memory blocks and manages them on it's own. I wouldn't be surprised if it also reserves memory so that it does not run out of memory in some critical code paths.


Mongodb 2.6 Server crash and not restarting

I just know a workaround: manually resync the member of the replica set.

Basically, it is done like this:

Shutdown the mongod instance in question
Delete the contents of the dbpath directory
Restart the instance. It will sync then


For amazon AWS you have two options:

Secondary Private IPs
Elastic IPs

Then you'll need to edit your IP2addr resource agent (under /usr/lib/ocf/resource.d/heartbeat/IPaddr2) to manage the IP failover in-front of amazon.

For Elastic IPs I found this solution.
For secondary IPs I guess you'll just need to include the AWS CLI
commands: ec2-assign-private-ip-addresses when promoting and
ec2-unassign-private-addresses when demoting.


If you're running MySQL in a Linux system, your maintainers will probably do it the next minute after a patch is released and will integrate it in the current version they're offering. If you're stuck with Windows, yes, you should upgrade to a not affected version.


SHRINKFILE best practices and experience

If you want to:

ignore all of the warnings from very smart people with a lot of experience in SQL Server;
assume that because in this case you didn't experience log growth and in this case fixing the fragmentation was quick and didn't cause any further growth, that will always be the case; and,
not confirm that this grow/shrink/grow/shrink cycle has any impact on query times;

Go ahead. Since you say you will only be doing this in rare scenarios, I'm not sure what answer you're looking for here. Do you want a big thumbs-up that you can ignore everything you've read about it and roll the dice?

I will suggest at least one thing: hitting the 10GB limit in Express is going to happen if you have 10GB of data. You avoid that by removing data in the data file (or spreading your data across multiple databases). If you free up a bunch of space inside the data file, that space will be re-used (if you dropped a table, this will be automatic; if you deleted rows or changed column structure, you will need to rebuild - which will also take some space at least temporarily). So if you free up 5GB of data in the file, just because the file is 9GB does not mean that it will try to become 11GB if you add 2GB - it will reuse space inside the file until it can't find any more. It will be a much more expensive operation to shrink the file to 4GB, then force it to grow to accommodate the new data. This is like washing an already clean towel that you're about to use to wipe up a mess...


Oracle 12c: installing expdp and impdp on Linux

Data Pump utilities are installed automatically during the installation of Oracle Database. It should be working without any issues as long as the environment variables(PATH, ORACLE_HOME) are set properly. To set PATH and ORACLE_HOME variable permanently-

1) Log in as oracle user in the OS.

2) [oracle@ora12c Desktop]$ vi ~/.bash_profile

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1; export ORACLE_HOME
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin

3)[oracle@ora12c Desktop]$ . ~/.bash_profile

4)Check the value of the variables.

[oracle@ora12c Desktop]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1
[oracle@ora12c ~]$ echo $PATH
.:/usr/local/java/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.1.0/db_1/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:/u01/app/common/oracle/bin

5) Try to run expdp

[oracle@ora12c Desktop]$ expdp

Export: Release 12.1.0.2.0 - Production on Tue Oct 4 16:46:44 2016

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

Username:

Setting Oracle Environment Variables


Connecting to the Target Database and Recovery Catalog

The syntax will be below mention like that

rman TARGET / CATALOG rman/cat@catdb;

where

rman is Owner of the recovery catalog having RECOVERY_CATALOG_OWNER privilege.

cat is The password for user RMAN specified in the recovery catalog's orapwd file.

catdb is The net service name for the recovery catalog database.

for your further ref Connecting to Databases with RMAN


Scheduled optimization of tables in MySQL InnoDB

OPTIMIZE TABLE performs the following steps internally on a table (mydb.mytable)

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;

Since there is DDL involved, there is no way around queries taking a big performance hit during the operation. Additionally, not performing any optimization would be just as bad.

What you need is to have MySQL Master/Master (aka Circular) Replication set up

You could then try this:

For Servers M1 and M2 and DB VIP pointing to M1

On M2, run the following

STOP SLAVE;
SET sql_log_bin = 0;
Perform OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB on all InnoDB tables
START SLAVE;
Wait for replication to catch (Seconds_Behind_Master = 0)

The SET sql_log_bin = 0 would prevent the DDL commands from replicating over the Master.

Once those steps are complete, promote the Slave to Master, and demote the Master to Slave (can done by just moving your DB VIP from M1 to M2). You could perform this maintenance every day and production will not feel any effects with the exception of the Master Promotion and Slave Demotion.

You could create the script and run it on M2 like this:

echo "SET sql_log_bin = 0;" > InnoDBCompression.sql
echo "STOP SLAVE;" >> InnoDBCompression.sql
mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBCompressionSQL FROM information_schema.tables WHERE engine='InnoDB' ORDER BY (data_length+index_length)" >> InnoDBCompression.sql
echo "START SLAVE;" >> InnoDBCompression.sql
mysql -u... -p... -A < InnoDBCompression.sql

From here, just wait for Seconds_Behind_Master to be 0 on M2, then move the DBVIP from M1 to M2. Now if you know the specific names of the tables you want optimized, you could adjust the query to fetch just those tables.

Give it a Try !!!

CAVEAT

Here is a fair warning: If you have innodb_file_per_table disbaled, every time you run OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB; the ibdata1 file just grows. You would need to cleanup the InnoDB infrastructure to prevent ibdata1 from growing out of control.


Does Detach/Attach or Offline/Online Clear the Buffer Cache for a Particular Database?

I initially thought you were on to something here. Working assumption was along the lines that perhaps the buffer pool wasn't immediately flushed as it requires "some work" to do so and why bother until the memory was required. But...

Your test is flawed.

What you're seeing in the buffer pool is the pages read as a result of re-attaching the database, not the remains of the previous instance of the database.

  And we can see that the buffer pool was not totally blown away by the
  detach/attach. Seems like my buddy was wrong. Does anyone disagree or
  have a better argument?

Yes. You're interpreting physical reads 0 as meaning there were not any physical reads

  Table 'DatabaseLog'. Scan count 1, logical reads 782, physical reads
  0, read-ahead reads 768, lob logical reads 94, lob physical reads 4,
  lob read-ahead reads 24.

As described on Craig Freedman's blog the sequential read ahead mechanism tries to ensure that pages are in memory before they're requested by the query processor, which is why you see zero or a lower than expected physical read count reported.

  When SQL Server performs a sequential scan of a large table, the
  storage engine initiates the read ahead mechanism to ensure that pages
  are in memory and ready to scan before they are needed by the query
  processor.  The read ahead mechanism tries to stay 500 pages ahead of
  the scan.

None of the pages required to satisfy your query were in memory until read-ahead put them there.

As to why online/offline results in a different buffer pool profile warrants a little more idle investigation. @MarkSRasmussen might be able to help us out with that next time he visits.


It looks like you are running the 2nd set of queries on the master.  Those functions need to be run on the replica, not the master.


How to connect to SQL Server Analysis Server via Management Studio command line

There isn't one.

But... why open and close Management Studio all the time? Leave it open and use the Registered Servers list (View | Registered Servers).


Slow performance when using a synonym that is configured to use a linked server

Fact is both are same.

CREATE SYNONYM [Testsyn]
FOR [RemoteServer].[DBName].[dbo]
GO

SELECT *
FROM [Testsyn].EmpTable
GO

SELECT *
FROM [RemoteServer].[DBName].[dbo].EmpTable
GO

Optimizer just decode synonym to actual object,there after both will have identical query plan."[Testsyn]" will be converted to "[RemoteServer].[DBName].[dbo]"

Poor performance for synonym could be :

i) Because permission issue to synonym on remote server.You should check with DBA of both team.


how much overhead an error in RDBMS has?

If the unique field is properly indexed, a UNIQUE violation should be fairly quick.

Honestly though, I think doing a "two inner select" (I think I know what you mean here) is a better solution.  Even with trapping for an exception (at the application level), that's just not a very clean way to handle programatic logic.  Exception handling should be reserved for those rare occasions where you have an "exception to the rule."

And (without knowing which language you're coding in) if you're just trapping for a "SQLException", it's important to remember that those can be thrown for a variety of reasons. Your application might produce unexpected results if you incorrectly assume why an exception happened.

Bottom-line, I would say that the best approach is to do the SELECT to check for duplication.


Maximum number subscribers node in PostgreSQL logical replication?

The limit built into PostgreSQL is 262143 (218 - 1).

So in theory you could bump up required parameters and have 200k logical rep subscribers.

In reality, you will start hitting performance issues much below this.

For the parameters - see documentation and also see this SQL query:

SELECT category, name, setting, min_val, boot_val, max_val
FROM pg_settings
WHERE category ~* 'replica'
AND name ~* 'max.*(workers|slots|senders)'

I get this:

           category            |               name                | setting | min_val | boot_val | max_val
-------------------------------+-----------------------------------+---------+---------+----------+---------
 Replication / Subscribers     | max_logical_replication_workers   | 4       | 0       | 4        | 262143
 Replication / Sending Servers | max_replication_slots             | 10      | 0       | 10       | 262143
 Replication / Subscribers     | max_sync_workers_per_subscription | 2       | 0       | 2        | 262143
 Replication / Sending Servers | max_wal_senders                   | 10      | 0       | 10       | 262143
(4 rows)


MySQL: what is AUTO_INCREMENT “free space”?

It's just the max id the auto_increment can reach.

You can make the same with the following SQL :

SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    `AUTO_INCREMENT` current_id,
    if(m.max_id =-1,'18446744073709551615',m.max_id) max_id,
    (m.max_id - `AUTO_INCREMENT`) free_id,
    (`AUTO_INCREMENT`/m.max_id*100) percent_used
FROM
    INFORMATION_SCHEMA.TABLES t
    INNER JOIN
    (
    SELECT
        TABLE_NAME,
        TABLE_SCHEMA,
        CASE
            WHEN COLUMN_TYPE LIKE "tinyint%unsigned"    THEN 255
            WHEN COLUMN_TYPE LIKE "tinyint%"    THEN 127
            WHEN COLUMN_TYPE LIKE "smallint%unsigned"   THEN 65535
            WHEN COLUMN_TYPE LIKE "smallint%"   THEN 32767
            WHEN COLUMN_TYPE LIKE "mediumint%unsigned"  THEN 16777215
            WHEN COLUMN_TYPE LIKE "mediumint%"  THEN 8388607
            WHEN COLUMN_TYPE LIKE "int%unsigned"    THEN 4294967295
            WHEN COLUMN_TYPE LIKE "int%"    THEN 2147483647
            WHEN COLUMN_TYPE LIKE "bigint%unsigned" THEN 18446744073709551615
            WHEN COLUMN_TYPE LIKE "bigint%" THEN 9223372036854775807
        END max_id
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE extra like "%auto_increment%"
    ) m
    on
        t.TABLE_NAME = m.TABLE_NAME
        AND t.TABLE_SCHEMA = m.TABLE_SCHEMA
-- WHERE TABLE_SCHEMA = 'DatabaseName'
-- AND   TABLE_NAME   = 'TableName';

oak-show-limits

  NAME

  oak-show-limits: show AUTO_INCREMENT "free space"

  SYNOPSIS

  Show all AUTO_INCREMENT columns, current AUTO_INCREMENT values and
  upper bounds, usage ratio, on all schemata and on all tables:

  DESCRIPTION

  oak-show-limits looks for AUTO_INCREMENT columns, and compares current
  values with the column type. It reports those columns where the
  AUTO_INCREMENT value is higher than a given threshold. This utility
  can be used for diagnosing underestimated and overestimated data type
  ranges. Underestimated data types are those in which the
  AUTO_INCRMENET current value is approaching the upper limit.
  Overestimated data types are those where the current value is
  extremely far from the upper limit. Output is in tab delimited, where
  each row specifies:


SQLServer 2016 continually increasing stolen memory

i have a suspicion.  First - are you able to open support tickets with Microsoft?

Easiest way to check my suspicion is capture [\SQLServer:Memory Node(*)\Stolen Node Memory (KB)] for both NUMA nodes, and compare the sum to [\SQLServer:Memory Manager\Stolen Server Memory (KB)].  If my suspicion is correct, when trouble is brewing the discrepancy between the two - which seem like they should always agree - will be quite high.  The other tell-tale characteristic: up to N-1 SQLOS NUMA nodes may have show this relationship (where N is the count of NUMA nodes)
[database node memory] + [stolen node memory] + [free node memory] > [total node memory]

i describe the problem somewhat in these blog posts.

https://sql-sasquatch.blogspot.com/2018/07/sql-server-2016-memory-accounting.html
https://sql-sasquatch.blogspot.com/2018/10/sql-server-2016-memory-accounting-part.html

The basic accounting problem is that sometimes buffer pool growth occurs in a way that buffer descriptor blocks get allocated from SQLOS node A but the pages referenced in the bdbs actually come from SQLOS node B.  The result of this condition is that a portion of physical memory controlled by SQLOS gets double-counted: the same memory is accounted on node A (where the bdbs live) as [Database Node Memory] AND accounted on SQLOS node B as [Stolen Node Memory].  That situation is confusing and inefficient... but its not the full bloom of the problem yet.

The problem fully blooms when so much Node B [stolen node memory] is also Node A [database node memory] that Node B [database node memory] drops to ~2% of Node B [target node memory].  When that happens, the rate of [\SQLServer:Buffer Manager\Free list stalls/sec] skyrockets - we saw 2000/sec when this happened to us.  SQL Server is trying to correct the issue (too little [database node memory]) on Node B by trimming various types of cache on Node B.  But it can't!! Because the [stolen node memory] isn't in any of the various expected cache types.

Temporary resolution: when [total node memory] approaches [target node memory] but [database node memory] approaches 2% of [target node memory], execute DBCC DROPCLEANBUFFERS.

kb4536005 resolves this issue in SQL Server 2017 CU20 and SQL Server 2019 CU2.
https://support.microsoft.com/en-us/help/4536005/improvement-fix-incorrect-memory-page-accounting-that-causes-out-of-me

There is a similar sounding fix in SQL Server 2016 SP2 CU5, kb4470916.
https://support.microsoft.com/en-ca/help/4470916/fix-out-of-memory-error-occurs-when-database-node-memory-kb-drops-belo

However, I don't believe kb4470916 resolves the issue with double-accounting.  So while it may improve SQL Server response to a single SQLOS node having [database node memory] at the ~2% threshold, i think it leaves open the possibility of poking the bear due to this double-counting.  And that may be the situation you are in.

However, if the sum of [stolen node memory] across the two nodes always aligns with [stolen server memory] in the instance, you can forget all about this as if it were a bad dream. :-)


Can't restore database: A transport-level error has occurred when receiving results from the server

Use SQLCMD instead of Management studio to do the restore and try this.  (Since you are using a named instance make sure to use the -S switch for SQLCMD)

C:\> SQLCMD -S GINO\SQLEXPRESS
> RESTORE DATABASE master FROM DISK='C:\SQLBackups\master.bak' WITH REPLACE;
> GO

Obviously replacing the path to your backup with the real path of where your backup is.

Have a look here for (slightly) more information.


'some test', so let's say you want to add 10 to 96 :

db.student.update({name:'student_1','courses.name':'Algebra'},{$inc:{"courses.$.grades.1":10}})

In the find part, you point to the courses with name 'Algebra'. The .$ points you to that found element. Of that found element, it will change the second (.1) value in the grades array. Using $, you don't need to know the position of the course in the array.


creating new table partition with values already existing in the default partition

The non-hack would be to not get into this situation in the first place, by not using a default partition when you are not really sure that that is permanently what you want.  When we fail to correctly foresee the future, sometimes we have to resort to hacks.

You could make it slightly less hack by populating a permanent table, then attaching it as the new partition, rather than juggling the data out and in with a temp table.

begin;
CREATE TABLE orders_foo (like orders);
INSERT into orders_foo SELECT * FROM orders where country_code = 'foo';
DELETE FROM orders where country_code = 'foo';
ALTER TABLE orders ATTACH PARTITION orders_foo FOR VALUES IN ('foo');
commit;

You could also combine the INSERT and DELETE into a single statement, but I don't know that that really gets you anything worthwhile, other than avoiding one opportunity to misspell 'foo'.

with t as (delete from orders where country_code='foo' returning *)
   insert into  orders_bar select * from t;


Calculate change in value window function when the identifier has dropped of the current period

You could do it like this, performance wise this could hurt on bigger datasets.

;WITH CTE
AS
(
SELECT DISTINCT employee_id,a.fiscal_year
from basic_pays
CROSS APPLY
(
SELECT distinct fiscal_year
from basic_pays
) as a)

SELECT   c.employee_id,
    c.fiscal_year,
    CASE WHEN salary IS NULL then 0.00 else salary END as salary,
    CASE WHEN salary IS NULL then 0.00 else salary END - LAG(salary)
        OVER (PARTITION BY c.employee_id ORDER BY c.fiscal_year) salary_change
FROM
    CTE c
LEFT JOIN basic_pays b
ON C.employee_id = b.employee_id
AND c.fiscal_year= b.fiscal_year;

Result

employee_id fiscal_year    salary       salary_change
1             2017        24000.00      NULL
1             2018        25920.00      1920.00
2             2017        17000.00      NULL
2             2018        NULL          -17000.00


(sorry, dunno)
--query will select or exclude entire documents, not fields.

It would be a good idea though :

--query '{datetime:{$gt:ISODate("2014-01-01T00:00:00.000Z")}},{_id:0,name:1,address:1,interests:1}'* )

Mongodump uses bson file structure and preserves the data types. Mongoexport will lose data type of the values. Such as NumberLong("1431677405876") would be converted to just 1431677405876. That's why mongodump is advised if you need to import back to MongoDB.


Splitting row into multiple records based on row value

You can use db<>fiddle for sql-server:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c87b972491dda6462cc4f8b80ff2bd95

This is just a sketch. You need to generate something out of nothing (1 row -> 3 rows). For this you can use a recursive CTE:

with gen (account_id, n, order_count) as (
    select account_id, 1, order_count from stats
    union all
    select account_id, n+1, order_count
    from gen where n < order_count
)
select n-2 as x, n as y, account_id, n
from gen
where n % 3 = 0
order by account_id, n;

I used mod(%) to pick each 3:rd row.

x   y   account_id  n
1   3   1   3
4   6   1   6
7   9   1   9
1   3   2   3


Is it possible to create AWS RDS as replication from external server?

yes , you can,
check below link.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_set_external_master.html


I recommend that you have the jobs enabled and scheduled on both servers and with the same configurations. Databases that are in a mirror role will automatically be skipped. There are checks for that in the script.


Cannot SUM datetime field in SQL Server migrated from Access

Presumably your new column has values which are a certain datetime relative to 1-Jan-1900, or some other date.

...in which case, you could rename your column, and create a calculated column with your original column name, which is the number of second (or minutes or whatever granularity you want), so that you can SUM that instead.

Example:

SELECT t = DATEADD(SECOND, SUM(DATEDIFF(SECOND, '19000101', myTimeColumn)), '19000101')
FROM dbo.someTable;


MySQL does not use index for wildcard-free query on an INT field - why?

To answer the question: http://sqlfiddle.com/#!9/1be04/2

The LIKE works on strings, so it has to CAST the id to a string - casting is a type of a function call (an expression) so index cannot be used.

I was not able to locate that specific information in the MySQL manual as it is an universal concept known as sargability (the article lists LIKE as sargable, but that holds only for string columns/indexes and prefix matching, not in general).

Another answer about what is and is not sargable: https://stackoverflow.com/a/799616/1786423

As ypercube pointed out in the comment - the 1 too is converted to '1' when the query is executed. It is done just once, not for every row, because the value is constant. It should not change the plan for this query but there are some cases when it can hurt you too - doing col IN (1,'2') will forbid the usage of any index no matter what type is set for the col.


I've realised that using an alternative query structure helps to speed this up.

UPDATE table1 SET is_active = 1 WHERE id IN (1,2,3,4,...)

Works much quicker and reduces the script file size to 11kb from 114kb and also reduces running time from 40 secs to just under 2 secs


There are a few ways to do this, some easier and safer than others.  You could mess around with detaching and attaching, but I will never recommend that solution for normal cases like these.  Since you have your database on SQL Server Express Edition, then it can't be larger than 10 GB.  It'll be a quick and painless process to just backup the database, and jump over to your other machine and do the restore.

The Import/Export features aren't worth the hassle in this case.  Backup and restore would be the easiest route.

If you're looking to only move a certain amount of data (say, a handful of tables), then I'd just as soon generate the scripts (for data and schema if that's desired).  You can do this by right-clicking the database in Object Explorer, Tasks, and Generate Scripts.  You'll have numerous options for this task, and you can specify a subset of objects to script out.  Then you could run that script on your destination instance.

Just to reiterate, I'd do the backup/restore for the entire database copy, or generate scripts for a subset of objects.


To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS

If you're loading to an empty database, you could/should take steps to avoid requiring any additional maintenance steps post load. Fragmentation is the enemy, that's what you're trying to avoid.

Drop all NC indexes before loading.
Post load, add the NC indexes for each table in sequence i.e. don't add an index to TableA, then TableB, then back to TableA. This is has no effect on fragmentation but it can improve the time taken to add the indexes on very large datasets (reduces buffer pool churn).
If you can insert data to a table in clustered index order, leave the clustered index in place. If you can't, dump the data into a heap and rebuild in to a clustered index post load.

Rather than copy and paste, I'll point you toward the very comprehensive list of references for ETL optimisation @Marian put together in an answer to a question I asked on BCP. Many of these will be equally applicable to your scenario.


Where to start learning to understand SQL Server architecture and internals?

Just to add to the suggestions on this thread, you might find these interesting
Books:

I found Thomas Larock's DBA Survior very useful. It doesn't go deep into SQL Server internals, but it has right pointers to guide a newbie towards becoming a successful DBA. If you are past that newbie stage, you can skip this.

Rod Colledge's SQL Server 2008 Administration in action was useful for me as a next step from DBA Survior

Troubleshooting SQL Server by Jonathan Kehayias is a great book. It is available as a free download. Also take a look at other books available on SSC. I am currently reading a book on execution plans by Grant Fritchey.

There are some awesome blogs out there:

Read everything under the category "involuntary DBA" on Paul Randal's blog. While I understand that you obviously are not an involuntary DBA, they tend to be simpler while explaining things to newbies.

Buckwoody has a great blog here and also a useful SQL Server reference guide here. He is blogging about Windows Azure stuff thesedays but you can check the archives

Other Blogs in random order

Aaron Bertrand
Grant Fritchey
Brent Ozar
Thomas LaRock
Pinal Dave
Phil Factor

Podcasts:

There are a few SQL Server related podcasts on DotNetRocks
Also here on SQL Down Under

Other great sites - SSC, MSSQL Tips, SQLCAT, LessThanDot.
This list is in no way complete and if I have missed some important books/sites/blogs, thats because I too am still learning and my knowledge is limited. Lets hope the others on this thread add more resources.
Good luck!
UPDATE: I forgot to include SQL Server Performance Survival Guide technet article which has loads of links and pointers.


Your current setup seems to be on the right track. I would cut SSIS out of the equation - the stored procedure INSERTs into the staging table, and then a C# app pulls the data out, creates the XML files, and SFTPs the files off to the destination. I realize that's more a 'developer' solution than an 'DBA' solution, but flat files and SFTP are not things well-suited to SQL environments.


First let's do a quick summary of the query plan for that insert query. SQL Server scans all rows from HVentas_1 to create the hash build and finds 0 relevant rows. It then scans through TR_TAB_OLAP_HVentas and stops if/when it finds 5 distinct rows such that v.fecha > @FechaMovimiento and that don't match a row in HVentas_1. You have an index defined on the table but SQL Server doesn't use it in the query plan that you uploaded. The worst case for performance should be when @FechaMovimiento is a value that doesn't match any rows in the table. In that case SQL Server will scan all 70 million rows from the table only to insert 0 rows. The number of rows inserted isn't relevant here. What's important is the amount of work that SQL Server did to find the rows to insert.

For the actual plan that you uploaded, SQL Server had to scan 70037142 rows from the heap before it found five distinct rows. It seems as if you had a very selective value for the variable, but you're using a local variable in a stored procedure without a RECOMPILE hint. You could be running into an issue with parameter sniffing. In any case, try it again with a RECOMPILE hint. That will allow SQL Server to build a query plan that accounts for the value of the local variable. This should be fine unless you're running this stored procedure hundreds of times per second. With better information, the query optimizer can make more informed decisions about when to use an index. Using an index here is likely to lead to better performance.

As a side node, you have a CCI that only has ten rows. Will that table continue to be that small? If so that may not be a good candidate for columnstore.


About SSDs, the main advice is to lower 'random_page_cost' to 1 (equals to 'seq_page_cost') in postgresql.conf, in addition to others usuals settings.


How to draw ER diagram for recursive relation when the number of roles is more than 3?

Organisations are generally far more flexible than we initially think.
For example, Jon might have two jobs in the organisation (Developer and Acting Team Lead).

He may report to Bill the Development Manager for his Acting Team lead role and to Bob for the Development work that he still carries out.

This necessitates having the self referencing relationship for REPORTS_TO expand out into another entity type so that each person can report to many others rather than just one.
You also need to be able to show that each person can have many roles/jobs and the you may want to have some roles that officially report to multiple roles.

From another perspective hierarchies like this are worth exploring in a graph database as that is what they excel at. Not having a formal schema also means that they are very flexible.


What is an example of an extproc.ora file?

Why do you expect that "there must be more to it"? extproc.ora simply sets environment variables for the extproc daemon, which is used to execute code for external stored procedures, and for procedures themselves. There is some documentation in the application development guide.

The one environment variable that seems to be recognized by extproc itself is EXTPROC_DLLS that you mention, which determines the locations where the daemon looks for shared libraries. If set to ONLY:<path> it won't look in $ORACLE_HOME/bin or $ORACLE_HOME/lib.

Any other variables set in extproc.ora are simply passed on to the environment where the external code runs, so they would be specific to each library.


Is the MySQL replica's data consistent if the Master was switched to binlog_format = MIXED but the Slave's relay logs were rotated sometime later?

Relay log rotation have nothing to do with it.

What's critical is that the slaves, if set up with log_slave_updates (i.e. in themselves logging to binary logs), are pre-configured with binlog_format = MIXED or binlog_format = ROW.

To elaborate: there is no problem if the master uses binlog_format = STATEMENT and the slaves use ROW. There is a problem if the master uses ROW and the slaves use STATEMENT. You just can't restore the exact original statement from ROW format.

Likewise, if you master is MIXED, then slaves must be wither MIXED or ROW.

Back to your question, assuming the slaves are already configured with MIXED, there is no problem in just SET GLOBAL binlog_format = MIXED on the master. There is no problem in the binary log containing some entries of this format and some entries of the other. There is no problem with the relay logs containing entries of both forms.

I disagree with the accepted answer you linked to.


Select each column along with the ID where the column IS NOT NULL. Use UNION to combine the result sets. This will already eliminate duplicates. Then GROUP BY the ID and use the group_concat() aggregation function to build your list.

SELECT id,
       group_concat(col
                    SEPARATOR ',') uniqval
       FROM (SELECT id,
                    col1 col
                    FROM elbat
                    WHERE col1 IS NOT NULL
             UNION
             SELECT id,
                    col2 col
                    FROM elbat
                    WHERE col2 IS NOT NULL
             UNION
             SELECT id,
                    col3 col
                    FROM elbat
                    WHERE col3 IS NOT NULL
             UNION
             SELECT id,
                    col4 col
                    FROM elbat
                    WHERE col4 IS NOT NULL
             UNION
             SELECT id,
                    col5 col
                    FROM elbat
                    WHERE col5 IS NOT NULL) x
       GROUP BY id;

SQL Fiddle


Note: As far as I am aware, you cannot create a database trigger as you are currently attempting to do...

That being said, your 2 error messages, "Incorrect syntax near 'INSERT'" and "Incorrect syntax near 'END'", are due to you using square brackets [] and missing a starting BEGIN respectively.

Your Code (with comments):

--Remove these Square Brackets...
CREATE TRIGGER check_if_view_exists ON DATABASE FOR [INSERT], [UPDATE]
AS
BEGIN
   IF EXISTS(SELECT view_name from MySpecial_views EXCEPT SELECT name from sys.views)
       -- You don't END an IF, you END a BEGIN
       RAISERROR ('You can only insert views in that table that really exist!',10, 1)
      ROLLBACK
   END -- end if
END -- end trigger

Working Example:

CREATE TABLE dbo.MySpecialViews (
    ViewName varchar(10)
);
GO

-- Table or View, not on the Database
-- ...also no [] around INSERT and UPDATE
CREATE TRIGGER CheckIfViewExists ON dbo.MySpecialViews FOR INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT ViewName FROM MySpecialViews EXCEPT SELECT name FROM sys.views)
    -- Added a BEGIN for the code after the IF
    BEGIN
        RAISERROR('you can only insert views in that table that really exist!', 10, 1)
        ROLLBACK
    END
END;
GO

DROP TABLE dbo.MySpecialViews;

Hope this helps.


Could be many reasons for TM waits but you can try running this script to check for un-indexed foreign keys first.

select cc.owner, cc.table_name, cc.column_name, cc.position
from dba_cons_columns cc
where
 cc.owner not in ('SYS','SYSTEM')
 and position is not null
minus
select i.index_owner, i.table_name, i.column_name, i.column_position
from dba_ind_columns i
where
 i.index_owner not in ('SYS','SYSTEM');


What permission do I need for viewing... all current user permissions?

I had to write a permissions audit for some compliance report a while back and now I have integrated that report into my suite of administrative DB scripts.

I put a copy of the procedure that generates the report in pastebin for you and I verified that it works with SQL Server 2000 still.

http://pastebin.com/08krN7jf

It generates the report in a tree view with the login and default database listed first, then any server level roles, then any database roles and explicitly granted permissions.

Edited to add the actual code directly in the reply rather than rely on the pastebin link

CREATE PROCEDURE [dbo].[spAdmin_PermissionsReport]
AS
BEGIN
    /*
        Description:    A report of each login in a database server along with its
                corresponding server roles and the databases that the login has access to.
                For each database, the database roles and explicit permissions in that
                database are also listed.

                The report is generated in the following format:

                ServerName
                    LoginName [DefaultDB]
                    Created
                    Full Name

                    ServerRole(s):
                        ServerRole1
                        ServerRole2
                        ...
                    Database(s):
                        db1
                            DatabaseRole(s):
                                dbr1
                                dbr2
                                ...
                            Permissions:
                                object1     type
                                oblect2     type
                                ...
                        db2
                            DatabaseRole(s):
                                dbr1
                                dbr2
                                ...
                            Permissions:
                                object1     type
                                oblect2     type
                                ...
                        ...
    */

    SET ANSI_NULLS ON
    SET ANSI_WARNINGS ON
    SET NOCOUNT ON

    DECLARE
        @SQL                VARCHAR(8000)
    ,   @DB                 VARCHAR(128)
    ,   @LoginName          VARCHAR(128)
    ,   @DefaultDB          VARCHAR(128)
    ,   @CreateDate         DATETIME
    ,   @MemberSID          VARBINARY(85)
    ,   @UID                SMALLINT
    ,   @ServerRole         VARCHAR(128)
    ,   @DatabaseRole       VARCHAR(128)
    ,   @Permission         VARCHAR(200)
    ,   @FirstServerRole    BIT
    ,   @FirstDatabase      BIT
    ,   @FirstDatabaseRole  BIT
    ,   @FirstPermission    BIT
    ,   @RecordSeparator    VARCHAR(1000)

    -- The 'first' variables flag when permission headers should be printed, 1 means yes.
    SELECT
        @FirstServerRole    = 1
    ,   @FirstDatabase      = 1
    ,   @FirstDatabaseRole  = 1
    ,   @FirstPermission    = 1
    ,   @RecordSeparator    = '================================================================================'

    CREATE TABLE #t_Logins (
        LoginName       VARCHAR(128)     NULL
    ,   MemberSID       VARBINARY(85)    NULL
    ,   DefaultDB       VARCHAR(128)     NULL
    ,   CreateDate      DATETIME     NULL
    )

    CREATE TABLE #t_Databases (
        DBName          VARCHAR(128)    NOT NULL
    )

    -- Roles for a server
    CREATE TABLE #t_SRVRoleMember (
        ServerRole      VARCHAR(128)    NOT NULL
    ,   MemberName      VARCHAR(128)    NOT NULL
    ,   MemberSID       VARBINARY(85)   NOT NULL
    )

    -- Roles for the current database
    CREATE TABLE #t_RoleMember (
        DBRole          VARCHAR(128)    NOT NULL
    ,   MemberName      VARCHAR(128)    NOT NULL
    ,   MemberSID       VARBINARY(85)   NULL
    )

    -- All database roles by database
    CREATE TABLE #t_DBRoleMember (
        DBName          VARCHAR(128)    NOT NULL
    ,   DBRole          VARCHAR(128)    NOT NULL
    ,   MemberName      VARCHAR(128)    NOT NULL
    ,   MemberSID       VARBINARY(85)   NOT NULL
    ,   UID         SMALLINT    NOT NULL
    )

    -- Permissions for a database
    CREATE TABLE #t_Protect (
        Owner           VARCHAR(128)    NOT NULL
    ,   Object          VARCHAR(128)    NOT NULL
    ,   Grantee         VARCHAR(128)    NOT NULL
    ,   Grantor         VARCHAR(128)    NOT NULL
    ,   ProtectType     VARCHAR(50) NOT NULL
    ,   [Action]        VARCHAR(50) NOT NULL
    ,   [Column]        VARCHAR(50) NULL
    )

    -- All permissions by database
    CREATE TABLE #t_DBProtect (
        DBName          VARCHAR(128)    NOT NULL
    ,   Owner           VARCHAR(128)    NOT NULL
    ,   Object          VARCHAR(128)    NOT NULL
    ,   Grantee         VARCHAR(128)    NOT NULL
    ,   Grantor         VARCHAR(128)    NOT NULL
    ,   ProtectType     VARCHAR(50) NOT NULL
    ,   [Action]        VARCHAR(50) NOT NULL
    ,   [Column]        VARCHAR(50) NULL
    ,   UID         SMALLINT    NOT NULL
    ,   MemberSID       VARBINARY(85)   NOT NULL
    )

    -- Populate server logins
--  PRINT 'Populate Server Logins'
    INSERT INTO #t_Logins
        SELECT
            sl.[name]
        ,   sl.SID
        ,   sl.DBName
        ,   sl.CreateDate
        FROM
            master.dbo.SYSLOGINS sl
        WHERE
            [name] IS NOT NULL
        AND SID IS NOT NULL
        ORDER BY
            [name]

    -- Populate the databases
--  PRINT 'Populate Databases'
    INSERT INTO #t_Databases
        SELECT
            d.[name]
        FROM
            master..sysdatabases d
        WHERE
            d.[name] NOT IN ( 'tempdb', 'model' )
        AND HAS_DBACCESS(d.[name])                          = 1
AND d.name = 'TMPSERVICE'
        ORDER BY
            d.[name]

    -- Populate logins' server roles
--  PRINT 'Populate Logins'' Server Roles'
    INSERT INTO #t_SRVRoleMember
        EXEC sp_HelpSRVRoleMember

    -- Populate logins' database roles
    DECLARE cDBs CURSOR FOR
        SELECT
            DBName
        FROM
            #t_Databases
        ORDER BY
            DBName

    OPEN cDBs

    FETCH NEXT FROM cDBs INTO @DB

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Get the current database role
        SET @SQL = 'INSERT INTO #t_RoleMember EXEC [' + @DB + '].dbo.sp_helprolemember'
--      PRINT 'Insert DB Roles for ' + @DB
        EXEC(@SQL)

        SELECT @SQL = '' +
        'INSERT INTO #t_DBRoleMember ( ' +
        '   DBName ' +
        ',  DBRole ' +
        ',  MemberName ' +
        ',  MemberSID ' +
        ',  UID ' +
        ') ' +
        '   SELECT ' +
        '       ''' + @DB + ''' AS DBName' +
        '   ,   ''public'' AS DBRole' +
        '   ,   tl.LoginName ' +
        '   ,   tl.MemberSID ' +
        '   ,   u.SID ' +
        '   FROM ' +
        '       #t_Logins tl ' +
        '       JOIN [' + @DB + '].dbo.sysusers u ' +
        '           ON  tl.MemberSID = u.SID '
        EXEC(@SQL)

        -- Store the current database role by database
        SELECT @SQL = '' +
        'INSERT INTO #t_DBRoleMember ( ' +
        '   DBName ' +
        ',  DBRole ' +
        ',  MemberName ' +
        ',  MemberSID ' +
        ',  UID ' +
        ') ' +
        '   SELECT ' +
        '       ''' + @DB + ''' ' +
        '   ,   t.DBRole ' +
        '   ,   t.MemberName ' +
        '   ,   t.MemberSID ' +
        '   ,   u.UID ' +
        '   FROM ' +
        '       #t_RoleMember t ' +
        '       JOIN #t_Logins tl ' +
        '           ON  t.MemberSID = tl.MemberSID ' +
        '       JOIN [' + @DB + '].dbo.sysusers u ' +
        '           ON  t.MemberName = u.[name] COLLATE SQL_Latin1_General_CP1_CI_AS ' +
        '   ORDER BY ' +
        '       t.MemberName ' +
        '   ,   t.DBRole '
        EXEC(@SQL)

        -- purge out the current database roles for next database
        DELETE FROM #t_RoleMember

        -- Get the database permissions
        SET @SQL = 'INSERT INTO #t_Protect EXEC [' + @DB + '].dbo.sp_helprotect'
--      PRINT 'Insert Permissions for ' + @DB
        EXEC(@SQL)

        -- Store the current database permissions by database
        SELECT @SQL = '' +
        'INSERT INTO #t_DBProtect ( ' +
        '   DBName ' +
        ',  Owner ' +
        ',  Object ' +
        ',  Grantee ' +
        ',  Grantor ' +
        ',  ProtectType ' +
        ',  [Action] ' +
        ',  [Column] ' +
        ',  UID ' +
        ',  MemberSID ' +
        ') ' +
        '   SELECT ' +
        '       ''' + @DB + ''' AS DBName ' +
        '   ,   t.Owner ' +
        '   ,   t.Object ' +
        '   ,   t.Grantee ' +
        '   ,   t.Grantor ' +
        '   ,   t.ProtectType ' +
        '   ,   t.[Action] ' +
        '   ,   t.[Column] ' +
        '   ,   u.UID ' +
        '   ,   u.SID ' +
        '   FROM ' +
        '       #t_Protect t ' +
        '       JOIN [' + @DB + '].dbo.sysusers u ' +
        '           JOIN #t_Logins l ' +
        '               ON  u.SID = l.MemberSID ' +
        '           ON  t.Grantee = u.Name COLLATE SQL_Latin1_General_CP1_CI_AS ' +
        '   ORDER BY ' +
        '       Grantee ' +
        '   ,   Object'

        EXEC(@SQL)

        -- purge out the current database permissions for next database
        DELETE FROM #t_Protect

        FETCH NEXT FROM cDBs INTO @DB
    END

    CLOSE cDBs
    DEALLOCATE cDBs

--  SELECT * FROM #t_Logins
--  SELECT * FROM #t_Databases
--  SELECT * FROM #t_SRVRoleMember
--  SELECT * FROM #t_DBRoleMember
--  SELECT * FROM #t_DBProtect

    -- GENERATE REPORT
    PRINT   'Begin report - ' + CONVERT(VARCHAR, GETDATE())
    PRINT   'Report: spAdmin_PermissionsReport'
    PRINT   'Description: A report of each login in a database server along with its'
    PRINT   'corresponding server roles and the databases that the login has access to.'
    PRINT   'For each database, the database roles and explicit permissions in that'
    PRINT   'database are listed.'
    PRINT   @RecordSeparator
    PRINT 'This report reflects local accounts only.'
    PRINT   @RecordSeparator

    SELECT  @SQL = 'SERVERNAME: ' + @@SERVERNAME
    PRINT   @SQL
    PRINT   @RecordSeparator

    DECLARE cLogins CURSOR FOR
        SELECT
            LoginName
        ,   MemberSID
        ,   DefaultDB
        ,   CreateDate
        FROM
            #t_Logins
        ORDER BY
            LoginName

    OPEN cLogins

    FETCH NEXT FROM cLogins INTO @LoginName, @MemberSID, @DefaultDB, @CreateDate

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @SQL = CHAR(9) + 'LOGINNAME:' + CHAR(9) + @LoginName + ' [' + @DefaultDB + ']'
        PRINT @SQL
        SELECT @SQL = CHAR(9) + 'CREATED:' + CHAR(9) + CONVERT(VARCHAR, @CreateDate)
        PRINT @SQL

        DECLARE cServerRoles CURSOR FOR
            SELECT
                ServerRole
            FROM
                #t_SRVRoleMember
            WHERE
                MemberSID = @MemberSID
            ORDER BY
                ServerRole

        OPEN cServerRoles

        FETCH NEXT FROM cServerRoles INTO @ServerRole

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF ( @FirstServerRole = 1 )
            BEGIN
                PRINT ''
                SELECT @SQL = CHAR(9) + 'SERVERROLE(S):'
                PRINT @SQL
                SET @FirstServerRole = 0
            END

            SELECT @SQL = CHAR(9) + CHAR(9) + @ServerRole
            PRINT @SQL

            FETCH NEXT FROM cServerRoles INTO @ServerRole
        END

        CLOSE cServerRoles
        DEALLOCATE cServerRoles

        DECLARE cDatabases CURSOR FOR
            SELECT
                DBName
            FROM
                #t_Databases
            ORDER BY
                DBName

        OPEN cDatabases

        FETCH NEXT FROM cDatabases INTO @DB

        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE cDatabaseRoles CURSOR FOR
                SELECT
                    DBRole
                FROM
                    #t_DBRoleMember
                WHERE
                    DBName = @DB
                AND MemberSID = @MemberSID
                ORDER BY
                    DBRole

            OPEN cDatabaseRoles

            FETCH NEXT FROM cDatabaseRoles INTO @DatabaseRole

            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF ( @FirstDatabase = 1 )
                BEGIN
                    PRINT ''
                    SELECT @SQL = CHAR(9) + 'DATABASE(S):' + CHAR(9) + '[' + @DB + ']'
                    PRINT @SQL
                    SET @FirstDatabase = 0
                END

                IF ( @FirstDatabaseRole = 1 )
                BEGIN
                    SELECT @SQL = CHAR(9) + CHAR(9) + 'DATABASEROLE(S):'
                    PRINT @SQL
                    SET @FirstDatabaseRole = 0
                END

                SELECT @SQL = CHAR(9) + CHAR(9) + CHAR(9) + @DatabaseRole
                PRINT @SQL

                FETCH NEXT FROM cDatabaseRoles INTO @DatabaseRole
            END

            CLOSE cDatabaseRoles
            DEALLOCATE cDatabaseRoles

            DECLARE cPermissions CURSOR FOR
                SELECT
                    UPPER(LTRIM(RTRIM(dbp.ProtectType))) + ' ' + UPPER(LTRIM(RTRIM(dbp.[Action]))) + CASE WHEN dbp.[Column] NOT IN ('.', '(All+New)', '(All)') THEN ' (' + dbp.[Column] + ')' ELSE '' END + ' ON ' + dbp.Object + ' TO ' + dbp.Grantee
                FROM
                    #t_DBProtect dbp
                    JOIN #t_Logins l
                        ON  dbp.MemberSID = l.MemberSID
                WHERE
                    dbp.DBName = @DB
                AND l.MemberSID = @MemberSID

            OPEN cPermissions

            FETCH NEXT FROM cPermissions INTO @Permission

            WHILE @@FETCH_STATUS = 0
            BEGIN
                IF ( @FirstDatabase = 1 )
                BEGIN
                    PRINT ''
                    SELECT @SQL = CHAR(9) + 'DATABASE(S):' + CHAR(9) + '[' + @DB + ']'
                    PRINT @SQL
                    SET @FirstDatabase = 0
                END

                IF ( @FirstPermission = 1)
                BEGIN
                    SELECT @SQL = CHAR(9) + CHAR(9) + 'PERMISSION(S):'
                    PRINT @SQL
                    SET @FirstPermission = 0
                END

                SELECT @SQL = CHAR(9) + CHAR(9) + CHAR(9) + @Permission
                PRINT @SQL

                FETCH NEXT FROM cPermissions INTO @Permission
            END

            CLOSE cPermissions
            DEALLOCATE cPermissions

            SET @FirstDatabaseRole = 1
            SET @FirstDatabase = 1
            SET @FirstPermission = 1

            FETCH NEXT FROM cDatabases INTO @DB
        END

        CLOSE cDatabases
        DEALLOCATE cDatabases

        SET @FirstDatabase = 1
        SET @FirstServerRole = 1

        PRINT @RecordSeparator

        FETCH NEXT FROM cLogins INTO @LoginName, @MemberSID, @DefaultDB, @CreateDate
    END

    PRINT 'End report - ' + CONVERT(VARCHAR, GETDATE())

    CLOSE cLogins
    DEALLOCATE cLogins

    DROP TABLE #t_DBProtect
    DROP TABLE #t_Protect
    DROP TABLE #t_DBRoleMember
    DROP TABLE #t_RoleMember
    DROP TABLE #t_SRVRoleMember
    DROP TABLE #t_Databases
    DROP TABLE #t_Logins
END