How to make trigger function to make daily entering-exiting report?

Let's assume every "in" is followed by an "out" event. In that case you can do:

SELECT worker_id,
    the_date as in_date,
    the_time as in_time,
    lead(the_date) over w as out_date,
    lead(the_time) over w as out_time,
    -- Check if it is really an out event
    (lead(in_or_out) over w) = 0 as out
FROM daily_report
-- Filter for in events
WHERE in_or_out = 1 AND worker_id = [your_worker_id]
-- specify the window function
WINDOW w AS (order by the_date, the_time)
GROUP BY worker_id;

You didn't write which PG version do you have. A better approach is to have LATERAL JOINs (available since 9.3):

SELECT worker_id, i.the_date, i.the_time, o.the_date, o.the_time
FROM daily_report i
LEFT JOIN LATERAL (
    SELECT r.the_date, r.the_time
    FROM daily_report r
    WHERE
        in_or_out = 0 AND r.worker_id = i.worker_id
        (
            (r.the_date = i.the_date and r.the_time > i.the_time) OR
            (r.the_date > i.the_date)
        )
    ORDER BY 1, 2
    LIMIT 1
) o ON true
WHERE in_or_out = 1 AND worker_id = [your_worker_id]

This is a more generic query and will give you back better results if the data is somehow not completely correct.


Delete old records based on its identifier

To delete records which have deviceid = 2 and are older than a year from the current date use

DELETE
FROM table
WHERE deviceid = 2
  AND servertime < CURRENT_DATE - INTERVAL 1 YEAR; -- or, maybe, <=.

To delete records which are older than a year from the current date except those which have deviceid = 2 use

DELETE
FROM table
WHERE deviceid != 2 -- or deviceid <> 2
  AND servertime < CURRENT_DATE - INTERVAL 1 YEAR;

To perform this action daily use MySQL's Event Scheduler. Create a schedule which is executed every day

CREATE EVENT delete_old_records
ON SCHEDULE EVERY 1 DAY          -- execute daily
            STARTS CURRENT_DATE  -- at 00:00
DO
    DELETE
    FROM table
    WHERE deviceid != 2
      AND servertime < CURRENT_DATE - INTERVAL 1 YEAR;

And do not forget to enable event scheduler!!! Once (until disable or server restart) - by the query

SET GLOBAL event_scheduler = ON;

Permanently - by adding  --event-scheduler=ENABLED to the service command-line or event-scheduler=ENABLED to the server configuration file (preferred).


is "%LIKE%" faster than executing multiple queries?

This is bad - it is a table scan

LIKE %1234

But thousands of rows is not much

Best would be to have second table with phone

phone:
userID PK FK to user
number PK

select *
from user
join phone
       on phone.userID = user.userid
      and phone.numer = '1234'

if you don't want to fix the data design then two column with = will be faster


Visio 2010 and SQL 2012 - Reverse Enginnering

The Generic OleDB Driver also works for reverse engineering a SQL Server 2012 database using Visio 2010.

(Might not be "supported", but this works).  Select the Generic OLE DB Provider from the list of Installed Visio drivers and click Next.  Then pick the Microsoft OLD DB Provider for SQL Server and then go to the Connection tab.

Enter in your server name,
choose the authentication method,
Select the database name from dropdown (or build from file) and click the Test Connection button.

I didn't need to do anything with the Advanced or All tabs after the connection succeeded, just clicked OK and was able to pick the object types and list of objects and it exported everything to the diagram.


Restore a SQL Server database to an offline copy

Install SQL Server, if it isn't already there.
Get the backup file onto you local box.
Open SSMS and connect to your local instance
Right-click the "Databases" folder and pick "Restore Database..."
Follow the wizard until you're done


How to install tds_fdw on AWS RDS for SQL Server

If RDS doesn't allow it, then you can't do it on RDS.  The straightforward solution is not to use RDS if it doesn't support the features you need.
But RDS does allow postgres_fdw.  So the convoluted solution is to run your own postgres server (perhaps running it on an EC2 instance) which does supports tds_fdw, and then connect to that from RDS and chain the foreign tables.


This instance of (MS) SQL Server has been using a process id of X since dd/mm/yyyy

No, this is perfectly normal, as the message says. It is just SQL Server's way of telling you the pid.

To verify, within SQL Server you can do select serverproperty('processid'), and then in the OS space you should be able to check the process ID like this in a one-liner PowerShell command:

Get-Process "sqlservr"


MySql remove counted value from another count

There are multiple ways to achieve that

Repeat the expressions in the new column definition:

SELECT `GameID`,
count(case `Liked` when 1 then 1 else null end) as likes,
count(case `Liked` when 0 then 1 else null end) as dislikes,
count(case `Liked` when 1 then 1 else null end)-count(case `Liked` when 0 then 1 else null end) as diff
FROM `rating`
GROUP BY `GameID`
ORDER BY diff DESC

Wrap it in another query:

select GameID, likes, dislikes, likes-dislikes AS diff
from (
    SELECT `GameID`,
    count(case `Liked` when 1 then 1 else null end) as likes,
    count(case `Liked` when 0 then 1 else null end) as dislikes
    FROM `rating`
    SET likes = likes + dislikes
    GROUP BY `GameID`
) as tmp
ORDER BY diff DESC


Efficient way to compute unique values using a PostgreSQL GIN index

This can't be done.  Of the two built-in GIN operators for indexing JSONB, one of them only stores hashed values, so you wouldn't be able to reverse them, and other one stores flattened keys, irrespective of what level of the JSONB they were at.  That second one also hashes the values if they exceed a certain length.

Both of the methods can lead to false positives, so both rely on "rechecking" the full JSONB in the tabe to make sure they meet the criteria.

For example, with the "jsonb_ops" operator, the value {"ABCD": {"EFGH": "IJKL"}} will initially match the query @> '{"EFGH":{}}', because the value does have "EFGH" as a key.  It would then fail the recheck, because that key occurs at the wrong depth.


Why are Column Defaults on my User-Defined Table Type (UDTT) not being honored?

Why doesn't SQL Server honor the default that I placed on my table type?

Actually, SQL Server does indeed hono[u]r that default as the following test (using the UDTT provided in the Question) shows:

DECLARE @Test dbo.MyObject_TableType;
INSERT INTO @Test (Name) VALUES ('ObjectOne');
SELECT * FROM @Test;

Returns:

Name        Index
ObjectOne              <--- empty string, not NULL

The reason you are getting NULL instead of an empty string is because that is what you are asking the app code to provide. You even (unknowingly, of course ;-) identify the root cause of the problem when stating (emphasis added):

  I build a DataTable and populate it with one DataRow. I provide that table as a parameter to a stored procedure:

A DataTable is a representation of an actual Table in memory, not of one or more INSERT statements. So when you create that single row, the Index column can't be undefined: it is either NULL or some value. And when you pass that DataTable into SQL Server as a TVP, you are passing in the equivalent of a Table.

Yes, you could probably get around this by specifying a default value for that DataColumn in the DataTable. But I agree that doing this is not the best approach.

My preference is to never use DataTables unless you already happen to have one, regardless of using a TVP or not. I can't think of a reason to ever create one simply for the purpose of passing data into a TVP. Besides this particular issue, it also duplicates whatever collection you are placing into it simply to be a temporary transport for the TVP (which wastes memory and the time it takes to copy that data, even if it isn't much of either of those).

Instead, create a method that will take whatever collection you already have and simply stream it, one item / row at a time, into the TVP. You do this by returning IEnumerable<SqlDataRecord> from this method, and then specify this method as the Value property of the SqlParameter representing the TVP.

method to stream the collection into the TVP:

private static IEnumerable<SqlDataRecord> SendRows(List<DbObject> DbObjects)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[]
   {
      new SqlMetaData("Name", SqlDbType.VarChar, SqlMetaData.Max,
                      true, false, SortOrder.Unspecified, 1),
      new SqlMetaData("Index", SqlDbType.VarChar, SqlMetaData.Max,
                      true, false, SortOrder.Unspecified, 2)
   };
   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   int _NumRows = DbObjects.Count;

   // read a row, send a row.
   // "for" should be faster than "foreach", right?
   for (int _Index = 0; _Index < _NumRows; _Index++)
   {
      _DataRecord.SetString(0, DbObjects[_Index].Name);
      _DataRecord.SetString(1, DbObjects[_Index].Index);
      yield return _DataRecord;
   }
}

using the method:

private struct DbObject
{
    public string Name;
    public string Index;
}

List<DbObject> _DbObjects = new List<DbObject>();

// Add one row that is missing an Index
_DbObjects.Add(new DbObject() { Name = "ObjectOne" });

// Make a parameter for the table
SqlParameter _ParamMyObjects = new SqlParameter("MyObjects", SqlDbType.Structured)
{
    TypeName = "MyObject_TableType", // not required for CommandType.StoredProcedure
    Value = SendRows(_DbObjects)
};

command.Parameters.Add(_ParamMyObjects);
command.ExecuteNonQuery();

The trick to getting the Default defined on the UDTT to populate the Index column with an empty string is the particular overload of the SqlMetaData Constructor that I used above. It has a boolean parameter for useServerDefault. Passing in true causes the column to not be passed into the INSERT statement if not "set" in the SqlDataRecord. Or, at least that is how I have always gotten IDENTITY columns in UDTTs to work (never tried it on an actual DEFAULT, but should be the same behavior).

P.S. Why are you using VARCHAR(MAX) as the datatype of these two columns in the UDTT? If either, or both, of these columns need to contain SQL Server identifiers (i.e. names of Tables, Views, Stored Procedures, Indexes, etc) then those are all sysname which is an alias for NVARCHAR(128). So you really need to at least be using NVARCHAR to avoid potentially data loss / hard-to-trace bug.


Storing massive amounts of data from a sensor array

Yes, there is a pretty big pitfall you're going to run into fairly quickly, and that is with the size and maintenance of the tables.  You are somewhat on the right track by saying that you want to put your data into a temporary table daily, and then move it into your permanent table, but you'll soon run into trouble with this scheme.

For example, let's say you want to "roll off" the oldest month's worth of data after two years.  In your design, you would have to issue a DELETE statement against your big, big table.  This will likely be somewhat slow, depending on the number of indexes you have.  Also, it will cause index fragmentation, and the only way to fix that would be to rebuild or reorganize the indexes on this very large table which would also cause performance problems.    There are a whole host of other issues with a big single table type design as well.  For example, with a big, single table, you can't do FILEGROUP based backups, which means that if you want to have a full backup of your database, it's gonna be BIG, and it's gonna take a LONG time to complete.

What's the solution?  Table partitioning.  Read about this in depth, in as many places as you can.  Basically, partitioning allows you to split up your data onto "tables within tables" -- each partition shares the same schema, and is accessed through the table object, but can be indexed and maintained differently.  Partitions are basically tables, cut up by some useful key.  In your case it will likely be date.  They can be dropped just like (and just as fast as) tables, which means that if you partition your big data tables by date, you can simply drop old partitions instantly, with no adverse effect to the indexes on any of the other partitions.  You can put partitions on different filegroups, which means that older partitions can be rolled off, or rolled on to cheaper commodity storage if it's not commonly used.  Last but not least, in SQL 2012 you'll be able to create COLUMNSTORE type indexes on your older, read-only partitions, while having a different, more insert-oriented indexing scheme on the active partition where you're inserting all your sensor data.

Hope this helps.  You have a good amount of research to do regarding partitioning and partitioning schemes, but hopefully now you know the direction you need to be looking.

P.S.:
Oh, and I forgot your bulleted list of questions...
Answer 1, 2, and 5.  See above.  Answer 3: In SQL Server, you can compress on a partition by partition basis, so compress your older partitions aggressively using PAGE compression.  But  I believe your out-of-row large data types will not be compressed if you do this -- again, you may want to alleviate this problem by normalizing your sensor values.  Answer 4: Absolutely not, but if all you want to do is store static data by day and never search on it any other way, compressed flat files may be a much easier way to go.

P.P.S:
Oh, and another thing.  You don't need your two-table solution to make this all work.  Large binary sensor data should be of type VARBINARY(MAX) because its values can be stored "out of row" but still be a column in a single table (see the sp_tableoption documentation).  You may want to consider normalizing some of your sensor data out of the binary data you have in the table, though, because your database won't be good for much beyond retrieving chunks of sensor data by time if you don't.


Make sure DB is up and running with status healthy

docker container ps

Make sure Oracle instant client is installed on host if trying to connect from host to containers  via sqlplus or any 3rd party gui tools and set up connections in tnsnames.ora on host

3̶. S̶e̶t̶ ̶u̶p̶ ̶c̶o̶n̶n̶e̶c̶t̶i̶o̶n̶s̶ ̶i̶n̶ ̶t̶n̶s̶n̶a̶m̶e̶s̶.̶o̶r̶a̶ ̶o̶n̶ ̶b̶o̶t̶h̶ ̶c̶o̶n̶t̶a̶i̶n̶e̶r̶s̶.̶   (skip this step)

Make sure Oracle listener is up and running and you can find out services names on each containers by running the command

lsnrctl status

You need to find out mapped ports. Execute command

docker port orcl

From my container the output is:

$ docker port orcl
1521/tcp -> 0.0.0.0:32769
5500/tcp -> 0.0.0.0:32768

You can connect directly from sqldeveloper or setup tns entries in tnsnames.ora for sqlplus client.

Here is the screenshot connecting from sqldeveloper

For sqlplus client on host

#tnsname entry for container 1
orclcdb =
(DESCRIPTION =
  # host ipaddress and mapped port for 1521 is 32769
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 32769))
 (CONNECT_DATA =
(SERVER = DEDICATED)
#service name from lsnrctl status command
(SERVICE_NAME = ORCLCDB.localdomain)
)
)

Verify tnsname entries with the command from host:

tnsping  orclcdb

Note

My setup was on Windows 10(host) and docker running on VM. I executed the following command to find the ipaddress of docker VM

Input:

docker-machine ls

Output:

Correction:

I didn't notice parameter -p 1521:1521 for the container start up command. That parameter exposes port 1521 for public. Just use container ipaddress(from docker-machine ls) and same port 1521 for sqlplus client connection from the host.


Oracle Procedure alter pivoted data to unpivot and push into different destination

Why do you not simply unpivot the hole table

SELECT * FROM push_data_temp;

ID_PK |  ID | COL1                         | COL2                                                                                | COL3     | COL4       | COL5                | COL6                                 | COL7           | COL8 | COL9 | COL10 | COL11 | COL12    | COL13 | COL14
----: | --: | :--------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------- | :--------- | :------------------ | :----------------------------------- | :------------- | :--- | :--- | :---- | :---- | :------- | :---- | :----
 null | 110 | Project title afor BYU heads | "The values are,
 "exactly" up to the requirement and analysis done by the team.
Also it is difficult to,
 prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team" | "Active" | "Disabled" | "25 tonnes of fuel" | "www.examplesites.com/html.asp&net;" | "Apprehension" | null | null | null  | "25"  | "Stable" | null  | null
 null | 111 | Project title for IT Heads   | "The values are,
 "exactly" up to the requirement and analysis done by the team.
Also it is difficult to,
 prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team" | "Active" | "Disabled" | "25 tonnes of fuel" | "www.examplesites.com/html.asp&net;" | "Apprehension" | null | null | null  | "25"  | "Stable" | null  | null

create table PUSH_DATA_PIVOT
(
id_pk NUMBER,
  id NUMBER,
  label_id number,
  label varchar2(4000),
  value varchar2(4000)
);

INSERT INTO PUSH_DATA_PIVOT
SELECT 1,ID,1,label,value
FROM
(SELECT * FROM push_data_temp
UNPIVOT INCLUDE NULLS(
    value
    FOR label
    IN (
        col1 AS 'COL01',
        col2 AS 'COL02',
        col3 AS 'COL03',
        col4 AS 'col4',
        col5 AS 'col5',
        col6 AS 'COL06',
        col7 AS 'COL07',
        col8 AS 'COL08',
        col9 AS 'COL09',
        col10 AS 'COL10',
        col11 AS 'COL11',
        col12 AS 'COL12',
        col13 AS 'COL13',
        col14 AS 'COL14'
    )
)) t1;

28 rows affected

select * from push_data_pivot;

ID_PK |  ID | LABEL_ID | LABEL | VALUE
----: | --: | -------: | :---- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1 | 110 |        1 | COL01 | Project title afor BYU heads
    1 | 110 |        1 | COL02 | "The values are,
 "exactly" up to the requirement and analysis done by the team.
Also it is difficult to,
 prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team"
    1 | 110 |        1 | COL03 | "Active"
    1 | 110 |        1 | col4  | "Disabled"
    1 | 110 |        1 | col5  | "25 tonnes of fuel"
    1 | 110 |        1 | COL06 | "www.examplesites.com/html.asp&net;"
    1 | 110 |        1 | COL07 | "Apprehension"
    1 | 110 |        1 | COL08 | null
    1 | 110 |        1 | COL09 | null
    1 | 110 |        1 | COL10 | null
    1 | 110 |        1 | COL11 | "25"
    1 | 110 |        1 | COL12 | "Stable"
    1 | 110 |        1 | COL13 | null
    1 | 110 |        1 | COL14 | null
    1 | 111 |        1 | COL01 | Project title for IT Heads
    1 | 111 |        1 | COL02 | "The values are,
 "exactly" up to the requirement and analysis done by the team.
Also it is difficult to,
 prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team"
    1 | 111 |        1 | COL03 | "Active"
    1 | 111 |        1 | col4  | "Disabled"
    1 | 111 |        1 | col5  | "25 tonnes of fuel"
    1 | 111 |        1 | COL06 | "www.examplesites.com/html.asp&net;"
    1 | 111 |        1 | COL07 | "Apprehension"
    1 | 111 |        1 | COL08 | null
    1 | 111 |        1 | COL09 | null
    1 | 111 |        1 | COL10 | null
    1 | 111 |        1 | COL11 | "25"
    1 | 111 |        1 | COL12 | "Stable"
    1 | 111 |        1 | COL13 | null
    1 | 111 |        1 | COL14 | null

db<>fiddle here


how to create an explain plan for the below query? I'm getting ORA-00900: invalid SQL statement error

A CREATE VIEW statement has no execution plan as the SELECT will not be evaluated when you create the view. You need to first create the view, then run EXPLAIN on a query using the view:

create or replace view TaskView
as
select T1.PersonID,T2.ID ,T2.Task
from Table1 T1,Table2 T2
where T1.TaskID=T2.ID;

Then

explain plan
  set statement_id = 'TASK'
select *
from taskview;

or simply explain the underlying query:

explain plan
  set statement_id = 'TASK'
select T1.PersonID,T2.ID ,T2.Task
from Table1 T1,Table2 T2
where T1.TaskID=T2.ID;


You might want to consider opening an issue on MongoDB's Issue Tracker and providing the *.mdmp file in the issue.

*.mdmp files are considered to be Microsoft Dump Files.

  Compressed file created by Windows after a program error or crash; contains data "dumped" from the program's memory space; saves information about the error, which can be used for debugging; commonly created along with an uncompressed .HDMP file and can be sent to Microsoft as part of a bug reporting process.

Reference: .MDMP File Extension (fileinfo.com)

If you have a Microsoft debugger and the symbols for the relevant progams, then you can have a look at the dump file yourself:

  A dump file is a snapshot of an app at the point in time the dump is taken. It shows what process was executing and what modules were loaded. If the dump was saved with heap information, the dump file contains a snapshot of what was in the app's memory at that point in time. Opening a dump file with a heap in Visual Studio is like stopping at a breakpoint in a debug session. Although you cannot continue execution, you can examine the stacks, threads, and variable values of the app at the time the dump occurred.

But be aware:

  Debugging dump files of optimized code can be confusing. For example, compiler inlining of functions can result in unexpected call stacks and other optimizations might change the lifetime of variables.

Debugging a kernel-mode dump file requires you to...

  To debug kernel-mode dump files in Visual Studio 2013, download the Windows 8.1 Version of Debugging Tools for Windows. See Kernel Debugging in Visual Studio.

Reference: Using Dump Files (Microsoft MSDN)

There are some third-party tool from NirSoft that might help in finding the culprit.

And then there is the following list that tells you which tools could possibly open the *.mdmp file for you:

.mdmp Extension - List of programs that can open .mdmp files (Extension NirSoft)

  Disclaimer:
  I am in no way affiliated with NirSoft. I only use their software.


how do I work out why my stored procedure won't compile?

The database provided you the exact location of the error. All you need to do is check what is there, in the database, and not in your file.

select text from all_source where name = 'PROCEDURE_NAME' and line = 5552;
select text from all_source where name = 'PROCEDURE_NAME' and line = 5556;


Delayed availability of historical data when using CDC

CDC relies on a SQL Server Agent job to capture information from the transaction log. You can customize the job parameters by updating msdb.dbo.cdc_jobs - there are multiple properties as described in this Books Online topic:

maxtrans
maxscans
continuous
pollinginterval

That doc has some information about these parameters, as does this white paper.

But really, CDC is not meant to be a real-time system, and you should use caution adjusting these parameters too much to make it so. Most people acknowledge that you need to wait to let CDC catch up (see here and here). If 5-10 seconds is not fast enough for you, you might be using the wrong technology.

As for the separate transaction question, this is something you could test, no?


How to make a relation database match a dynamic composition of data?

I'd go for the normalized design, mainly for readability and maintainability reasons.

Looks like you have n:m relationships all the time, then i'd use

Resource(key,p1,p2,...,pn)
Consumer(key,p1,p2,...,pm)
Consumer_Resource(resourcekey,consumerkey,...)
Consumer_Consumer(consumerkey1,consumerkey2,...)

This is a tried and tested way to go about such a scenario, and will be understood by lots of database folks.


Count based on status on latest association

One way to accomplish this is with a window function and grouping applied using the GROUP BY clause and an aggregate function with a case statement like so:
WITH CTE_Log_Sorted AS -- Creates a CTE (can do this in a subquery too) to generate unique row numbers for each status within a partition of parentProcess starting with the latest status first (ordered by created descending).
(
    SELECT status, ROW_NUMBER() OVER (PARTITION BY parentProcess ORDER BY created DESC) AS SortId
    FROM Log
)

SELECT
    SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS PENDING, -- Case statements to sum up only the status we want to count in this column
    SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS FAILED, -- Case statements to sum up only the status we want to count in this column
    SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS SUCCESS -- Case statements to sum up only the status we want to count in this column
FROM CTE_Log_Sorted
WHERE SortId = 1 -- Filter out everything but the latest status of each parentProcess
GROUP BY status -- Group on each status so we can sum up the count of each in our SELECT clause


How can I remove info message and column lines from SQL query result file?

I played around with your example and was able to hardcode the expected column names and used UNION ALL against the actual result set.  I did have to convert the port column to varchar(10) to get the UNION ALL to work.  I then added the @query_result_header = 0 parameter to eliminate the query header.  I never did get the 'Rows Affected' message in my example because I was using the SET NOCOUNT like you did.

DECLARE @tab CHAR(1) = CHAR(9)

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
    ,@recipients = '<recipients>'
    ,@subject = 'query'
    ,@body_format = 'TEXT'
    ,@query = N'
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
    SELECT ''Instance_Name'' as Instance_Name, ''IP_Address'' as IP_Address, ''Port'' as Port
    UNION ALL
    SELECT  SERVERPROPERTY(''ServerName'') as ''Instance_Name'',
    exc.local_net_address as ''IP_Address'',  convert(varchar(10),exc.local_tcp_port) as ''Port''
    FROM sys.dm_exec_connections exc
    --WHERE  exc.session_id = @@SPID
    '
    ,@attach_query_result_as_file = 1
    ,@query_attachment_filename = 'InstanceInfo.txt'
    ,@query_result_separator = @tab
    ,@query_result_width = 32767
    ,@query_result_no_padding = 1
    ,@exclude_query_output = 1
    ,@query_result_header = 0


Unable to create partial indexes using bloom

The WITH clause comes before the WHERE clause in the CREATE INDEX syntax.  You are not free to rearrange the order as you have attempted here.
This ordering is a little unintuitive to me, but that is how it works.


SSDT Not Dropping all Foreign Keys before Drop Table

check under Tools->options->SQL Server Tools->Data compare if  "Disable foreign keys" is checked


MySQL Partitioning on event tables with start and end datetime columns

Do the time ranges overlap for a given location?

If you need to delete all rows where start_datetime < @some_date, then the best way is via PARTITIONing based on start_datetime.  Use PARTITION BY RANGE(TO_DAYS(start_datetime)) and base it on days or weeks or months, such that you have about 20-50 partitions.  See details.

Plan A:

Once you partition that way, then have the PRIMARY KEY be (location_id, end_datetime, start_datetime), in this order.  If those 3 columns are not sufficient to be unique, and, say, you have id AUTO_INCREMENT, then do this:

PRIMARY KEY(location_id, end_datetime, start_datetime, id),
INDEX(id)

Now, let's analyze

WHERE     location_id = @queried_location_id
AND       start_datetime < @queried_end_datetime
AND       end_datetime > @queried_start_datetime

The following steps happen:

"partition pruning" based on start_datetime.  This may or may not help much, depending on how new the 'start' is.  On average(?), it will eliminate half the partitions (not much benefit).
Using the PK, it will home in on the appropriate location_id and the end_datetime.
Scan forward in the data (since the PK is 'clustered' with the data), checking the rest of those location_ids for a match.
Repeat for each partition not pruned out by step 1
Combine the results from the partition.

Plan B:

If your start..end ranges are predominately recent dates and if you can purge based on end_datetime, then this would probably be better because it would predominately focus on the last partition:

PARTITION BY RANGE(TO_DAYS(end_datetime))

PRIMARY KEY(location_id, start_datetime, end_datetime, id),
INDEX(id)

If the ranges are not overlapping, then folding in this may give significantly more performance.

But... If end_datetime is NULL initially, this will throw the row in to the "first" partition.  So... Be sure to have an extra partition at the beginning for that -- else the DROP for purging old records will toss them by mistake.  That is, have the first range be VALUES LESS THAN (0).
 Then have the second range be the oldest month (or week or whatever), and drop that for purging.

Another issue with NULL -- when you do set end_datetime, the record will have to be moved from one partition to another, thereby making the UPDATE statement somewhat costly.  (It is effectively a DELETE plus an INSERT.)  For this reason, I don't recommend Plan B for very high activity.


How to find XXth day of previous month in SQL server?

DECLARE @d DATETIME = '20140311', @dm TINYINT = 26;

SELECT DATEADD(DAY, @dm-1, DATEADD(MONTH, -1, DATEADD(DAY, 1-DAY(@d), @d)));

Result:

2014-02-26 00:00:00.000


From a user table check if one of them exists on a table

Assuming my reading of the schema is correct, this should do what you want:

create procedure dbo.AddSerials (
      @serials as dbo.SerialList readonly
    , @parentCode nvarchar(20)
    , @isAuto bit
    ) as
begin;
  set nocount on;
  declare @AggregationId int;
  select @AggregationId = CodeId
    from dbo.[Code] c
    where c.Serial = @parentCode;
  if @AggregationId is null
    begin;
      -- if the parent doesn't exist, children don't exist
      return 0;
    end;
  -- parent exists, check children
  if exists (
    select 1
      from @serials s
      where not exists (
        select 1
          from dbo.AggregationChildren as ac
            inner join dbo.[Code] as acc
              on ac.AggregationChildrenId = acc.CodeId
                and ac.AggregationId = @AggregationId
          where acc.Serial = s.Serial
        )
    )
    begin;
      -- at least one @serials does not exist under parent
      return 0;
    end;
    else
    begin;
      -- all of the @serials exist under parent
      return 1;
    end;
end;

If you want to skip checking the parent code, this would also work:

create procedure dbo.AddSerials (
      @serials as dbo.SerialList readonly
    , @parentCode nvarchar(20)
    , @isAuto bit
    ) as
begin;
  set nocount on;
  if exists (
    select 1
      from @serials s
      where not exists (
        select 1
          from dbo.[Code] as ac
            inner join dbo.Aggregation as a
              on ac.CodeId = a.AggregationId
                and ac.Serial = @parentCode
            inner join dbo.AggregationChildren as ac
              on a.AggregationId = ac.AggregationId
            inner join dbo.[Code] as acc
              on ac.AggregationChildrenId = acc.CodeId
          where acc.Serial = s.Serial
        )
    )
    begin;
      -- at least one @serials does not exist under parent
      return 0;
    end;
    else
    begin;
      -- all of the @serials exist under parent
      return 1;
    end;
end;


How to convert SQL view creation script into a query, instead?

Your query uses a common table expression, so replacing the first line with SELECT won't work. Comment out the first line of your script and you should have a SELECT query that returns the same results as the view. Reproducing your code in full:

WITH FUND_CERTIFIED AS (
  SELECT REQUEST_ID, MIN(TIME_STAMP) AS FUND_CERTIFIED_DATE
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 6
  GROUP BY REQUEST_ID
),
APPROVED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID NOT IN ( 5, 8 )
),
CANCELED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 4
),
LAST_STATE AS (
  SELECT MAX(ID) AS LAST_STATE_ID, REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID <> 8
  GROUP BY REQUEST_ID
)
SELECT CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ) AS FUND_CERTIFIED,
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ) AS CANCELED,
NVL2( A.REQUEST_ID, 'Y', 'N' ) AS APPROVED,
SUM( NVL( CRD.AMOUNT, 0 ) ) AS AMOUNT,
LS.LAST_STATE_ID
FROM COMMITMENT_REGISTER CR
LEFT OUTER JOIN FUND_CERTIFIED FC ON ( FC.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN APPROVED A ON ( A.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN CANCELED C ON ( C.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN LAST_STATE LS ON ( LS.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN COMMITMENT_REGISTER_DETAIL CRD ON ( CRD.COMMIT_REG_ID = CR.COMMIT_REG_ID )
GROUP BY CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ),
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ),
NVL2( A.REQUEST_ID, 'Y', 'N' ),
LS.LAST_STATE_ID;


How we can take backup oplog on every hour and apply on top on full backup for mongodb

The oplog is idempotent, you can run through the operations in it as many times as you want and you won't get duplicates or issues unless you run the operations on a set of data files in an odd state.

However, it should be noted that as long as you have the journal as part of the LVM snapshot, re-running the oplog is not necessary for a consistent backup.

With that said, if you do have a copy of the oplog, you can "replay" it in two ways:

Start up a new MongoDB instance, use it to access the oplog backup, and then create a custom script to read each operation and apply it in order (has the benefit of allowing you to filter and being rather quick)
Use mongodump/mongorestore to replay the oplog (see below)

To use mongodump in this way, you first have to dump it out into BSON format so that it can be resored:

mongodump --dbpath /path/to/folder/with/oplog/files -d local -c oplog.rs -o oplogDump

Next you move the bson file out of the folder (this is basically for convenience):

mkdir oplogRestore
mv oplogDump/local/oplog.rs.bson oplogRestore/oplog.bson

Now you can use mongorestore to replay the oplog and apply it to a given running instance:

mongorestore --host host:port --oplogReplay oplogRestore

You can also use the oplogLimit option if you only wish to replay up to a certain point.  See Asya's excellent answer here for more on that.

Please note that none of this will be particularly quick, nor is it strictly necessary (as mentioned above).


Go to Sql Server Configuration Manager, and check on your client protocols if TCP/IP is enabled.


MDX & DAX are query languages for OLAP cubes.

MDX is used on multidimensional OLAP cubes and is used by many vendors.

DAX is Microsofts own query language and can only be used in a tabular OLAP cube or within Excel.

Assuming you are using Microsoft  SSAS you must choose which type of cube you want when you install SSAS.

If you (or someone else) has already installed SSAS then you need to find out   if its Multidimensional or Tabular, and then use the appropriate query language.


This can be surprisingly simple with a smart application of a window function.
Based on this table definition:

CREATE TEMP TABLE tbl (
  fecha timestamp PRIMARY KEY
, ignicion bool NOT NULL  -- use an actual boolean!!
);

Query:

SELECT period, min(fecha), max(fecha), count(*) AS row_ct
FROM  (
   SELECT *, count(*) FILTER (WHERE NOT ignicion) OVER (ORDER BY fecha) AS period
   FROM   tbl
   ) sub
WHERE  ignicion
GROUP  BY period
ORDER  BY period;

... combining the new aggregate FILTER clause (pg 9.4+) with a window function in a subquery.

Return counts for multiple ranges in a single SELECT statement

The trick is to compute a running count rows with ignicion = FALSE in the subquery, and then group rows with ignicion = TRUE by that count.

If fecha is not unique or one of the columns can be NULL, you need to do more.


Large MySQL operations slowing down SELECT queries on unrelated tables

The real problem is all the I/O that is needed in creating indexes on huge tables.

When the index is being created, does SHOW PROCESSLIST say "Repair by key_buffer" or "by sorting"?  Sorting is better for large tables.

Are you running only MyISAM?  If so, make these changes:

innodb_buffer_pool_size = 0
key_buffer_size = 3G

However, if you switched to InnoDB and a new enough version of MySQL, you could CREATE/DROP indexes "online" -- that is, without recreating the table (as happens in MyISAM).  This would significantly decrease the I/O, hence cut back on the interference with the other queries.

A 5 second query using SSDs and RAID-10?  Yikes!  What is the query doing?  Please show us the query, together with SHOW CREATE TABLE.  It can possibly be sped up to be sub-second.

You say it is not I/O -- would you share the details of the evidence.

Yes, into "Multiple Key Caches".

query_cache_size=256M

is too high.  Don't go above about 50M, so as to avoid lots of overhead in "pruning".

Summary:

Change some settings;
Switch to InnoDB;
Speed up the 5-25s query.


select rows older then column DATETIME - INT value of other column in single query

If sendgap is the number of seconds, the correct syntax is this

SELECT * FROM table WHERE lastsendattempt <= (NOW() - INTERVAL sendgap SECOND);


Can Transactional Replication and triggers be used for near-realtime ETL

If you're using a version and edition that supports Change Data Capture or Change Tracking, these mechanisms would be better suited to monitoring for changes to your reference tables because they provide a lightweight solution for identifying only changed rows.

You could implement CT or CDC on your reference tables and poll them frequently for changes. When a change is encountered, you can pull in the changed rows only, update your data warehouse and kick off your process for fixing the denormalised data in your data warehouse.

You may also need to re-evaluate the design of your data warehouse. From your description, these reference tables are possibly slowly changing dimensions and with the correct DWH design, you shouldn't need to update your denormalised data in the warehouse after a change to a dimension, that change should be handled automatically.

There isn't really a complete answer to your problem because there are a lot of unknowns that impact design decisions for both your data warehouse and ETL process. You need to properly define your requirements around performance, data latency (how far behind can the DWH be), historical data (should your fact table records reflect the dimension details at the time of insert or the current state of the dimension), reporting and several other factors which then inform your DWH and ETL design.

After the requirements are defined, you can start looking at technologies and architectures to meet these requirements.


Big difference between send_request_date and sent_date in sysmail_sentitems after running sp_send_dbmail,

Network Communication or a problem with the mailserver would be my guess. Check the log for the Mail server during this period. Chanses are you'll find it was unavailiable.
Regards Marten


Where can I find some guidance on index strategies?

Short

The "too many indexes" rule is a bit misleading I think.

Long

Given that the average database is around 98% reads (or higher) reads need to be optimised. An INSERT is a read if there is a unique index, for example. Or the WHERE on an update. I once read that even a write intensive database is still 85% reads.

What you do have is poor quality indexing. Examples:

wide clustered indexes (SQL Server especially)
non-monotonic clustered indexed
overlapping indexes (eg cold, cole and cold, cole, colf)
many single column indexes (also overlapping with more useful indexes) that are useless for your queries
no INCLUDEs, not covering (eg all single column indexes)
...

Note it's quite typical to have indexes several times bigger than your actual data even in OLTP systems.

Generally, I'd start with the

clustered index (usually PK)
unique indexes (not constraints, these can't be covering)
foreign key columns

Then I'd look at:

common queries and see what I need. A query running every seconds needs tuning. The report at Sunday 4am can wait.
with SQL Server, the weighted missing index DMVs

Saying that, I have broken these rules for some systems after seeing how things panned out (10 billion rows later) to tune a system. But I'd never consider not indexing unless I could demonstrate why I'm doing so.


[oracle@o71 ~]$ echo "TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o71.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS1)))" > ~/tnsnames1.ora
[oracle@o71 ~]$ echo "TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o72.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS2)))" > ~/tnsnames2.ora
[oracle@o71 ~]$ cat tnsnames1.ora
TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o71.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS1)))
[oracle@o71 ~]$ cat tnsnames2.ora
TNS1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o72.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS2)))

I changed the HOST and SERVICE_NAME values for demonstrating how name resolution still works after making one of the files inaccesible.

Now include them with IFILE:

[oracle@o71 ~]$ echo -e "IFILE=/home/oracle/tnsnames1.ora\nIFILE=/home/oracle/tnsnames2.ora" > ~/tnsnames.ora
[oracle@o71 ~]$ cat ~/tnsnames.ora
IFILE=/home/oracle/tnsnames1.ora
IFILE=/home/oracle/tnsnames2.ora
[oracle@o71 ~]$ export TNS_ADMIN=/home/oracle

Test:

[oracle@o71 ~]$ tnsping tns1

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 18-OCT-2018 21:37:00

Copyright (c) 1997, 2018, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o72.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS2)))
OK (10 msec)

tnsping picked up the last occurence of the same TNS alias. Now make this second file inaccessible:

[oracle@o71 ~]$ mv /home/oracle/tnsnames2.ora /home/oracle/tnsnames2.ora.corrupt

Test again:

[oracle@o71 ~]$ tnsping tns1

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 18-OCT-2018 21:37:19

Copyright (c) 1997, 2018, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=o71.balazs.vm)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=TNS1)))
OK (0 msec)

TNS resolution still works, but from the first file (HOST, SERVICE_NAME).

With this method you basically enumerate multiple files containing TNS aliases using the IFILE parameter, which was not designed for this, but it is something.


Difference between two XMLTYPE data types

That's simple.

Check the object type of the "public" object

select object_type
from all_objects
where owner = 'PUBLIC'
and object_name = 'XMLTYPE';

the above returns

OBJECT_TYPE
-----------
SYNONYM

So we have to look into all_synonyms:

select owner, synonym_name, table_owner, table_name
from all_synonyms
where synonym_name = 'XMLTYPE';

returns:

OWNER  | SYNONYM_NAME | TABLE_OWNER | TABLE_NAME
-------+--------------+-------------+-----------
PUBLIC | XMLTYPE      | SYS         | XMLTYPE


Table has 14 GB in unused space - How to shrink table size

Whether a heap or a clustered index, you should find that you will be able to reclaim space in a table by rebuilding:

ALTER INDEX ALL ON dbo.myTableName REBUILD;

Note that reclaiming space in a table, and shrinking a database, are two completely separate things. Shrinking a database should be an exceptional thing - don't shrink a database just to free up disk space, since in most cases, the database will just have to grow and use that space again anyway.

As for determining how the table is distributed, sp_spaceused is pretty useless, since it just gives you a single line item with all data. First, check to see if your table is partitioned. You can see this quickly using:

SELECT COUNT(*)
  FROM sys.partitions
  WHERE partition_number > 1
  AND object_id = OBJECT_ID(N'dbo.myTableName');

Next, see how the data is laid out across your seven indexes. If one of these is a lot bigger than it should be, you could try dropping/re-creating (including potentially removing, say, your XML column from the INCLUDE list), as there may be cases where LOB space for rows you've deleted or set to NULL doesn't get fully recovered, even after a rebuild.

 ;WITH x AS
  (
    SELECT i.name, type = pa.allocation_unit_type_desc, kb = COUNT(*) * 8
    FROM sys.indexes AS i
    CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), i.object_id, i.index_id,
      NULL, 'LIMITED') AS pa
    WHERE i.[object_id] = OBJECT_ID(N'dbo.myTableName')
    GROUP BY GROUPING SETS((), (i.name, pa.allocation_unit_type_desc))
  )
  SELECT name, type, kb,
    [%] = CONVERT(decimal(5,2), kb*100.0/(SELECT kb FROM x WHERE name IS NULL))
    FROM x
    WHERE name IS NOT NULL
    ORDER BY name, type;

If you want to see individual row sizes in the base table, you could do:

SELECT TOP (100) key, rowsize = DATALENGTH(col1) + DATALENGTH(col2) + ...
  FROM dbo.myTableName
  ORDER BY rowsize DESC;

If you have some that are quite large, you might want to make sure you need that data (otherwise set it to NULL, as it may be what's contributing to your size.


Arithmetic Overflow at RunningPackages (SSIS) problem

I've found the solution.As I've told already this is related to MsDtsSrvr.dll

So, I've just Restarted the MsDtsServer110 Service.

No it works Perfectly


No, creating several databases will not help you if the underlying tables and their dependencies stay unchanged. Think of it this way: why would it?

You should get your database setup checked out by an expert and hire a database consultant. Your problems can probably be solved with changes to your indexes, partitioning or tuning DBMS parameters.


Yes, there will be problems. That won't work.
You have to restore the backup with PostgreSQL 9.2, only then you can upgrade.


Why is MySQL Access denied after .bashrc edit?

Never put your password into any script.
The accepted way to do this for MySQL is with a configuration file.

Use a separate configuration file to hold the password.
Protect that file so that you are the only one that can access it.
alias mysql to use that configuration file.

Something like this:
$ ls -ld ~/.milenko.cnf
-rw------- 1 ... ~/.milenko.cnf

$ cat ~/.milenko.cnf
[mysql]
username=milenko
password=REALPASSWORD

In .bash_profile
alias mysql="/usr/lib/mysql --defaults-extra-file=~/.milenko.cnf --umilenko hlocalhost"

Related answer.


Changing replication snapshot folder, require re-snapshot?

You can change in this way

exec sp_changepublication  @publication
@property='snapshot_in_defaultfolder'
@value='0'
@force_invalidate_snapshot=0,--always remmeber that
@force_reinit_subscription=0--always remmeber that

exec sp_changepublication  @publication
@property='alt_snapshot_folder'
@value='C:\your_dir'
@force_invalidate_snapshot=0,--always remmeber that
@force_reinit_subscription=0--always remmeber that


How to return duplicates if IN (...) has duplicates?

You can do this with a JOIN:

SELECT a.*
FROM a
  JOIN
    ( VALUES (1), (1), (2)
    ) AS p (id)
  ON a.id = p.id ;


`uuid_generate_v1mc` in Postgres “uuid-ossp” module: What is random multicast MAC address?

RFC4122 documentation (section 4.1.6) specifies:

  For UUID version 1, the node field consists of an IEEE 802 MAC
     address, usually the host address.  For systems with multiple IEEE
     802 addresses, any available one can be used.  The lowest addressed
     octet (octet number 10) contains the global/local bit and the
     unicast/multicast bit, and is the first octet of the address
     transmitted on an 802.3 LAN.

  For systems with no IEEE address, a randomly or pseudo-randomly
  generated value may be used; see Section 4.5. The multicast bit must
  be set in such addresses, in order that they will never conflict with
  addresses obtained from network cards.

If I interpret this correctly, I'd say: a random Multicast address is any randomly generated MAC adress which has just the multicast bit set. The multicast bit is just one of the bits from the node part of a UUID (for all practical purposes, this just forces one specific bit of the UUID to be set).

Side Notes

I don't think you actually can specify a MAC address to the PostgreSQL function. If it follows the RFC, the function(s) must either use any of the MAC addresses available in your system, or a random one (with a specific bit set).

Whether this random value is always the same for a specific machine (which wouldn't look very random to me) or is just purely (pseudo)random and changing every time, is not clear from this explanation... but can be very easily tested:

SELECT uuid_generate_v1mc() AS u1, uuid_generate_v1mc() AS u2

gets me right now:

'91ccbe0c-488c-11e7-8d61-b7a8bb0bd0e3','91cd902a-488c-11e7-8d61-8bdf8f55ae02'

This translates (using the PERL program from https://stackoverflow.com/questions/1709600/what-kind-of-data-can-you-extract-from-a-uuid) to

time: Sat Jun  3 20:43:43 2017 +682.51ms
clock id: 36193
Mac: b7:a8:bb:0b:d0:e3
broadcast/multicast bit set.

and

time: Sat Jun  3 20:43:43 2017 +687.889ms
clock id: 36193
Mac: 8b:df:8f:55:ae:02
broadcast/multicast bit set.

... so, the MAC are actually completely (pseudo)random.

As pointed out by @EvanCarrol: I also think you're better off with v4 UUIDs, I don't think you'll get less collision risk with a randomly generated MAC.

Besides, very many network devices (routers, switches, etc.) have programmable MAC addresses (this is very handy when you want to replace one broken device by another, and make sure all the other devices don't notice any difference at all). This, somehow, makes the MACs not as unique as you probably thought.

Alternatvies: If you work with Windows, may be this tool can let you fake a MAC address. I've not tried it myself, so, "no strings attached".


Does DB2 have equivalents for Oracle's UTL_ENCODE and UTL_RAW modules?

I believe you are correct. It does not look like DB2 has built in support for base64 encoding.

So that would leave the following as options:

Handle the encoding within your programs.
Write a Java routine (stored procedure) to handle it.
Using a different encryption scheme. DB2 comes with encryption built into itself. There is an ENCRYPT function. Check it out.

Be careful with Java implementations of base64 though. A coworker of mine has mentioned he has seen a bug in all versions of it, in which "words" that have duplicate letters can decrypt into junk. So be careful and double check your base64 encoding/decoding.


Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?

You have to use InnoDB. Here is why :

The major advantages of InnoDB over MyISAM

InnoDB caches data and index pages, whereas MyISAM only caches index pages.
InnoDB is designed for ACID-compliant transactions
InnoDB is designed for row-level locking, MyISAM uses table-level locking.
InnoDB is designed for Multiversion Concurrency Control, critical for transaction isolation
InnoDB can be tuned to engage multiple cores

MyISAM is great in a read-heavy environment. Although you can configre private caches on a per-table basis, MyISAM never caches data.

InnoDB is your best bet !!!

CAVEAT

Get enough RAM on the DB Server to cache everything in the InnoDB Buffer Pool (75% of Installed RAM).


Best practice to delete big portion of big mysql table

While there is more than one way to do that (including the one you mentioned), if you can temporarily stop the traffic to the DB, I suggest this way:

Stop the traffic (At least to that table if possible)
Create a new table with the same fields, but no indexes: CREATE TABLE new_table AS SELECT * FROM myTable WHERE 1=2;
Insert into this new table all the rows that you want to keep: INSERT INTO new_table SELECT * FROM MyTable WHERE id>123456; -- OR, INSERT INTO new_table SELECT * FROM myTable ORDER BY id DESC limit 10000000;
Create indexes on this new table.
Switch the tables by renaming them: RENAME TABLE myTable TO old_table, new_table to myTable;
Drop the old table
Restart the traffic

If the ID of this table is an auto increment one, and you don't care about its value, you will have the chance to reset it to start from 1, so you don't run out of IDs in the near future (This is totally dependent on other factors)

HTH


Calculating the total row size of a table - SQL Server

You can use the DATALENGTH function to get the number of bytes for each column in a row. You can sum these up to get the total for the row, get the MIN, MAX, AVG and so forth.

SELECT DATEPART(year,created) AS [Year],
       DATEPART(month,created) AS [Month],
       COUNT(*) AS Rows,
       SUM(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS TotalRowSize,
       AVG(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS AvgRowSize,
       MIN(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS MinRowSize,
       MAX(DATALENGTH([User]) + DATALENGTH([UserID]) + DATALENGTH([True]) + DATALENGTH([Comment]) + DATALENGTH([Created]) + DATALENGTH([Flag]) + DATALENGTH([Destination])) AS MaxRowSize
FROM Connections
GROUP BY DATEPART(year,created),DATEPART(month,created)

The advantage of DATALENGTH is that it provides the actual used bytes for variable length fields, so there is no need to guess/estimate. See this db<>fiddle for a working example.


Should I put common fields in each specific tables or one common table then join when data selecting?

I think the benefit of having a common table for blog, video, gallery, comment  is that you will have unique id for each one of these, but that is really not needed.

And It seems that all these share some common attributes, so we need to have common table to save those, but from application point of view they will be different and needs to be save in different table.

Saving all of them in common table will be problem in maintainance, and also while fetching those entry to show in UI, So it would be better to have different table for all these four.

Then there comes thing is how to store comments, Well comments can be on video or blog or gallery, and with time it can be very large in numbers so you can choose one comment table for each category.


ORA-00933: SQL command not properly ended

Your syntax for using the database link is incorrect, you've got the link and table identifiers in the wrong order.

It should be:

select ... from table_name@db.link.name

Having too many . in a table name results in an ORA-00933 error, like you're getting.


How to find duplicate digits in the middle of similar numbers?

Here is one method for doing that.  It assumes that the strings are all the same length and you really want to find all the cases where the middle string is duplicated.

Finds all duplicate "middle", update the CTE_Split to use your actual table and column names as appropriate.

;WITH CTE_Split AS
    (
    SELECT ID
        , LEFT(ValueString, 4) AS PreFix
        , SUBSTRING(ValueString, 5, 4) AS Middle
        , RIGHT(ValueString, 2) AS Ending
    FROM (VALUES (1, '1234567890')
                , (2, '2086567845')
                ) AS P (ID, ValueString)
    )
, CTE_Duplicate AS
    (
    SELECT ID
        , Prefix
        , Middle
        , Ending
        , RN = ROW_NUMBER() OVER (PARTITION BY Middle ORDER BY ID)
    FROM CTE_Split
    )
SELECT ID, Prefix, Middle, Ending, RN
FROM CTE_Duplicate
WHERE Middle IN (   SELECT Middle
                    FROM CTE_Duplicate
                    WHERE RN >= 2
                    )


How to decide when is use ERD or UML for data modeling?

I think you answered your own question. E-R diagrams are meant to be problem solving diagrams for the sake of creating a database schema, whereas UML are supposed to model a completed db. I personally don't place a huge amount of value on ER diagrams, as I would personally do such brainstorming in my head, but I imagine many people would swear by them. As such, the answer is UML as a general rule, but ER can hypothetically be useful in the construction of databases.


Grant user from my database to execute xp_cmdshell

I suggest you wrap the code that executes xp_cmdshell in a stored proc in your user database and sign it with a certificate that has the needed permissions. That way, users are limited to the user stored procedure and can't execute ad-hoc xp_cmdshell commands. OS permissions are also limited to the proxy account for non-sysadmin role members. This certificate technique is detailed in Erland Sommarskog's Packaging Permissions in Stored Procedures article.

Below is an example script.

-- Enable xp_cmdshell and create proxy account
USE master;
EXEC sp_configure 'show',1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE;
EXEC dbo.sp_xp_cmdshell_proxy_account 'YourDomain\YourProxyAccount', 'YourPr0xy@accountPassw0rd';
GO

-- Create certificate in master.
CREATE CERTIFICATE xp_cmdshell_cert
   ENCRYPTION BY PASSWORD = 'All you need is love'
   WITH SUBJECT = 'For xp_cmdshell privileges',
   START_DATE = '20020101', EXPIRY_DATE = '20300101';
GO

-- Create a login for the certificate.
CREATE LOGIN xp_cmdshell_cert_login FROM CERTIFICATE xp_cmdshell_cert;
CREATE USER xp_cmdshell_cert_login;
GRANT EXECUTE ON dbo.xp_cmdshell TO xp_cmdshell_cert_login;
GO

-- Copy cert to user database
DECLARE @cert_id int = cert_id('xp_cmdshell_cert')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
        @private_key varbinary(MAX) =
           certprivatekey(@cert_id,
              'All you need is love',
              'All you need is love')

SELECT @cert_id, @public_key, @private_key

DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE xp_cmdshell_cert
       FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          convert(varchar(MAX), @private_key, 1) + ',
          DECRYPTION BY PASSWORD = ''All you need is love'',
          ENCRYPTION BY PASSWORD = ''All you need is love'')'

EXEC YourUserDatabase.sys.sp_executesql @sql;

ALTER CERTIFICATE xp_cmdshell_cert REMOVE PRIVATE KEY;
GO

USE YourUserDatabase;
GO

CREATE PROC dbo.CreateFile
AS
EXEC master..xp_cmdshell 'echo hello > c:\file.txt';
EXEC master..xp_cmdshell 'echo appended data >> c:\file.txt';
EXEC master..xp_cmdshell 'echo more data >> c:\file.txt';
GO
ADD SIGNATURE TO dbo.CreateFile BY CERTIFICATE xp_cmdshell_cert
   WITH PASSWORD = 'All you need is love';
GRANT EXEC ON dbo.CreateFile TO YourUserOrRole;
GO
ALTER CERTIFICATE xp_cmdshell_cert REMOVE PRIVATE KEY;
GO


What is the best way to shut down a 2 node availability group

No need to touch the AGs.  You should have full set of database backups on a USB external drive or on Azure Blob Storage before you shut down the datacenter.
Just shut down the secondary node first, then the primary.  Bring them back up in the reverse order.  Boot up your domain controller VMs first, then any other supporting infrastructure, like the VM hosting the FSW.  When you bring the VM hosting the primary replica online it should establish quorum and come online.


Can and should relational database contain tables that don't have primary keys?

I love that your question includes the qualifier "under a university setting." It just highlights that there is a difference in theory and practice.

We have a few tables in production that don't have an indexed primary key and they are lightning fast to read. We do have indexes on the columns that we read on and join from. But in the University, they probably want you to say always include a primary key column.


Update single table with mutually exclusive threads

Yes, it's ok to use (READPAST, UPDLOCK) where inUse = 0.

UPDLOCK hint will force SQL Server to use (U) locks instead of (S) locks and (U) locks are incompatible with each other, so the two threads will not be able to fetch the same row.

READPAST hint will force SQL Server to skip the locked rows rather than being blocked.

The batches are not "gone". They are just not always consist of records with consecutive IDs.

To illustrate this , let's add a FetchTime column to the table and modify the relevant parts of the procedure:

      DECLARE MY_CURSOR CURSOR
         LOCAL STATIC READ_ONLY FORWARD_ONLY
         FOR
         SELECT top X Id,GETUTCDATE() as FetchTime
         FROM Names WITH (READPAST, UPDLOCK)
         where inUse = 0
         order by 1

.
.
.

  OPEN MY_CURSOR
        FETCH NEXT FROM MY_CURSOR INTO @Id,@Fetchtime

.
.
.

 update Names set InUse = 1, Thread = @Thread,FetchTime=@FetchTime where id = @Id

All the rows in a batch will have the same value in the FetchTime column.

Now it is more evident that the batches are not "gone" :

Yes. In fact, this approach is more efficient than synchronizing access to the table by implementing a critical section or using a mutex because the two threads can read the data in parallel without blocking each other.


Is there anything I can do to increase performance in the future once table gets bigger?

If all queries are of the form:

select * from testtable where C_CustomerID = 12345678

then what you have already is not entirely unreasonable. There is a cost to retrieving columns not covered by the nonclustered index from the base table (currently a heap), but unless there are many rows per C_CustomerID, this may not be a practical concern.

That said, the current arrangement is not ideal. There is a chance that SQL Server will decide to scan the whole heap for matches, rather than seek into the nonclustered b-tree index and then perform multiple single-row lookups.

In the absence of any other considerations, I would probably replace the nonclustered index with a (non-unique) clustered index:

DROP INDEX inx_profitability ON dbo.testtable;
GO
CREATE CLUSTERED INDEX [CX dbo.testtable C_CustomerID]
ON dbo.testtable (C_CustomerID);

The clustered index defines the logical ordering of the rows in the table, and provides direct access to the whole row (so no lookups needed). To a large extent, a clustered index is largely 'free', since it is not a separate structure with a copy of (some of) the data.

For rows with the same C_CustomerID, SQL Server will add a hidden integer 'uniquifier' to ensure each row is uniquely identifiable internally. You cannot reference this value directly, but it does add a small overhead.

Making this a clustered table (by adding the clustered index) will also likely improve unused future space management for the table, in the event you delete rows (the question mentions an export).

You might choose to specify a fill factor on the clustered index build above (and when performing maintenance) e.g. by adding WITH (FILLFACTOR = 70). This will reserve a proportion of free space at the leaf levels for future inserts (remember the rows and pages are logically ordered by C_CustomerID). The choice of 70 is entirely arbitrary. The value you choose (if any) is entirely situation-dependent. If all queries are as shown above, there is probably little need to specify a fill factor.

In principle, every table should have a key (primary or otherwise) that enforces whatever real-world combination of columns uniquely identifies each row. This could be a nonclustered primary key or unique constraint in your case.


How to mysqldump to a subset of rows in specific table during a complete backup

Dumping subsets of table data can only be done backing up individual tables.

I once answered a post entitled Is it possible to mysqldump a subset of a database required to reproduce a query?. In that post, I used mysqldump strictly against the tables in question.

The only thing you can do is to produce two dumps

mysqldump of the database without table1 (means you have to name all other tables (See my old post How do you mysqldump specific table(s)?)
mysqldump only table1 using -w or --where
concatenate the second dump to the first dump

Give it a Try !!!


How bigger page size effect at performance?

For bulk operations in index order a larger page size can give a noticeable, sometimes significant, performance increase. For very random access, particularly many small writes, there is similar potential for significant performance reduction.

A single read of a 16K block will take four times longer than the read for a 4K block, assuming the drive heads are in the right place at the time, but reading four sequential 4K blocks will be almost the same as reading a single 16Kb one.

Unfortunately it isn't something we can give you more precise advice on, especially without a lot of information about your application's I/O patterns - it is something that you probably need to benchmark with your specific application in your test environments to be sure about.

4Kb is usually picked for the default (at least on x86 or a64 based CPUs) as it matches pages sizes in the underlying OS and sometimes hardware so in the general case is more efficient because it is the "natural size". DB specific considerations override this though, which is presumably why MS SQL Server uses 8Kb page sizes.


How many stored procedure parameters is too many?

Issues? I'd argue none.

The limit is 2100 parameters. IIRC it has been 2100 since SQL2000 but a documentation error suggested it was 1024.
If a table has 1000 columns (due to a Sharepoint-esque sparse column arrangement for example) and you are enforcing access via stored procedures, your insert proc may have 1000 parameters. Nothing wrong with that.
Do pause to review the schema when you encounter a wide table (not that 30 is particularly wide). It's not uncommon to find tables that started off life normalized but through laziness and/or fecklessness have expanded beyond recognition.
Don't even briefly consider passing a set of parameters as a CSV list or XML. Blinds the query optimiser and saves little to no time or effort.
Don't hand crank the client code to call a procedure with a large number of parameters. Code generation tools like T4 templates or CodeSmith to the rescue.


How to model local exceptions to distributed tables

Put the COALESCE in a view write queries against the view, not the base tables. It would help if you could remove access to these tables and grant access to the view only.
There will be a small runtime penalty with this, as two tables must be read. If you could change the load to perform the COALESCE once at load time that would be better. Subsequent changes to local price must be written to both local table and to the master list. It would not be possible to run reports on, say, the cost of local pricing as the official prices would be unknown.


How to calculate streaks of consecutive dates for a user

To keep track of longest streak and when it occurred.

Table columns needed:

current_streak          int
longest_streak          int
longest_streak_end_date datetime

Pseudocode:

if user has logged in today already
    return
if user logged in yesterday
    add 1 to current_streak
    if current_streak > longest_streak
        set longest_streak = current_streak
        set longest_streak_end_date = today
else
    set current_streak = 1


The first thing I'd do is check the stats for the columns individually with these queries:

select count(*) from table1 where col1 = 123;
select count(*) from table1 where col2 = '1';

If the estimated cardinalities here agree much better with the actual row counts, it means there is some correlation between the columns that the CBO cannot guess based on the histograms generated by a vanilla gather_table_stats. Dynamic sampling should improve things at the cost of increased parse time, but is not a silver bullet.

Alternatively, with 11g, "Oracle Database can also gather statistics on a group of columns within a table"

create a column group
gather stats on a column group


Should I load the data into Temp table or keep calling it from the normal table?

If the volume of data you're retrieving is large - roughly, millions of narrow rows, or hundreds of thousands of wide rows, or tens of thousands of rows with large objects - you should probably cache the data in a temp table and reuse that.  If you're performing significant processing to get the data - such as a table scan - you should probably cache it.  Aside from speed, I can think of a couple other reasons to dump the data to a temp table:

It minimizes the length of time the source table is locked (this can be very important).
It gives you the opportunity to rename fields or use more restrictive data types (such as NOT NULL constraints) that only apply to your data set.
It gives you a chance to add calculated fields or indexes which are not present in the original.

...and against:

It entails a little extra development work.
It requires additional space, in memory, on disk, or both.

That said, this may be premature optimization; how long does your sproc take to run?


Sql Server: A seemingly strange behaviour of BEGIN TRAN - COMMIT

The behaviour depends on SET XACT_ABORT and is explained in the following quote from the linked article.

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a
run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL
statement that raised the error is rolled back and the transaction
continues processing. Depending upon the severity of the error, the
entire transaction may be rolled back even when SET XACT_ABORT is OFF.
OFF is the default setting.


How to get host server CPU info from SSMS

I found this query here:
----------------------------------------------------------------------------------------------------------------
-- CPU VISIABLE ONLINE CHECK
----------------------------------------------------------------------------------------------------------------
DECLARE @OnlineCpuCount int
DECLARE @HiddenCpuCount int
DECLARE @LogicalCpuCount int

SELECT @OnlineCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'
SELECT @HiddenCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status != 'VISIBLE ONLINE'
SELECT @LogicalCpuCount = cpu_count FROM sys.dm_os_sys_info

SELECT @LogicalCpuCount AS 'ASSIGNED ONLINE CPU #', @OnlineCpuCount AS 'VISIBLE ONLINE CPU #', @HiddenCpuCount AS 'HIDDEN ONLINE CPU #',
   CASE
     WHEN @OnlineCpuCount < @LogicalCpuCount
     THEN 'You are not using all CPU assigned to O/S! If it is VM, review your VM configuration to make sure you are not maxout Socket'
     ELSE 'You are using all CPUs assigned to O/S. GOOD!'
   END as 'CPU Usage Desc'
----------------------------------------------------------------------------------------------------------------
GO

And if you want you can also check each CPU one by one with this:
SELECT scheduler_id,
       cpu_id,
       STATUS,
       is_online
FROM sys.dm_os_schedulers;
GO

Now, if you are trying to calculate the licensing you can use several websites:

MS SQL 2019 Std Licensing Calculator (unofficial)
MS SQL 2019 Ent Licensing Calculator (unofficial)
https://www.dxc.technology/SLMS/flxwd/148445-dxc_sql_calculator
https://www.sqlgovernor.com/en/calculator/

I hope this helped.


Best way to copy a SQL Server database from a remote server to local computer?

You should talk to your support team at appharbor. Presumably there is a way to take a backup of your database, store it on the file system somewhere, and then download the .BAK file (through http or, more likely, ftp). This should be a lot less work than your current process, but it depends on your host providing the facility to do so. This also assumes that your backup file would be < 10 GB (since this is the maximum supported by Express) and that it doesn't use any features not supported in Express Edition (such as Data Compression, SQL Server Agent, etc.).

You can do this using just Management Studio, e.g. you can right-click your database, Tasks > Generate Scripts and select all objects and on the Scripting Options tab make sure to go into advanced and select the option to script both schema and data. This will generate a humongous script that you can run on your local machine after you've created an empty database. Personally I find a backup / restore much more reliable and a lot less hassle.

But asking here isn't going to be much help - unless you happen to come across someone who has done this specific thing at that specific host using the specific hosting plan you have, there is little chance any of us knows what options you have - appharbor is going to be able to help you answer that question a lot more accurately than any of us.


Force some composite type fields to be unique?

First of all, you can't add constraints to types therefore the type definition you've provided will error out.
Domains as you've already observed won't work since they can only accomodate a single type at a go rather than a composition of types.
You also don't have to add a not null constraint to a primary key - it's redundant, Postgres by default disallows null values for primary key columns.
The best approach will be to normalize the table asset so that you can maintain the restrictions you require since both domain definitions and type defintions can't accomodate these restrictions.
For the sake of completion, here's a suggestion for the table defintions.
Given that an asset has multiple images but an image can only belong to one asset, a one-to-many relationship captures this perfectly as shown below. Furthermore, given how your code is currently structured, different assets can have the same (lang, size) combo. Therefore, the unique constraint is added across both the asset_id, lang and size
create table asset (
  id bigserial primary key,
  /* other asset columns ... */
);

create table img (
    asset_id bigint references asset(id) not null,
    lang varchar(2) not null,
    size varchar(32) not null,
    img_path varchar(255) not null,
    unique(asset_id, lang, size)
);

Note, the unique constraint creates an index hence the checks will be fast. Therefore, as expected, the following insert fails:
insert into img
values
    (1, 'en', '32 kb', 'bar path'),
    (1, 'en', '32 kb', 'foo path');


High Disk I/O from sql server or is High disk I/O slowing sql server?

Have a look at the following perfmon counters:

SQL Server, Buffer Manager Object:
Page lookups/sec
Page reads/sec
Readahead pages/sec
SQL Server, Access Methods Object
Full Scans/sec
Range Scans/sec
Skipped Ghosted Records/sec
SQL Server, Wait Statistics Object
Page IO latch waits

SQL Server driving a high number of IO requests would be corroborated with a high number scans, increase in page lookups and page reads and high page IO latch waits. Is worth trying a look at sys.dm_exec_query_stats for entries with high physical reads counts. They could quickly pinpoint the culprit.

In general approaching the problem as a performance troubleshooting problem, following a methodlogy like Waits and Queues is the right approach. You DBA seems to be doing the right thing so you should listen to him.


Database structure user service connection

In agreement with the comments made by @Laughing Vergil, this is the structure I would use:

CREATE TABLE users
(
    user_id BIGINT PRIMARY KEY,
    username CHARACTER VARYING(255) NOT NULL,
    password CHARACTER VARYING(255) NOT NULL
) ;

CREATE TABLE services
(
    service_id BIGINT PRIMARY KEY,
    name CHARACTER VARYING(45) NOT NULL
) ;

CREATE TABLE connections
(
    connection_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(user_id),
    service_id BIGINT NOT NULL REFERENCES services(service_id),

    authorization_code CHARACTER VARYING(255),
    refreh_token CHARACTER VARYING(255),
    access_token CHARACTER VARYING(255),

    service_username CHARACTER VARYING(255),
    service_password CHARACTER VARYING(255),

    /* Either one of authorization code or username must be null,
       but not both */
    CHECK ((authorization_code IS NULL) <> (service_username IS NULL)),

    /* If the authorization_code is NULL;
           refresh_token and access_token must also be null */
    CHECK (
        CASE WHEN (authorization_code IS NULL) THEN
            (refreh_token IS NULL) AND
            (authorization_code IS NULL)
        ELSE
            true
        END),

    /* If the service_username is NULL; service_password must also be null */
    CHECK ((service_username IS NULL) = (service_password IS NULL))
) ;

You can do a few tests:

INSERT INTO users VALUES(1, 'user', 'pwd');
INSERT INTO services VALUES (1, 'service');

/* Good */
INSERT INTO connections (connection_id, user_id, service_id, service_username, service_password)
VALUES (10, 1, 1, 'user', 'pwd');

/* Also good */
INSERT INTO connections (connection_id, user_id, service_id, authorization_code, refreh_token, access_token)
VALUES (20, 1, 1, 'auth_code', NULL /* Don't know yet */, NULL /* Don't know yet */) ;

/* Bad */
INSERT INTO connections (connection_id, user_id, service_id, service_username, authorization_code)
VALUES (21, 1, 1, 'user', 'auth') ;

/* Bad */
INSERT INTO connections (connection_id, user_id, service_id, service_username, service_password)
VALUES (30, 1, 1, 'user', NULL);


SQL update multiple columns matching a pattern

I think you're trying to avoid writing:

  UPDATE mytable SET
   door_open = door_open + INTERVAL '1' HOUR,
   door_close = door_close + INTERVAL '1' HOUR,
   car_open = car_open + INTERVAL '1' HOUR,
   car_close = car_close + INTERVAL '1' HOUR,
   ... blah blah ...
  WHERE
   ...

and instead want a way to match column names by wildcard.

If that is the case, then no, there is no built-in way to do that.

You can construct a query dynamically using PL/PgSQL: Use a query against the information_schema.columns view to get column names, then string concatenation to form the query. Then you can run it with EXECUTE. There are many examples of such dynamic SQL elsewhere on Stack Overflow.

e.g.

DO
$$
DECLARE
  sqlstring text;
BEGIN
  SELECT INTO sqlstring
   'UPDATE blah SET '
   || string_agg(format('%I = %I + INTERVAL ''1'' HOUR', column_name, column_name), ', ')
   || ' WHERE true'
  FROM information_schema.columns
  WHERE table_name = 'blah'
    AND table_schema = 'public';

  EXECUTE sqlstring;
END;
$$;


The easiest way to do that is on the back end.  Define a user that has only the access you want and then update the ODBC DSN that Access uses to that user account.


This is because your temp folder is full.
you can make a new temp folder where is more space after that let mysql know about your newly created temp folder(dont forget to restart mysql service),
or you can also clear your temp folder, it will work.
you can follow this stackoverflow link for more information ERROR 1114 (HY000): The table 'XXX' is full


OPENROWSET shuting down SQL Server service, but only running on the server

Go check your provider (Microsoft.ACE.OLEDB.12.0) settings in SSMS.

Server Objects -> Linked Servers -> Providers ->
Microsoft.ACE.OLEDB.12.0 -> Properties

If "Allow inprocess" is ticked off, the provider code is running inside the SQL Server process. This can cause the entire process to shut down. More info in this article


using lag() on multiple columns (gaps and islands issue)

lag() only works over a single column, so you need to define one new "column" for each lag value. You can use a common Window clause so that it's clear to the query planner that it only needs to sort the rows once -- although I think Postgres can determine this for itself too -- but it also saves typing.

After that it's just a matter of comparing the "lagged" values to see if they are in any way different. is distinct from is handy here as the first "lagged" values will all be null, so they are distinct from the first row's values and so the first row will be included in the output.

Putting it all together, it works like this:

select textColA, textColB, textColC, textColD, textColE, numCol1, numCol2, eventNum
from (
  select
    l.*,
    lag(textColA) over w as lagTextColA,
    lag(textColB) over w as lagTextColB,
    lag(textColC) over w as lagTextColC,
    lag(textColD) over w as lagTextColD,
    lag(textColE) over w as lagTextColE,
    lag(numCol1) over w as lagNumCol1,
    lag(numCol2) over w as lagNumCol2
  from lagger l
  window w as (order by textColA, textColB, textColC, textColD, textColE, eventNum)
) x
where
   textColA is distinct from lagTextColA
or textColB is distinct from lagTextColB
or textColC is distinct from lagTextColC
or textColD is distinct from lagTextColD
or textColE is distinct from lagTextColE
or numCol1 is distinct from lagNumCol1
or numCol2 is distinct from lagNumCol2
;

Complete fiddle is here: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=2e11af872a47bd75a20c64f87e7a6cd6 (NB In the fiddle I forgot the other columns in the Window clause)


Why is the CTE much worse than inline subqueries

Try this:

with me as (
    select * from users where id = 2
)
select u.id, u.popularity from users u, me
    where u.gender = (select gender from me)
    order by  u.latest_location::geometry <-> (select latest_location from me)::geometry ASC
    limit 50;

When I look at the fast plan here's what jumps out at me (bolded):

 Limit  (cost=5.69..20.11 rows=50 width=36) (actual time=0.512..8.114 rows=50 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using users_pkey on users users_1  (cost=0.42..2.64 rows=1 width=32) (actual time=0.032..0.033 rows=1 loops=1)
           Index Cond: (id = 2)
   InitPlan 2 (returns $1)
     ->  Index Scan using users_pkey on users users_2  (cost=0.42..2.64 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)
           Index Cond: (id = 2)
   ->  Index Scan using users_latest_location_gix on users  (cost=0.41..70796.51 rows=245470 width=36) (actual time=0.509..8.100 rows=50 loops=1)
         Order By: (latest_location  $0)
         Filter: (gender = $1)
         Rows Removed by Filter: 20
 Total runtime: 8.211 ms
(12 rows)

In the slow version, the query planner is evaluating the equality operator on gender and the geometry operator on latest_location in the context of a join, where the value from me could vary with each row (even though it has correctly estimated only 1 row).  In the fast version the values of gender and latest_location are treated as scalars because they are emitted by inline subqueries, which tells the query planner it only has one value of each to deal with.  This is the same reason why you get the fast plan when you paste the literal values.


triggers or else between 2 postgres databases hosted on a different datacenter

I don't think this is a good idea, you should read the documentation on Streaming Synchronous Replication instead which guarantees consistency and is a ton easier to configure.

Even if you're not going to use that, you should consider postgres_fdw instead of the older dblink.


Please consult the Oracle Update and Migration Guide

There is no direct upgrade path, for your data a export/import might work.
Your old Forms application will not work, Oracle 10.1 was the last version that could run old 6i Client/Server Forms,IIRC


SQL squash rows returned when joining several tables

Something like

WITH BN AS (SELECT id,
                   item_id,
                   ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY id) rn
            FROM B),
     CN AS (SELECT id,
                   item_id,
                   ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY id) rn
            FROM C)
SELECT A.id, BC.id1, BC.id2
FROM A
LEFT JOIN (SELECT BN.item_id item_id, BN.id id1, CN.id id2
           FROM BN
           FULL OUTER JOIN CN ON BN.item_id = CN.item_id
                             AND BN.rn = CN.rn ) BC ON A.id = BC.item_id


What types of methods can we call from a .NET assembly in SQL Server

SQLCLR is a very limited CLR host. Yes, you can only call static methods due to the App Domain being shared across all sessions (i.e. @@SPID), and hence any "shared" instances or variables would be very much not "thread safe". App Domains are created on a per-Database, per-"owner" (i.e. whatever User is listed in the AUTHORIZATION clause of CREATE ASSEMBLY, or dbo as the default if that clause is omitted), so there is a minimal amount of separation, but for any given method that is exposed via a CREATE {object} AS EXTERNAL NAME statement, any User in any Session executing that SQLCLR Stored Procedure, Function, Trigger, or User-Defined Type will be hitting the exact same App Domain. This is why you are also not allowed to store values in static class variables (unless they are marked as readonly) as they are shared across Sessions and one Session can overwrite the value that another Session just stored and hence you would get odd / erratic / unpredictable behavior.

Regarding SQLCLR being a restricted environment:

Please see the following MSDN page for a list of what is not allowed in SQLCLR: CLR Integration Programming Model Restrictions.
Please see the following MSDN page for a list of what few .NET Framework libraries are included in SQLCLR: Supported .NET Framework Libraries.
For more information on working with SQLCLR in general, please see the series I am writing on this topic on SQL Server Central (free registration is required to read their content, but it's worth it): Stairway to SQLCLR.


There is no built-in way to do so with mysqldump. However, all is not lost, you should be able to get away with it using sed.

Let's say your routine is a function, which is called split_token. The following code:

mysqldump --all-databases ... | sed "/DROP FUNCTION IF EXISTS \`split_token\`/,/^DELIMITER ;$/d"

will disregard the lines starting with DROP FUNCTION... and until the DELIMITER statement which ends the function's definition.

The above does not verify:

A function of the same name may exist in multiple databases
The text may appear within a string -- need better regexp matching (try and match the code starting the line using ^ -- look at the following line:

/*!50003 DROP FUNCTION IF EXISTS split_token */;

Leaving this up to you to find the best regular expression.


How to use OpenLDAP for Oracle database user authentication

Permitted Features, Options, and Management Packs by Oracle Database Offering

  Enterprise User Security is a feature for directory-based management
  of database users. It requires a corresponding Oracle Identity
  Management Directory Services Plus to be licensed.

Enterprise User Security: The Big Picture

  Enterprise User Security requires Oracle Internet Directory 10g
  (9.0.4) or higher. Other LDAP-compliant directory services are
  supported by using Oracle Internet Directory Integration Platform to
  synchronize them with Oracle Internet Directory. Another directory
  services product, Oracle Virtual Directory, provides a single, dynamic
  access point to multiple data sources through LDAP or XML protocols.
  Oracle Virtual Directory can provide multiple application-specific
  views of identity data stored in, for example, Oracle Internet
  Directory, Microsoft Active Directory and Sun Java Systems Directory
  instances, and can also be used to secure data access to the
  application-specific sources and enhance high-availability to existing
  data-sources.

Through Oracle Virtual Directory or Oracle Unified Directory.
See above. Technically it should be possible to use as standalone, but not supported at all.
None for direct connection. See above.


PostgreSQL will stop logging when logrotate rotate postgresql log file

You can try using the copytruncate directive of logrotate if you can accept the small risk of losing log data.


Is there a way of determining the count of branches for a current request?

You can kind of get at that information with a query like this:

SELECT
    r.session_id,
    r.dop,
    r.parallel_worker_count
FROM sys.dm_exec_requests r
WHERE r.session_id = 62;

Note that there's nothing special about 62, it's just where my parallel query happened to be running.

You can see this query is running at DOP 4, and has a parallel worker count of 12.  So this means that the query likely has 3 parallel branches.  In this case it definitely does, as I'm reusing a demo query from my blog post about CXCONSUMER waits:

I'm not sure how this works out if SQL Server is able to reserve less than DOP * branches - the situation Paul describes in the blog post you linked to:

  The presence of blocking operators means that one or more parallel branches might be guaranteed to complete before others can start. Where this occurs, SQL Server can reuse the threads used to process a completed branch for a later branch in the sequence. SQL Server is very conservative about thread reservation, so only branches that are guaranteed to complete before another commences make use of this thread-reservation optimization.

To your other question:

  Is there a way of relating tasks to a branch?

That's covered in detail in Paul's answer on this other question: What's the easiest and most accurate way to visualize parallel thread usage in SQL Server?

The way that query relates threads to branches is through the "nodeId" property, which you can find in the query's execution plan by hovering over the different operators.

As far as I know, there's nothing explicit in execution plans that identifies branches, or where they start and end, or which nodes belong to them.  It's just known that branches are bounded by exchange operators.

If you really wanted to "assign" a number to different branches for a running request, you could divide the exec_context_id by dop, letting it round to the nearest integer.

Modifying Paul's query from the linked answer, that idea would look like this:

DECLARE @session_id smallint = 51;

SELECT
    DOT.task_state,
    DOT.scheduler_id,
    DOT.exec_context_id,
    CASE
        WHEN DOT.exec_context_id = 0 THEN 'N/A'
        ELSE CONVERT(varchar(3), ((DOT.exec_context_id - 1) / DER.dop) + 1)
    END AS branch_id,
    DEQP.physical_operator_name,
    DEQP.node_id,
    DEQP.thread_id,
    DEQP.row_count,
    DOWT.wait_duration_ms,
    DOWT.wait_type,
    DOWT.resource_description
FROM sys.dm_os_tasks AS DOT
INNER JOIN sys.dm_exec_requests DER
    ON DOT.session_id = DER.session_id
OUTER APPLY
(
    -- What each thread did most recently
    SELECT *
    FROM sys.dm_exec_query_profiles AS DEQP
    WHERE
        DEQP.session_id = DOT.session_id
        AND DEQP.request_id = DOT.request_id
        AND DEQP.task_address = DOT.task_address
    ORDER BY
        DEQP.last_active_time DESC
    OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS DEQP
OUTER APPLY
(
    -- Longest wait, if any
    SELECT *
    FROM sys.dm_os_waiting_tasks AS DOWT
    WHERE
        DOWT.session_id = DOT.session_id
        AND DOWT.exec_context_id = DOT.exec_context_id
    ORDER BY
        DOWT.wait_duration_ms DESC
    OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS DOWT
WHERE
    DOT.session_id = @session_id
    --AND DOT.task_state = N'RUNNING'
ORDER BY
    DEQP.node_id,
    DOT.exec_context_id;


Automatic database restore fails as cannot find file

Copy step of your job will just inform operating system to start copy process and will mark this step completed and this will initiate next step "Restore Database", but meanwhile operating system is still copying your file to destination.

Before restore, you must verify file existence first.

Declare @file_exists int
EXEC xp_fileexist 'c:\MyDatabase.bak', @file_exists out
IF @file_exists = 1
BEGIN
  RESTORE DATABASE ....................
END
ELSE
  RAISERROR ('Restore Job Step Failure!', 16, 1)


how to set default value of column 0 when NULL is inseerted

If the NULL value is explicitly specified, you'll need to use a DML trigger to update NULL values to 0 after insertion.  This can't be done with a default constraint.


Parsing JSON data from a text column in Postgres

To answer this question what I did was the following (see fiddle here):
This was quite a learning experience for me (+1 for that BTW) so I'll go through the steps in my logic and hopefully this will help you - but I'm doing it for myself really :-)
Step 1:
CREATE TABLE temp_1
(
  json_input TEXT NOT NULL
);

Step 2:
Populate it with your two records. However, your two records aren't proper JSON - they need a [ at the beginning and ] at the end in order for them to be proper JSON - so, I've put them into the INSERT at the very beginning of the process - I leave it up to the reader to experiment with what happens if you leave them out - you can put them in later in the process - see below.
There may be a way of not having to do this with a different JSON function - beyond my pay grade I'm afraid - but I'd appreciate any suggestions for improvements.
INSERT INTO temp_1 VALUES  -- strings copied from the question
('{"technology":"AAA","vendor":"XXX","name":"RBNI","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RBNI\", \"temporal_unit\": \"hour\", \"technology\": \"LTE\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RBNI\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"ZTE\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":1}'),
('{"technology":"AAA","vendor":"XXX","name":"RRCADD","temporal_unit":"hour","regional_unit":"cell","dataset_metadata":"{\"name\": \"RRCADD\", \"temporal_unit\": \"hour\", \"technology\": \"AAA\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RRCADD\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"XXX\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}","rk":1}');

Then, turn the strings into JSON as follows - this can be done in bulk:
--
--  This is to turn the "ordinary" strings into JSON per record. If this isn't
--  done, the process will fail further down. Will work for an arbitrary number
--  of records
--

UPDATE temp_1
SET json_input = '[' || json_input || ']';

Step 3:
Create another table:
CREATE TABLE temp_2
(
  final_json JSON NOT NULL
);

Step 4:
Populate this by:
INSERT INTO temp_2 SELECT json_input::JSON FROM temp_1;

The ::JSON cast is necessary, otherwise the INSERT will fail as json_input is of type TEXT.
Then, clear down `temp_1':
--
--  Clear down temp_1.
--  This is needed for next batch, otherwise you'll have strings beginning
--  with '[[' and then '[[[' (endings same with multiple ']'s) and so on!
--

DELETE FROM temp_1;

Step 5:
Create a table to hold the data:
CREATE TABLE json_table
(
  technology TEXT,
  vendor TEXT,
  name TEXT,
  temporal_unit TEXT,
  regional_unit TEXT,
  dataset_metadata JSON,
  rk SMALLINT
);

Step 6:
From here, I obtained this method to populate the table:
INSERT INTO json_table
SELECT (json_populate_recordset(null::json_table, final_json)).* FROM temp_2;

There's another way shown in the fiddle - from here - YMMV?
Step 7 - final check:
SELECT * FROM json_table;

Result:
technology  vendor  name    temporal_unit   regional_unit   dataset_metadata    rk
AAA XXX RBNI    hour    cell    "{\"name\": \"RBNI\", \"temporal_unit\": \"hour\", \"technology\": \"LTE\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RBNI\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"ZTE\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}"   1
AAA XXX RRCADD  hour    cell    "{\"name\": \"RRCADD\", \"temporal_unit\": \"hour\", \"technology\": \"AAA\", \"is_dimension\": false, \"timestamp_column_pattern\": \"yyyyMMddHHmmss\", \"data_type\": \"PM\", \"source_name\": \"RRCADD\", \"intervals_epoch_seconds\": [[1609941600, 1609945200]], \"identifier_column_names\": [\"CELLID\", \"CELLNAME\", \"radio_frequency_band\", \"ENODEBID\", \"ENODEBNAME\", \"SBNID\", \"SITEID\", \"SITENAME\", \"CLUSTER_PRIORITY\", \"CP_SITE\", \"IBC\", \"NETWORK_TIER\", \"SITE_TYPE\", \"T3_FLAG\", \"CLUSTERID\", \"CLUSTERNAME\", \"REGION\", \"NETWORK\"], \"vendor\": \"XXX\", \"timestamp_column_name\": \"COLLECTTIME\", \"regional_unit\": \"cell\"}"   1

A couple of thoughts:

Your dataset_metadata field doesn't appear to be "proper" JSON - maybe a suitable REGEXP_REPLACE and/or a CAST might be in order? I don't know the process by which you get your /home/xyz/cgs_test.json file - but it may be worth looking at how the record is generated - ideally, you should correct any issues as early in the pipeline as possible.

I don't know your exact circumstances, but you should carefully consider the advice here - you might want to hive your metadata off into a separate table with a FOREIGN KEY connecting it to the parent record?