Copy database fails while trying to copy login I'm trying to copy a database from a server that lives in a unique domain, call it AMCO.

The destination server, which is on domain BCO, is running sql server agent, and as far as I can tell everything is set up properly there.

When I use the copy database feature of management studio, I can set everything up just fine. During the actual job execution however, this appears in the log:

  Started:  5:27:19 PM  Progress: 2012-07-31 17:27:20.04     Source:
  192_168_50_11_SQLSERVER2008_localhost_SQLSERVER2008_Transfer Objects Task      Task just started the execution.: 0% complete  End Progress  Error: 2012-07-31 17:27:27.34     Code: 0x00000000     Source: 192_168_50_11_SQLSERVER2008_localhost_SQLSERVER2008_Transfer Objects Task      Description: ERROR : errorCode=-1073548784 description=Executing the query "CREATE LOGIN [AMCO\AMCOAdmin] FROM WINDOWS WITH DEFA..." failed with the following error: "Windows NT user or group 'AMCO\AMCOAdmin' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.   helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}

Why does this user need to be copied first? I am removing the option of "copy logins" from the additional data to copy, and yet this error still comes up.

Do yourself a favor and stay away from the copy database functionality of SSMS.  Just do a full database backup and then restore on the instance you are looking for.  You can fix orphaned users on the destination instance through the use of sp_change_users_login.

On your Source Instance:

backup database YourDatabase
to disk = 'C:\YourBackupDir\YourDatabase.bak'
go

On your Destination Instance:

restore database YourDatabase
from disk = 'C:\YourBackupDir\YourDatabase.bak'
go

You may have to move that backup onto a share or port it to the destination server, but you get the idea.


Mysql Update columns from same table columns I have a table where payments are located with invoice_id and payment_id and also payment_due_date => payment receiving date and period_months that is total period of the payment

I have added 2 columns in this table named service_start_date and service_end_date. I want to update these columns. service_start_date will be the first payment date and service_end_date will be payment_due_date+period_months. Then next service_start_date will be (payment_due_date+period_months)+1 day and its service_end_date will be service_start_date+period_month and so on for the next payments.

here is the queries to implement

CREATE TABLE `temp_table` (
    `invoice_id` int(11) DEFAULT NULL,
    `payment_id` int(11) NOT NULL DEFAULT '0',
    `payment_due_date` datetime DEFAULT NULL,
    `period_months` varchar(255) DEFAULT NULL,
    `service_start_date` date DEFAULT NULL,
    `service_end_date` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
;

insert into `temp_table`
    (`invoice_id`,     `payment_id`,     `payment_due_date`,     `period_months`,     `service_start_date`,     `service_end_date`)
values
('2065','3337','2015-12-15 00:00:00','3',null,null),  -- '' is not a valid date
('2065','3338','2016-03-14 00:00:00','3',null,null),
('2065','3339','2016-06-12 00:00:00','3',null,null),
('2065','3340','2016-09-10 00:00:00','3',null,null);

select * from temp_table returns this:

What I want is this:

Note: There are multiple invoices in this table and there are multiple payments for each table. I need this to be solve in one single query.

If you have further questions and queries, then please comment.

How can I do this?


Optimize multiple group_concat Just wondering if this query could be optimized or if sub-queries would be better?

select t.entry_id,
t.title,
d.field_id_249 as unique_id,
d.field_id_577 as keywords,
d.field_id_681 AS video,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id = 0 THEN sub.cat_name ELSE NULL END SEPARATOR ",") as subject_name,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id = 0 THEN sub.cat_url_title ELSE NULL END SEPARATOR ",") as subject_url,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id = 0 THEN sub.cat_order ELSE NULL END SEPARATOR ",") as subject_order,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id != 0 THEN sub.cat_name ELSE NULL END SEPARATOR ",") as topic_name,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id != 0 THEN sub.cat_url_title ELSE NULL END SEPARATOR ",") as topic_url,
GROUP_CONCAT(DISTINCT CASE WHEN sub.parent_id != 0 THEN sub.cat_order ELSE NULL END SEPARATOR ",") as topic_order,
GROUP_CONCAT(DISTINCT CASE WHEN sw2.status = "open" THEN sw2.title ELSE NULL END SEPARATOR ",") as software_name,
GROUP_CONCAT(DISTINCT CASE WHEN sw2.status = "open" THEN sw2.url_title ELSE NULL END SEPARATOR ",") as software_url,
GROUP_CONCAT(DISTINCT CASE WHEN sw2.status = "open" THEN sw3.field_id_166 ELSE NULL END SEPARATOR ",") as software_icon,
GROUP_CONCAT(DISTINCT grade.cat_description SEPARATOR ",") as grades,
GROUP_CONCAT(DISTINCT CASE WHEN ab.grade_code != "" THEN ab.grade_code ELSE NULL END SEPARATOR ",") as ab_grade,
GROUP_CONCAT(DISTINCT ab.state SEPARATOR ",") as ab_state,
GROUP_CONCAT(DISTINCT ab.state_id SEPARATOR ",") as ab_standards
from exp_channel_titles AS t
inner join exp_playa_relationships as p on (t.entry_id = p.child_entry_id)
left join exp_channel_data as d on (t.entry_id = d.entry_id)
left join api_ab AS ab on (ab.unique_id = d.field_id_249)
left join exp_category_posts AS cp on (cp.entry_id = p.parent_entry_id)
left join exp_categories AS sub on (sub.cat_id = cp.cat_id)
left join exp_playa_relationships AS sw1 on (sw1.parent_entry_id = t.entry_id)
left join exp_channel_titles AS sw2 on (sw2.entry_id = sw1.child_entry_id)
left join exp_channel_data AS sw3 on (sw2.entry_id = sw3.entry_id)
left join exp_category_posts AS gr on (t.entry_id = gr.entry_id)
left join exp_categories AS grade on (grade.cat_id = gr.cat_id)
where `p`.`parent_field_id` = 678 AND `t`.`status` = 'open' AND FIND_IN_SET("animal", d.field_id_577) OR `t`.`title` LIKE '%animal%'
group by p.child_entry_id

You are generating logically unreasonable cartesian product and then technically "fixing" it with DISTINCT (If you survive the generated set volume).
Yes, sub-queries is definitly the way to go here.


Determining whether a MySQL column is ever used How can I determine whether a particular column in a MySQL table is ever SELECTed or explicitly UPDATEd or INSERTed or referenced in a WHERE clause?  It'd be nice if I could determine the db user who is using the column, too.

This is on MySQL 5.0, but I could move that to 5.6 or 5.7.

Background:  I'm maintaining a legacy app with an AUTOINCREMENT field ("id") that will foreseeably reach its maximum INT value before the app is retired.

I suspect, but do not know, that no one actually references this field in SQL at all.  Ideally I'd "monitor" the column for a week or two and, if it appeared in no queries, I'd turn it into a BIGINT or drop it in favor of a natural key.  If the column does appear in someones' queries, I'll need to track that person down and coordinate the change.

Mind the Gap

Let me give you a third option if the ids have a lot of gaps...

One significant cause of "running out of id values" is queries that leave gaps in AUTO_INCREMENT values.  An INSERT statements where row is not inserted first allocates an auto-inc value, then fails to use it.  INSERT IGNORE will do this silently.

Sometimes a useful workaround is to change the code such that it does not do the INSERT if it is not going to succeed.  Normalization is perhaps a normal case for this.  Here is a technique for batch normalization that avoids the problem.

(OK, this suggestion is not useful if you can't get to the source code.  But it could be useful to other readers; it has saved me a few times.)

General log

By turning on the "general log" for a period of time, you can collect all queries.  Then you can search for the table or "id", etc.  It will be tedious.  It will not be 100% safe, since some queries may not have been executed during that time.

innodb_autoinc_lock_mode

innodb_autoinc_lock_mode did not exist until 5.5, so that is not an option.

(Again, I mention this for other readers.)


Populate a table with "WITH" clause How to perform the following select query?

with x (x, y, z) as
((2 5 6), (6,3,2))
select * from x

I am not sure if this approach is possible. Not even sure if the syntax is correct. But I don't want to make a table in my database. Actually I am writing a PL/pgSQL function where I have few numeric values like 2,5,6,3 etc in few variables and I want to create a table from those variables so that I can perform some SQL operations on them.

The syntax is almost correct. You need the keyword values and some commas that are missing:

with x (x, y, z) as
  (values
    (2, 5, 6),
    (6, 3, 2)
  )
select *
from x;


Table creation along with dynamic partitioning (interval partitioning) in Oracle 10g EE I am trying to create partitioning using interval partitioning using the below given query:

CREATE TABLE DATA_HOURLY
(
  tmstamp TIMESTAMP,
  elementName VARCHAR2(255),
) PARTITION BY RANGE (tmstamp)
interval (numtoyminterval(1,'MONTH'))
(
   PARTITION p1 VALUES LESS THAN (to_date('2016-01-31','yyyy-mm-dd'))
);

The error that I am getting in SQL Developer is:

  SQL Error: ORA-00922: missing or invalid option
  00922. 00000 -  "missing or invalid option"
  *Cause:
  *Action:

How can I fix this issue?

I'm afraid that interval partitioning was introduced on Oracle 11g.


Is it possible to get schema ID in another database without using dynamic sql? I start with an object ID and a database ID. Inside a user defined function, I want to get the full name and ID of the database, schema, and object. How can I get the schema ID without using dynamic SQL, which is prohibited inside UDFS?

Note:

I can get the database name with DB_NAME
I can get the object name by using OBJECT_NAME because it accepts the db ID as a second parameter
I can get the schema name by using OBJECT_SCHEMA_NAME because it also accespts a db ID as a second parameter
It's easy to get the schema ID using dynamic SQL to select from [db_name_i_want].sys.schemas, but this is not allowed in a  UDF

Update (purpose)

For a possible solution to DB_ID context from farther up call stack, I'm adapting some call stack functions by Gabriel McAdams to work accross multiple databases. His version just pushes the proc ID at each call level onto the CONTEXT_INFO stream. I've modified this to also push the db ID. I could push the schema ID as well, but then things start to get crowded because CONTEXT_INFO is limited to 128 bytes. So, I was hoping to be able to reconstruct the schema ID from the db id and object id in the function the creates a view of the call stack (CallStackView).

Code

-- @db_id, @proc_id are saved in a logging function
DECLARE @db_name nvarchar(128)     = DB_NAME(@db_id)                      -- OK
DECLARE @obj_name nvarchar(128)    = OBJECT_NAME(@proc_id, @db_id)        -- OK
DECLARE @schema_name nvarchar(128) = OBJECT_SCHEMA_NAME(@proc_id, @db_id) -- OK
DECLARE @schema_id int = ? -- What can I do here???

To answer the question literally, no there isn't a way to do this in T-SQL without using dynamic SQL (but see Aaron's suggestion to use a view).  You can, however, use a SQLCLR function (which can be called from a T-SQL function).  The following is demo code to illustrate the principle:

-- Returns 7 from any database context
SELECT dbo.GetSchemaID (N'AdventureWorks2012', N'Production');

T-SQL script:

CREATE ASSEMBLY [CLR] AUTHORIZATION [dbo]
FROM [encoded data, 9218 characters]
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[GetSchemaID] (@DatabaseName [nvarchar](4000), @SchemaName [nvarchar](4000))
RETURNS [int]
AS EXTERNAL NAME [CLR].[UserDefinedFunctions].[GetSchemaID];

C# source:

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess=DataAccessKind.None,
        IsDeterministic=false,
        IsPrecise=true,
        SystemDataAccess=SystemDataAccessKind.Read
        )
    ]
    public static int GetSchemaID(string DatabaseName, string SchemaName)
    {
        using (SqlConnection conn = new SqlConnection("context connection = true"))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = String.Format("SELECT @id = [schema_id] FROM {0}.sys.schemas WHERE [name] = '{1}';", DatabaseName, SchemaName);
                cmd.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                return (int)cmd.Parameters[0].Value;
            }
        }
    }
}


Invalid directory path error while connecting to database I am using oracle 11g and using sqldeveloper to run/edit queries.

My table is:

ID     FIRST_NAME   LAST_NAME    MAJOR                 CURRENT_CREDITS
10008   A            Dd          Computer Science         2
10009   B            DD          History                  3
10010   D           dd           Computer Science         3
10011   C           ad           Economics                3
10012   D           Da           History                  3
10013   E           df           History                  3

Above table is in CSV format (lect.csv) stored in D:\1Deepak\Data.

I have created below procedure to import the csv file in oracle, but it’s not working getting below error :

  Connecting to the database r.

  ORA-29280: invalid directory path

  ORA-06512: at "SYSTEM.LOADLECTURER", line 39

  ORA-06512: at line 2

  Process exited.

  Disconnecting from the database r.

Code :

create user D identified by d
CREATE DIRECTORY log_dir AS 'D:\1Deepak\Data';
Grant all privileges to D;
Grant Read, Write on Directory log_dir to D;

CREATE TABLE lecturer (
  id               NUMBER(5) PRIMARY KEY,
  first_name       VARCHAR2(20),
  last_name        VARCHAR2(20),
  major            VARCHAR2(30),
  current_credits  NUMBER(3)
  );

CREATE OR REPLACE PROCEDURE Loadlecturer
AS
p_TotalInserted number;
  v_FileHandle UTL_FILE.FILE_TYPE;
  v_NewLine  VARCHAR2(100);  -- Input line
  myFirstName lecturer.first_name%TYPE;
  v_LastName lecturer.last_name%TYPE;
  v_Major lecturer.major%TYPE;
  v_FirstComma NUMBER;
  v_SecondComma NUMBER;
BEGIN
  v_FileHandle := UTL_FILE.FOPEN ('Log_dir','Lect.csv', 'r');
  p_TotalInserted := 0;
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(v_FileHandle, v_NewLine);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;

    v_FirstComma := INSTR(v_NewLine, ',', 1, 1);
    v_SecondComma := INSTR(v_NewLine, ',', 1, 2);

    myFirstName := SUBSTR(v_NewLine, 1, v_FirstComma - 1);
    v_LastName := SUBSTR(v_NewLine, v_FirstComma + 1,
                         v_SecondComma - v_FirstComma - 1);
    v_Major := SUBSTR(v_NewLine, v_SecondComma + 1);

    INSERT INTO lecturer (ID, first_name, last_name, major) VALUES (1, myFirstName, v_LastName, v_Major);

    p_TotalInserted := p_TotalInserted + 1;
    dbms_output.put_line(p_totalinserted);
  END LOOP;
  UTL_FILE.FCLOSE(v_FileHandle);
 Exception
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE;
END Loadlecturer;

From where is the above mentioned error is coming?

I already had that problem. The thing is that Oracle saves your objects in Uppercase. When you use the FOPEN, you MUST use directory name in uppercase as well. Try this:

v_FileHandle := UTL_FILE.FOPEN ('LOG_DIR','Lect.csv', 'r');


how to use trigger to set value based on query result would appreciate any example on how to set value on the added row based on query results from different table.

something like this

delimiter |

CREATE TRIGGER trigger BEFORE INSERT ON table1
FOR EACH ROW
  BEGIN
    NEW.value = (select value from table2 where id=NEW.id)
    NEW.value2 = (select value2 from table2 where id=NEW.id)
  END;
|

delimiter ;

That looks like it should work. I would run one SELECT to retrieve both values

SUGGESTION #1

delimiter |

CREATE TRIGGER trigger BEFORE INSERT ON table1
FOR EACH ROW
  BEGIN
    select value,value2
    INTO NEW.value,NEW.value2
    from table2 where id=NEW.id;
  END;
|

delimiter ;

SUGGESTION #2

If the value and value2 are of datatype INT, do the following

delimiter |

CREATE TRIGGER trigger BEFORE INSERT ON table1
FOR EACH ROW
  BEGIN
    DECLARE v1,v2 INT;

    select value,value2 INTO v1,v2
    from table2 where id=NEW.id;

    NEW.value  = v1;
    NEW.value2 = v2;
  END;
|

delimiter ;

or do DECLARE v1,v2 VARCHAR(45); or whatever datatype is appropriate


How can I copy a subset of columns from one InnoDB table to another, without locking? I have a MySQL instance running on Amazon's RDS. This means that I cannot change any of the binlog settings.

I have a table source, created like this, which contains around 2 billion rows:

CREATE TABLE `source` (
   id INT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

I have another table destination, with the same columns, but the id is a BIGINT:

CREATE TABLE `destination` (
   id BIGINT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

The source table has gaps in the ID that I want to compact. I'd like to copy all rows in source to destination, without the id column, something like this:

INSERT INTO `destination` (value1, value2)
SELECT value1, value2 FROM `source`;

How can I accomplish this, without locking the source table? The copy itself is going to take an extremely long time, due to the size of the table, and I can't have it locked for that long.

I essentially want to run the above statement with READ UNCOMMITTED isolation, but this isn't possible due to my inability to change any binary log settings.

IF you are not updating or deleting old rows, the way to do it with minimal locking is to do it chunk by chunk.  See here for doing chunking for deleting; it can be adapted for your 'copy'.  When you get to the end of the copying, stop writes long enough to copy the final chunk and rename:

Then, if your real goal was to change to BIGINT, finish with

RENAME TABLE source TO old,
             destination TO source;
DROP TABLE old;


BCNF decomposition step without functional dependencies I am performing the BCNF decomposition for the relation R = (A, B, C, D, E), that has the set of functional dependencies AB -> C, CD -> A, C -> E, and C -> B.

After checking that the first dependency AB -> C violates the BCNF (by calculating the closure of AB), I split the original relation into two - R1 = (A, B, C) and R2 = (A, B, D, E).

At this point, the functional dependencies associated with R1 are AB -> C and C -> B, so I can see that R1 is in the BCNF. When it comes to R2, I cannot use any of the functional dependencies to verify if there are any violations to the BCNF, as all of them have the C attribute. How can I proceed if I do not have any functional dependencies to check?

When you decompose R(T) to find the BCNF, if the dependency X -> Y violates the BCNF, the decomposition that the classical algorithm requires is not R1(X) and R2(T-X), but R1(X+) and R2(T-(X+ - X)).

So the first decomposition, considering AB → C, should be:

R1(A, B, C, E) (since AB+ = ABCE)
R2(A, B, D) (= {ABCDE - (ABCE - AB))

In R1 the remaining dependencies are AB → C, C → E, C → B, while in R2 there are no (non-trivial) dependencies.

R1 is not in BCNF, since the two dependencies C → E, C → B violates that form (the only candidate keys are AB and AC). So it can be decomposed in R3(B, C, E), with dependencies C → E, C → B, and R4(A, C) (again without non-trivial dependencies).

So the final decomposition is:

R2 < (A B D) ,
{ } >

R3 < (B C E) ,
{ C → E
C → B } >

R4 < (A C) ,
{ } >

Note that the dependencies:

{ C D → A
A B → C }

are not preserved in this normalization.

Finally, note that if, during this process, you get a relation schema without (non-trivial) dependencies, this schema is already normalized, since the only candidate key is formed by all the attributes and there is no dependency that violates the BCNF.


how to generate the creation script for partition function and partition schema? I have a script to generate the create table script. You can see it on my answer to this question.

However, now I need to script my partition functions and partition schemes

On this question here, see the answer has examples on how to add a partition to a table and how to remove a partition from a table, and I would like to have a look at the scripts at any stage.

I have found this link: how to find partition function text applied to a table

how to generate the scripts for create partition function and partition schema?

I was working on a script to generate the create partition function
as you can see below, but it only works for my own PF_year partition function
because it was tricky to get over the fact that sys.partition_range_values has a sql_variant data type as value.

when I used a case I got the following error:

  Msg 206, Level 16, State 2, Line 35

  Operand type clash: int is incompatible with date

Then in his great answer Dan Guzman, showed us SQL_VARIANT_PROPERTY some wonderful thing that I was not aware of, now I am a new DBA and person.

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT
    RADHE = 'CREATE PARTITION FUNCTION' + space(1) + quotename(spf.name) +
'(' +
    COALESCE(baset.name,'data type not found') +   ')' + CHAR(13) +
    'AS RANGE ' + CASE WHEN CAST(spf.boundary_value_on_right AS int) = 1
THEN 'RIGHT ' ELSE 'LEFT ' END  + CHAR(13) +
    'FOR VALUES (' +
    (SELECT STUFF(
    (SELECT
    [text()] = N',''' + CAST(
                      CASE st.system_type_id
                       WHEN 40 THEN CONVERT (date, sprv.value,126)
                       --WHEN 56 THEN CONVERT (int,  sprv.value)
                       --ELSE CONVERT (int,  sprv.value)
                      END
                      AS NVARCHAR) + ''''
    FROM sys.partition_range_values sprv
    WHERE sprv.function_id=spf.function_id
    order by sprv.boundary_id
     FOR XML PATH('') ), 1, 1,N'')+ N');')

    ,st.system_type_id
    ,spf.name AS [Name],
    spf.function_id AS [ID],
    CAST(spf.boundary_value_on_right AS int) AS [RangeType],
    spf.create_date AS [CreateDate],
    spf.fanout AS [NumberOfPartitions]
    FROM
    sys.partition_functions AS spf

    INNER JOIN sys.partition_parameters AS spp
            ON spp.function_id=spf.function_id

    INNER JOIN sys.types AS st
            ON st.system_type_id = st.user_type_id
           and spp.system_type_id = st.system_type_id

    LEFT OUTER JOIN sys.types AS baset
                  ON (     baset.user_type_id = spp.system_type_id
                       and baset.user_type_id = baset.system_type_id)

                  or (     (baset.system_type_id = spp.system_type_id)
                       and (baset.user_type_id = spp.user_type_id)
                       and (baset.is_user_defined = 0)
                       and (baset.is_assembly_type = 1))
    WHERE spf.name = 'PF_YEAR';

I see from your comment on Terry C's answer that you are looking for a T-SQL solution to script partition functions and schemes. Although I think SMO or SSDT (via SSMS, Powershell, Visual Studio) is the right tool for scripting DDL, it is possible accomplish via some ugly T-SQL. Below are sample scripts for this particular task.

SELECT
      N'CREATE PARTITION FUNCTION '
    + QUOTENAME(pf.name)
    + N'(' + t.name  + N')'
    + N' AS RANGE '
    + CASE WHEN pf.boundary_value_on_right = 1 THEN N'RIGHT' ELSE N'LEFT' END
    + ' FOR VALUES('
    +
    (SELECT
        STUFF((SELECT
            N','
            + CASE
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'char', N'varchar')
                    THEN QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'nchar', N'nvarchar')
                    THEN N'N' + QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'date'
                    THEN QUOTENAME(FORMAT(CAST(r.value AS date), 'yyyy-MM-dd'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetime'
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime), 'yyyy-MM-ddTHH:mm:ss'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN(N'datetime', N'smalldatetime')
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime), 'yyyy-MM-ddTHH:mm:ss.fff'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetime2'
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetime2), 'yyyy-MM-ddTHH:mm:ss.fffffff'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'datetimeoffset'
                    THEN QUOTENAME(FORMAT(CAST(r.value AS datetimeoffset), 'yyyy-MM-dd HH:mm:ss.fffffff K'),'''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'time'
                    THEN QUOTENAME(FORMAT(CAST(r.value AS time), 'hh\:mm\:ss\.fffffff'),'''') --'HH\:mm\:ss\.fffffff'
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') = N'uniqueidentifier'
                    THEN QUOTENAME(CAST(r.value AS nvarchar(4000)), '''')
                  WHEN SQL_VARIANT_PROPERTY(r.value, 'BaseType') IN (N'binary', N'varbinary')
                    THEN CONVERT(nvarchar(4000), r.value, 1)
                  ELSE CAST(r.value AS nvarchar(4000))
              END
    FROM sys.partition_range_values AS r
    WHERE pf.[function_id] = r.[function_id]
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,N'')
    )
    + N');'
FROM sys.partition_functions pf
JOIN sys.partition_parameters AS pp ON
    pp.function_id = pf.function_id
JOIN sys.types AS t ON
    t.system_type_id = pp.system_type_id
    AND t.user_type_id = pp.user_type_id
WHERE pf.name = N'PF_Year';

SELECT
      N'CREATE PARTITION SCHEME ' + QUOTENAME(ps.name)
    + N' AS PARTTITION ' + QUOTENAME(pf.name)
    + N' TO ('
    +
    (SELECT
        STUFF((SELECT
            N',' + QUOTENAME(fg.name)
    FROM sys.data_spaces ds
    JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = ps.data_space_id
    JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id
    WHERE ps.data_space_id = ds.data_space_id
    ORDER BY dds.destination_id
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,N'')
    )
    + N');'
FROM sys.partition_schemes AS ps
JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
WHERE ps.name = N'PSC_Year';


Filter mysql group by negative having contains How can I get all of the values from 1 column, which do not (or do) have a specific value in one of their rows?

For instance, consider example table:

| ColA | ColB |
---------------
| aaa1 | zzz1 |
| aaa1 | zzz2 |
| aaa1 | zzz1 |
| aaa2 | zzz3 |
...

I want to get each of the values from ColA, where the set group by ColA does not contain a row with a specific value in ColB. i.e.

select ColA from test group by ColA not containing ColB like 'zzz2'

Unfortunately, as far as I know there isn't a containing keyword in MySQL. So what can I do instead? Is there some way to tell having to filter by another column?

This is an anti-semijoin. Can be written with NOT IN, LEFT JOIN / WHERE IS NULL or NOT EXISTS:

select distinct ColA       -- if there is another table where ColA is unique
from test as a             -- use it here, instead of table test
                           -- and remove the distinct
where not exists
      ( select 1
        from test as b
        where b.ColB = 'zzz2'
          and b.ColA = a.ColA
      ) ;

You could also use GROUP BY with a CASE expression:

select ColA
from test
group by ColA
having count(case when ColB = 'zzz2' then 1 end) = 0 ;


Inner join on array column in Postgres In Postgres 9.4, I'm trying to pull the names of all columns that are involved in a UNIQUE constraint for a given table in Postgres.

It looks like the names of such columns are contained in pg_constraint. According to the docs, the column relevant to my problem is called conkey, which happens to be an array of ints.

The point is, the query I've come up with gives me the wrong results, and I'm pretty sure it's because I'm joining on conkey in the wrong way. The query is as follows:

SELECT
    pg_attribute.attname,
    pg_constraint.*
FROM
    pg_attribute
INNER JOIN pg_constraint
        ON pg_attribute.attnum = ANY (pg_constraint.conkey)
WHERE pg_constraint.conrelid = (
    SELECT
        oid
    FROM
        pg_class
    WHERE
        relname LIKE 'test_table'
)
AND pg_constraint.contype = 'u'; -- to filter out non-unique constraints

Here's a table DDL for a quick repro:

CREATE TABLE "test_table" (
"date" date DEFAULT now() NOT NULL,
"foo" varchar COLLATE "default" NOT NULL
CONSTRAINT "test_table_foo_key" UNIQUE ("foo")
)
WITH (OIDS=FALSE);

ALTER TABLE "test_table" OWNER TO "postgres";

Now, can anyone tell me what I'm doing wrong? Is there an easier way to get the information I want altogether?

I'm trying to retrieve the names of constrained table because the app I'm writing needs to work around the current lack of UPSERT in Postgres. The idea is to do the insert-or-update with a CTE, but since I don't know what the constrained columns are beforehand, I need to do this bit of introspection.

You half miss the connection between the two tables.  The ...relid columns must match, too:

SELECT attname, c.*
  FROM pg_attribute a
  JOIN pg_constraint c
    ON attrelid = conrelid -- this was missing
   AND attnum = ANY (conkey)
 WHERE attrelid = 'test_table'::regclass;


MySQL - maximum of sum over different months I have a table called commodities where I have nature_of_goodscolumn in it. nature_of_goods contains all the different types of goods like machinery, household etc.

I have another column called as created at which contains the date and time in timestamp format.

I need to find the maximum goods took place in particular month.

I have tried the following query:

select max(mycount)
from   (select   nature_of_goods, count(nature_of_goods) AS mycount
        from     commodities
        group by month(created_at));

You need to GROUP BY nature_of_goods, if the counts should be separated by their values. To only show results of a particular month you have to put that in the WHERE, otherwise all months will be used.

Try something like this:

select max(mycount)
from   (select   count(nature_of_goods) AS mycount
        from     commodities
        where    month(created_at) = <insert desired month here>
        group by nature_of_goods) x;

.


When is dm_exec_procedure_stats updated? I just created a new proc and ran it, but it is not showing up in my query:

SELECT  schema_name(schema_id) SchemaName, name ProcName, last_execution_time LastExecuted, last_elapsed_time LastElapsedTime, ps.object_id
FROM    sys.dm_exec_procedure_stats ps JOIN
        sys.objects o ON ps.object_id = o.object_id
WHERE   DB_NAME(ps.database_id) = 'MyDatabase'

Should I not expect a new proc to appear in the results of this query immediately after running the proc for the first time?

I've run the proc I created several times now, and I've run sys.dm_exec_procedure_stats several times as well yet it still doesn't appear. I have the assumption that running a stored procedure will always cause a procedure plan to be cached for it if one does not already exist... but wondering if that's a correct assumption now?

The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached.
NOTE: An initial query of sys.dm_exec_procedure_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

Resource
Is sp_recompile or WITH RECOMPILE being used? Those can cause the stored procedure to be removed. Also, it could be in regards to the name of the procedure. This answer talks about that.


How to efficiently combine user authentication with token authentication? What is the most efficient approach of authentication, considering an user based authentication system combined with a token based authentication system?
For this question please consider the following tables:
create table users (
  id int generated always as identity primary key,
  username text not null unique,
  password text not null
);

create table tokens (
  id int generated always as identity primary key,
  user_id int not null references users(id),
  access_token text unique default generate_random_token(),
  refresh_token text default generate_random_token(),
  updated_on timestamp with time zone default current_timestamp,
  created_on timestamp with time zone default current_timestamp
);

As far as I know to create an user and allow it to authenticate the following steps are mandatory (explained with query statements):

Optional: Create an user record.
insert into users (username, password) values ('foo', create_password_hash('bar'));

Check if the user can authenticate itself and remember the user's ID:
select * from users where username = 'foo' and password = validate_password_hash('bar', password);

Check if the user has an existing token and hand over the access_token:
select access_token from tokens where user_id = 123;

Optional: Create a token if the user doesn't have one yet:
insert into tokens (user_id) values (123) returning access_token;

If my assumption for the steps to be taken is right, then I wonder how to minimize these four statements into a single one or have at least some guidance how this could be done more efficient. Also I could imagine that my approach for this use case incorrect and that there is an other more efficient approach, please let me know.

BONUS
Perhaps your approach could also cover the process of refreshing a token. Considering the approach explained above the refresh should only occur when a token is found at the third step and according to the updated_on timestamp the token is "expired".

Apparently this use case is fairly specific and thus it is quite a challenge to write an "upsert" statement what covers all the steps as described in the question. Therefor I see the following procedure as the only way out.
create or replace function user_auth(p_user text, p_pass text)
returns token_response as
$$
declare
    v_user_id bigint;
    v_token_id bigint;
    v_updated_on timestamp with time zone;
    v_access_token text;
    v_refresh_token text;
begin
    select id
    into v_user_id
    from users
    where username = p_user
        and password = validate_password_hash(p_pass, password)
    limit 1;

    if (v_user_id is not null) then
        select id, updated_on, access_token, refresh_token
        into v_token_id, v_updated_on, v_access_token, v_refresh_token
        from tokens
        where user_id = v_user_id
        order by updated_on desc
        limit 1;

        if (v_token_id is null) then
            insert into tokens (user_id)
            values (v_user_id)
            returning access_token, refresh_token
            into v_access_token, v_refresh_token;
        end if;

        if (v_updated_on < current_timestamp - interval '1 day') then
            update tokens
            set access_token = generate_random_token(),
                refresh_token = generate_random_token(),
                updated_on = current_timestamp
            where id = v_token_id
            returning access_token, refresh_token
            into v_access_token, v_refresh_token;
        end if;
    end if;

    return (v_access_token, v_refresh_token)::token_response;
end;
$$ language plpgsql;

Here is a working DB fiddle


Sql server table insert performance optimization Setting

In a datawarehouse, I am joining a fact table to 20 dimensions. The fact table has 32 million rows and 30 columns. This is a temporary staging table so I don't have to deal with other users reading or writing to the table. I select 10 columns from the base table and 20 columns from the respective dimensions. The dimension tables are small (between 3 and 15.000 rows). The fields on which are joined are both integers and nvarchars. I use a SELECT ... INTO statement. There are no indexes on the tables.

The execution speed of this query is too slow to be useful.

Tried-out solutions

Because the query takes too long to process, I tried out following solutions:

Split the 20 joins into 4 joins on 5 tables. The query performance remains low however.
Put indexes on the foreign key columns. No significant time decrease.
Make sure the fields of the join condition are integers. I noticed a performance increase of 25%. Not quite what I am searching for.
Use an insert into statement instead of select into. Worse performance because of log file growth although the database is in simple recovery mode.

These findings led me to including the actual execution plan which shows that 89% of the cost lies in the table insert. The other costs are 8% table scan on the fact table and 2% on hash matching for the inner joins.

Questions

What are the possible reasons of the slow table insert?
What are ways to identify this bottleneck without the execution plan?
What actions can I take to reduce the cost of the table insert?

What are the possible reasons of the slow table insert?
  What are ways to identify this bottleneck without the execution plan?

Read How to analyse SQL Server performance, specially the part about Analyzing individual query execution wait times.

  What actions can I take to reduce the cost of the table insert?

That would depend largely on the result of the performance analysis. First and foremost, ensure the SELECT part is as fast as possible. Assuming that problem is the single threaded fully logged insert, some solutions are:

Use partition switch to move 'in' the data. This is, by far, the best solution. Prepare the staging data in a separate staging table, then switch this staging table into the DW table. Read Transferring Data Efficiently by Using Partition Switching.
Make sure the INSERT is minimally logged. Read Operations That Can Be Minimally Logged and Prerequisites for Minimal Logging. Even if you use partition switch operations, is still worth making sure the build of the staging table is minimally logged.
Make sure your IO subsystem is capable of driving a fast load. Read Introducing SSDs.


Handling NULL in SQL Server I am facing issue when trying to handle null.

Here is example

I have a table

Create table Test_Table
(
id int null,
pName varchar(10)
)
GO

Id column has NULL value.

INSERT INTO Test_Table
SELECT NULL, 'A' UNION
SELECT NULL,'B' UNION
SELECT NULL, 'C' UNION
SELECT NULL,'D'
GO

Now when I execute different select statements, last two conditions are failing. I can use ISNULL to handle it but there are approx 500 SPs which I would need to change.

So, I need solution apart from that, if possible.

SELECT * FROM Test_Table WHERE id is NULL --True

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=NULL --True

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=id --False. Why???

Declare @ID INT = 0

SET ANSI_NULLS OFF
Select case when @ID > 0 then @ID else id end as Statements, *
From   Test_Table
Where  ID = case when @ID > 0 then @ID else ID END -- Fails. Why??

When Id=NULL is true then why Id=Id is false??

Thanks!

Have a look at remarks on MS Docs about IS NULL:

  Remarks
  To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=).
  Comparison operators return UNKNOWN when either or both arguments are NULL.

And at SET ANSI_NULLS MS Docs:

  SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

Let me add some other records to your table:

INSERT INTO Test_Table
VALUES (NULL, 'A'),(NULL,'B'),(NULL,'C'),(NULL,'D'),(1,'E'),(2,'F');

Now, your query:

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=id;

Returns:

id | pName
-: | :----
 1 | E
 2 | F

And the same for your second query:

Declare @ID INT = NULL

SET ANSI_NULLS OFF
SELECT CASE WHEN @ID > 0 THEN @ID ELSE id END AS Statements, *
FROM   Test_Table
WHERE  ID = CASE WHEN @ID > 0 THEN @ID ELSE ID END
GO

Statements | id | pName
---------: | -: | :----
         1 |  1 | E
         2 |  2 | F

Null indicates lack of value and you must explicitly check for NULL values using IS NULL:

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id IS NULL;

dbfiddle here


Postgres: foreign key constraint for components of a composite type? In Postgres, is it possible to set up a foreign key contraint involving components of a composite type, in a situation like the following one?

Suppose I have

CREATE TABLE main (
    -- a and b are separate fields
    a INTEGER,
    b INTEGER,
    PRIMARY KEY (a,b)
);
CREATE TYPE abtype AS (
    a INTEGER,
    b INTEGER
);
CREATE TABLE sub (
    -- a and b are components of a composite type
    ab abtype,
    PRIMARY KEY (ab)
);

How do I set up a foreign key constraint specifying that sub.ab references main.(a,b)?

The following attempts are all syntactically wrong.

ALTER TABLE sub ADD CONSTRAINT sub_ab_fkey FOREIGN KEY
... (ab) REFERENCES main(a,b);
... (ab) REFERENCES main((a,b));
... (ab) REFERENCES main((a,b)::abtype);
... (ab) REFERENCES main(a,b)::abtype;
... ((ab).a,(ab).b) REFERENCES main(a,b);
... (ab.a,ab.b) REFERENCES main(a,b);

As I pointed out in comments, you could add a new composite column in main table:

CREATE TYPE abtype AS (
    a INTEGER,
    b INTEGER
);

CREATE TABLE main (
    -- a and b are separate fields
    a INTEGER,
    b INTEGER,
    ab abtype generated always as ((a,b)) stored,
    PRIMARY KEY (a,b)
);

CREATE TABLE sub (
    -- a and b are components of a composite type
    ab abtype,
    PRIMARY KEY (ab)
);

And set a unique index:

create unique index on main (ab);

Now you can reference your FK to this column:

ALTER TABLE sub ADD CONSTRAINT sub_ab_fkey FOREIGN KEY (ab) references main(ab);

db<>fiddle here


Missing commands in MySQL command line history Why aren't all command that are entered in the MySQL command line saved in the command history?

For example, entering the commands

DROP DATABASE mydb;
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8;
GRANT ALL ON mydb.* TO username@host IDENTIFIED BY 'password';

and then trying to go through the history by pressing   ▲   I only see the first two commands.

I'm running MySQL Ver 14.14 Distrib 5.7.17, for osx10.12 (x86_64) installed with Homebrew

There is an option to prevent some commands that contain specific words from being logged. One common practice is not to log commands that has the word password.

This option is called histignore

More info:

https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_histignore
https://dev.mysql.com/doc/refman/5.7/en/mysql-logging.html


Execute a query which returns uniformly at maximum 100 records I have a table in which every record has an ID, a date and a value.
There are a lot of records, and I want to get those dates and values, to create a graph. But I do not want to have more than 100 points.

What I need to do, is if for example there are 300 records, one for every day, to execute a query which will take 100 records, but uniformly. I mean if I have for example

23, 1 January 2014, 5
34, 2 January 2014, 54
45, 3 January 2014, 35
56, 4 January 2014, 55
67, 5 January 2014, 35
78, 6 January 2014, 56
89, 7 January 2014, 53
90, 8 January 2014, 55
94, 9 January 2014, 57

For example, If I wanted 3 results maximum, my query should return for example the record for 1 January 2014, 4 January 2014 and 7 January 2014. How is it possible to do that? I cannot think anything...

You could assign a row number to your results, then show every Xth record from that result.  You do this by calculating the total number of records, and dividing that by your desired number of intervals.  If the modulo is 0, spit out that record.

In my example below, I want to 10 records (approx) from the sysobjects table, sorted alphabetically by name.  The result is 10 records.

This was a lot harder to explain than to write.

;with SummaryTable as
(
select row_number() over (order by name) as RowNum,
       name,
       (select count(*) from sys.objects) as 'TotalCount'
 from sys.objects
)
select *
  from SummaryTable
 where RowNum % (TotalCount / 10)  = 0


MySQL - reformat a column value dependant on existing value Am needing to provide a concatenated field from a select query which is dependant on existing values.

SELECT `delegate`.`FirstName` AS `First Name`,
   `delegate`.`LastName` AS `Last Name`,
   `organisation`.`Name` AS `Organisation`,
    IF (`delegate`.`PhoneWork`  IS NOT NULL, 'Yes','No') AS `WorkPhone`),
   `delegate`.`EmailAddress` AS `Email Address`
FROM `delegate`
JOIN `organisation` on((`delegate`.`OrganisationID` = `organisation`.`ID`))

Can anyone point me in the right direction?

In Mysql you can use CONCAT function to concatenate values ,you can use your logic in if() to show yes/no according to the criteria.if you want to show the value from column when its not empty then replace 'Yes' with your column name

SELECT
   `d`.`FirstName` AS `First Name`,
   `d`.`LastName` AS `Last Name`,
   `o`.`Name` AS `Organisation`,
    IF (`d`.`PhoneWork`  IS NOT NULL AND `d`.areacode IS NOT NULL,CONCAT(`d`.`PhoneWork`,' ',`d`.areacode),'your value') AS `concat_colum`,
    IF (`d`.`PhoneWork`  IS NOT NULL,'Yes','No') AS `WorkPhone`,
    IF (`d`.`areacode`  IS NOT NULL,'Yes','No') AS `areacode`,
   `d`.`EmailAddress` AS `Email Address`,
FROM `delegate` d
JOIN `organisation` o ON(`d`.`OrganisationID` = `o`.`ID`)

You can do the same by using CASE too

SELECT
   `d`.`FirstName` AS `First Name`,
   `d`.`LastName` AS `Last Name`,
   `o`.`Name` AS `Organisation`,
    CASE WHEN
    `d`.`PhoneWork`  IS NOT NULL AND `d`.areacode IS NOT NULL
    THEN CONCAT(`d`.`PhoneWork`,' ',`d`.areacode)
    ELSE 'your value' END  AS `concat_colum`,
    CASE WHEN `d`.`PhoneWork`  IS NOT NULL THEN 'Yes' ELSE 'No' END AS `WorkPhone`,
    CASE WHEN `d`.`areacode`  IS NOT NULL THEN 'Yes' ELSE 'No' END  AS `areacode`,
   `d`.`EmailAddress` AS `Email Address`,
FROM `delegate` d
JOIN `organisation` o ON(`d`.`OrganisationID` = `o`.`ID`)


Group by Hour within start enddate Im hoping you guys can help me.

I have a table full of users logging in and out.
Example row:
username,startdatetime,enddatetime

Im trying to select hourly how many users were active.
I Found the following:

select Time_Stamp_Hour=dateadd(hh,datepart(hh,StartDateTime), cast(CAST(StartDateTime as date) as datetime)), Count(*) as amount
from dbo.Sessions
group by dateadd(hh,datepart(hh,StartDateTime), cast(CAST(StartDateTime as date) as datetime))
order by Time_Stamp_Hour desc

This indeed groups together the amount of sessions started per hour. but im looking for how many would be active.  i cannot seem to figure out how i would build a query like that.
So bassicly grouped per hour a count which falls between Start and End datetime

You can create a table of 24 rows representing the 24 hours in a day to check the number of active users in each hour. Example below.

NOTE: In my example, I only really have data for a single day. You can add a WHERE clause to the subquery to filter larger data sets to a single day to check active users for a single day.

Setup:

CREATE TABLE #Hours (Hour INT)

;WITH CTE AS (
    SELECT 1 AS Level
    UNION ALL
    SELECT 1+Level
    FROM CTE
    WHERE Level <= 23
)

INSERT INTO #Hours (Hour)
SELECT Level
FROM CTE

CREATE TABLE #Sessions (UserID INT,
    StartDate DATETIME,
    EndDate DATETIME
)

INSERT INTO #Sessions (UserID, StartDate, EndDate)
VALUES
(1, '2019-05-29 08:00:00', '2019-05-29 16:00:00')
,(2, '2019-05-29 10:00:00', '2019-05-29 21:00:00')
,(3, '2019-05-29 02:00:00', '2019-05-29 08:00:00')
,(4, '2019-05-29 15:00:00', '2019-05-29 23:00:00')
,(5, '2019-05-29 09:00:00', '2019-05-29 17:00:00')
,(6, '2019-05-29 13:00:00', '2019-05-29 18:00:00')
,(7, '2019-05-29 04:00:00', '2019-05-30 00:00:00')

Query:

SELECT h.Hour,
    COUNT(UserID) AS [ActiveUsers]
FROM
(
    SELECT UserID, StartDate, EndDate,
        CASE
            WHEN DATEPART(HOUR, StartDate) = 0 THEN 24
            ELSE DATEPART(HOUR, StartDate)
        END AS StartHour,
        CASE
            WHEN DATEPART(HOUR, EndDate) = 0 THEN 24
            ELSE DATEPART(HOUR, EndDate)
        END AS EndHour
    FROM #Sessions
) s
FULL OUTER JOIN #Hours h ON h.Hour BETWEEN StartHour AND EndHour
GROUP BY h.Hour

Results:

Hour    ActiveUsers
-------------------
1       0
2       1
3       1
4       2
5       2
6       2
7       2
8       3
9       3
10      4
11      4
12      4
13      5
14      5
15      6
16      6
17      5
18      4
19      3
20      3
21      3
22      2
23      2
24      1


Efficient Value Update with Large Database I have a table as large as 8GB and with 20M records. There is an int field called mth. The mth field stores date information in form of YYYYMM, I want to transform the mth field to consecutive integers. So I use a formula to get the year and month from field mth and calculate the month order, specifically, I use the following code:

create function mth_to_num(@month int)
returns int
as
begin
   return(round(@month/100,0)*12+@month-100*round(@month/100,0))
end

Then I use the following code to update the value in the huge table

update full_orig_month_Q1_1999
set mth_order = dbo.mth_to_num(period)
Go

However, the code took quite long time to execute, roughly 2-3 minutes. My system is windows 10 64 bit with SQL server 2016. Is there any way I can speed it up?

Another problem is that I found SQL server occupying as much as 8GB in the database after executing the above query. Does it need so much memory? How can I release them?

Thanks for your help in advance!

Jason

When possible don't use scalar user defined functions (UDF) in queries. They force the entire plan to be serial, they can cause a lot of additional CPU resources to be used, they are a black box to the optimizer and can lead to cardinality estimation issues in other parts of the plan, and they can result in needlessly large memory grants.

Try running your UPDATE query without the UDF. Just take the code that you have and put it into the UPDATE query directly. Something like this:

update full_orig_month_Q1_1999
set mth_order = round(period/100,0)*12+period-100*round(period/100,0);

Does that improve the runtime and memory usage?


Sql*Plus ERROR: ORA-12154: TNS:could not resolve the connect identifier specified I installed Sql*PLUS on a cloud Oracle Linux Environment in order to access an remote database. I used the installation instructions provided as an answer here: https://stackoverflow.com/questions/23488394/how-to-install-sql-plus-client-in-linux

My connection string is correct, but I get the error

ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

I tried resolving this issue by attempting to looking at my tnsnames.ora, but it did not exist and the path connected to my $ORACLE_HOME did not have the subfolders "/network/admin/". In order to resolve this, I created both folders and created a tnsnames.ora file in the new admin/ containing my connection string. Unfortunately, I still get the same error.

The path to my new tnsnames.ora file being:
/usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora

For reference my connection string is structured the following way:

(DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST= asdasdasd.com)(PORT=1524))
    (CONNECT_DATA =
      (SID = AARCER1)
    )
  )

And my command I am putting into my terminal is:
sqlplus "userName/myPassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))"

What should I do to resolve this error?

When you use sqlplus userName/myPassword@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))" then you don't need any tnsnames.ora file.

Your entry in tnsnames.ora file should be like this:

AARCER1 = (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST= asdasdasd.com)(PORT=1524))
    (CONNECT_DATA =
      (SID = AARCER1)
    )
  )

Then you can shorten your command to sqlplus userName/myPassword@AARCER1 - that is the purpose of tnsnames.ora.

In order to use tnsnames.ora you must also have file sqlnet.ora.
There you need an entry like this:

NAMES.DIRECTORY_PATH = (TNSNAMES)

However, default value is NAMES.DIRECTORY_PATH=(tnsnames, ldap, ezconnect), so actually you can skip it.

Set environment variable TNS_ADMIN to location of these files, i.e.

export TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin


How to check user defined schemata creation date in SQL server I want to find Schema creation date and user who created schema in SQL server,
I am unable to find information in  system views.

Note: Not asking about schema scoped objects

You may find the information in sys.traces as long as the information not reset which happens upon service restart.

If that's not the case, try following query, but this is quick solution, the proper solution in your case is Extended Events which stores the selected trace events information outsize SQL Databases, so that you can retrieve  and monitor such event info whenever you need since the captured data won't be retested upon service restart.

For more details on all event classes and sys.traces

select PATH, Trace.*
from sys.traces AS T
        CROSS APPLY fn_trace_gettable(T.path, default) as Trace
Where ObjectName like '%YourSchema%'
go


How do I store World/event data in a treelike structure SQL/SQLite3 I would like to create a database which contains tables of the following form, where each record in a table, is a table in and of itself. Ideally at any one time I would be able to query any table of the following format. e.g I would be able to lookup/export Paris table and see names of persons and other data contained in that table.
I've currently not found a solution for this use case and any help would be much appreciated
Continent | Occurrences in continent|
Europe. ....
...
Country | Occurrences in country
France ...
...
City | Occurrences in city
Paris. ...
...
Name of persons in City | number of occurrences | City | Country | Continent | Unrelated data
...
I'm not too familiar with building databases and I think one issue I have is that currently, the only unique identifier would be names of persons for each city. Naturally, the number of occurrences in each continent would be the sum of the occurrences for each country in that respective continent and so on. I would like the db to work such that an increase in number of occurrences in the lowest table would be reflected in all tables.
So far I've created the relevant tables at each node but I don't know how to relate them to one another

I have seen a reporting database that had join tables that assigned a city to an administrative district (county), the administrative districts are assigned to another, bigger district (state), which is assigned to a country. The nesting action is done through the queries, instead of trying to have Russian Nesting Dolls of tables inside of tables.
Example:
CityID | CityName | DistrictID
1        New York   5
DistrictID | DistrictName  |  Province\State
5            New York County  7
ProvinceID   |ProvinceName|  CountryID
7             New York        127
CountryID | CountryName
127            USA


Is it possible to prevent data loss with Postgres WAL archiving? As I understand from the docs, a WAL segment is archived when the segment file reaches some size or due to archive_timeout.

So if the database crashes, all the data from the incomplete, currently active WAL segment would be lost (assuming we don't have any access to that incomplete segment in the Postgres directory).

Do I get it right and if so, is there a way to avoid this data loss?

You are correct.

The way to avoid this is to use pg_receivewal, which streams WAL information from the database server and persists it in WAL segment copies immediately.

By default this is asynchronous, so you could lose some split seconds in case of a crash. It you need to avoid that, you'd have to use synchronous replication with pg_receivewal, but be warned that

This makes DML statements slower, since COMMIT cannot succeed until it has heard back from pg_receivewal.
you shouldn't set synchronous_commit = remote_apply, because pg_receivewal never applies the changes, it just saves them to a WAL file.


DB2: how to export data file and own by db2inst1 via ADMIN_CMD There is some requirement that I need to run the export command in Java via JDBC. Hence below is the command I execute

CALL SYSPROC.ADMIN_CMD('EXPORT TO /tmp/header.csv OF DEL SELECT * FROM MYTABLE');

However, when I ssh login to the database server via db2inst1, I checked the /tmp/header.csv is own by db2fenc1 user, which not allow me to delete but read-only access as shown below:

[db2inst1@26ea60b7e4a1 /]$ ls -lrt /tmp/header.csv
-rw-r--r-- 1 db2fenc1 db2fadm1 24335 Nov 12 12:25 /tmp/header.csv

May I know how to execute ADMIN_CMD as db2inst1? Or any other workaround?

This is by design; ADMIN_CMD and other fenced routines execute using the fenced user authority in order to protect ("fence") the instance from buggy or malicious code.

Some people set the fenced user to the instance owner ID during the instance creation, which would cause fenced routines to run with the instance owner authority. However, this creates a serious security vulnerability.

It's better to ask your system administrator to allow the instance owner limited sudo or runuser rights to execute the necessary commands as db2fenc1.


Database system checksumming to prevent network corruption not caught by TCP checksumming Since TCP checksumming is not sufficient to detect/prevent all corruption, do modern database systems (e.g. SQL Server, MariaDB / MySQL, Oracle, etc.) use any additional checksumming of network traffic as part of their client/server protocol?

I did some searching but wasn't finding anything which made me wonder if perhaps they don't and just roll the dice.

Got a good answer on Oracle. Would like to hear about SQL Server and MariaDB  / MySQL as well.

Oracle has a variety of encryption and integrity features that are a part of its network protocols.

See Configuring Encryption and Integrity Parameters Using Oracle Net Manager in section 13 Configuring Oracle Database Network Encryption and Data Integrity of the Oracle Database Security Guide.


Raise Error Concatenation in SQL Server Step 1 :I created a custom error message in sys.messages

EXEC sp_addmessage
    @msgnum = 50001,
    @severity = 10,
    @msgtext =
        N'Error 50001, severity 10, state 1 was raised, Please check mdf file.' ,
    @lang = 'us_english',
    @replace='REPLACE';

Step 2: Inside a Job when a condition is met I am raising the error

RAISERROR(50001, 10,1,@freespacePct, @dbname,@drive) with LOG;

I would like to see FreespacePCt,database name and drive details inside the error message.

But when I look at the raised error I am just seeing just what I declared inside the sp_addmessage. How can I log the other details?

Message I am seeing :

  Error 50001, severity 10, state 1 was raised, Please check mdf file.

You need to add some parameters to your @msgtext so SQL Server knows where you put your data. Something like so:

EXEC sp_addmessage
    @msgnum = 50001,
    @severity = 10,
    @msgtext =
        N'Error 50001, severity 10, state 1 was raised, Please check mdf file. The freespace is %d percent for %s residing on %s' ,
    @lang = 'us_english',
    @replace='REPLACE';

Then, you would raise it....

declare @freespacePct int = 45
        ,@dbname sysname = 'MYDB'
        ,@drive varchar(16) = 'F:/'
RAISERROR(50001, 10,1,@freespacePct, @dbname,@drive) with LOG;

That would return:

  Error 50001, severity 10, state 1 was raised, Please check mdf file.
  The freespace is 45 percent for MYDB residing on F:/

The %s is used for strings, %d is used for signed integers. You can find all of the type specifications in the RAISERROR documentation.

Type specification  Represents
d or i              Signed integer
o                   Unsigned octal
s                   String
u                   Unsigned integer
x or X              Unsigned hexadecimal

  These type specifications are based on the ones originally defined for
  the printf function in the C standard library. The type
  specifications used in RAISERROR message strings map to Transact-SQL
  data types, while the specifications used in printf map to C language
  data types. Type specifications used in printf are not supported by
  RAISERROR when Transact-SQL does not have a data type similar to the
  associated C data type. For example, the %p specification for pointers
  is not supported in RAISERROR because Transact-SQL does not have a
  pointer data type.


Merge Replication Invalid Object ( on an object that no longer exists ) ComErrorException My merge replication scheme has suddenly started failing.  When trying to sync it complains that there is an invalid object.  'dbo.myfunction'.  The function used to exist but was removed.  There was only one reference to it from a table valued function and it has been updated as well.  I've generated a new snapshot but it continues to complain that the object is invalid and will not sync at the subscriber.

Somewhere within the replication it's still looking for that function.  How do I find and remove it - when it's not there anymore.

I've even looked through the files in the snapshot and cannot find anywhere that it is being referenced.

My publisher is SQL 2008 R2 - all my subscribers are SQL 2008 Express

From what I can tell... I had a table valued function that used to require another table valued function as part of it's logic.  That behavior was improved (combining the two tbf's into one) and because our replication included schema changes the improvement was pushed out to subscribers and the extra tbf object was removed... which was then also pushed to subscribers.

A few weeks later when a subscriber tried to reinitialize however it went back to the original tbf and then couldn't find the other one that it relied on (before it got to the incremental change that told it, it no longer needed the other one)... and simply died.

I was able to recreate the behavior and I could see the old version of the tbf appearing in the subscriber as it tried to reinitialize.  So, moral to the story is... replicating a work in progress is delicate.


Any possible way to encrypt SQL Server 2012 database or backup? We don't have SQL Server 2014 or later versions, so I can't take advantage of the backup encryption enhancement available in those. But I would like to know if there are any ways to use any type of encryption for a SQL Server 2012 databases. It could be encrypting the database, backup, or any other object. Any help is appreciated along with the scripts to do so if available.

There are plenty of ways to utilize encryption in SQL Server 2012.  Here is an article that goes into further detail:
http://sqlmag.com/database-security/sql-server-encryption-options

Note, transparent data encryption is only available in Enterprise edition.
https://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx


What is the meaning of "select * from relational(owner.table)"? We recently got some errors in our alert log of the form

301598: Tue Jul 16 03:58:29 2013
301599: ORA-01555 caused by SQL statement below
301600: SELECT * FROM RELATIONAL(<owner>.<table>)

I know how to handle ORA-01555 errors, but I never encountered something like SELECT * FROM RELATIONAL. I looked in DBA_OBJECTS, but there is no such function/operator/whatever.

Googling for 'SELECT * FROM RELATIONAL' only returned questions related to ORA-01555.

So, my question is: What is the meaning of the RELATIONAL keyword here?

It appears to be inert. I tested this on XE 11 and found that it does nothing.

CREATE TABLE dbausers AS SELECT * FROM dba_users;

-- Function doesn't affect result set.
SELECT * FROM dbausers
MINUS
SELECT * FROM RELATIONAL(dbausers);

SELECT * FROM RELATIONAL(dbausers)
MINUS
SELECT * FROM dbausers;


Updating rows from other rows in a huge table I have a huge table with items

item-id | name | type | extra

Many of them are "old versions" of type 0.

I have to update the extra field in all new items (same item-id, but type > 0) with the extra data we have for the old version.

Since this is a very large table with millions of rows, I have to be very careful with joining the table with itself.

Basically I want to do this

update mytable t1 set extra = (select extra from mytable where t1.itemid = mytable.itemid and type=0) where type > 0;

But MySQL doesn't accept t1 in the sub-select.

Recently read an article that might help you, see: https://dzone.com/articles/specifying-target-tables-in-mysql

Still think a self join would be faster than a correlated sub-query. Especially on MySQL 5.5 and less, where these type of sub queries are badly optimized.

Self join like:

UPDATE mytable t1
  JOIN mytable t2
    ON t1.item-id = t2.item-id
SET t1.extra = t2.extra
WHERE t1.type > 0;


Do you connect an application to the load balancer name or the cluster name? My DBA delivered a 2 server WSCF for me to connect my sharepoint farm to.  I have a load balancer name and a windows cluster name, and 2 server names.  I realize I need to connect either to the cluster name or the load balancer name but which would be the correct choice and why?

Let's assume this is just a Windows Server Failover Cluster and that there is no Always On availability group involved.

The terms are often used somewhat interchangeably, so I don't know what your DBA or server admin calls them. But in the Failover Cluster management console, there are the following terms and concepts.

Cluster Name. This is the name for the Windows Server Failover Cluster, and is independent of any resources (SQL in your case, but other resources (DHCP, file server, generic services) can be added to a cluster). This is not what you want to point any SQL-using application to.
Node Name. This is the name of each server in the cluster. This would be used if you want to connect to a specific member of the cluster, without regard to whether the clustered service (i.e., SQL) is running on that node or on another one. Generally used for OS management tasks, this is also not what you want to point your SQL-using application to.
Server Name. This is where it really gets confusing. Within each role of a cluster (SQL Server in your case), there is a resource line item called "Server Name". That's the name (and associated IP address) that the role listens to. That name/address is the one that swings back and forth between the nodes whenever there is a failover. This is the one that you want to point your SQL-using applications to. (The IP address associated with this Server Name, and sometimes the name itself, is often called a "Virtual IP Address" or "VIP", referring to the idea that the IP address doesn't belong to a specific server.)

I suspect that the person providing this information to you is using "load balancer" to refer to the Server Name that you should point your application to. The term "load balancer" is a bit mis-used in this case, because there isn't really any balancing happening -- either by round-robin or by resource capacity.


MySQL innodb: if all the tuples are stored in the clustered Index then why do we call it index? So not sure If I am confused or not, but  based on these websites :

https://docs.oracle.com/cd/E17952_01/mysql-5.1-en/innodb-table-and-index.html

http://www.mysqltutorial.org/mysql-index/mysql-clustered-index/

  Every InnoDB table has a special index called the clustered index where the data for the rows is stored.

the actual table data is stored in the clustered Index

but i don't understand, i thought indexing was a key-pointer pair stored in a tree for faster lookups, and doesn't store the actual data hence the name Index, like a book index.

if the entire data is in the index then why call it Index?! and wouldn't this slow down a lot of things?

if the actual table is in a b+tree index then just adding a simple tuple or deleting one is logn, is this true or am i mistaking something here?

Considering .IBD files contain the actual data in the tables and .frm files are some sort of metadata (correct me If Im wrong), then which one of these is the clustered index aka the actual table?

and i couldn't find anything useful regarding this in their own documentation.

Let's approach it from two other perspectives...

The data needs to be stored somehow.  Perhaps unordered, perhaps based on a ROWNUM, or InnoDB's approach of ordering it according to the PRIMARY KEY.
In InnoDB the data structure and an INDEX structure are virtually identical -- both are BTrees of 16KB blocks with the 'items' ordered according to some KEY.  (I suggest that the documentation has confused you because it is hinting at the symmetry without enough background.)

Ordering the data according to one of the indexes (the PK) is actually a performance advantage for some queries.  In particular, if you want to look up a row by the PK, you can't get any faster.  If you want to scan a subset of the rows and do it in PK, order, you can do it any faster.

I assume you understand a BTree?  (See Wikipedia.)  InnoDB actually uses a B+Tree, very similar to a BTree, but adds links to make range scans more efficient.

Aside from nodes arranged in a tree, what is in the "leaf" nodes?

For the data / PRIMARY KEY, the leaf nodes contain all the columns of each row.  This is what is meant by "clustered".

For a secondary key (any index other than the PK), the leaf nodes contain a copy of the columns of the PK.  So, to look up a row via a secondary key, first you dig into its BTree to find the PK of the actual row, then you dig into the data to find the row.

One optimization is to use a "covering" index.  This is when all the columns needed anywhere in a SELECT are in a single index.  Guess what?  Now a Secondary index acts like a "table".  You don't need to bounce over to the data/PK BTree.

Each BTree for one table has its nodes stored in the .ibd file (assuming innodb_file_per_table=ON).  Each BTree is separate, aside from the PK linking.  (Think of ibdata1 and "tablespaces" are collections of tables thrown into a single disk file.)

You mentioned "pointer".  Mostly InnoDB gets away from pointers.  In the PK case, you arrive at the data; no need for a "pointer".  In the case of a secondary key, the copy of the PK serves as the "pointer".

As a Rule of Thumb, the nodes of either type of BTree have about 100 subnodes.  This implies that the BTree for a million-row table or index is about 3 levels deep.  (Think "log-base-100".)

Since fetching blocks is far more costly than rummaging through a block, counting disk hits is the main performance metric in an I/O-bound InnoDB table.

(MyISAM is radically different.)

(From comments:)

An InnoDB with just a PRIMARY KEY contains a single BTree.  The data is sorted according to the PK.  Lookups by PK are optimal.  That is, 'entire table and its data is in the cluster index structure'.

Inserting into such a table has multiple situations:

If you are 'randomly' inserting into the table, then the operation is technically Order(log n), such as 3 for a million-row table.  For all practical purposes, you can pretty much ignore drilling down the BTree.

A typical case is having id INT AUTO_INCREMENT PRIMARY KEY.  In this case, inserts are (normally) at the 'end' of the table.  (I do not happen to know if there is any optimization to avoid the repeated drill-down, but since this is a very common situation, I expect the developers have done a good job.)

Is an insert-heavy table inefficient?  Not really.  INSERTs have to go somewhere.  Granted, the typical table is read more than it is written, but still, "don't worry".

If you are doing thousands of INSERTs per second, there are various techniques to speed things up.  However, they focus on transactions, parsing, scattering, etc, not on the structure of the PRIMARY KEY.  One notable exception:  if the PK is a UUID, and the table is huge, you will be I/O-bound and probably have big problems.


How to limit the connections per host in MongoDB? I'm running a mongo server which accepts connections from other clients through java driver. The thing I have noticed is after a while some users open too many ports, and this prevents other users to connect to the mongo. They create only 1 mongoClient object, though checking their IP result in watching hundreds of ports.

I came across an example to limit the connection per host in the java driver, but I don't want the client to mess with it. How can I limit the client from my mongod instance?

The instance is 1 mongod that runs on a Linux remote server.

I've listed some alternatives for connection management below, in order of most to least recommended.

Increase the connections allowed on the server

The total incoming connection limit on the server is determined by the lesser of the limits imposed by the operating system or maxIncomingConnections (aka maxConns in MongoDB 2.4 and earlier).

Typically Linux distributions limit file descriptors per process to 1024, of which MongoDB will use 80% for incoming connections (leaving about 819 available connections).

You can check current and available connections in the mongo shell via:

db.serverStatus().connections

For production systems it is typical to adjust the ulimit settings on Linux to allow more concurrent connections. For more best practices, I would recommend reviewing the Production Notes in the MongoDB manual.

Provide an API

If you are managing a shared server with resource limits, it is common to provide your own API rather than direct database access. This approach gives you an extra layer of abstraction so you can manage resource usage and server deployment independent of the client configuration. For example, you could move your database server or reconfigure from a standalone to a replica set, and the clients would not have to be aware of this. You can also manage custom resource limits (such as connections per client) via your API, based on the credentials the client uses to connect.

Reduce the connection pool size in the clients

MongoDB (as at 2.6) doesn't have an option to limit the connections per client. Normally client limits would be imposed via the driver (i.e. setting the connection pool size). For example, in the Java driver the MongoClient default maximum pool size is 100.

You've already suggested this isn't a desirable option as you don't want the clients to mess with the connection limits, but if you are going to impose a server side limit it would still be reasonable to have them set the pool size appropriately. Otherwise their applications will get frequent exceptions as you kill off excess connections.

Monitor client operations

If adjusting limits on the client or server isn't an option, an alternative to consider is implementing a script to count concurrent client connections (by IP) via db.currentOp() and kill excess connections via db.killOp(). You'd have to be very careful to only kill client requests. The killOp() command is a superuser command that will let you kill internal database threads as well (which can lead to unpredictable outcomes).

NOTE: This approach will be unsuccessful if your clients are connecting via a shared gateway (i.e. where the source IP does not uniquely identify a client).


Databases design decision - Mobile & Web This may be a question a little broad but... I'm currently having the following dilemma:

Having a web application (already deployed) and a new mobile app, I'm currently working on the mobile app database of the server side and my question is... Do you think it is better to have separate databases for the web & app, or is it better to integrate them in a single one?

I can think of pros & cons for every approach. For example, a single DB is easier to update and mantain. However, keeping them separated gives every app some sort of independence, allowing you to, for example, change the DBMS of any of them, if you need to...

I know it's a bit difficult to respond without knowing the databases design, context, etc. but what is your general thought? and what do the "big companies" usually do?

EDIT: Also, for example, if we wanted to scale up the mobile app in the future and migrate it to another server... would it be harder in any of the previous scenarios?

You can consider the web and mobile as just delivery interfaces to the functionality/services for your application. With this regard the backend functionality (server side - processing etc), should be the same for both interfaces, so yes they should have the same database.

You may want to add data to identify the sources of data addition or changes or traffic, but essentially its the same app.

UPDATE:
Think of a mobile app just like a separate UI to be supported, I am not going to exhaust the possibilities but look at potential list of possible interfaces below:

Web Application - available
Mobile App - is this HTML 5 web app, Android or
IOS? each may require its own 'different infrastructure'
Desktop app - for a subset, administration, operations
Web Services (REST, SOAP) - for machine to machine communication which does not have a definite UI per se, but may be provided to 3rd parties to develop their own apps (even the 3 UIs above can be considered as such)

The way to think about it is that there is one application with a single database which is being accessed through multiple channels, whether from the same server or different servers


is it safe to compress backups for databases with TDE enabled? I've been reading about this for a long time, and it seems it's not safe to compress a backup when the database has TDE enabled.
is anyone experiencing errors during restore with compressed backups for tde databases?
I've never experienced these kind of errors but i'm really worried about it.
It seems the problem is with SQL SERVER 2016, and as I'm using 2012, I can't see any problems.
Also, even using MAXTRANSFERSIZE = 131072 the backup is not compressed. what could It be? I'm using NO CHECKSUM and WITH CHECKSUM both with the same size.

I've been reading about this for a long time, and it seems it's not safe to compress a backup when the database has TDE enabled.

You asked about safety so replying. Using backup compression with TDE is safe and I have been using it quite a lot. What doesn't works well, for version SQL Server 2014 and below is the backup compression mileage (the amount of compression) you would get for versions below SQL Server 2016 is not great, let me put that the backups are slightly compressed as compared to backup compression on non TDE enabled database. From the Docs which Andrew Sayer shared (Backup compression with TDE
Starting from SQL Server 2016

Starting with SQL Server 2016 (13.x), setting MAXTRANSFERSIZE larger than 65536 (64 KB) enables an optimized compression algorithm for Transparent Data Encryption (TDE) encrypted databases that first decrypts a page, compresses it, and then encrypts it again. If MAXTRANSFERSIZE is not specified, or if MAXTRANSFERSIZE = 65536 (64 KB) is used, backup compression with TDE encrypted databases directly compresses the encrypted pages, and may not yield good compression ratios.

Starting from SQL Server 2019

Starting with SQL Server 2019 (15.x) CU5, setting MAXTRANSFERSIZE is
no longer required to enable this optimized compression algorithm with
TDE. If the backup command is specified WITH COMPRESSION or the backup
compression default server configuration is set to 1, MAXTRANSFERSIZE
will automatically be increased to 128K to enable the optimized
algorithm

is anyone experiencing errors during restore with compressed backups for tde databases?

Show me the error, you must be restoring TDE enabled database without certificates. My best guess


Capture when any database is restored on the server Is there any way that I can identify when a database is restored on the server? I created a server trigger but only triggers when I create a new database not after is restored from another one.

I'm using SQL Server 2014, trying to achieve this using MSMS, using msdb.dbo.restorehistory is good but I don't want to create a job for that, trying to find something straightforward

I want to know as s DBA when any database is created or restored, just for control.

I found that there is no DDL trigger for RESTORE, there is however Event Notification for the AUDIT_BACKUP_RESTORE_EVENT but I didn't find any good example of how to use it.

could someone tell me which accounts I need to merge? I'm confuse, I did it with my account on stackoverflow and it says is already linked and I can't continue

Under the assumption that you are using SQL Server, you can create a trigger against the msdb..restorehistory table.

Go ahead and query that table to see what information is in there; I think you can achieve what you are doing.

See Know when a database has been restored


How to assign privileges on a postgresql schema to a user? pg_hba.conf contains:
...
host 10.10.10.10 service_db admin md5
...

Logged in as root, executed the following commands:

###CREATE DB ADMIN
sudo -u postgres psql -p 5432 -d postgres -c "CREATE USER 'admin' ENCRYPTED PASSWORD 'admin_pwd';"
###CREATE DB
sudo -u postgres psql -p 5432 -d postgres -c "CREATE DATABASE service_db OWNER 'admin' ENCODING 'UTF-8' TABLESPACE service_ts LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' LIMIT -1;"
###CREATE SCHEMA
sudo -u postgres psql -p 5432 -d service_db -c "CREATE SCHEMA 'service_schema';"
###REVOKE PREVILEGES ON service_schema from public
sudo -u postgres psql -p 5432 -d service_db -c "REVOKE ALL ON SCHEMA 'service_schema' FROM 'public';"
###GRANT PREVILEGES ON TABLES OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON TABLES IN SCHEMA 'service_schema' TO 'admin';"
###GRANT PREVILEGES ON SEQUENCES OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON SEQUENCES IN SCHEMA 'service_schema' TO 'admin';"
###GRANT PREVILEGES ON FUNCTIONS OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON FUNCTIONS IN SCHEMA 'service_schema' TO 'admin';"

and all commands were executed successfully.

Observed that:
a) Got permission denied error while selecting from a table in that schema in cli, as follows:

sudo -u postgres psql -H 10.10.10.10 -p 5432 -d service_db -U admin
service_db=#
service_db=#select * from service_schema.customers;
permission denied

While connecting and executing the command from Netbeans:

select * from service_schema.customers;

throws error:

Exception, Error Code 0, SQL State 42501
ERROR permission denied for schema admin

Please guide me in resolving the above issue.

Thanks in Advance!

Update:

GRANT USAGE ON SCHEMA... just before GRANT PRIVILEGES... resolved the problem. ie.

###CREATE DB ADMIN
sudo -u postgres psql -p 5432 -d postgres -c "CREATE USER "admin" ENCRYPTED PASSWORD 'admin_pwd';"
###CREATE DB
sudo -u postgres psql -p 5432 -d postgres -c "CREATE DATABASE service_db OWNER "admin" ENCODING 'UTF-8' TABLESPACE service_ts LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' LIMIT -1;"
###CREATE SCHEMA
sudo -u postgres psql -p 5432 -d service_db -c "CREATE SCHEMA "service_schema";"
###REVOKE PREVILEGES ON service_schema from public
sudo -u postgres psql -p 5432 -d service_db -c "REVOKE ALL ON SCHEMA "service_schema" FROM 'public';"
###GRANT USAGE ON service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT USAGE ON SCHEMA "service_schema" TO "admin";"
###GRANT PREVILEGES ON TABLES OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON TABLES IN SCHEMA "service_schema" TO "admin";"
###GRANT PREVILEGES ON SEQUENCES OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON SEQUENCES IN SCHEMA "service_schema" TO "admin";"
###GRANT PREVILEGES ON FUNCTIONS OF service_schema TO admin
sudo -u postgres psql -p 5432 -d service_db -c "GRANT ALL ON FUNCTIONS IN SCHEMA "service_schema" TO "admin";"

works fine for me!

You don't mention which user created service_schema.customers and what permissions were given, if any, to the admin user for that table.

That user is presumably not admin and it presumably gave no permission at all to admin, so that would explain the error.

After creating the schema, the script does that:

GRANT ALL ON TABLES IN SCHEMA service_schema TO admin;

(BTW I've removed the single quotes around the schema name and user name. It's an error to put single quotes around identifiers, only double quotes or no quotes at all are allowed).

Anyway that command doesn't really affect anything because there are no tables yet in a schema that has just been created.

What you probably want is to (quoting the doc here) set the privileges that will be applied to objects created in the future with

ALTER DEFAULT PRIVILEGES IN SCHEMA service_schema GRANT ALL ON TABLES TO admin;

See ALTER DEFAULT PRIVILEGES in the documentation.


how to add a data source to SSRS - reporting services 2014? For infomation as Create, Modify, and Delete Shared Data Sources (SSRS)
but I want to get this done quickly, so when I have been requested to add a new data source to our reporting services:

This is the way I am currently doing it:
I look for another data source that I already have -  (example only - the real server name has been modified)

  http://myreportingserver/Reports/Pages/DataSource.aspx?ItemPath=%2fData+Sources%2fOEL_UK

there I have already another data source working fine,
I get the information like the connection string,
and the credentials (user name of the account running the report) and modify accordingly for my new data source.

then, for creating a new data source I go to the page below and press new data source adding the relevant data.

Question

Isn't there another simpler way of creating a data sources?

I was thinking something like from the ReportingServices database there must be some table with the datasource records, and all the information.
Just copying that info into another record, doing the appropriate changes - and I will have my new data source.

This question shows how to find data sources in the reporting services database

How can I tell which data sources are being used in SSRS?

With Visual Studio with SSDT-BI installed you can create new shared DataSources or copy/paste them.
https://msdn.microsoft.com/en-us/library/ms155845(v=sql.120).aspx


When should you specify PAD_INDEX? So, you can use FILLFACTOR to leave space in leaf index pages. Specifying PAD_INDEX also leaves space in intermediate nodes. What scenarios should you specify PAD_INDEX, and what benefit does it provide to the index?

From Kimberly Tripp's post:

  Typically, PAD_INDEX is not needed unless you have extremely unbalanced activity and are unable to rebalance the tree (meaning you need to achieve high availability).

Read more: http://www.sqlskills.com/BLOGS/KIMBERLY/post/MSDN-Webcast-QA-Index-Defrag-Best-Practices-Fragmentation-Questions-Part-I.aspx

What she means by high availability is that you don't have the ability to run index maintenance jobs that would lock the table.  Personally, I wouldn't recommend ever setting this by default - leave it to DBAs who find that they're unable to manage index maintenance appropriately with any other methods, like using Ola Hallengren's maintenance scripts during off hours.  His scripts are genius - they'll do as much as possible online.


DBMS recommandation for C# applications with provides triggers, procedures and if possible free I'm here because I need somme advices about the best DBMS to use in my case. Indeed,  I've only studied the theory about the databases. So I don't know a lot about the pratice.

I'm searching after a free DBMS if possible, which provides the following tools :

Trigger/Check
Procedure
View

The database must be adapted to be installed on a hard disk locally. This database will be used by C# applications at first time and so with .NET

Which is the easiest to use according to my case ?

Thank you for any help.

I think you have two that I would recommend:

The first is PostgreSQL, which runs on your local computer as a database server.  It has an impressive feature set and allows stored procedures to be written in a number of languages (including PL/PGSQL, PL/Perl, and PL/Python).  It is free and it is enterprise-grade.  PostgreSQL ideas have made their way into Informix and elsewhere.

The second is Firebird which is the open sourced version of Interbase.  It contains triggers, stored procedures, and more, and is free.  The feature set is not as large as it is with PostgreSQL in terms of programmability, but it has the additional feature of being able to be run as an embedded library in the process of your application.  PostgreSQL also has a larger community.


Multi part identifier in Microsoft SQL Server I'm migrating a SQL Server 6.5 database to SQL Server 2012. Migration went well but one of the software using this database performs a strange query:

UPDATE myTable SET Field1='',Field2=0, a.Field3=14 WHERE Field10=1

The problem lies of course with the identifier ('a') before Field3. There are no views, aliases or join with that name but for SQL Server 6,5 that is a valid query.

Since I'm not familiar with SQL Server 6.5, do any of you remember the meaning of this syntax?

The software using this query is an old one of which I don't have any sources (and the company that made it is now closed...), so changing the query is not an option.

The full query is

UPDATE SituazioneMacchina
SET DatiSetPointSpediti=''
,   SpedMappa=0
,   NrRichMappa=0
,   DatiSetPoint=''
,   a.IDProduzione=14
WHERE NumMacchina=1

If I change "a.IDProduzione" with something like "fasdffas.IDProduzione", the query still works in SQL Server 6.5

As you say the query doesn't make any sense.

Probably just a parser bug in 6.5. There have been a few similar other issues ORDER BY nonsensealias.somecolumn worked until 2005 with the SQL Server 2000 behavior being

  Column names in the ORDER BY clause are resolved to columns listed in
  the select list, regardless if they are qualified. For example, the
  following query executes without error:

USE pubs
SELECT au_fname AS 'FName',
  au_lname AS 'LName'
FROM authors a
ORDER BY a.LName

  SQL Server ignores the qualifier a in the ORDER BY clause and resolves
  the column name LName to the select list.

and there is still a similar issue for INSERT statements where the following syntax is accepted.

CREATE TABLE nisse(hult int NOT NULL)
go
INSERT nisse(ab.xx.hult)
VALUES(12)
go
DROP TABLE nisse


SQL Server Distributed Availability Group databases not syncing after a server reboot We're getting ready to perform a large upgrade on our SQL Servers and are noticing some unusual behavior with Distributed Availability Groups that I'm trying to resolve before moving forward.

Last month, I upgraded a remote secondary server from SQL Server 2016 to SQL Server 2017. This server is a part of multiple Distributed Availability Groups (DAGs) and a separate Availability Group (AG). When we upgraded this server, we were unaware that it would get into an unreadable state, so during the past month we've solely been relying on the primary server.

As a part of the upcoming upgrade, I applied the CU 4 patch to the server and rebooted it. When the server came back online, the just-patched secondary showed all of the DAGs/AGs were syncing without any issues.

However, the primary was showing a very different story. It was reporting that

the separate AG was syncing without any issues
but the DAGs were in a Not Synchronzing / Not Healthy state

After initially panicking, I attempted the following things to get things synchronizing again in the DAGs:

From the primary, I stopped and resumed the data movement. This did not start syncing the data.
On the secondary (the one I just patched) I ran ALTER DATABASE [<database] SET HADR RESUME; - which execute without errors, but did not resume any syncing

My last attempt at syncing the data again was to login to the secondary, and manually restart the SQL Server service. Manually restarting the service seems a bit extreme, as I'd expect the server being rebooted would have been enough.

Has anyone run into this issue where a DAG doesn't start syncing to a secondary after a reboot? If so, how was it resolved?

I checked both the SQL Server error log, and the event viewer on the secondary server, there was nothing out of the ordinary that I could see.

Please note, this is not a definitive answer but it's the best answer after chatting with Taryn.

  However, the primary was showing a very different story. It was reporting that the separate AG was syncing without any issues but the DAGs were in a Not Synchronzing / Not Healthy state

If the individual databases and AGs underlying the distributed ag say they healthy and synchronizing, there is a good chance this is just a hiccup in the DMVs and/or SSMS dashboards. Since there was nothing in the errorlog to suggest the replica didn't connect or was in a disconnected state.

Unfortunately since the issue has resolved, it's hard to say exactly what it was... but in the future if this occurs for someone:

Check sys.dm_hadr_database_replica_states on all clusters looking for anything that isn't healthy. If all shows healthy, it's possible the DMV hasn't updated yet
If it's unhealthy check the errorlog/DMVs for connectivity issues (such as not being able to connect to the forwarder/global primary)
Dan's answer mentions issues that could arise from database startup - though in this case the instance can't be read so that most likely wasn't an issue but could be in your case
If the database is readable, smoke test with a dummy table/insert or ...
Extended event session using the DEBUG channel items sqlserver.hadr_dump_log_block or sqlserver.hadr_apply_log_block to see if the secondary is actually receiving/applying the log blocks or ...
Perfmon object SQLServer:Database Replica\Log Bytes Received/sec

If you're receiving data on that secondary but the distributed ag still shows not synchronizing or not healthy then I'd let it go for a bit to see if the DMV values change since it's obviously receiving and processing log blocks.

If, however, it isn't then we'll need to investigate further which is out of scope of the answer.