Setting up a simple "permissions architecture" in PostgreSQL

To answer your direct question "if I revoke the CREATE privilege from the PUBLIC role, how do I see that ? "...

Use the following SQL (from this stackoverflow question) to get database-specific privileges for PUBLIC role:

   SELECT nspname,
       coalesce(nullif(role.name,''), 'PUBLIC') AS name,
       substring(
          CASE WHEN position('U' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', USAGE' ELSE '' END
          || CASE WHEN position('C' in split_part(split_part((','||array_to_string(nspacl,',')), ','||role.name||'=',2 ) ,'/',1)) > 0 THEN ', CREATE' ELSE '' END
       , 3,10000) AS privileges
FROM pg_namespace pn, (SELECT pg_roles.rolname AS name
   FROM pg_roles UNION ALL SELECT '' AS name) AS role
 WHERE (','||array_to_string(nspacl,',')) LIKE '%,'||role.name||'=%'
 AND nspowner > 1;

Result:

      nspname       |   name   |  privileges
--------------------+----------+---------------
 pg_catalog         | postgres | USAGE, CREATE
 pg_catalog         | PUBLIC   | USAGE
 information_schema | postgres | USAGE, CREATE
 information_schema | PUBLIC   | USAGE
 test               | postgres | USAGE, CREATE
 public             | postgres | USAGE, CREATE
 **public             | PUBLIC   | USAGE, CREATE**

Now, run the following:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Re-run the first SQL, and we get the following result-set:

   nspname       |   name   |  privileges
--------------------+----------+---------------
 pg_catalog         | postgres | USAGE, CREATE
 pg_catalog         | PUBLIC   | USAGE
 information_schema | postgres | USAGE, CREATE
 information_schema | PUBLIC   | USAGE
 test               | postgres | USAGE, CREATE
 public             | postgres | USAGE, CREATE
 **public             | PUBLIC   | USAGE**

Compare the last lines from the 2 result-sets. You will see the missing CREATE privilege.

Don't forget to add the following back:

GRANT  CREATE ON SCHEMA public TO  PUBLIC;

To answer your other direct question : "Does that mean that the public role has all privileges granted (by default) ? "

'PUBLIC' is not an explicit role. This is what tripped me when I started Postgres.

Run the following command to confirm this - PUBLIC is absent from this result set:

select * from pg_roles;

Erwin Brandstetter has given a great explanation of PUBLIC role and another even more detailed explanation of PUBLIC role here .

My own notes - just paraphrasing the Postgres documentation on GRANT...

Any particular role will have the sum of following privileges:

privileges granted directly to it +
privileges granted to any role it is presently a member of +
privileges granted to PUBLIC.

This official Postgres post does a great job of explaining how to setup rights:
Managing Rights in Postgres


The following T-SQL will enable Database Mail in SQL Server Agent:

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail',
    N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile',
    N'REG_SZ', N'<profilename>'
GO

For future reference, you can easily determine this by expanding the "SQL Server Agent" node in SQL Server Management Studio's Object Explorer pane.  Then right-click on "SQL Server Agent", click "Properties", select "Alert System", enter the appropriate details, and click on "Script":


How to clear the InnoDB redo log before starting mysqld for crash recovery

What you should be doing, is

kill mysqld process. Disable the MySQL service (or whatever it is called), if it runs as service.
upgrade to latest 10.2 (it is already on 10.2.24 as of today, you are using a very old version)
Enable the service and start again.

If it does not start again, take the mysqld process minidump while it is hanging, and submit a bug to JIRA https://jira.mariadb.org/browse/MDEV

This is definitely more helpful than trying to "recover" by deleting Innodb redo logs (this is one of the things you should not be doing).


Stored procedure with recompile causing CPU spike

Without more detailed info it is difficult to be more specific regarding what should be done, but I think I could say the following:

An increase in CPU is to be expected when recompiling a frequently executed stored procedure upon each execution.
For the moment, if it were me, I would remove the WITH RECOMPILE because an occasional 100% CPU utilization that can be fixed with a recompile is preferable to a sustained 90% CPU utilization.
If the bad cached plan / parameter-sniffing issue only happens "once every few days", then that implies one or both of the following:

an infrequently used value that has a widely different amount of rows in the table/index than the more frequently used values
statistics getting out of date.

In which case:

Make sure you are updating statistics regularly, at least on the table(s) referenced in this stored procedure that are causing the CPU spike.
If you can figure out which value(s) and which statement(s) in the stored procedure cause the CPU spike, then it might be possible to place one or two statements in an IF block that make use of OPTION (RECOMPILE) (i.e. statement-level recompile, or at least handle in a slightly different way that is more appropriate for the infrequent, problem-causing value(s).

Something you could try is putting the following in a SQL Server Agent job that runs every few minutes. You just need to change the variable values above the dashed line, and then the database name references below that line, both marked by inline comments.

Please keep in mind that the CPU % calculated here is not the same that you would see in Task Manager, but it seems to be close enough (the value in Task Manager is a little higher).

DECLARE @MaxCPU INT = 75; -- only proceed if SQL Server CPU is at least this much

DECLARE @MaxCpuSeconds INT = 60,
        @MaxElapsedSeconds INT = 60,
        @ProcName [sysname] = N'{proc_name}',
        @KillSession BIT = 0;
---  ---  ---  ---  ---  ---  ---  ---  ---  ---  ---  ---  ---
DECLARE @DefaultCPU FLOAT,
        @DefaultCPUbase FLOAT;

SELECT @DefaultCPU = ISNULL(@DefaultCPU,
                            CASE cntr.[counter_name]
                               WHEN N'CPU usage %' THEN cntr.[cntr_value]
                               ELSE NULL END),
       @DefaultCPUbase = ISNULL(@DefaultCPUbase,
                                 CASE cntr.[counter_name]
                                   WHEN N'CPU usage % base' THEN cntr.[cntr_value]
                                   ELSE NULL END)
FROM   sys.dm_os_performance_counters cntr
WHERE  cntr.[object_name] = N'SQLServer:Workload Group Stats'
AND    cntr.[counter_name] LIKE N'CPU usage [%]%'
AND    cntr.[instance_name] = N'default';

SELECT @DefaultCPU, @DefaultCPUbase, 100 * (@DefaultCPU / @DefaultCPUbase) AS [Default];

IF ((100 * (@DefaultCPU / @DefaultCPUbase)) >= @MaxCPU)
BEGIN
  DECLARE @SessionID INT;

  SELECT @SessionID = req.[session_id]
  FROM   sys.dm_exec_requests req
  CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) txt
  WHERE  OBJECT_NAME(txt.[objectid], txt.[dbid]) = @ProcName
  AND    (req.[cpu_time] > (@MaxCpuSeconds * 1000)
     OR   req.[total_elapsed_time] > (@MaxElapsedSeconds * 1000))
  AND    txt.[dbid] = DB_ID(N'{db_name_for_proc}'); -- put your DB name here

  IF (@SessionID IS NOT NULL)
  BEGIN
    EXEC {db_name_for_proc}.sys.sp_recompile @ProcName; -- put your DB name here
    PRINT 'Recompiled ' + @ProcName;

    IF (@KillSession = 1)
    BEGIN
      DECLARE @SQL NVARCHAR(4000);
      SET @SQL = N'KILL ' + CONVERT(NVARCHAR(50), @SessionID);
      PRINT @SQL;
      EXEC (@SQL);
    END;
  END; -- IF (@SessionID IS NOT NULL)

END; -- IF ((100 * (@DefaultCPU / @DefaultCPUbase)) >= @MaxCPU)


I manually changed the identity fields with DBCC CHECKIDENT.  No automated way or configuration to fix it.


Is there an Oracle rdbms monitoring API?

All of the internal database information is obtained from the system data dictionary.  Static metadata can be found in the data dictionary views (e.g. DBA_DB_LINKS for database links) or their underlying tables (obj$ etc.).  Runtime stats can be obtained through the dynamic system ('v$') views (e.g. V$DBLINK).  You may need to look at the documentation for the views to find out how to get the metrics you want.  A bit of google-fu may also turn up code snippets showing how to use the views.

Anything external to the database will have to be monitored through its own logging or diagnostic facilities.  Again, I can't really answer specifics without knowing what processes you actually need to monitor.

Enterprise Manager has a plug-in API which you can use to add external data feeds.  This page has links to the documentation describing how to do this.


From here, the sql_severity column is the "severity of any SQL Server error."

When this column has the value 0, that means either no message was returned (and it's therefore a magic value), or a message with severity 0 was returned. I would argue this is a bad table design, as the column does not allow NULL.

It's almost the same thing with the sql_message_id column. This column will contain 0 when no message was returned -- in this case, though, it's purely a magic value, as there's no message id 0 (run SELECT * FROM sys.messages WHERE message_id = 0;).

You may want to clarify the intent of the report query you've inherited, because only comparing the sql_severity column may not do exactly what the author thought. (Severity 0 errors being raised are undoubtedly rare, but hey, you asked.)

If it's supposed to look for steps that returned a message, then it should check for sql_message_id > 0. As mentioned, it's completely possible that a job step can have a sql_severity > 0 and/or sql_message_id > 0 while still succeed. **
If the query is supposed to look for failed steps, it should be looking at the run_status column instead.

** You can try this yourself by creating and running a job with a T-SQL step that simply calls RAISERROR.


Operation System error 665 with VLDB backup (The requested operation could not be completed due to a file system limitation)

This error usually means that NTFS cannot add more fragments to the file being written to. Normally, this happens for CHECKDB sparse files but it could happen to any file. It is not sparse file specific.

I suspect your NTFS volume is already heavily fragmented. If there are many free space holes NTFS is prone to create many small fragments for new files. I have just seen a 16GB paging file with 120,000 fragments that was created on such a volume.

There is no way to tell NTFS to allocate more smartly. (Subjective note: NTFS is really stupid when it comes to allocating files. Many simple improvements come to mind but no investment is being made.)

Use a defrag tool that compacts the drive and removes holes. O&O Defrag can do that for example. Of course you can manually compact by recreating the partition. But I don't know how realistic that is at your storage sizes.

  I also saw some references to file fragmentation and I see that these drives show 0% percent.

But the free space could be fragmented. That's where new data goes.


Inserting in a table after crossing the allocated tablespace space

Oracle will throw an error and rollback the query.

  ORA-01653: unable to extend table by in tablespace


Deny Delete to An Active Directory Account in Sql Server

I solved my issue by executing "DENY DELETE TO [UserGroup\UserName]" statement one by one in every databases on the server. It will solve your issue if you have same situation like this.


How to loop through JSON array of JSON objects to see if it contains a value that I am looking for in postgres?

There is no way around unnesting the array for every row (the drawbacks of de-normalizing data). But you don't need a cross join, an EXISTS condition should work just fine.
select *
from demo d
where exists (select *
              from jsonb_array_elements(d.rawjson) as r(jdoc)
              where (jdoc ->> 'a')::int < 2
                and (jdoc ->> 'b')::int < 8);

If your columns is not a jsonb (which it should be), use json_each() instead
Online example


Typically indexes will be used by SQL Server if it deems it more expedient to use the index than to directly use the underlying table.

It would seem likely the cost-based optimizer thinks it would be more expensive to actually use the index in question.  You may see it use the index if instead of doing SELECT *, you simply SELECT T1Col1.

When you SELECT * you are telling SQL Server to return all columns in the table.  To return those columns SQL Server must read the pages for the rows that match the WHERE statement criteria from the table itself (clustered index or heap).  SQL Server is probably thinking the amount of reads required to get the rest of the columns from the table means it might as well scan the table directly.  It would be useful to see the actual query and the actual execution plan used by the query.


Storage efficiency of Postgres index column

My question is, does the presence of the index make Postgres realize it does not have to store the group_id column behind-the-scenes?

No. PostgreSQL uses heaps, not index-organised tables.


Minimum permissions required for mail attachment in DbMail

As seen in the TechNet article on sp_send_dbmail:

  Execute permissions for sp_send_dbmail default to all members of the
  DatabaseMailUser database role in the msdb database. However, when the
  user sending the message does not have permission to use the profile
  for the request, sp_send_dbmail returns an error and does not send the
  message.

As for attachments, the SQL Server service account (the actual Windows/AD account that the SQL Server process runs on) is also going to need read access to the actual folder where the file is stored.


SQL server does not trace revoke or deletes by default. In order to trace those activities, you need to run a server side trace (or XEvents as Aaron pointed out).

Below trace will help you to find command (Text), start date, end date, login, database (I have filtered it for test_kin), and many other fields.

Adjust the filters as per your need and environment. Also, specify how big the trace file would be through @maxfilesize.

/****************************************************/
/* Created by: SQL Server 2014 Profiler          */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 11, 2, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 3, @on
exec sp_trace_setevent @TraceID, 41, 4, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 7, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 26, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 41, @on
exec sp_trace_setevent @TraceID, 41, 48, @on
exec sp_trace_setevent @TraceID, 41, 50, @on
exec sp_trace_setevent @TraceID, 41, 64, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 9, @on
exec sp_trace_setevent @TraceID, 40, 3, @on
exec sp_trace_setevent @TraceID, 40, 4, @on
exec sp_trace_setevent @TraceID, 40, 5, @on
exec sp_trace_setevent @TraceID, 40, 6, @on
exec sp_trace_setevent @TraceID, 40, 7, @on
exec sp_trace_setevent @TraceID, 40, 8, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 26, @on
exec sp_trace_setevent @TraceID, 40, 29, @on
exec sp_trace_setevent @TraceID, 40, 30, @on
exec sp_trace_setevent @TraceID, 40, 35, @on
exec sp_trace_setevent @TraceID, 40, 41, @on
exec sp_trace_setevent @TraceID, 40, 49, @on
exec sp_trace_setevent @TraceID, 40, 50, @on
exec sp_trace_setevent @TraceID, 40, 51, @on
exec sp_trace_setevent @TraceID, 40, 55, @on
exec sp_trace_setevent @TraceID, 40, 60, @on
exec sp_trace_setevent @TraceID, 40, 61, @on
exec sp_trace_setevent @TraceID, 40, 64, @on
exec sp_trace_setevent @TraceID, 40, 66, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'test_kin'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Below is the screen shot :


Decompostion with loss of information shown by junction

The trick to solve this exercise it to repeat as much as possible the elements of the table. Since A is the key, you cannot repeat A, but you can, for instance, repeat two times B and C and three times D. Here is a very simple and short example (note that the functional dependencies are respected):

r =
+---+---+---+---+
| A | B | C | D |
+---+---+---+---+
|a1 |b1 |c1 |d1 |
|a2 |b1 |c1 |d1 |
|a3 |b2 |c2 |d1 |
+---+---+---+---+

And since this is an exercise, I will leave to you the task of projecting r over R1, R2 and R3, and discovering how the natural join of the three relations differs from the original relation r.

Finally a terminological note: a relation instance is not an array! There is no order in the rows or the columns of a relation, while in an array the order is essential.


Oracle 11g - Database Job About User Lock

create or replace procedure p1 as
begin
  for u in (select username from dba_users where account_status in ('EXPIRED', 'EXPIRED(GRACE)') and username not in ('SYS', 'SYSTEM', '...'))
  loop
    execute immediate 'alter user "' || u.username || '" account lock';
  end loop;
end;
/

For 12c and higher you could use the ORACLE_MAINTAINED='N' for filtering the built-in users instead of the IN-list.


Checking IFI enabled on SQL server below 2016

According to this MSSQLTips.com article, you can use sys.dm_server_services beginning with SQL 2012 SP4.

SELECT  @@SERVERNAME AS [Server Name] ,
        RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
        LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
        + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
        service_account ,
        instant_file_initialization_enabled
FROM    sys.dm_server_services
WHERE   servicename LIKE 'SQL Server (%'

For older versions, you'll need to scrape the log, looking for "Database Instant File Initialization: enabled."


Query returns repeating arrows when getting sum of a column grouped by some others columns

Three rows are returned because Inventory has three rows. Inner joining the aggregated table won't filter out the three rows. To do that, you can use DISTINCT or simply group by description as well. See this fiddle.

Distinct

SELECT DISTINCT T1.gid
      ,T1.description
      ,T1.version
      ,T1.warehouse
      ,T2.quantity
FROM Inventory T1
INNER JOIN (
    SELECT gid, version, warehouse, SUM(quantity) AS quantity
    FROM Inventory
    GROUP BY gid, version, warehouse
) T2
ON T1.gid = T2.gid

Group By Description

SELECT gid, version, warehouse, description, SUM(quantity) as total
FROM Inventory
GROUP BY gid, version, warehouse, description;


Does Oracle optimize database while you use?

I would guess that you have indexes that are unusable. Check and see if you have unusable indexes. If you do, then you need to rebuild them.

SELECT owner, status, COUNT(*) FROM dba_indexes GROUP BY owner, status ORDER BY 1,2;


In mysql, how to add a primary key and a composite key to a table so that it forbids duplicate rows, when there are null values within the columns?

Possible solution: add into the table structure one more column which is generated and concatenates the whole row, then define this column unique.
Of course if you'll realize this directly then this index will be larger than the table body itself, and it will decrease processing speed dramatically. To avoid this select some hash function, which provides the collision probability small enough, and apply it to concatenated row content. For NULLable columns add a byte which depends of the column value is null, and replace the column's NULL with some constant value.
For example, for the table
CREATE TABLE test ( i_column INT,
                    t_column DATETIME,
                    s_column TEXT );

this may be
ALTER TABLE test
ADD COLUMN check_uniqueness BINARY(128)
           GENERATED ALWAYS AS (SHA2(
                                     CONCAT(
                                            i_column IS NULL,
                                            COALESCE(i_column, 0),
                                            t_column IS NULL,
                                            COALESCE(t_column, '2000-01-01'),
                                            s_column IS NULL,
                                            COALESCE(s_column, '')
                                           ),
                                     512
                                    )
                               ) VIRTUAL,
ADD UNIQUE INDEX checking_uniqueness (check_uniqueness);

DEMO fiddle


Using sp_send_dbmail with SQL authentication to send attachments from UNC path

I managed to get this working eventually in my Dev environment.

Enable xp_cmdshell
Add proxy account DomainLogin
grant execute on xp_cmdshell to SQLLogin
Grant logon as a batch job on Server
On server open Secpol. > Security Settings > Local Policies – User Rights Assignment node. Add DomainLogin
Create SQL Credential "CredForMail" using DomainLogin
Grant Credential to : ALTER LOGIN SQLLogin ADD CREDENTIAL CredForMail
ALTER DATABASE DatabaseName SET TRUSTWORTHY ON

The end result is that I have a non-sysadmin user that can send emails with attachments it has picked up from a share on a remote server.

I think the proxy account and Credential may be overlapping and both may not be necesary. I have tried so many things I am no-longer sure of the bare minimum required to make this work. (and that bothers me)

Because of the complexity to set this up, enabling features that could be seen as a security risk, I am going to back all of this out, and instead will separate out this application function and give it it's own domain account.


SQL Server 2016 full backup and log shipping and newbie questions

once the restore on the secondary has been done, can the trn files be
  deleted?

Yes you can but you want to consider keeping files for certain period of time depending on your recovery point objective.

  if I wanted to combine the transaction log shipping with a
  full/differential backup plan

Full and differential backup will not break your log chain.

Make sure you do not take any log backup outside log shipping. All log backups must be done through the log shipping. Because that will break your log chain and hence log shipping will fail.


Oracle 11gR2 can I delete datafiles to reduce tablespace?

You can use DROP DATAFILE from ALTER TABLESPACE to drop a datafile which is empty (has no extents allocated from it).

Example:

ALTER TABLESPACE <tablespace_name> DROP DATAFILE '/path/datafile_name';

However if the datafile is not empty, you need to run something like a tablespace map to see which object extents are allocated from it.  You can use this script, but there are other searchable scripts which give pretty outputs.

SELECT
  'free space' owner, null OBJECT, null segment_type, file_id, block_id, blocks
FROM dba_free_space
WHERE tablespace_name = UPPER ('&Tblspc')
UNION
SELECT
  owner, segment_name, segment_type, file_id, block_id, blocks
FROM dba_extents
WHERE tablespace_name = UPPER ('&Tblspc')
ORDER BY 4, 5;

Once you find the objects in the datafile you want to drop, you can reorganize them into the same tablespace (which might put them into free space in other datafiles) or other tablespaces.

Here are some example commands for moving tables and indexes.

ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name>;
ALTER INDEX <index_name> REBUILD TABLESPACE <tablespace_name>;

With those examples don't forget to add your storage and statistics clauses as needed.

You could also try resizing datafiles but this will only work up to an allocated extent.  This means that if there is an extent half way through the datafile, you would only be able to resize it to 50%.  You can use the tablespace map to check these also, or do it by trial and error.

ALTER DATABASE DATAFILE '/path/datafile_name' RESIZE <new_size>;


Mysql Server 5.6 Mysqld high memory consumption

Which version are you using? 5.6.?

I did however came across this bug, hopefully it helps.

http://bugs.mysql.com/bug.php?id=68287


Repairing a table that is not crashed shouldn't break anything in the ideal case. Unfortunately, the real world is not an ideal case; a power outage or a hardware failure during the repair can leave the table in an inconsistent state. That's why backing up the table before repairing it is strongly recommended.

Also having the database offline during the maintenance operations helps fixing it faster.

Please take a look at the documentation of REPAIR TABLE for more information.


Updating a table which contains an Image datatype column

The table in question is a heap. This storage structure does not release pages made empty by delete operations, unless the delete occurs while an exclusive table lock is taken (and not always then).

From DELETE (Transact-SQL) in the documentation:

  To delete rows in a heap and deallocate pages, use one of the following methods.
  Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. This allows the pages to be deallocated.

The upshot is that your table likely contains a large number of empty pages allocated for rows the heap held in the past. You can check this using:

SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.index_type_desc,
    DDIPS.alloc_unit_type_desc,
    DDIPS.index_depth,
    DDIPS.index_level,
    DDIPS.page_count,
    DDIPS.record_count,
    DDIPS.ghost_record_count,
    DDIPS.version_ghost_record_count,
    DDIPS.forwarded_record_count
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.Heap', 'U'), -- Your table name here
        0,
        NULL,
        'DETAILED'
    ) AS DDIPS;

Without any indexes, the only way for the SQL Server to process the update query is to employ a full table scan. This will touch every data page allocated to the table, including the empty ones. If there are many pages that must be brought into memory from permanent storage, this can take a long time.

The solution is to rebuild the heap table using ALTER TABLE <table_name> REBUILD, or create a clustered index.

The image data type has been deprecated in favour of varbinary(max) for a very long time now. If you have any freedom to change this, you probably should. Even so, it is also possible that the existence of this LOB column is causing the slowness through a quite separate mechanism I describe in detail here on SQLblog.com:

Deletes that Split Pages and Forwarded Ghosts

From that article:

  Summary

  If you ever wonder why your deletes are so slow, it is worth checking to see if you are suffering from page splitting due to an enabled trigger and a table definition that allows for LOB allocations or ROW_OVERFLOW.

  Any table with a LOB column (including the max data types) qualifies, as does one with even a surprisingly small number of variable-length columns, as shown in the examples in this post.

  This is a great reason to avoid using 'max' or old-style LOB data types unnecessarily, and to be careful about the maximum length of 'normal' variable-length data types too.

  Remember, it is the potential maximum row size that is important, not the actual row size.


Installing SQL Server, I get unmet dependencies libc++1 and libjemalloc1 but it is not installable

This is because both of those packages are in the Ubuntu Universe, to enable that repository you can use,

sudo add-apt-repository "deb http://archive.ubuntu.com/ubuntu $(lsb_release -sc) universe"

Note that the Universe Packages are not Officially Supported by Canonical and the Ubuntu Team, but instead by the community. For more information see the Security FAQ


You caused an endless loop by making the trigger BEFORE INSERT OR UPDATE . Use instead:

CREATE FUNCTION update_if_exists()
  RETURNS trigger AS
$keyword$
BEGIN

-- Check whether the keyword alrady exists.
UPDATE keywords
SET    views  = views + NEW.views
      ,visits = visits + NEW.visits
WHERE  keyword = NEW.keyword;

IF FOUND THEN
   RETURN NULL;
ELSE
   RETURN NEW;
END IF;

END
$keyword$ LANGUAGE plpgsql;

CREATE TRIGGER id BEFORE INSERT ON keywords     -- not OR UPDATE!
FOR EACH ROW EXECUTE PROCEDURE update_if_exists();


Deleting all rows from INNER JOINed 3 different tables

If you have more than one join you could use comma separated USING statements:

DELETE
FROM
      AAA AS a
USING
      BBB AS b,
      CCC AS c
WHERE
      a.id = b.id
  AND a.id = c.id
  AND a.uid = 12345
  AND c.gid = 's434sd4'

Reference


Oracle - how to find what caused CPU to spike 3 weeks ago

If you have your AWR settings were set up to retention other than default 8 days, for example 30 days, then answer is yes. Or if you have backup of database of that time.

Check settings by script:

SQL> @?/rdbms/admin/awrinfo.sql

At the start of output check for row:
"Snapshot interval is 60 minutes and Retention is 30 days"

If AWR retention is enough and if you have license on Oracle Diagnostic Pack then you can run ashrpt script to see the approximate load on this interval.

SQL> @?/rdbms/admin/ashrpt.sql


Remove 5th position character From column using MYSQL Query

You can use string functions like LEFT and RIGHT, and CONCAT the final result.

create table tbl(`Order` varchar(20));
insert into tbl values
('PRSW000000001'),
('PRSW000000002'),
('PRSW000000003'),
('PRSW000000004');

update tbl
set `Order` = concat(left(`Order`, 4), right(`Order`, length(`Order`) - 5));

select * from tbl;

| Order        |
| :----------- |
| PRSW00000001 |
| PRSW00000002 |
| PRSW00000003 |
| PRSW00000004 |

dbfiddle here


Migrating SQL Server tables data from one application to another totally different application tables

For a one-off migration that's a good way. For continuing movement of data from a source to destination system e.g. OLTP system to a data warehouse, a proper ETL tool (SSIS/ BIML) would be better.


Assuming SQL Server

getdate gives local server time
getutcdate gives utc / gmt

There is no timezone setting or changes possible inside SQL Server


Troubleshooting memory pressure - how to avoid paging out SQL Server?

a significant part of sql server process memory has been paged out

Let me explain you what this message means. This means SQL Server process was paged to disk because the OS or applications running on OS requested memory and OS could not find continuous chunk of it so paged SQL Server to disk, temporarily, and provided the physical memory to the process requesting.

If you read Karthick PK's blog there are couple of situations where SQL Server Process working set might be paged out by Windows

If windows is not sending the correct notifications to all listening processes at the right moment and thresholds.
If SQL Server is not responding fast enough to the low memory resource notification from Windows.
When low physical memory notification is received by SQL Server it will scale down its memory usage by trimming internal caches. This effect is applied for 5 seconds and then paused for 1 minute. This is to avoid any hungry/faulty application to consume all the memory making SQL Server to  scale its usage continuously. If low memory conditions still exist after 1 minute pause, the effect is applied again. So if there is physical memory pressure even after SQL Server scaling its usage for 5 seconds windows will still page out SQL Server’s working set.
Conditions in Windows where working sets of all processes are trimmed.
Windows might decide to trim a certain percentage of working set of various or specific processes

Why did SQL Server allowed OS to take away its memory, well this is how SQL Server is designed.

  should I raise the Minimum server memory to 1740 too?

I don't know from where you got this idea, min server memory has no role here don't touch it. Its OS which is facing memory pressure here NOT the SQL Server.

  We received an alert at 12:15:37 PM on the wrsleic1 default instance that "a significant part of sql server process memory has been paged out."

As I can read your question you got this message previous year and one occurrence now. Keeping its mind in occurrence I would suggest its not that grave issue something running on OS sometimes requests more memory and SQL Server becomes victim.

Workaround

The workaround would be here to provide SQL Server service account Locked pages in memory privilege. This will avoid paging out of buffer pool but non buffer pool can still be paged out.

Solution.

Find out what is that which is running on system and requesting more memory. Perfmon counters Process: Private bytes and Working set can come handy here.

Or download RAMMAP tool, that is there is Karthick's blog, and see memory consumption.

What I can feel is there are other services running on SQL Server machine and sometimes there occurs situation where memory requirement increases and thus forcing OS to take hard step of paging. I suggest you also add more RAM on windows machine


Why is SQL Server 2012 working set memory low?

SQL Server 2012 has vastly changed the way it controls and manages memory. Before 2012, max server memory controlled only 8K pages, and now it controls much more than that.

Here are some good articles about this from the SQLOS team:

Article 1 | Article 2 | Article 3 | Article 4

With these changes, what you see in working set, max server memory, etc. will almost certainly be different between versions. The key is to focus on what "normal" is for these figures and see if they deviate vastly when there is a specific performance issue. I would not just look at a number when the system is working perfectly fine and panic and assume something is wrong (this happens a lot with magic numbers like 300 for page life expectancy - maybe PLE on your system is always 120).

A few queries that might help ballpark and set some baselines:

SELECT TOP (10) page_type,
  numpages = COUNT(*),
  size_kb = 8*COUNT(*)
FROM sys.dm_os_buffer_descriptors
WHERE database_id < 32767
GROUP BY page_type
ORDER BY size_kb DESC;

SELECT * FROM sys.dm_os_process_memory;

SELECT TOP (10) [type],
  size_kb = SUM(pages_kb)
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY size_kb DESC;

SELECT * FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Broker%'
   OR object_name LIKE '%Buffer Manager%'
   OR object_name LIKE '%Memory Manager%'
   OR object_name LIKE '%Memory Node%';


Oracle 12c RAC Cluster / CRS fails to start after reboot

ASM manual startup:

[oracle@rac1 ~]$ cd /u01/app/11.2.0/grid/bin
[oracle@rac1 bin]$ ./crsctl check crs
CRS-4639: Could not contact Oracle High Availability Services
[oracle@rac1 bin]$
[oracle@rac1 bin]$ su - root
Password:
[root@rac1 ~]# cd /u01/app/11.2.0/grid/bin
[root@rac1 bin]# ./crsctl start crs
CRS-4124: Oracle High Availability Services startup failed
CRS-4000: Command Start failed, or completed with errors
[root@rac1 bin]#

[root@rac1 bin]# ./crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@rac1 bin]#

[root@rac1 bin]# nohup /etc/init.d/init.ohasd run &
press Enter:
[root@rac1 bin]# ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager

[root@rac1 bin]# cd /dev/oracleasm/disks
[root@rac1 disks]# ls -lrt
total 0
brw------- 1 root root 8, 17 May  6 10:16 DISK1
brw------- 1 root root 8, 33 May  6 10:16 DISK2
brw------- 1 root root 8, 49 May  6 10:16 DISK3
brw------- 1 root root 8, 65 May  6 10:16 DISK4
brw------- 1 root root 8, 81 May  6 10:16 DISK5

[root@rac1 bin]# ps -ef  | grep css
root      3784     1  0 10:17 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdmonitor
root      3801     1  0 10:17 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdagent
root      4189  4107  0 10:26 pts/1    00:00:00 grep css

[root@rac1 disks]# chown -R oracle:dba /dev/oracleasm/disks
[root@rac1 disks]# chmod -R 777 /dev/oracleasm/disks
[root@rac1 disks]# ls -lrt
total 0
brwxrwxrwx 1 oracle dba 8, 17 May  6 10:16 DISK1
brwxrwxrwx 1 oracle dba 8, 33 May  6 10:16 DISK2
brwxrwxrwx 1 oracle dba 8, 49 May  6 10:16 DISK3
brwxrwxrwx 1 oracle dba 8, 65 May  6 10:16 DISK4
brwxrwxrwx 1 oracle dba 8, 81 May  6 10:16 DISK5

[root@rac1 bin]# ./crsctl start cluster
CRS-2672: Attempting to start 'ora.cssd' on 'rac1'
CRS-2676: Start of 'ora.cssd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'rac1'
CRS-2676: Start of 'ora.ctssd' on 'rac1' succeeded
CRS-2679: Attempting to clean 'ora.asm' on 'rac1'
CRS-2681: Clean of 'ora.asm' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'rac1'
CRS-2676: Start of 'ora.asm' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'rac1'
CRS-2676: Start of 'ora.crsd' on 'rac1' succeeded

[root@rac1 bin]# ps -ef | grep css
root      3784     1  0 10:17 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdmonitor
root      4372     1  0 10:30 ?        00:00:01 /u01/app/grid/11.2.0/bin/cssdagent
oracle    4387     1  1 10:30 ?        00:00:02 /u01/app/grid/11.2.0/bin/ocssd.bin
root      5347  4107  0 10:33 pts/1    00:00:00 grep css

[root@rac1 bin]# ./crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@rac1 bin]#

Su – oracle

Srvctl start database –d dbname

Create diskgroup FRA external redundancy ‘ORCL:VOL5’;(in ASM (sqlplus / as sysasm))

Asmcmd lsdg
Alter diskgroup FRA mount; (if not in mount).

http://mbc-dba.blogspot.com/2014/08/crs-4639-crs-4124-oracle-high.html
https://shivanandarao-oracle.com/2012/06/03/crs-4535-cannot-communicate-with-cluster-ready-services/


What is the possibility that mirroring a database will damage, corrupt, or erase either the primary or target database?

I appreciate that you are looking for anecdotes about the experience of using mirroring; however this site is not really the place for opinion.
Having said that, you may find the following excerpt from Microsoft.com regarding database mirroring helpful:
Database mirroring is a simple strategy that offers the following benefits:

Increases availability of a database.

In the event of a disaster, in high-safety mode with automatic failover, failover quickly brings the standby copy of the database online (without data loss). In the other operating modes, the database administrator has the alternative of forcing service (with possible data loss) to the standby copy of the database.

Increases data protection.

Database mirroring provides complete or almost complete redundancy of the data, depending on whether the operating mode is high-safety or high-performance.
A database mirroring partner running on SQL Server 2008 Enterprise or later versions automatically tries to resolve certain types of errors that prevent reading a data page. The partner that is unable to read a page requests a fresh copy from the other partner. If this request succeeds, the unreadable page is replaced by the copy, which usually resolves the error. For more information, see Automatic Page Repair (Availability Groups/Database Mirroring).

Improves the availability of the production database during upgrades.

To minimize downtime for a mirrored database, you can sequentially upgrade the instances of SQL Server that are hosting the failover partners. This will incur the downtime of only a single failover. This form of upgrade is known as a rolling upgrade. For more information, see Install a Service Pack on a System with Minimal Downtime for Mirrored Databases.

Paul Randal's sqlskills.com blog offers an excellent set of articles about mirroring at http://www.sqlskills.com/blogs/paul/category/database-mirroring/
Glenn Berry offers up some pertinent tips for running mirroring at http://sqlserverperformance.wordpress.com/2012/03/12/sql-server-database-mirroring-tips-and-tricks-part-1/


Any disadvantage to automatically changing FULL backup on AlwaysON Secondary to COPY_ONLY?

Besides being unaware that your backup is being performed as a COPY_ONLY backup instead of not being backed up at all, are there any caveats that anyone can think of if this switch is set to default Y or even if it's used at all?

A COPY_ONLY full backup is still a full backup. The only difference between the two is the COPY_ONLY will not reset certain tracking bitmaps. This means your differential backups will eventually be the size of your database.

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server


SQL Server: Help in writing a query to get 3 rolling months average for a table

select month
     , year
     , vol
   --, sum(vol) over (partition by costx, name order by month,year ROWS BETWEEN 2 PRECEDING AND current row) sumnetvol,
     , avg(vol) over (partition by costx, name order by month,year ROWS BETWEEN 2 PRECEDING AND current row) '3monthra'
     , name
     , cnt
     , costx
from (
SELECT month(convert(datetime, cast(monthnumber as nvarchar(10))+'01')) as [Month]
     , year(convert(datetime,cast(monthnumber as nvarchar(10))+'01')) as [Year]
     , sum(SumOfNetAmount) as vol
     , count(*) as cnt
     , costx
     , name
FROM [ngl_OTB]
GROUP BY costx
       , MONTH(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
       , YEAR(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
       , name
       , SumOfNetAmount)s
where costx = '1000.002' and name = 'ngl-raw' order by month,year


Decrypting a Master Key works well using SQLCMD but not in the script

As @JohnEisbrener make me noticed in the comment, the issue is due to the fact that the script is missing of the
USE [mydatabase] statement.

So the right way to write the stored procedure variable is

SET @exec_sql = 'USE MYDATABASE; OPEN MASTER KEY DECRYPTION BY PASSWORD = N' + quotename(@dbMKey,'''') + '; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; CLOSE MASTER KEY;';


How to link data from a table to another table in sqlite database?

Here is an idea that seems to work. Generally speaking though, you can do this like this:

Table1
------
table_key
data_type1
data_type2

Table2
------
table_key
table_key_in_table1 (linked to table1)
data_type1
data_type2
...

And so on.


Why won't SQL Server optimize the UNIONs?

The query optimizer does have n-ary operators, though the execution engine has rather fewer.  To illustrate, I'm going to use a simplified version of your tables - (SQL Fiddle).

SELECT DISTINCT
    number
INTO foo
FROM master..spt_values
WHERE
    number < 1000;

SELECT DISTINCT
    number
INTO boo
FROM master..spt_values
WHERE
    number between 300 and 1005;

SELECT DISTINCT
    number
INTO koo
FROM master..spt_values
WHERE
    number > 1006;

ALTER TABLE dbo.foo ADD PRIMARY KEY (number);
ALTER TABLE dbo.boo ADD PRIMARY KEY (number);
ALTER TABLE dbo.koo ADD PRIMARY KEY (number);

Given those tables and data, let's look at the input tree for a three-way UNION query:

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_Union
    OUTPUT(COL: Union1006 )
    CHILD(QCOL: [f].number)
    CHILD(QCOL: [b].number)
    CHILD(QCOL: [k].number)
    LogOp_Project
        LogOp_Get TBL: dbo.foo(alias TBL: f)
        AncOp_PrjList
    LogOp_Project
        LogOp_Get TBL: dbo.boo(alias TBL: b)
        AncOp_PrjList
    LogOp_Project
        LogOp_Get TBL: dbo.koo(alias TBL: k)
        AncOp_PrjList

The logical union operator has one output and three child inputs.  After cost-based optimization, the physical tree chosen is a merge union with three inputs:

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607);

PhyOp_MergeUnion
    PhyOp_Range TBL: dbo.foo(alias TBL: f)(1) ASC
    PhyOp_Range TBL: dbo.boo(alias TBL: b)(1) ASC
    PhyOp_Range TBL: dbo.koo(alias TBL: k)(1) ASC

The optimizer's output is reworked into a form that the execution engine (without n-ary merge union) can handle:

The post-optimization rewrite unfolds the n-ary PhyOp_MergeUnion into multiple Merge Union operators.  Notice how all the estimated cost remains associated with the 'original' union operator - the others have a zero cost estimate.

That the optimizer reasons about unions using n-ary operators provides one explanation for why it does not consider rewriting your first example to the same plan as the second example (the three-way union is a single tree node).

The second reason is there are no constraints to enforce the 'lack of overlap'.  Before constraints are in place, a union between boo and koo cannot be guaranteed not to produce duplicates, so we get a duplicate-removing plan (a Merge Union in this case):

SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k;

Adding the following constraints ensures the non-overlap condition cannot be violated without invalidating cached plans for the query:

ALTER TABLE dbo.foo WITH CHECK ADD CHECK (number < 1000);
ALTER TABLE dbo.boo WITH CHECK ADD CHECK (number BETWEEN 300 AND 1005);
ALTER TABLE dbo.koo WITH CHECK ADD CHECK (number > 1006);

Now it is safe for the optimizer to simply concatenate:

However, even with those constraints in place, the three-way union query still appears as three unions because the optimizer does not normally consider splitting n-ary operators to explore alternatives.  The n-ary operator thing is very useful in keeping the search space under control; breaking it apart would often be counter-productive given the optimizer's goal of finding a good plan quickly.

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k;

When written as a UNION and UNION ALL, an n-ary operator can no longer be used (the types do not match) so the tree has separate nodes:

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION ALL
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_UnionAll
    OUTPUT(COL: Union1007 )
    CHILD(COL: Union1004 )
    CHILD(QCOL: [k].number)

    LogOp_Union
        OUTPUT(COL: Union1004 )
        CHILD(QCOL: [f].number)
        CHILD(QCOL: [b].number)

        LogOp_Project
            LogOp_Get TBL: dbo.foo(alias TBL: f)
            AncOp_PrjList

        LogOp_Project
            LogOp_Get TBL: dbo.boo(alias TBL: b)
            AncOp_PrjList

    LogOp_Project
        LogOp_Get TBL: dbo.koo(alias TBL: k)
        AncOp_PrjList


log_slow_verbosity is for MariaDB, not MySQL

Perhaps your OS has MariaDB repos installed instead of MySQL. If you downloaded MySQL and installed it, mysqld encountered an already existing my.cnf that was for MariaDB.


Working out validity of table relationship

Though a one-to-many relationship exists between the zoo and the keepers, it's not a terribly useful relationship to model, since every single keeper works at a single zoo. This is probably why you are having an issue. Your Zoo table is not really set up to relate to anything unless you have more than one zoo. You could add a Zoo_ID field to both the Zoo and Keepers table. But, with one zoo, what's the point?

For more than one zoo, a one-to-many relationship must be explicitly laid out using keys in order for you to track which zoo a keeper works for. Each zoo gets a unique Zoo_ID (primary key), and each keeper has a Zoo_ID field that matches up with one of the Zoo_Ids (foreign key).

It gets trickier if your keepers can work at multiple zoos. Now you have a many-to-many relationship. You could have multiple rows in your keeper table, one per zoo, but that's kind of tacky and duplicates information. The proper way to represent the relationship is a junction (join) table with the id of the keeper and the id of the zoo. If you imagine a diagram with each zoo and keeper drawn out, and lines connecting the keepers with the zoos, each row in your junction table is like one of those lines, with the zoo's id on one end, and the keeper's id on the other.

tl;dr: Yes, it's a valid relationship. No, you shouldn't bother modeling it, unless you plan on multiple zoos in the future.


Checking whether my pgAdmin III is a 32 or 64 bits version

Your image path may give that away:

get-wmiobject win32_process | where {$_.Name -like "pgadmin3.exe"} | select -property Name,ExecutablePath | fl


Why are multivalued dependencies named that way?

A multivalued dependency A->->B means that each value of A determines a set of values of B (and not a single value of B as in functional dependencies).

For instance, suppose that programmers have an attribute programmer-id and an attribute known-language, and each programmer can know several languages, you have the multivalued dependency:

programmer-id ->-> know-language

So, supposing that the programmer with program-id 7 knows SQL and Ruby, this means that in a table in which you have both attributes programmer-id and know-language, every time there is program-id 7 there must be two different rows, one with language 'SQL' and one with language Ruby, and all the other attributes equal:

programmer-id  know-language other-attribute1 other-attribute2 ...
    7              SQL           XXX               YYY
    7             Ruby           XXX               YYY
    8             Java           AAA               BBB
 ...


If you're really using LocalDB and haven't just named a regular SQL Server instance as if you were, there are definitely some extra hurdles in establishing connectivity from remote machines. This feature is targeted primarily at local, isolated development.

I wrote a pretty lengthy article on LocalDB, including how to get over some of the connectivity hurdles, in this tip (and there is likely some valuable information in some of the comments, too):

Getting Started with SQL Server 2012 Express LocalDB

More recently, I added a tip to handle a more modern version, which I highly recommend if for no other reason that most of the connectivity hurdles have been reduced or eliminated:

Getting Started with SQL Server 2017 Express LocalDB

I would strongly recommend installing a proper instance of SQL Server (even if only the Express Edition), which can be set to always be running (no matter who is or isn't logged into the machine), does not have any of these additional connectivity hurdles, etc.


How to move a 80GB MySQL Database with least effort and least time offline?

The main problem with MyISAM is that you cannot create a copy of the database without locking the tables (otherwise the copy may be inconsistent). I would recommend you to research a potential migration to InnoDB.

You will have to execute FLUSH TABLES WITH READ LOCK in order to lock all tables in read only mode, then copy all the .MYD, .MYI and .frm files from the filesystem (basically, your entire datadir). Once you copy those files, you will want to get your binary log coordinates so you can setup replication with SHOW MASTER STATUS. If you cannot setup replication, you will have to leave the server in  read_only mode while you setup the other one. Otherwise, UNLOCK TABLES;. In previous versions of MySQL, you had the mysqlhotcopy script that automatized some of those tasks for MyISAM.

EDIT: As you have some InnoDB tables, you must be sure to copy the whole data directory (including ib_logfile* and ib_data*ones), and not only single files- otherwise the copy won't succeed.

Setup the new server copying the files to the right place, start the new server, and you can now setup a replication between the old server and the new one. Once you double check that the setup is correct and the key_buffer/buffer pool is warm, you will put the old server in read_only mode and failover your applications to the new one.

The idea is to avoid mysqldump, which is slower for exporting, but above all, for importing, although it is easier to use. That it is what it is internally executed with phpMyAdmin's "export/import database" feature.

I know this is a high level overview, but I recommend you to start reasearching the links I mentioned and about MySQL replication and asking additional specific questions if needed.


Resource Governor on 2014 - CPU Not Being Throttled

I actually ended up engaging Microsoft on this. The explanation that the engineer gave is that RG doesn't do a good job at throttling an OLTP vs. OLAP type workloads on the same instance. Our use case was a server with 1000's of tiny queries taking 1-2 ms, with occasional queries taking 6-7 seconds, with the longer queries being the ones we wanted to throttle. He acknowledged this was a bug and they would fix in a future release. Apparently, RG is getting rewritten from scratch in SQL 2016 to resolve these particular issues.


What is the best way to recover from a mysql replication fail?

We can start replication from the position from where it stopped. You should check the Mysql  log on slave servers log must have the last mysql bin log postion where the replication stopped.

 STOP SLAVE;

 RESET Slave;

 FLUSH TABLES WITH READ LOCK;

 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

 Where the values of the above fields are the ones where replication stopped.

Finally type

 START SLAVE;


ON DELETE CASCADE doesn't work even storage table is InnoDb

The CASCADE clause works in the opposite direction. From the parent to the child table.

When you delete a row in the parent table, the related rows in the child table are deleted as well.


Recommended SQL Server transaction logs settings for ephemeral databases?

Simple recovery model doesn't mean that the transaction log isn't used.
It just means that SQL Server can empty the log (aka "truncate" the log), instead of having to do it yourself scheduling regular transaction log backups. I.e., the log need space for the largest/earliest open transaction, since the log cannot be truncated past the oldest transaction - regardless recovery model.
Simple does mean, however, that some operations can be performed in minimally logged form. Such include create/alter/drop index, SELECT INTO, bulk loading of data and if the stars are properly aligned also INSERT ... SELECT.
So, if simple doesn't cut it for you regarding space usage, then you'd have to look into how the operations are done - i.e., educate the developers about minimal logging as I mention above.
As for speed, minimally logging will help there as well, since you have less I/O. Larger transactions (within some reason) means less synchronous I/O to the LDF file. From a pure config aspect, I guess some RAM disk option for the ldf file could help - but that might not be doable if you need large ldf files.


In MySQL :  show global variables like '%slow%';

Update :
here's a link to the manual:
link text


Differences between development and live database setups

I'd say that not having in development/test environments one that matches production will be a bad bet.

I worked for a shop where we didn't have an environment that matched production, so we couldn't do many of the tests as we should. I'm not saying that each developer/tester should have an env identical to production, but at least one environment for all devs/testers. And for usual development any environment will do. Testing performance of the code should be mandatory, if not for all pieces of code, than at least for the most used ones (can use profiling tools to find out).


Postgres full text search with multiple columns, why concat in index and not at runtime?

No you don't need separate indexes. Use the weights feature. They are just a label your can query against. You can have up to four labels to query against (A-D).

--search any "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick'::tsquery; --true

--search B "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:B'::tsquery; --false

--search B or C "fields" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:BC'::tsquery; --true

You might want to concatenate tsvectors, so that you can separately apply weights to them and then put them together:

select
  setweight( name_column::tsvector, 'A') || setweight( phone_column::tsvector, 'B');


Having Updatable Views that obey initial select conditions

maybe it works, it is in SQL server but concept maybe the same in your case:
If its always required you can set default. It will update always when you insert the view [is] with 'Study':
ALTER TABLE t ADD CONSTRAINT  df_is DEFAULT 'Study' FOR [is];

Or you can create another view only for insert:
CREATE VIEW v_insert AS SELECT name,[is] FROM t WHERE [is] = 'Study';
DECLARE @is NVARCHAR(max)='Study';
INSERT INTO dbo.v_insert (name, [is]) VALUES ('nametoinsert', @is )


how to extract current year and combine with specific month and date as a date

The following T-SQL gives you the result you need.

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GetDate()))+'-06-16')


Removing grants for databases that no longer exist

I would start by verifying that the information in:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

is valid, i.e. is all existing databases, but nothing more. Then you can loop over %PRIVELIGES tables (8 I think it is) and create REVOKE statements from there, example for TABLE_PRIVILEGES:

SELECT 'REVOKE GRANT ON TABLE '
      || RTRIM(TABLE_SCHEMA) || '.' || RTRIM(TABLE_NAME)
      || 'FROM USER ' || GRANTEE
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE TABLE_SCHEMA NOT IN (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA)

In the same matter, go through the remaining _PRIVILEGES tables. All from the top of my head and untested, but it should give you a start.


Performing large amounts of schema updates and transaction log issues

Starting with SQL Server 2012 adding of not NULL column with default values to an existing table is metadata only operation.

From what you wrote it's clear that it's not your case, i.e. or your server is beyond 2012, or one of the following probably is your case:

the newly added column increases the maximum possible row size over
the 8060 bytes limit the column cannot be added online
your server Edition is not Enterprise

(you can find the complete list of restictions here: Online non-NULL with values column add in SQL Server 2012

So how you can resolve the issue?
If you cannot upgrade to at least 2012 or your Edition is not Enterprise, the only thing I can suggest you is to split this one big transaction to many transactions. This will permit you to clear the log (on checkpoint in simple recovery model or on a log backup in full / bulk logged).

So instead of adding not null column first add null column and then update it with your values in batches. So the log will have the possibility to clear between batches.


It appears Oracle version 18c is compatible with Autonomous database, but not prior versions.


PostgreSQL autoincrement up to certain number then reset for sharding

As serial columns simply select a nextval from a sequence do they not?
  It's just an extra step of reseting a sequence once it reaches n

Not even an extra step because cycling is a built-in functionality of sequences,
See CREATE SEQUENCE.

Example:

=> create sequence s maxvalue 5 cycle;

=> select nextval('s') from generate_series(1,7);
 nextval
---------
       1
       2
       3
       4
       5
       1
       2


How to join at least two tables with different column names but same data, and only show the matching data

From a high level, you'd simply do this:

SELECT *
FROM dbo.member m
    INNER JOIN dbo.tasklist_data tld ON m.UID = tld.TaskID;

The INNER JOIN will take rows from dbo.member where the UID column values match values contained in the TaskID column from the tasklist_data table.  If you wanted ALL the rows from dbo.member, and only matching rows from dbo.tasklist_data, you'd rewrite that as:

SELECT *
FROM dbo.member m
    LEFT JOIN dbo.tasklist_data tld ON m.UID = tld.TaskID;

Steve Stedman has an excellent resource for understanding how the different types of JOIN statements work.

If you need to do this on more than two tables, you simply add JOIN clauses as necessary:

SELECT *
FROM dbo.member m
    INNER JOIN dbo.tasklist_data tld ON m.UID = tld.TaskID
    INNER JOIN dbo.some_other_table sot ON m.UID = sot.some_column_name;

I have broken a (arguably) cardinal sin above by using SELECT * to return all columns from all of the tables mentioned in the FROM clause of the query.  It is generally considered good practice to explicitly specify the columns required, for instance:

SELECT m.UID
    , m.SomeColumn1
    , m.SomeColumn2
    , tld.TaskID
    , tld.SomeColumn3
FROM dbo.member m
    INNER JOIN dbo.tasklist_data tld ON m.UID = tld.TaskID;


How to deny connections to localhost and force to use FQDN instead

As always, a few minutes after posting a question, I found the answer myself. The issue was SELinux. It was set on enforced; permissive made it work instantly. Now I'll have to figure out a way to make it work with the enforced mode.

The following appeared in the audit.log:

  type=AVC msg=audit(1429781752.260:6738): avc:  denied  { name_connect } for  pid=40354 comm="httpd" dest=3306 scontext=system_u:system_r:httpd_t:s0 tcontext=system_u:object_r:mysqld_port_t:s0 tclass=tcp_socket


pgAdmin4 v4: TypeError: string indices must be integers

I have the same problem in Ubuntu 18.04 with pgAdmin 4.11 and postgres 11.

What works for me is to go to the process list and kill the pgadmin4 process and reload it. You may do it from Task Manager on windows. Good luck.

BTW Datagrip always work fine for me too.


Does large text datatypes reserves storage on disk?

Mot eactly, see mysql manual

TEXT and BLOB columns are implemented differently in NDB; each row in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which is stored in a hidden table. The rows in this second table are always 2000 bytes long. This means that the size of a TEXT column is 256 if size <= 256 (where size represents the size of the row); otherwise, the size is 256 + size + (2000 × (size − 256) % 2000).

As you can see it save 256 Bytes if you have equal or less than 256 bytes.
And if you have more than 256 bytes mysql reserved 200 Bytes blocks till it can store all data


Required free Disk Space for Index rebuild offline Operations

The Offline index rebuild operation creates a copy of the original index, then drops the original, and replaces it with the copy.

However, you should realize this is per index, not per database.  The typical index rebuild solution will rebuild indexes one-at-a-time in a serial fashion; so you only need space available on the data drive equal to double the size of the largest index.

Since index rebuilds are a logged operation, the log drive should allow enough space for at least a copy of the largest index to be rebuilt, plus any other concurrent operations that may be occurring.  You might consider the use of SORT_IN_TEMPDB = ON for the index rebuild operations, since that allows much of the index rebuild operation to occur in tempdb - if tempdb is located on a different drive that may potentially reduce disk space requirements on your target database's log drive.

Microsoft Docs has some good advice about the transaction log requirements for index operations.

If you regularly run index rebuilds, the database and log file should already have enough unallocated space to run the rebuild operation without requiring these files to grow on disk.  This is assuming, of course, that you don't shrink your database and log files after each rebuild.


How to get all roles that a user is a member of (including inherited roles)?

You can query the system catalog with a recursive query, in particular pg_auth_members:

WITH RECURSIVE cte AS (
   SELECT oid FROM pg_roles WHERE rolname = 'maxwell'

   UNION ALL
   SELECT m.roleid
   FROM   cte
   JOIN   pg_auth_members m ON m.member = cte.oid
   )
SELECT oid, oid::regrole::text AS rolename FROM cte;  -- oid & name

The manual about the cast to object identifier type regrole.

BTW 1: INHERIT is the default behavior of CREATE ROLE and doesn't have to be spelled out.

BTW 2: circular dependencies are not possible. Postgres disallows that. So we don't have to check for that.


How to PIVOT/UNPIVOT if I don't know how many columns will it have?

As Julien mentioned, dyanamic sql will be your answer for this. Below is an example of using pivoting with an EAV system. It really helps with reading the data.

DECLARE @Cols NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)
DECLARE @ClassName NVARCHAR(50)
SET @ClassName = 'customer'

SELECT @Cols = STUFF((SELECT distinct ',' + QUOTENAME(property_name)
                                FROM Entity_Property ep
                                INNER JOIN Entity e ON ep.eid = e.eid
                                INNER JOIN Class c ON c.cid = e.cid
                                WHERE c.class_name = @ClassName
                              FOR XML PATH(''), TYPE
                              ).value('.', 'NVARCHAR(MAX)')
                              ,1,1,'')

SET @Query = 'SELECT *
FROM
      (SELECT ep.eid, ep.property_name, ep.property_val FROM Entity_Property ep INNER JOIN Entity e ON e.eid = ep.eid INNER JOIN Class c ON c.cid = e.CID WHERE c.class_name = ''' + @ClassName + ''') as src
PIVOT
(
      MAX(Property_val)
      FOR property_name IN (' + @Cols + ')
) AS piv;'

EXEC(@Query)

- EDIT -

Here is the code that will do what you are looking for. I used a temp table as my source of information instead of the query you gave, so feel free to use that instead of #Table. The code below can be run stand alone to see what the output would look like, so with some tweaking, you can tie it to your tables.

-- Mimicing what we have for testing purposes
CREATE TABLE #Table (
    Unidade VARCHAR(100),
    Placa CHAR(8)
)

INSERT INTO #Table (Unidade, Placa) VALUES
('Divisao Administrativa', 'DJL-2937'),
('Instituto de Crimalistica', 'DJL-2690'),
('Instituto de Crimalistica', 'CDV-7883'),
('Instituto de Crimalistica', 'DJP-2304'),
('Instituto Medico Legal', 'DJL-2925'),
('Instituto Medico Legal', 'DJL-2689'),
('Instituto Medico Legal', 'DJL-2851'),
('Instituto Medico Legal', 'CMW-6316')

-- Using a temp table to hold a copy of our data with a new column for column headers
SELECT
        Unidade,
        'Placa' + CAST(ROW_NUMBER() OVER (PARTITION BY Unidade ORDER BY Placa) AS VARCHAR(10)) AS NewColumnHeader, -- If you have an order for numbering you want to use, add it to the above query and use it here
        Placa
INTO #TableWithNewColumnHeaders -- The 'dirty' way of creating a temp table.
FROM #Table

-- Now grab what all our new columns will be (Placa1, Placa2, etc)
DECLARE @Cols NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)

SELECT @Cols = STUFF((SELECT distinct ',' + QUOTENAME(NewColumnHeader)
                           FROM #TableWithNewColumnHeaders
                           FOR XML PATH(''), TYPE
                           ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

-- Now we pivot our results
SET @Query = 'SELECT p.*
        FROM
       (SELECT Unidade, NewColumnHeader, Placa
        FROM #TableWithNewColumnHeaders)     AS SourceQuery
PIVOT
(
      MAX(Placa)
      FOR NewColumnHeader IN (' + @Cols + ')
) AS p'

EXEC(@Query)

The idea is, we create a new column that will become our new column header. This is what lets us get the column names we want. Once we do that, it's a simple pivot statement to turn the header values into our new column names.


Stored procedure arguments get immediately truncated

MySQL timestamp is not a Unix timestamp.  It has the form '2012-02-02 12:12:12'.  I think it is worth to mention that it differs from datetime in that while timestamps minimum and maximum values correspond to that of a Unix timestamp, datetime has a broader range.

See the docs here:
http://dev.mysql.com/doc/refman/5.5/en/datetime.html

The easiest solution is changing arguments to integers.  The other way is of course producing valid timestamps in the calling application and feed those to your stored proc, but in this case you will have to modify the proc as well (by removing the FROM_UNIXTIME conversions).  (Thanks for DTest for the suggestion.)


Oracle: Creating Case Insensitive Database with NLS_COMP and NLS_SORT

Even if you manage to create a database with nls_comp=LINGUISTIC, nls_sort=BINARY_CI (from scratch (Custom Database or manually), not seed or template) , the indexes will be still created without these settings and you have to take specify them manually.

SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');

PARAMETER       VALUE
--------------- ---------------
NLS_COMP        LINGUISTIC
NLS_SORT        BINARY_CI

SQL> select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');

PARAMETER       VALUE
--------------- ---------------
NLS_SORT        BINARY_CI
NLS_COMP        LINGUISTIC

SQL> create table t1(id varchar2(1), something char(2000));

Table created.

SQL> insert into t1 select 'a', 'a' from dual connect by level <= 10000;

10000 rows created.

SQL> insert into t1 values ('b', 'b');

1 row created.

SQL> commit;

Commit complete.

SQL> create index t1_i1 on t1(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto for columns id size 2');

PL/SQL procedure successfully completed.

SQL> select /*+ index(t1 t1_i1) */ count(*) from t1 where id = 'B';

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  34cypvvhnwczh, child number 0
-------------------------------------
select /*+ index(t1 t1_i1) */ count(*) from t1 where id = 'B'

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   907 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     2 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  5001 | 10002 |   907   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NLSSORT("ID",'nls_sort=''BINARY_CI''')=HEXTORAW('6200'))

Index can not be used, hint does not help, and the estimated cardinality is just simply CEIL (NUM_ROWS / NUM_DISTINCT). This time with the proper index:

SQL> create index t1_i2 on t1(NLSSORT("ID",'nls_sort=''BINARY_CI'''));

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto for columns id size 2, SYS_NC00003$ size 2');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where id = 'B';

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9pvkbfkycz6q4, child number 0
-------------------------------------
select count(*) from t1 where id = 'B'

Plan hash value: 3225262789

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_I2 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."SYS_NC00003$"=HEXTORAW('6200'))


SSIS Adding column to excel sheet template destination when importing from SQL Table Source

When you do Export data from the database (using the wizard - Import\Export)

select -- write a query to specify data transfer.

If you are creating package from scratch using BIDS, then in the execute sql task editor, in the sql statement

then in the data flow task in the source query specify T-SQL query as below :


How to select the largest value of the same column in multiple tables?

If you are using a DB that doesn't support CTEs (such as mySQL currently) or the query planner doesn't can't optimise what richardtallent's solution is trying to do so ends up scanning instead of seeking on user_id or event_timestamp (I suspect it MS SQL Server's planner may end up doing this), you could try unrolling the CTE a little and using it as a derived table:

SELECT MAX(et)
FROM   (
           SELECT MAX(event_timestamp) AS et FROM tableA WHERE user_id=@u
           UNION ALL
           SELECT MAX(event_timestamp) AS et FROM tableB WHERE user_id=@u
           UNION ALL
           SELECT MAX(event_timestamp) AS et FROM tableC WHERE user_id=@u
           UNION ALL
           SELECT MAX(event_timestamp) AS et FROM tableD WHERE user_id=@u
       ) AS dt

This should make it obvious to the planner that it can use indexes on the user timestamp columns (or better still, an index covering both) for each source table before unioning the four single row results and picking the max from there.

Of course if the query planner is smart enough to see how to perform richardtallent's version this more efficient way then which you chose comes down to which you find easier to maintain. The above may be hassle due to needing user_id=@u in several places making it impossible to use as a view in that form but it should be fine in a stored procedure or ad-hoc prepared query.


Difficult migration from Postgres 8.1 (postgis 1.5) to Postgres 9.4 (postgis 2.1)

Follow the hard upgrade procedure which uses postgis_restore.pl to help translate the differences between the versions.


How to automate insertion of new column in Mysql table?

primary key...will be the concatenation of two field values within the table.

no NO NO!!!!!

This breaks all the rules of database normalization (well, rule 1 and rule 2 actually) and it's totally unnecessary! It uses more storage, makes your queries less efficient, and may cause bugs in your processing.

Use the existing columns as the primary key.

Presumably it doesn't have a primary key declared already.

Check that you've got no duplicates in the table:

SELECT ContractNum, PlanId, COUNT(*)
FROM ContractPlans
GROUP BY ContractNum, PlanId
HAVING COUNT(*)>1;

Fix the data if you need to, then

ALTER TABLE ContractPlans
ADD PRIMARY KEY (ContractNum, PlanId);


Unique clustered index on indexed view fails because of aggregates

Your issue is definitely your function, and you will have to do something to make that work differently.  Here is a very basic example to illustrate the problem.

First create your test data.

CREATE TABLE dbo.testResults
(
    id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    ,col1 VARCHAR(200)
);
GO
INSERT INTO dbo.testResults
        ( col1 )
VALUES  ( 'test1' ), ( 'test1' ), ( 'test2' ), ( 'test3' ), ( 'test4' ), ( 'test4' );
GO

Now we will create a view (that works correctly) and add the clustered index to that view.

CREATE VIEW vwResults
WITH SCHEMABINDING
AS
SELECT col1
,COUNT_BIG(*) AS cbcol1
FROM dbo.testResults AS tr
GROUP BY tr.col1;
GO
--Works great
CREATE UNIQUE CLUSTERED INDEX cls_tr ON dbo.vwResults (col1);
GO

Next we will add a very simple and basic function that does nothing more than very basic string manipulation (left 3 characters of a given string).

CREATE FUNCTION dbo.left3 (@str varchar(200))
RETURNS varchar(3)
WITH schemabinding
AS
begin
RETURN LEFT(@str, 3)
END;

Now we will get rid of our previous view add this column to the view.

DROP VIEW dbo.vwResults;
GO
CREATE VIEW vwResults
WITH SCHEMABINDING
AS
SELECT col1
,dbo.left3(col1) AS left3col1
,COUNT_BIG(*) AS cbcol1
FROM dbo.testResults AS tr
GROUP BY tr.col1, dbo.left3(col1);
GO

So far, so good.  But then by adding even this simplistic function to the view and nothing else, we are no longer able to index the view.

/*FAILURE!!!!!!*/
CREATE UNIQUE CLUSTERED INDEX cls_tr ON dbo.vwResults (col1);
GO

So for this example there is a pretty simple solution where I can fairly easily create a computed column and then everything works.   Here is how I would do that.

ALTER TABLE dbo.testResults
ADD left3col1 AS LEFT(col1, 3);

After doing this, I can create the same view and this time I can add the unique clustered index as follows.

DROP VIEW dbo.vwResults;
GO
CREATE VIEW vwResults
WITH SCHEMABINDING
AS
SELECT col1
,left3col1
,COUNT_BIG(*) AS cbcol1
FROM dbo.testResults AS tr
GROUP BY tr.col1, left3col1;
GO
/*IT WORKS!!!!!!*/
CREATE UNIQUE CLUSTERED INDEX cls_tr ON dbo.vwResults (col1, left3col1);
GO

This might not be an option for your scenario, but basically your function is the problem and you need to work through some alternatives (depending on what it does) if you want to make an indexed view work for your scenario.


How to automatically execute a stored procedure every day at a particular time?

If you are not comfortable using Powershell, then you can go for native service broker functionality.

This might sound complex, but below links has all the scripts :

Scheduling Jobs in SQL Server Express - Part 1 and

Scheduling Jobs in SQL Server Express - Part 2
Alternatively, you can use Standalone SQL Agent - still in BETA statge from codeplex.
You can use the native sqlcmd functionality and then schedule it using windows scheduler or Schtasks

sqlcmd -E -S server_name -d database_name -Q "EXEC schema_name.StoredProcedureName @p1"


MongoS clients not seeing databases after config server change to CSRS

There were no errors during the migration process. BUT a NEW dbpath was used so the initial config server didn't use the existing details but in fact created 'empty' details in the new dbpath.

Basically when doing the migration, the first cfg server does not change it's dbpath nor it's port throughout the migration procedure. Personally I found this unclear in the procedure, especialy when you are migration from SCCC to CSRS and using WiredTiger.


Implement a deduplication trigger in Oracle

This is not the sort of task that you would want to use triggers on tables for.

If you really wanted to use a trigger, you could declare t_alarms as a view, rather than as a table.  You could then write an INSTEAD OF trigger on the view that translated INSERT statements on the view into UPDATE statements against a new base table.

Were it me, though, I'd let the INSERT statements go into a table which allows duplicates and then create a materialized view on top of that table that does the aggregation.  You can have that materialized view refresh ON COMMIT if you want to ensure that the data in the materialized view is always in sync with the data in the detail table.


What is the language of my SQL Server setup files?

Within the root directory / folder of the install media, there should be several sub-directories/folders, including: x64, redist, resources, etc. One of those sub-directories will have a name starting with a 4-digit number and then 3 letters, possibly followed by another short group of letters:

####_AAA[_AA?]

That first group of 4-digits is the LCID, and the 3 letters are the language code. My installs for several versions now all have the following folder:

1033_ENU_LP

While the LCID is highly specific with regards to which language, the ENU portion at least matches the first pop-up you posted in the question that mentions:

  ... ENU localized files.

If you want to know what the LCID translates to, please see the following list on MSDN: Locale IDs Assigned by Microsoft. The number in the directory / folder name will match something in the far-right column: LCID Dec. For example, 1033 indicates a locale of: "English - United States".


How SQL Server handles concurrent requests?

Yes, it's possible (as per JNK's answer: 2 overlapping MAX reads, both get 6, one fails with duplicate assuming a constraint)

Use IDENTITY columns for this very reason.

If you have some odd notion to not use IDENTITY, then you have to use lock granularity (TABLOCKX ) or semaphores (sp_getapplock) to restrict concurrency and allow only one process to run MAX at a time.

Edit:

Two requests that come in at the same time (or very close to it) run concurrently. They are both separate from each other but overlapping.

So each process will read the table using MAX and both get 6. At this point, both processes and reading and the INSERTs have not started yet.

The leading processes INSERTs 6, closely followed by the lagging process. The lagging process will get the unique key violation (you have uniqueness, right?)

If the gap between processes is enough so that the 2nd read happens after the 1st INSERT you're OK.

However, if you have enough calls that you have this risk of duplicate, then using locks/semaphores to decrease concurrency is madness...


Use a window function

...sum(PL) over(order by [Date] range between 3 preceding and current row)


Return jsonb object
If there are never additional keys in the JSON object, it can be as simple as:
SELECT jsonb '{"1": 15, "2": 15, "3": 15, "4": 15, "5": 15}' || (json->'key')
FROM   tbl;

Parentheses are required to overrule operator precedence!
This does not remove additional keys from the JSON object.
Returns NULL if the JSON object is not found.
See example in the fiddle.
Consider notes for jsonb concatenation in the manual:

jsonb || jsonb → jsonb
Concatenates two jsonb values. Concatenating two arrays generates an
array containing all the elements of each input. Concatenating two
objects generates an object containing the union of their keys, taking
the second object's value when there are duplicate keys. All other
cases are treated by converting a non-array input into a
single-element array, and then proceeding as for two arrays. Does not
operate recursively: only the top-level array or object structure is merged.

Return int[]
Expanded the JSON object to a set with jsonb_each_text(), RIGHT JOIN to the full set (generated with generate_series() in a CTE), and aggregate with ARRAY constructor (fastest):
WITH a(key) AS (SELECT generate_series(1, 5)::text)
SELECT ARRAY (SELECT COALESCE(value::int, 15)
              FROM   jsonb_each_text(t.json->'key') RIGHT JOIN a USING (key)
              ORDER  BY key) AS int_arr
FROM   tbl t;

This trims any additional keys from the JSON object.
Returns an array of default values if the JSON object is not found.
See example in the fiddle.
db<>fiddle here
Related:

Why is array_agg() slower than the non-aggregate ARRAY() constructor?


Greenplum ERROR: Canceling query because of high VMEM usage

From ERROR: Canceling query because of high VMEM usage on the vmware Tanzu Community by Faisal Ali:

"Canceling query because of high VMEM usage" is when the segments uses 90% ( default ) of the gp_vmem_protect_limit. If it's the same query with the same error all the time, it would be better to check how much memory is the query using and take a explain plan to understand the query execution path and fine tune the query.


What about piping a gzipped mysql dump over ssh.


All queries on the server have slowed down, cpu is normal, plenty of free memory but SQL is using very little

When you're trying to find out why a SQL Server is slow, start by checking these things:

In Task Manager, is the CPU consumer actually sqlserver.exe, or something else? If it's something else, troubleshoot that first.
If it's actually sqlserver.exe, check what queries are running right now. The easiest way is with Adam Machanic's excellent sp_WhoIsActive. Here's how to run it.
Check the server's wait stats. My favorite way to do this is with sp_AskBrent, my stored proc that takes a 5-second sample of wait stats plus checks some diagnostic queries.

For more detail, check out these resources:

Our First Responder Kit - a PDF checklist of things to look at
The free book Troubleshooting SQL Server - I like this one because it's a problem/solution approach. You don't have to read the whole thing to get value out of it - just put it on your virtual shelf and when you've got a CPU problem, turn to the CPU chapter.


If you want to block all connections from whatever machine B is running on to A whether they are not they are using a database link, you can use the TCP.EXCLUDED_NODES parameter in your sqlnet.ora file.

If that is not what you want, you could potentially write a login trigger that tries to deduce whether the connection is via a database link presumably based on things like the machine and program from v$session or the IP address from sys_context('USERENV', 'IP_ADDRESS').  A login trigger can work reasonably well assuming your goal is mostly to keep honest users honest.  But since you are depending on information that is supplied by the client, a hostile attacker can often work around the issue-- often rather easily.


SQL Server does indeed write at commit. What it writes is to the transaction log, however. The data (dirty pages) can be written at a later time (for instance at checkpoint). Thanks to "force log write at commit" SQL server can re-construct a consistent view of the database at startup, where what should be committed is persisted.


MySQL using a index referencing a different column

According to your question, you use select * from MyTable where business_key = 1234 and parent = id;  to get the last record.
There is caveat you need to be aware of with regard to the index. If a particular business_key has 1000 records, the query will do a index range scan through all 1000 key entries. Since you know that parent=id indicates the last record, it would plausible for you to change the query to take advantage of that. Why? The index you would automatically have the ids sorted for any given business_key,parent combination. With that in mind, please change two things
CHANGE #1 : Refactor the Query to the following:
select B.* from
(
    select MIN(id) id FROM MyTable
    where business_key = 1234
) A INNER JOIN MyTable B using (id);

Keep in mind that MAX(id) would be the original (the oldest) record.
CHANGE #2 : Add the following index
ALTER TABLE MyTable ADD INDEX NewerIndx (business_key,id);

If you have queries that have this WHERE clause (WHERE business_key=1234 AND parent=999), then do not drop NewIndx. You may want to drop your old index on a staging or dev server and test all your queries, if any of the queries get worse on the dev server, keep your old index.
Give it a Try !!!


Could this ActiveRecord/SQL statement in my code base be susceptible to SQL injection?

It's probably open to a generic SQL injection, according to this discussion in the pgsql-general  mailing-list:

http://www.postgresql.org/message-id/20140626102204.36ced4dd@red.yakaraplc.local

The poster looked into ActiveRecord internals and reports that it uses libpq's PQsendQuery, which does accept several queries separated by semi-colons. This is from 2014, so presumably current.

So, assuming that your params[:query].downcase has not already gone through a layer of quoting, a DROP TABLE could be injected into it.

Even if ActiveRecord used PQexecParams or a similar call that doesn't accept semi-colon seperated queries, there would still be the possibility of injecting a function call, as in ...LIKE '%whatever' AND do_something_bad(...) AND 'whatever'='%'. This possibility is opened by lack of proper literal strings quoting, or lack of handling the parameters as recommended by the API.

And even when using prepared statements or injection with proper escaping, LIKE with user-supplied contents require an additional special quoting anyway, since it has its own syntax with % and _ being wildcards and its own escape mechanism with \ configurable with the ESCAPE clause. Otherwise the users can't search a literal % or _


Is it possible for heroku postgres follower database to follow non-heroku postgres master?

It's not supported in either direction, with the most pressing problem being the platform and architecture dependence of that replication method.

In the case of migrating to the service, the problem is doubly so, because slurping random, physical base backups from the internet as would be required to set up replication as it is seen today is a somewhat ticklish sounding proposition.

It sucks, and I'd like to lift that restriction some day, if that and the other problems (some not) can be solved.

Please feel free to write into postgres@heroku.com if you want to discuss your problem in more detail, although I'm most unsure if we have the technology to make this a super-easy proposition right now.  Still, the main way we can make it better is by studying it, if you'd be so kind to share.


First consider a query that computes which rows are actually relevant from tablethree. With the assumption that with "most recently entered result" you mean "most recent enddate" the following query would gather the appropriate rows:

SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid

Now you can build a join to retrieve not only sid, but all of the data of tablethree:

SELECT a.*
FROM tablethree a
INNER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) b
ON a.sid = b.sid AND a.enddate = b.enddate

This is the result set you actually want to "left join in". You have to insert this into your original query:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN (
  SELECT a.*
  FROM tablethree a
  INNER JOIN (
    SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
  ) b
  ON a.sid = b.sid AND a.enddate = b.enddate
) AS t3
  ON t3.sid = t2.sid
WHERE t1.fieldone = 1
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate

What should also work is the following:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN tablethree AS t3
  ON t3.sid = t2.sid
LEFT OUTER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) mostrecent
  ON t3.sid = mostrecent.sid AND t3.enddate = mostrecent.enddate

WHERE t1.fieldone = 1
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate
  AND mostrecent.enddate IS NULL

This includes both tablethree and the new SELECT as left joins, and sorts out the rows where mostrecent.enddate IS NULL (meaning those rows which are actually not most recent). This should lead to the same result, but MySQL may be able to compute this result a little faster. EXPLAIN on both queries should reveal possible differences in computation.


Receiving error when running DBCC CheckDB - multiple IAM pages

Several years after op. We ran into this error on SQL Server 2014.
Rebuilding the clustered Index on the table fixed the error
ALTER INDEX pk_table ON dbo.table REBUILD WITH (ONLINE = OFF)

after that DBCC CHECKDB (MyDatabase) ran with no errors.


UNION ALL vs CASE - SQL query optimization

Plan A -- no useful indexes:

Do the CASE approach.

Reason:  It is a single pass over the data.

PS:  SUM(approved = 1) is more concise, possibly faster.

Plan B -- useful indexes:

Do the UNION ALL approach if you have this composite index:

INDEX(approved, deleted_at)

and don't do COUNT(id), instead do COUNT(*).

That can be done in range scans on that one index.  The index is smaller than the table and each count only needs to scan part of the index.

Furthermore, UNION ALL is better optimized in 5.7 / 10.1.  So upgrade if you are not there yet.


Is PostgreSQL function parallel safety intended for functions which update data?

The reason is that both CTEs and temporary tables are private to the backend process that created them, and parallel worker processes are different processes that do not have access to the private resources of the leader process.

To make CTE scans parallel safe, CTEs would have to be materialized in shared memory.

I would be careful marking a function that creates a table as parallel safe, since that creates locks in the database. Can you guarantee that there can never be any conflicts so that your worker processes lock each other?


Windows ODBC Driver versions for Netezza

Based on the current release driver release compatibility matrix, found here, if you want to support Netezza NPS v6.0, v6.1, and v7.2 with the same ODBC driver, you should pick the most recent patch of the 7.1 ODBC driver.

If you don't need to have the same driver support all three server levels, then you would likely be better off going with the most recent patch of the v7.1 ODBC driver for Netezza v6.0 and v6.1, and going with the most recent patch of the v7.2 ODBC driver for Netezza v7.2.


What is the impact of changing the index creation memory setting in SQL Server?

Basing my assumptions off this question, your error message seems to indicate to me that you need to set "index create memory" to [1024 * DOP].  The error also seems to indicate that your DOP is set to 2, so if I were you, I would set index create memory to 2048KB(as stated in the error message) and see what happens.

If that doesn't work, I would try reducing the minimum memory per query and setting index create memory to [minimum memory per query * DOP](assuming you don't change DOP).


How do i specify a type for a new column through a select statement

Yes it is possible:

create view ... as (
    select movie_id, person_id, cast(null as char(1)) as position_name
    from writers
);

I assumed that null (which is part of any type) is what is causing you the problem. Replace null with whatever if that is not the case


SQL Server 2008 R2: Problems after computer name change

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

sp_addserver 'servername', local