CSV (UTF-8) to Postgres database (UTF-8), doesn't show äöå characters

I solved my own problem. Everything was right from server side and problem was few little things. My first mistake was that I made two text files that I used in my code to generate users. These two text files was in ANSI encoded. I made them with notepad in windows and didn't think much of it.

Even though Visual Studio Code show these ANSI encoded text-files correctly and writes them inside of UTF-8 csv-file correctly. Even if I open that csv-file with notepad++ it's show's those characters as UTF-8 encoded. However when I pushed them to database they all broke up. Not sure why they show up correctly in UTF-8 file in notepad++? Is there some weird translation correction?

I solved this to convert those ANSI text files to UTF-8 with notepad++.

However my second mistake was that I didn't open those files as UTF-8! So even when I converted them to UTF-8 my Visual Studio tried to read them as latin-1 (ISO-8859-1). This round they where broken before I pushed them to database. I took me while to notice, because end result was still the same as ANSI coded files.

I corrected this with adding encoding="utf-8" to all my open statements. Example here with open('users.csv', 'r',encoding="utf-8") as f: I wanted to be sure that Visual Studio Code will open them as UTF-8 and not some weird format.

This finally solved my issue!! This was really specific problem, so I'm not sure if this will help anyone else. However I wanted to post solution here just in case. I hate those post that just writes up that this is now solved and never tells how.


How to create root user after running mysql_install_db?

There are two techniques

TECHNIQUE #1 : Use an init file

INITFILE=/var/lib/mysql/init-file.sql
echo "SET @newpass = PASSWORD('mypass');" > ${INITFILE}
echo "CREATE USER 'root'@'localhost' IDENTIFIED BY @newpass;" >> ${INITFILE}
chown mysql:mysql ${INITFILE}
service mysql stop
service mysql start --init-file=${INITFILE}
rm -f ${INITFILE}

TECHNIQUE #2 : Inject User into mysql.user

With --skip-grant-tables enabled, run this:

REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = PASSWORD('whateverpasswordiwant'),
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;

Then, restart mysql as usual.

See my post In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table


Enterprise Manager cannot connect to 11g database instance

You may need to resecure the agent. Here is a link that should help.

http://jhdba.wordpress.com/2008/12/10/clearing-an-enterprise-manager-agent-that-fails-to-upload/


How often is Postgres parsing a foreign table based on file_fdw?

The file is read whenever the foreign table is scanned, which can happen several times during processing of a query.

There is no caching except the file system cache of the operating system.


ORA-01122: database file 1 failed verification check

The SCN in the data file is greater than the SCN in the control file or wrong control file is being used. Please use correct control file.

If the the control fle is correct then do BACKUP CONTROLFILE RECOVERY.

Steps:

SQL>startup mount

SQL> recover database using backup controlfile until cancel;

it will ask you for archive logs, give the path of all archive logs which it needs until it gives you message,

MEDIA RECOVERY COMPLETED.

Open the database.

SQL> alter database open;


Ola Hallengren backup systax when excluding a database

it has to be

@Databases =  'USER_DATABASES, -Db1'

See https://ola.hallengren.com/sql-server-backup.html

Assuming you want to backup all User Databases except one named DBA, your statement should be:

EXECUTE [dbo].[DatabaseBackup]
    @Databases = 'USER_DATABASES, -DBA',
    @Directory = N'G:\BACKUP',
    @BackupType = 'DIFF',
    @Verify = 'Y',
    @CleanupTime = 336,
    @CheckSum = 'Y',
    @LogToTable = 'Y'


Find jobs and dependencies run between two timestamps

There is no guaranteed method for this. The closest you can get to this is: DBA_SCHEDULER_JOB_RUN_DETAILS, but:

logging for scheduler jobs can be disabled, so it does not necessarily contain all jobs runs
there is no information about what started the job

Also, there is no history for DBMS_JOB jobs.


Applying Transaction Logs to an existing Database

You have to start over and restore full and diffs with no recovery and then the tlogs  with stand by option. This will allow you to restore tlogs later as well.

You can configure logshipping with option of stand by, so you can read from the secondary database.

There will be a .tuf (transaction undo file) file created when you restore tlogs with standby option.

For manually restoring tlogs use restore gene or you can just use logshipping for automated restores.


How expensive is a connection to pgbouncer?

PgBouncer speaks the normal PostgreSQL protocol.  So a client application connects to PgBouncer normally, except by specifying the host and port where PgBouncer runs instead of the host and port where the PostgreSQL server runs.  (The default port of PgBouncer is 6432.)

If PgBouncer has already established connections to the PostgreSQL backend, then opening a new connection from the client to PgBouncer is very fast.  That is one of several reasons to use PgBouncer.


Failover multiple Basic Availability Groups

This is a difficult scenario to truly handle. I would probably start with running a job on your secondary replica that checks the state of all the databases, and should it become primary for one of them then performs the ALTER AVAILABILITY GROUP [AG] FAILOVER; command for all of the others.

The biggest challenge with this is also running jobs on the primary, and taking care to not accidentally try to fail one of those databases back. You could manage this by checking the AlwaysOn extended event session and looking for failovers there, and do not do work if the database was failed away (as the most recent captured event), this way you don't end up bouncing the databases around all over the place.

You can use sys.fn_hadr_is_primary_replica to check and see if any particular database is the primary on your system.


Azure SQL Databases: High number of SA connections with "BRKR TASK" and "TASK MANAGER" commands

Those BKRK are related system processes and back end connections and do not count upon the limit of connections associated with every service tier. They can safely be ignored. BKRK processes are related to service broker on on-premises SQL Server instances and Azure SQL Database may be using it as part of the service it provides.

With Azure SQL Database you will always see many connections and processes related to the automated features the PaaS provides to the customer. Learn more about back end connections here.

Instead of focus on system processes, please try to know if blocking is the culprit.

select r.session_id,r.blocking_session_id,r.wait_type,r.wait_time,r.wait_resource,r.total_elapsed_time,r.cpu_time,r.reads,r.writes,
s.nt_user_name,s.program_name,s.total_elapsed_time
from sys.dm_exec_requests r
join sys.dm_exec_sessions s
on s.session_id=r.session_id

Consider also deadlocks may be a possibility, and you can find them using below query.

select *
from sys.event_log
where event_type <> 'connection_successful'
and start_time >= CAST(FLOOR(CAST(getdate() AS float)) AS DATETIME)
order by start_time desc  -- event type = deadlock


It sounds like your data is to large for the constraints placed on the table/columns that you are trying to populate.

Take a look at the Data Types of the columns you are adding to and the Limits of those Data Types, then compare that to your values that are producing the errors.


Ignoring both physical and VM memory issues for now.

Queries that take longer and longer to run can be cause by several things, but
I will guess that you perhaps do you have regular index and stats maintenance.


You are looking for the skeys function:

select skeys(hstore_column)
from the_table;

If you also need the values for each key:

select (a).key, (a).value
from (
  select each(hstore_column) as a
  from the_table
) t;

More details in the manual: http://www.postgresql.org/docs/current/static/hstore.html


CAST throwing error when run in stored procedure but not when run as raw query

You're better off not depending on the indexing to avoid these errors, and instead writing the query to guard against this situation.  Since you're on SQL Server 2012, one option would be to use TRY_CAST:

SELECT TRY_CAST(FT.DOP AS SMALLINT)
FROM TRACKING_DATA
WHERE date > @mydate and identifier = 0000000000;

This will result in NULL being selected for values that fail to convert from varchar to smallint.  But as long as you know there aren't any results like that, or your application can handle the NULL results, you should be good.


Query to get the users who have a application approved

You can join the same table twice, once to check that there exists some submission (preferably a rejected one) and second time you left join the same to get any approved submission - then you can check if the left join found something (and remove those - where approved_table.id is null) - to get only one row per user you can do a group by Users.ID.

select users.* from users
inner join Submissions exist_grant
    on users.ID = exist_grant.userID
left join Submissions approved_grant
    on (approved_grant.status="Approved" and users.ID = approved_grant.userID)
where approved_grant.userID is null
group by users.ID


You dropped "F" somewhere.

Since you're given {ABC} is a candidate key, then you know that ABC->DEF. (That just follows from the definition of "candidate key".) And ABC->D, ABC->E, and ABC->F, by Armstrong's axiom of decomposition.

The important part as far as BCNF is concerned is E->A.

  So the final result should be: E1(ABC), E2(ABD), E3(CDE), E4(EA),
  E5(ABCF)

I think that's right, except for E1. You don't need a separate relation for a candidate key.  I'm not sure how you derived that one given what's in the problem.

Usually, your textbook will have at least one algorithm for normalizing relations. In the real world, there's often more than one 5NF schema for a given set of FDs.


Create rows groups by using subgroups limits

After some work I have the following query:

select
    min(box_start) as box_start,
    box_end,
    box_label,
    is_box,
    item_code,
    item
from
    (
        select
            box_start,
            box_end,
            box_label,
            is_box,
            item_code,
            item
        from
            table1
        where
            box_label is not null

        union all

        select
            table1.box_start as box_start,
            table1.box_end as box_end,
            intervals.A || '-' || intervals.B as box_label,
            table1.is_box as is_box,
            table1.item_code as item_code,
            table1.item as item
        from
            (
                select
                    box_start,
                    box_end,
                    box_label,
                    is_box,
                    A,
                    B,
                    max(max_interval_size) as max_interval_size
                from
                    (
                        select
                            box_start,
                            box_end,
                            box_label,
                            is_box,
                            A,
                            B,
                            max(interval_size) as max_interval_size
                        from
                            (
                                select
                                  fixed_table.box_start as box_start,
                                  fixed_table.box_end as box_end,
                                  fixed_table.box_label as box_label,
                                  fixed_table.is_box as is_box,
                                  fixed_table.box_start as A,
                                  windowed_table.box_end as B,
                                  (windowed_table.box_end - fixed_table.box_start) as interval_size
                                from
                                  table1 fixed_table
                                  join table1 windowed_table on
                                    fixed_table.box_start <= windowed_table.box_end
                                where
                                  interval_size >= 0
                                  and fixed_table.box_label is null
                                  and windowed_table.box_label is null
                                  and fixed_table.is_box = 'FALSE'
                                  and windowed_table.is_box = 'FALSE'

                                except

                                select
                                  without_a_box.*
                                from
                                    (
                                        select
                                          fixed_table.box_start as box_start,
                                          fixed_table.box_end as box_end,
                                          fixed_table.box_label as box_label,
                                          fixed_table.is_box as is_box,
                                          fixed_table.box_start as A,
                                          windowed_table.box_end as B,
                                          (windowed_table.box_end - fixed_table.box_start) as interval_size
                                        from
                                          table1 fixed_table
                                          join table1 windowed_table on
                                            fixed_table.box_start <= windowed_table.box_end
                                        where
                                          interval_size >= 0
                                          and fixed_table.box_label is null
                                          and windowed_table.box_label is null
                                          and fixed_table.is_box = 'FALSE'
                                          and windowed_table.is_box = 'FALSE'
                                    ) as without_a_box
                                    ,
                                    (
                                        select distinct
                                            with_box.box_start as start_with_box
                                         from
                                            table1 with_box
                                         where
                                            with_box.is_box = 'FALSE'
                                            and with_box.box_label is not null
                                    ) as items_inside_a_box
                                where
                                    items_inside_a_box.start_with_box > without_a_box.A
                                    and items_inside_a_box.start_with_box < without_a_box.B
                            ) as without_intervals_that_intersect_boxed_items
                        group by
                            A
                    ) as final
                group by
                    B
            ) as intervals
            join table1 on
                table1.box_start >= intervals.A
                and table1.box_end <= intervals.B
                and table1.box_label is null
    )
group by
    box_label,
    is_box,
    item_code,
    item
order by
    box_start,
    item_code

SQL Fiddle: http://www.sqlfiddle.com/#!7/4a643e/142

Even though it seems to do its job I am not sure is correct in all cases and if it won't be a performance bottleneck.

I hope someone has a better solution


Set column to only accept negative values

CREATE TABLE t ( n INTEGER NOT NULL CHECK (n < 0) );

works in most RDBMS I know.

Edit: A comment by @IMSop prompts me to specify why I wrote “most RDMS I know:” it is well known (and very unfortunate) that CHECK constraints aren’t honored by MySQL. In MySQL, you have to use triggers instead. Another option is to switch to MariaDB.


Can't create database in SQL Server Express 2005

I don't really think the problem is creating the database with model as active database as Reaces mentioned in this answer.

It is possible to create a new database using

USE Model
GO
CREATE DATABASE TEST;
GO

The issue might be some process or session holding lock on the model database and the CREATE Database statement not able to acquire the exclusive lock required.

Check what processes are currently using the model database, you can use

SELECT  *
FROM    sys.sysprocesses
WHERE   DB_NAME(dbid) = 'model'

Depending on what sessions are running, you can probably issue a KILL <SPID>, where <SPID> is the session_id of the session holding a lock on model, and try creating the database again.

Update: Here is a query to find what sessions are currently running, the locks requested and additional information: (Run on model database)

SELECT  resource_type ,
    request_session_id ,
    t.TEXT AS Command,
    request_mode ,
    request_type, request_status,
    s.program_name, s.status
FROM    sys.dm_tran_locks l JOIN sys.dm_exec_sessions s
ON l.request_session_id = s.session_id
    JOIN sys.dm_exec_requests r ON r.session_id = l.request_session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE   resource_database_id = DB_ID()


Conversion failed when converting the varchar value to data type int in SQL Server

The first thing I would do is identify rows where those 6 characters can't possibly be an integer.

SELECT ProcessInstanceAppianID, ProcessInstanceDescription
  FROM InternalUseOnly.dbo.processinstance
  WHERE RIGHT(ProcessInstanceDescription, 6)
    NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]';

Then fix that data or somehow exclude it from the results. However this gets tricky because you have no control over whether SQL Server will try the conversions before or after the filter (even if the filter is in a CTE or subquery). So you could dump the filtered results to a #temp table, for example, and then run the conversions on the output.

In SQL Server 2012+ you can use TRY_CONVERT() instead.


Does update lock the row or the entire table?

The links go into gory details, but this question seems to need a simple yes/no answer.
For ENGINE=MyISAM or MEMORY, the only lock is a table lock.
For ENGINE=InnoDB:
Think of it this way -- It locks every row it had to look at.

No index on the column -- It had to check every row, so all rows are locked.  That effectively locks the entire table.

UNIQUE index on the column -- Only one row need be touched, hence, locked.

In between...  A non-unique INDEX on the column -- It must lock all the rows with that value.  (Possibly the 'next' row will get locked, too.)

PS: the PRIMARY KEY is a UNIQUE index in MySQL.
Some other vendors have different index definitions, and some do have "table locks".  Some "escalate" a bunch of row locks to a "table lock".
Bottom line:

Add an INDEX, either UNIQUE or not, as appropriate.
If you need to look at the row before potentially UPDATEing it, use a transaction and SELECT ... FOR UPDATE.


You can't work on the partition <=> files relationship. Partitions are just affected to a Filegroup. However, splitting your Filegroup into different files will help with performance.
The numbers I always use is to have the number of partitions per Filegroup set to the MAXDOP value. I got this looking at how SQL Server split the tempdb into different files and I did notice some improvement copying this behavior.

Regarding partitions, what you should do is split them on filegroups so that the ones that are no longer updated never move. We typically have a Hot filegorup in our DWH, and a Cold one. The cold one is Read Only and we only change that status on the day we move data from Hot to cold. You could do this without having to create one filegroup per partition. That just makes it harder to manage.


First - you must be able to backup and restore a database. You must know how to set up a recurring schedule to backup both the database and the transaction logs.

You should know what other maintenance is required periodically such as updating statistics.

You should understanding indexing - how to create them and when to create them and when not to create them.

You should understand how to read query plans - execution plans or explain plans depending on the db backend.

You should understand datatypes and why using the correct one is important. You should also understand why every table needs a PK and how to set up PK/FK relationships and you should never allow application developers to think this stuff should be handled by the application and not the db.

You should be familiar with database normalization.

You shoud read about performance tuning and database internals for your particular database and be proficient in advanced SQL.

You should know how to monitor performance of your database.

You should know how to set up new users and use roles based security.

You should be able to install the database on a new server.

I'm sure there's more, but this is a starting point.


After some experimentation, I got this:

-- x and y are not actually used, they just give the function its signature
CREATE FUNCTION R_cluster_dbscan(x float8, y float8, eps float8)
RETURNS int
WINDOW
STRICT
VOLATILE
LANGUAGE plr
AS $$
    if (pg.state.firstpass == TRUE) {
        pg.state.firstpass <<- FALSE
        c <- fpc::dbscan(cbind(farg1, farg2), eps, MinPts = 2, method = "hybrid", seeds = FALSE)
        assign("cluster", c$cluster, env = .GlobalEnv)
    }

    return(cluster[prownum])
$$

This window function is called once for each row and returns the cluster id of the record, but it calculates the cluster only once using the DBSCAN algorithm and stores it in a global variable. It is used like:

  SELECT c                             AS cluster_id,
         COUNT(*)                      AS place_count,
         ST_Centroid(ST_Collect(geom)) AS center
    FROM (
             SELECT R_cluster_dbscan(ST_X(geom), ST_Y(geom), 1) OVER () AS c,
                    geom
               FROM points
         ) AS sub
GROUP BY c

Et voilà, a DBSCAN clustering implementation using PL/R. Not sure if this is the optimal implementation, but it's an implementation and hopefully some useful sample code.


Can I do anything about a very long running KILL command?

First of all, thank you posting the DELETE query and the table layout
With regard to your question, there is nothing you can really do because a rollback is being done via the UNDO tablespace inside ibdata1 (it should have grown immensely). If you kill the mysqld process and restart it will just pickup where it left off as part of the crash recovery cycle.
Here is how you can handle large and small deletes in the future:
LARGE DELETEs
Instead of deleting 70% of the table and create lots of rollback info, try copying the tables you want to keep.
CREATE TABLE adc_fetched_documents_new LIKE adc_fetched_documents;
INSERT INTO adc_fetched_documents_new
    SELECT * FROM adc_fetched_documents
    WHERE id > 73097292
;
ALTER TABLE adc_fetched_documents RENAME adc_fetched_documents_zap;
ALTER TABLE adc_fetched_documents_new RENAME adc_fetched_documents;
TRUNCATE TABLE adc_fetched_documents_zap;

Whenever, you delete 70% or more of the table, do it like this.
SMALL DELETEs
If you are deleting smaller chunks, perhaps you can do it with a DELETE JOIN:
CREATE TABLE deletion_ids (id INT NOT NULL PRIMARY KEY (id)) ENGINE=InnoDB;
INSERT INTO deletion_ids
    SELECT id FROM adc_fetched_documents
    WHERE id <= 73097292
;
DELETE B.* FROM deletion_ids A INNER JOIN adc_fetched_documents B USING (id);

Give it a Try !!!


If you just need this for a one-time query, I'd recommend something like this:

WITH AdjustedVehicleInfo AS
(
    SELECT DATEADD(HOUR, -7, Timestamp) AS MST_Timestamp
    FROM Vehicle
)
SELECT MST_Timestamp
FROM AdjustedVehicleInfo
WHERE MST_Timestamp BETWEEN @StartDate AND @EndDate

where @StartDate and @EndDate are set to MST times.

The reason I put that disclaimer is because the offset between GETDATE() and GETUTCDATE() is not always -7 for MST, and indeed is not the same across all timeframes for most timezones (places like Arizona and other non-DST-observant localities being exceptions). If you want this to become a query that people/software is going to use for a long time, you'll need to figure out a different way of adjusting the values to the desired timezone.


I'm going to attempt to show you why you have a vested interest in the Sql Server Transaction log.

Bear with me as I lay out a few assumptions...

For simplicity sake, I'm assuming FULL RECOVERY model.
Because you're following BEST PRACTICES, you've separated the MDF/NDF files onto different drives than where your LDF file is stored.  So, you have your MDF on Drive-E and your LDF on Drive-F. You'll see why this is important later.
Your company wants to minimize data loss in the event of a disaster,
so you're actually backing up your transaction logs every minute.
For disaster recovery purposes, you have some sort of HA/DR configured.  Let's just go with simple transactional replication.

Here's a simple scenario using two different transactions:

--Start transaction for Salary
BEGIN TRANSACTION

UPDATE Payroll SET YourSalary = YourSalary + 5000.00

COMMIT
--Congrats, you just got a $5,000 raise!

--Start a transaction for BankCheckingAccount
BEGIN TRANSACTION

--Subtract $500.00 from your bank checking account
UPDATE YourBankCheckingAccount SET AccountBalance = AccountBalance - 500.00

--No COMMIT yet.

When those statements executed, Sql Server read data into memory (if it wasn't already there), updated the data pages in memory and wrote that information into the transaction log indicating the update.  It tracks the BEFORE and AFTER of the data modification in the transaction log.  The data pages only have the 'final' result.  However, at this point, the modified data pages are not 'necessarily' written to disk for performance reasons.  Imagine how slow things would be if you had to wait on disk for each data 'page' modification to be written.  Data modifications are always written to the log before a 'completion' response is returned to the user.  That's the 'D' part of ACID.  At this point, the data page in memory is flagged as IsDirty meaning that one or more modifications have been made to it since it was read into memory and those changes have not been written to disk.  These 'dirty' pages are written to disk during a CHECKPOINT.

Now, back up your database (without committing that transaction).  The back up process initiates the CHECKPOINT process and the 'dirty' pages are written to disk.  Keep in mind that you have NOT committed this transaction and are free to undo (rollback) it at any time.  Your updated information is in that backup.  That backup also has enough information (from the log) to identify committed and uncommitted transactions at the time the backup was taken.  If you restored that backup to another database, I'm sure you'd like your $500.00 back, right?  During a restore to the other database, Sql Server would put the data page (with your -$500.00 account balance) back to disk.  However, after the data pages have been restored, Sql Server goes through 'crash-recovery' and uses the transaction log to identify transactions that need to be undone (due to uncommitted transactions like yours) and also transactions that need to be 'redone' (due to data being modified and committed after the backup started, but are not reflected in the actual data pages because the updates occurred AFTER the CHECKPOINT.)

Now, on to another scenario still using our transactions.  Your Salary was updated (and committed) 5 minutes ago.  Your E-drive, which hosts the MDF file suffers a disk failure and Sql Server goes down due to not being able to access it.  Your F-Drive (which hosts the LDF file) is intact.  Again, because you're following BEST PRACTICES, you've been taking FULL (and possibly DIFFERENTIAL) backups. Your last FULL backup was 1 hour ago, but that was BEFORE your Salary increase transaction took place.  The information about your Salary increase is in the transaction log.  At this point, you should attempt to do a 'tail-of-the-log' backup of the transaction log to try and salvage any transactions that occurred between the last log backup and the failure.   When you restore your backup from 1 hour ago and subsequent log restores, Sql Server looks through the transaction log for transactions that need to be undone (not committed) or redone (committed).  Your salary increase needs to be 'redone' because it has been committed but it's not in your backup.  Your transaction log (and backups) saves the day.

The Sql Server transaction log is a 'circular' file.  Let's say your transaction log was created with a maximum size of 1gb.  You have complete control over how you define your transaction log space.  As transactions occur, information about those transactions is written to the transaction log.  Eventually, you'll run out of that 1GB space allocated for your log.  When you backup your transaction log (remember, you're doing that every minute), Sql Server is able to 'reuse (circular)' transaction log space allocated as long as transactions logged in that space have committed.  Transaction log space allocated to transactions that are are still 'in-flight' (not committed) OR that have not been completely replicated (you're using transactional replication) cannot be release during a transaction log backup because the log needs to be used to 'rollback' the update should the user decide to 'rollback' the transaction or the system goes down.  In transactional replication (or other HA/DR scenarios like mirroring, etc.), those transactions need to be completely replicated before the log space can be reused.  Actually, in our scenario, our uncommitted bank account transaction will prevent the log from being reused and you'll eventually run out of log space even though you're taking log backups every minute.  So make sure transactions are being committed or rolled back in a timely fashion.

Speaking of 'Rolling a restored database, file, filegroup, or page forward to the point of failure.'

Your company wants to minimize data loss and you're taking transaction log backups each minute.  Remember, only the log knows the BEFORE and AFTER information for a transaction.  The only thing in the MDF/NDF files is the data as it is NOW and that's only if a CHECKPOINT has occurred since the update.  Now assume your entire server dies.  You have FULL backups AND you have transaction log backups to the minute just before the server died.  Knowing that this possibility could happen, you knew to store your backups on a backup server that was isolated from THIS server.  Your $5000 raise is in those backups AND you want your $500.00 back into your checking account.  Those changes are NOT in your FULL backup because it was taken over an hour ago.  When you get around to being able to restore your backups, you restore your FULL (and maybe a DIFFERENTIAL) and all of the transaction logs you have since the FULL/DIFF (just up to a minute before disaster).  Sql Server then goes through crash-recovery.  It makes sure your $500.00 back account transaction is 'undone' because it was uncommitted and then makes sure your $5000 raise is 'redone' because it was committed.

As far as replication, Sql Server uses the transaction log to replicate these changes to remote servers, not the data pages.  Also, the transaction log space cannot be 'reused' until the transactions participating in replication or mirroring have been 'replayed' on the target server.

To summarize - the transaction log is your BEST friend.

It supports 'Individual transaction recovery' and 'Recovery of all incomplete transactions when SQL Server is started' during crash-recovery to undo uncommitted transactions and redo committed transactions because there is no guarantee these data page modifications were actually written to disk before disaster struck.
Similarly, it supports 'Rolling a restored database, file, filegroup, or page forward to the point of failure' again using the restoration of transaction logs and crash-recovery.
It supports 'transactional replication' and 'high availability and disaster recovery solutions: Always On Availability Groups, database mirroring, and log shipping', by sending the transaction logs to the remote server for 're-play'

My attempt to explain the usefulness of the transaction log is minimal at best.  Refer to Paul Randal's excellent post about how all of this stuff works.


How to promote an existing index to primary key in PostgreSQL

You'd use ALTER TABLE to add the primary key constraint. In Postgres you can "promote" an index using the "ALTER TABLE .. ADD table_constraint_using_index" form

Note, the index need to be unique of course for a primary key

ALTER TABLE my_table
    ADD CONSTRAINT PK_my_table PRIMARY KEY USING INDEX my_index;


They don't tend to be shown in ERDs. An ERD focuses on the Entities and their Relationships, but an index is a copy of the data from one (or potentially more) of the entities, created to assist in the execution of queries. While it's possible that an index could be unique and therefore contribute to the database design, they are not typically shown on ERDs.

...which is a shame, because in my experience, a database design should involve careful acknowledgement of the indexes, as they can make-or-break the effectiveness of a database system.


How to move folder of SQL Server LocalDB instances from the default location?

See my comment above. I used Process Monitor to trace the registry access and found it was getting an Access Denied while trying to write:

3:16:40.8405491 PM sqlservr.exe    8756    RegCreateKey    HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer   ACCESS DENIED   Desired Access: Write

I got around this by opening the Registry Editor and giving Everyone full access to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer. Then I changed the default location and removed the Everyone access once it was done. This made the following extra keys that weren't there before:

DefaultData

DefaultLog

BackupDirectory


How should I Schedule SQL Server 2008 queries to run every month?

SQL Jobs is indeed the right way about it.

If you don't have the permissions, talk to your manager/DBA in order to set up the job.

If this is not possible, you can use the task scheduler in conjunction with sqlcmd to execute queries on the SQL Server.

Another alternative is to write a windows service that will execute these queries, but in terms of scheduling I would trust the task scheduler over custom code.


Column Store Index over an clustered index (created when primary key is declared) - SQL Server

We can learn a lot by creating a test script!  You should be able to run the SQL below on any SQL 2014 instance (probably SQL 2012 as well, but I didn't test it there).

From this script, we can see that the non-clustered columnstore index on (b, c) does store the data for the clustered index column (a) and it does so in the same manner (via segments) that it stores b and c.  This allows the columstore to efficiently process queries that access a, b, and c.  Technically, it even allows the columnstore to be used, in combination with a key lookup, in order to process a query that needs all of the columns in the table (although this is not likely to be favored by the optimizer given the expense of the key lookup).

In terms of your questions about the "structure" of the columnstore index, I think that's too deep a question to answer here.  But if you are interested in learning more, I think that Niko Neugebauer's excellent 55-part (and growing) series on columnstore has a ton of valuable information on the structure and internals: http://www.nikoport.com/columnstore/

-- Create a table to your specs
CREATE TABLE dbo.XPTO (a BIGINT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL, e INT NOT NULL, CONSTRAINT PK_XPTO PRIMARY KEY (a))
GO

-- Insert some trivial dummy data
INSERT INTO dbo.XPTO (a, b, c, d, e)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)), v2.x, v3.x, v4.x, v5.x
FROM ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v2(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v3(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v4(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v5(x)
CROSS JOIN ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) v6(x)
GO

-- Create the nonclustered columnstore
CREATE NONCLUSTERED COLUMNSTORE INDEX cs_XPTO ON dbo.XPTO (b, c)
GO

-- Turn on "Include Actual Execution Plan"

-- Access only the columns specified in the columnstore index
-- Here we get a Columnstore Index Scan, as expected
SELECT SUM(b), SUM(c)
FROM XPTO
GO

-- Add in the clustered index column
-- Now we also get a Columnstore Index Scan!
-- This shows that the non-clustered columnstore does maintain data for "a"
-- because it is able to process this query without touching the clustered index
SELECT SUM(a), SUM(b), SUM(c)
FROM XPTO
GO

-- Access all columns
-- Now we get a Clustered Index Scan, and the columnstore index is not used
SELECT SUM(a), SUM(b), SUM(c), SUM(d), SUM(e)
FROM XPTO
GO

-- Try to force usage of columnstore
-- Now we see a tplan that uses the columnstore index but performs a key looked up to the clustered index
-- This is a plan shape I have never seen the query optmizer generate on its own, but it makes sense
-- that it is possible given that the columnstore index stores data for the clustered index column,
-- which is essentially a pointer to the corresponding row in the table
SELECT SUM(a), SUM(b), SUM(c), SUM(d), SUM(e)
FROM XPTO WITH(INDEX(cs_XPTO))
GO

-- View the column store segments
-- Here we see that there is a column store segment for columns a, b, and c (even though we asked for just b and c!)
-- This seems to indicate that clustered index columns are implicitly added to any nonclustered columnstore index
-- and are stored within that columnstore index in the same way as the requested columns
SELECT cs.*
FROM sys.partitions p
JOIN sys.column_store_segments cs
    ON cs.partition_id = p.partition_id
WHERE p.object_id = OBJECT_ID('XPTO')
GO

-- Cleanup
DROP TABLE dbo.XPTO
GO


How many Read IOPS are too many? (Should I upgrade my RDS instance memory?)

we'd like to know we're "ok" on resources for the term of the lease.

This is a "feeling" now you'll have to put this feeling into tangible, can be tracked and trended. This is how you can make predictions about your workload.

What would this look like? There are three areas you'll need to document:

What are the limits of my hosted solution?

There may be more than this, it's just to get you started thinking.

How much CPU (total) does the hosted environment have? (1 CPU = 100%)
How much RAM (total)?
How many Disks, Total Space, IOPS/Disk, IOPs Total
What is my networking bandwidth (Per card and totaled)

What should I monitor to trend my workload appropriately?

Whatever the limits of the hosted environment are, you'll definitely want to be gathering those metrics and statistics. There may be additional items that are Application defined which should also be included.

For example, if your application is a website that takes orders from users and the fulfills them there may some metrics you could add at the application layer:

Orders/Hour
Milliseconds to accept/place an order

Then the environment metrics based off of the first paragraph of information (not exhaustive, just an example):

CPU usage
Page file/Memory
Per disk IOPs, average seconds per IOP (transfer), etc.
Network Bandwidth

What are acceptable SLAs?

If an order takes 50 ms to place, is that bad? Is 2.5 seconds good? How many orders per hour must be accepted, minimally? Are there stored procedures associated with these orders that should be watched and have certain thresholds put on them?

Putting all of this information together will allow for the forecasting of expected volume, current volume, and potential needs in order to meet your SLAs for the business.

How many places do I know that actually do this? You'd be surprised. It's taken a few of them years to put it in place but many have these statistics readily viewable and available at a moments notice. Is it easy to do? Yeah, it's just time consuming. If you don't want to put in the time, just buy a bigger/better hosted solution - if you want to create a repeatable diagnostic infrastructure in order to provide this information then be willing to customize it to your environment.

Summary

I can't speak for RDS (amazon) and in all transparency, I work for Microsoft - but there are other hosted solutions such as Azure that already monitor many of these systems based items (CPU, Memory, IO, etc) for you. This puts the onus on you to grab the information and create your own application based metrics.

Each environment is different and while the system based attributes are a great start for monitoring, the whole picture is needed in order to make appropriate business decisions.


How do I facilitate access to an Azure based SQL View for non-SQL users on Prem?

You can use Power BI service and Power BI Mobile Apps to share reports with others as explained here. Take a look here at the Publish to Web feature in Power BI.

Here you can see how easy is to create a report from SQL Azure data on Power BI.

Hope this helps.


Best practices for generating a tnsnames.ora entry

Rather than using tnsnames, I have used openLDAP to do the names resolution.
You can replicate across two nodes if you want. As soon as you change the entry in openLDAP every server has the new information. You can even get the resolution from an ssl encrypted port if you want.
using openLDAP the name resolution


It's impossible to do what you want, and if not impossible, not a good idea. The columns in a resultset belong to every row in the resultset. You can't have a different alias for each row. That wouldn't make sense. Imagine a table in a spreadsheet that had a column header for each column and for each row. How would you even notate that?

It seems, though, that you want to be able to consider some upstream column to be a network_id in some cases and a group_id in others. I won't question your schema or query design, though it is suspect. If it's reasonable, then you can easily achieve this by simply having two columns: network_id and group_id. Only fill in network_id if parent = 0, and only fill in group_id if parent is not 0, like so (using standard SQL):

...
CASE WHEN parent = 0 THEN alias ELSE NULL END AS network_id,
CASE WHEN parent <> 0 THEN alias ELSE NULL END AS group_id,
...

Your application code can use whichever column needs to be used.


Can I set PostgreSQL "stringtype=unspecified" behavior as default?

Unfortunately the answer is no.
stringtype is not server parameter. It's JDBC's own client parameter.
Check the function here;

org.postgresql.jdbc.PgConnection.getStringVarcharFlag()

and here

org.postgresql.jdbc.PgPreparedStatement.getStringType()

You will see that JDBC sets parameter type to VARCHAR or UNSPECIFIED type according to the stringtype property.


How to optimize stored procedure so it won't end with "Out of memory exception"

You can do it in a single statement.

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION;

DELETE FROM
    Errn
WHERE
    erid IN (
             select
                 distinct m.ID
             from
                 Member m
             left join
                 (select
                      *, row_number() over (PARTITION BY rid order by ceid desc) as rn
                  from TypeA) x
                 on m.ID = x.ID and x.rn = 1
             where
                 (
                     (x.ceid is null and m.LastChangedDateTime < @Deletion)
                     or
                     x.Resignation < @Deletion
                 );

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    RAISERROR('ERR MSG', 16 1);

END CATCH


Online Schema change when DB has existing triggers

Yes there are.

DB doesn't have any triggers. A table can has triggers.
If you want to alter the table which hasn't fulltext index maybe online DDL can help you.(It is implemented in MySQL/MariaDB)

But gh-ost is the best choose when you have triggers on the table.

Percona Online schema change and another tools such as oak-online-alter-table,soundcloud/lhm, facebook-osc are good when your table doesn't have any triggers. But in MariaDB 10.2 you can create multiple triggers for the same event So maybe they can work (I did not test)

Tools that maybe you didn't know:

https://github.com/soundcloud/lhm

https://github.com/shlomi-noach/openarkkit

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html


MySQL store procedure use select responce in INSERT...SELECT where in array

I'll choose to make the database generate a (virtual) table with all (id_v, lang) pairs that you are expected to have at the end.
To do so, you start by getting the list (actually set) of all DISTINCT lang:
SELECT DISTINCT
    lang
FROM
    ven_data ;

... and let's imagine it is this one

| lang |
| :--- |
| EN   |
| FR   |
| IT   |
| CA   |
| DE   |
| DK   |
| GR   |
| NL   |

We do the same to get the set of all DISTINCT id_v:
SELECT DISTINCT
    id_v
FROM
    ven_data ;

| id_v |
| ---: |
|    1 |
|    2 |
|    3 |
|    4 |
|    8 |
|    9 |
|   11 |
|   12 |
|   13 |

What should be in your ven_data table is actually the cartesian product of both sets. That is accomplished in SQL by means of a CROSS JOIN.
That results in:

id_v | lang
---: | :---
   1 | EN
   2 | EN
   3 | EN
   4 | EN
   8 | EN
   9 | EN
  11 | EN
  12 | EN
  13 | EN
   [...]
   1 | NL
   2 | NL
   3 | NL
   4 | NL
   8 | NL
   9 | NL
  11 | NL
  12 | NL
  13 | NL

What you actually want to do is INSERT in your table all the rows in this cartesian product that aren't already there.
You do this by means of:
INSERT INTO
    ven_data
    (id_v, lang)
SELECT
    id_v, lang
FROM
    -- The whole set of (id_v, lang) pairs
    (SELECT DISTINCT
        lang
    FROM
        ven_data
    ) AS languages
    CROSS JOIN
    (SELECT DISTINCT
        id_v
    FROM
        ven_data
    ) AS id_list
WHERE
    -- The pair (id_v, lang) is not already in place
    NOT EXISTS
    (SELECT
        1
    FROM
        ven_data v2
    WHERE
        v2.id_v = id_list.id_v AND v2.lang = languages.lang
    ) ;

Note that this WHERE condition can also be written in more than one fashion (in MySQL):
WHERE
    (id_v, lang) NOT IN
    (SELECT
        id_v, lang
    FROM
        ven_data v2
    ) ;

You can see the whole setup (together with all the steps 1 by 1) and you can experiment at
dbfiddle here

NOTEs: you don't need to do this from a stored procedure, nor you need to use arrays. This code is quite standard SQL. Note that it also works with PostgreSQL  or SQL Server, for instance.


Excessive compile blocking on sp_procedure_params_90_rowset

There is an excellent blog post
http://blogs.msdn.com/b/support_sql_france/archive/2012/02/07/sql-server-compilation-gateways-and-resource-semaphore-query-compile.aspx
that explains what's happening.

SQL Server allows for a set number of compilations based on their complexity.  It groups them into small, medium, and large.  For large compilations, there can be onlyi one compiled at a time, so let's say all of your procs are considered large, then each one has to be compiled serially.  That could account for the blocking.
I think there may be several approaches to the problem - consider more resources (more CPUs will allow more small and medium queries to be concurrent or may up the threshold for what is considered medium).  Also, more memory may solve the problem.

If you're like most of us, that might not be possible.  Another option might be to review the ADO calls and see if the number of calls can be reduced or spread out so that not all calls happen at the same time.  Reducing the number at any given time should reduce your wait time.

If that doesn't work, consider fixing the 'compilability' of the stored procs.  Maybe break them down into smaller chunks which might reduce them to the small or medium buckets and allow more parallel compilations.  Or determine why the procs need to be recompiled each time.  See if they can be rewritten such that they don't need to be recompiled.  Finally, I'd consider using Plan Guides.  These will allow the procs to be precompiled and may save some time.

Hope that helps


How do I make SQL Server database accessible to PC's on the LAN?

Sounds like you have an issue with permissions - if one database is visible they've clearly connected to the instance. I'd suggest you run SSMS as yourself from the other PC and see what happens.


Insert output into another newly created table

Make use of the OUTPUT clause.

Let's say you need to insert #TEMP1 data into #TAB2 when an INSERT is done on #TEMP1:

CREATE TABLE #TEMP1
([SERVER] NVARCHAR(200),
[NAME] NVARCHAR(200),
);

CREATE TABLE #TAB2 ([SERVER] NVARCHAR(200),
[NAME] NVARCHAR(200));

INSERT INTO  #TEMP1
OUTPUT INSERTED.[SERVER],INSERTED.NAME INTO #TAB2
SELECT  (SELECT @@SERVERNAME) AS SERVER,NAME FROM MASTER.SYS.DATABASES;

Now see the results in both tables:

SELECT * FROM #TEMP1;
SELECT * FROM #TAB2;

See the product documentation: OUTPUT Clause (Transact-SQL) for more information, including some important restrictions.

If you want to automate this process, you need to write an AFTER INSERT trigger. Please find a very good example exactly matching your requirement in SQL Server: Coding the After Insert Trigger in SQL Server by Vishwanath Dalvi.


Installing default MySQL Tables in Windows (mysql_install_db)

I knew I had faced this before and managed to figure it out, so I searched my drives and found a batch file I had written last time.

For anyone else who experiences this issue, what you need to do is to run the server daemon with the --bootstrap parameters.

Here is the script I used to dump all the databases out to an SQL file and re-import them (that is, manually—and inconveniently—implementing a MySQL counterpart to SQLite’s vacuum command).

::Start the server, change to the MySQL bin directory, and dump all databases
net start MySQL
cd       /d "%serverdir%\MySQL\bin"
mysqldump  -uroot -p --all-tablespaces --all-databases --add-drop-database --add-drop-table --add-locks --comments --complete-insert --compress --create-options --events --routines --quick --quote-names --set-charset --triggers > %temp%\all.sql

::Stop the server and rename the data directory (as backup)
net stop mysql
ren         "%datadir%\MySQL"        MySQL_

::Delete data folder in MySQL directory, optionally copy old mysql tables
rd    /s /q "%serverdir%\MySQL\data"
md          "%serverdir%\MySQL\data"
xcopy /s /e "%datadir%\MySQL_\mysql" "%serverdir%\MySQL\data"

::Bootstrap the server (creates grant tables if they don’t exist)
mysqld --bootstrap

::Run server, optionally upgrade tables and move upgrade log to data directory
start mysqld --skip-grant-tables
mysql_upgrade --force
move "%serverdir%\MySQL\bin\mysql_upgrade_info" "%serverdir%\MySQL\data"

::Import all databases, shutdown, delete logs, then move to old data dir
mysql      -uroot -p < %temp%\all.sql
mysqladmin -uroot -p shutdown
md  "%datadir%\MySQL"
del "%serverdir%\MySQL\data\ib_logfile?"
xcopy /s /e "%serverdir%\MySQL\data\*" "%datadir%\MySQL"
rd    /s /q "%serverdir%\MySQL\data"

::Start the server, if it works, then all should be well, so del
net start mysql
rd    /s /q "%datadir%\MySQL_"


SSAS - Applying Conversion Rates for Units of Measure

I didn't have the enough reputation to be able to upload my images to the site. I hope the descriptions could help. If you want to see the complete document to this problem you can send me an email and I will send you back the complete doc with images. My Email Address is:
alireza.marahemi@gmail.com

Suppose you have the following data as measurement unit

Uom     ConvertRate( to smallest unit)
Second  1
Minute  60
Hour    3600
Day     3600* 24

Your fact table contains the data based on your smallest unit of measurement (in this case seconds). you want to be able to select other measurement units (e.g Hour) and your measure (Time in our example) displayed according to your selection. For instance if sum of your data items is 1200(sec) if you select Minute the result should be 20.
In this case I assume you do not want to add extra measures for each unit of measurement to your data.
Here is the design method.
First you have to create your Measurement unit table in following manner:

CREATE TABLE [DimUom]
(
    [UomID] INT NOT NULL primary key,
    [UomName] VARCHAR(50) NOT NULL,
    ParentUomID INT,
    ConvertRate float
)

I have used the fields ParentUomID and ConvertRate to identify the base measure and ConverRate of current measure to base measure.
you can fill this table with the following script.

INSERT INTO [DimUom] ([UomID], [UomName], [ParentUomID],[ConvertRate])
SELECT [UomID], [UomName], [ParentUomID],[ConvertRate]
FROM (
    SELECT 1 AS [UomID], 'TimeMaster' AS [UomName] , NULL as [ParentUomID], 1 as [ConvertRate]
    UNION ALL
    SELECT 2 AS [UomID], 'Seconds' AS [UomName], 1, 1
    UNION ALL
    SELECT 3 , 'Minutes', 1, 60
    UNION ALL
    SELECT 4 , 'Hours' , 1, 3600
    UNION ALL
    SELECT 5 , 'Days' , 1, 86400
    UNION ALL
    SELECT 6 , 'Weeks' , 1, 86400 * 7
    UNION ALL
    SELECT 7 , 'Months (30 days)' , 1, 86400 * 7 * 30
) A;

Your fact data can be as simple as the following fact table with only 3 fileds DataID as Primary key( not needed actually) your DataItem and your measurement unit.

CREATE TABLE [FactData] (
[DataID] INT primary key identity,
dataItem INT NOT NULL,
[UomID] INT NOT NULL);
GO

The following script adds a minimal data to your table. You have noticed that I used only the TimeMaster measurement unit to the table( Actually I could have used
only seconds). Anyhow your table should contain the smallest measurement unit.

INSERT INTO [FactData]([dataItem],[UomID] )
SELECT [dataItem],[UomID]
FROM (
    SELECT 600 AS [dataItem], 1 AS UomID
    UNION ALL
    SELECT 3600 , 1 AS UomID
    UNION ALL
    SELECT 86400 , 1 AS UomID
    UNION ALL
    SELECT 60 , 1 AS UomID
) A;

Now it is time to go through SSAS and build our dimensional model. In your data source view add your fact and dimension table.

You see after adding the tables I've changed the dimUom to a Named Query and added a field named AlternateParentID as following:

SELECT        UomID, UomName, ParentUomID, ConvertRate, ParentUomID AS AlternateParentID
FROM            dbo.DimUom;

I've done this because I want to relate FactData to DimUom via this field. I couldn't relate dimUom to FactData by ParentUomID field as I want to set ParentUomId usage as Parent in ssas and you can not relate a fact table to a dimension by a parent field.
After adding tables and editing the named query add the following relations to data source view.
FactData(UomID)  dimUom(AlternateParentID)
dimUom(ParentUomID)  dimUom(UomID)
After building the cube in the dimension usage tab of the solution you will see this structure:

After creating the cube go to dimUom attribute tab and set Namecolumn attribute of UomId to UomName field to display measurement unit name field instead of Id field.

Now we have to add the convert rate as a measure to the cube. So in the cube structure tab of the solution and select the "dimUom" as source table and ConverRate as the source column.

After adding the new measure your cube should look like this

and the structure as follows

After doing above instructions you have to create a calculated measure in the calculation tab as follows:

As you may have noticed in format string section I used a script instead of predefined format string. You can do this if script view of calculation tab and editing the calculation formula in following way.

CREATE MEMBER CURRENTCUBE.[Measures].[ConvertedRateQty]
 AS [Measures].[Data Item]/[Measures].[Convert Rate],
FORMAT_STRING = case [Dim Uom].[Parent Uom ID].Currentmember
WHEN
    [Dim Uom].[Parent Uom ID].&[2] THEN "#,#"
ELSE
    "#,#.0000"
END,

It is time to process the cube and view the results. Process the cube and go to the browser tab and add DataItem and ConvertedRateQty measures to the measure section. You will see the following results.

As we did not indicate any measurement unit the ConvertedRateQty shows our measure ( dataItem = 90660) divided by sum of all conversion rate of uomTable ( 1 + 60 + 3600 + 86400 + 7 * 86400 + 7 * 30 * 86400 = 18,838,861)
In our example 90,660 / 18,838,861 = .0048
If you select any individual measurement unit from you will see the converted DataItem.

Or you can see all the quantity in all measurement units converted simultaneously as follows:


How can I link to query file that opens in SQL Server Management Studio showing database login window?

SSMS will not prompt for a login if you are already connected to an instance (e.g. Object Explorer), this is by design. If you want to change connections simply right-click in the query window and go to change connection.

Your request to include the username and password in the file somewhere is bad security. Especially if this particular account has elevated privileges to the environment.

If you are looking to automate the process, allow minimal involvement, allow anyone to execute the queries, and run them against any number of servers then I would suggest building the queries in PowerShell. A simple function that accepts say a server name and the script file can be written around using Invoke-Sqlcmd or .NET code if you are so inclined. Or write a function for each query and then just have them pass in the server name.

  somebody with very little knowledge

This is dictated by your documentation of your process. If your process is written to a 3rd grade level then any adult is assumed to be able to perform that process strictly by following your documentation.


During times of heavy network usage, we are sometimes seeing failovers to a secondary server. After some research, I see that the way round this is to have a dedicated NIC for the AG traffic on a private IP address range.

I'm skeptical that this will actually fix the issue. You'd basically need to collect network captures to prove the point. You'd also need a separate network if the one you were on was overloaded if you didn't QoS the current traffic.

  What I was wondering is, it is possible to drop and create existing endpoints/listeners with the updated IP addresses or would I have to create a new AG and move databases across to it?

It's completely possible to alter/drop the endpoint(s) while everything is running. There is no need to remove the availability group already setup. However, there will be communications issues and failures while the endpoints are down/unavailable.

Again, I'm not sure this is going to actually solve your issue.


Last character in column shift to next column

Here is a trivial way using left, right, and stuff.

declare @table table (column1 varchar(16), column2 varchar(16))

insert into @table
values
('abcdef','ghijkl')

update @table
set
    column1 = left(column1,len(column1) - 1)
    ,column2 = stuff(column2,1,0,right(column1,1))

select * from @table

If you have trailing spaces, you'll need to use RTRIM accordingly.

column2 = stuff(column2,1,0,right(rtrim(column1),1))

After fixing your sample data to add commas in your DDL, and removing the extra column from the insert, you can still use the method above with a small tweak:

declare @dda table
(
    comp_acct_num varchar(17)
    ,internet_code char(3)
    ,type_code char(1)
    ,cr_rate_code char(1)
)

INSERT INTO @dda (  comp_acct_num,
                    internet_code,
                    type_code,
                    cr_rate_code)
VALUES
 ('00000000000000000','111','7','')
,('00000365874001000','111','7','')
,('00000000000001000','211','7','')
,('00003101118001000','211','7','')

update @dda
set
    comp_acct_num = left(comp_acct_num,len(comp_acct_num) - 1)
    ,internet_code = left(stuff(internet_code,1,0,right(comp_acct_num,1)),3)

select * from @dda


Can't create/write to file '/var/log/mariadb/mariadb.log'

Is SELinux running in enforcing mode? Run sestatus to find out. If so, it should be logging to the /var/log/audit/ directory. You can do grep mysql /var/log/audit/* to see if there are any problems related to "mysql" i.e. MariaDB.

Also, the correct privileges and SELinux context for the log directory and files are:

# ls -ldZ /var/log/mariadb/ drwxr-x---. mysql mysql
system_u:object_r:mysqld_log_t:s0 /var/log/mariadb/
# ls -ldZ /var/log/mariadb/mariadb.log
-rw-r-----. mysql mysql system_u:object_r:mysqld_log_t:s0 /var/log/mariadb/mariadb.log

For instructions on how to set the right SELinux context, refer to the documentation: What to Do if MariaDB Doesn't Start - SELinux and MariaDB Path Changes.


Create multiple files for each file group for table partitioning?

One file per filegroup, one file per volume.
So a partition read/write goes to one volume: which is one reason to partition.

Otherwise, you are simply using all 4 drives all the time. So why bother with partitioning

I personally don't like multiple files per filegroup (except tempdb of course).

I assume "4 drives" means 4 discrete SAN LUNs or RAID arrays...


Multi-master synching in three geographically separate datacentres

I don't have experience of your exact situation, but could you use:

auto_increment_increment=3
auto_increment_offset=1 [then 2 and then 3]

on your different masters, so that each one generates a different set of id's.

e.g.

server 1: 1, 4, 7

server 2: 2, 5, 8

server 3: 3, 6, 9


SQL Server Agent fails after installing SQL Server 2019 CU2

Thank you for reporting this. There is a feedback item in https://feedback.azure.com/forums/908035-sql-server/suggestions/39754837-ssms-18-4-sqlserver-2019-cu2-object-explorer-fails. All updates will be provided there as we investigate.
FYI, the bug introduced in CU2 with xp_sqlagent_enum_jobs is now understood and a fix will ship in CU3. Meanwhile, if you're hitting this issue, revert back to CU1. We apologize for any inconvenience we may have caused.
The Cumulative Update 2 for SQL Server 2019 article now has the following warning:

Known issue
There is a known issue with SQL Server Agent in SQL Server 2019 CU2. This will be fixed in the next CU release (SQL Server 2019 CU3).
If you are using SQL Server 2019 SQL Server Agent, it is advised to skip this CU upgrade, and wait for SQL Server 2019 CU3.
If you have already applied SQL Server 2019 CU2 and are experiencing issues with SQL Server Agent, please revert to SQL Server 2019 CU1.

Fixed in CU3
FIX: Error occurs when you interact with SQL Server Agent in SQL Server 2019


SELECT from slave and INSERT into master in MySQL

Dangerous. Read about "Critical Read". If you blindly send all SELECTs to a Slave, you could screw up transactions on the master. Or you could miss data when replication is "behind".

Instead, modify your application to connect to either the Master or to some Slave, based on whether it is safe to get the data just from a Slave.


Best practices when storing logging information to table

To directly answer each of your questions:
Q: What should we do about unique key?
Q: How should our clustered index be?
A: In most cases it's important to have a primary key so you can guarantee uniqueness (including this case). Only in a staging table it could possibly be an exception to not use one. It may even serve useful for adhoc debugging later on and is cheap to use an INT or BIGINT auto-increment field as one. But you can (and should) still make your logTime column the clustered index on the table for performance reasons, since it's what will be used as a predicate in a lot of your queries, and the uniqifier step SQL Server does is super negligible from a performance perspective. If you do add a LogId as the primary key, you can add a nonclustered index to it. That way if you need to do any adhoc maintenance (e.g. deleting / updating specific records in the table, you can efficiently seek them by the primary key).
Q: Should we use lookup tables?
A: In this case I would recommend using a lookup / "enums" table. Not so much from a space saving or performance perspective, but mainly because if one of those values change you can very efficiently maintain it by only updating the lookup table as opposed to having to update every record referencing that value in the main table.
Q: Can we use WITH (NOLOCK)?
A: It is not possible for you to use the WITH (NO LOCK) query hint here and be guaranteed to always have 100% accurate data, even if you're selecting from a different set of rows than the ones being INSERTED or UPDATED. I've maintained queries in the past that do use it and very rarely have had an issue, but my personal choice (unless data accuracy is not a concern) is to avoid it. There are much better performance improvement alternatives for query tuning. By the way, I once thought the same way about the NOLOCK hint and you can see Microsoft's David Browne's answer to my question for a conclusive reason why the data accuracy isn't guaranteed. In short, the rows you're selecting against may be part of a changing data structure because of the other further away rows that are being INSERTED or UPDATED also being part of that same data structure, e.g. B-Tree for a particular index both rows are a part of.
Q: Any other best practices?
A: Maybe consider other possible ways logs might need to be queried, e.g. all logs by a specific user, specific process, or both (even irrespective of when they occurred)? If so, adding a couple extra nonclustered indexes on those fields could help you later on too. E.g. nonclustered index on processUser, processID and then a separate nonclustered index just on processID (the first index would be covering for predicates that only use processUser, or use processUser and processID which is why you don't need a third nonclustered index that's just on processUser here).


Altering table schema takes too much time

Yes, there is a quicker way to do it if you are using version 9.1.  The PostgreSQL version 9.1 release notes include this:

  Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in
  appropriate cases (Noah Misch, Robert Haas)

  For example, converting a varchar column to text no longer requires a
  rewrite of the table. However, increasing the length constraint on a
  varchar column still requires a table rewrite.

Further improvements in this area are coming in version 9.2, which hits beta release next week.  But for now it appears the faster way is to alter the type to text, in which case it is a quick update to system tables with no change to the heap.


Error message - view server state permission was denied - when connecting to SQL Server 2014 using SQL Server 2012 Management Studio

The least intrusive way to get this working is to either use the following command or have the DBA issue this command for you:

use master
go
GRANT VIEW SERVER STATE TO <user_name>


You can store A and B columns in a separate table. Make sure your table has a primary key on A, and a unique constraint on (A,B) Refer to (A,B) from your table. The primary key on A will guarantee only one B per A. The foreign key without ON UPDATE CASCADE will make sure B does not change as long as the row is referred from the child table.


Cannot remotely access PostgreSQL on EC2 instance

(self-answering in case someone else has these same symptoms!)

Then I stumbled upond Shubham Dipt's post on
"PostgreSQL on EC2 (Ubuntu) in AWS",
making me aware of the "Security group" layer AWS maintains.  It's
easy to specify a new one  via the AWS console, specifically adding  Custom TCP: 5432, Source: Anywhere and SSH TCP:22, Source: Anywhere rules for Inbound:

Then, added this security group to the instance, and it worked for me.


Data Source connects but linked server cannot initialize data source object in MS SQL Server

So after trying a ton of things with options in the driver, settings in the MSDASQL provider, etc. Replacing the MySQL ODBC driver with an older version worked immediately. This solved my issue. thanks!


Query with Indexes slower than without Indexes

My understanding:
The table contains 1M rows of which 250k are returned by the query.
There are 500k rows with foreign_key_id = 1 and 500k rows with af.foreign_key_id2 IS NOT NULL.

The query using full table scan (actually doing full index scan on the PRIMARY key in InnoDB) will read all 1M rows sequentially and check each of them for the conditions.

The query using foreign_key_idx (and it should be the same if it used 'foreign_key_2_idx') has to read 500k rows by "random" order (supposing the rows were inserted or assigned the ID randomly) and check the other condition on them. That means the query reads 500k rows by their primary key from the table - but that means it will probably access 100% of data pages of the table. So in the end the query reads half of the index and all of the table - more data to go through in total and the table is accessed by random seeks, not sequentialy. It should be no surprise that such query is slower than full table scan.

The query going by foreign_key_comp_idx will find the part of the index for foreign_key_id = 1 by ref access method and then fetch its part satisfying af.foreign_key_id2 IS NOT NULL by range access - finding 250k rows which it will then read from the table. Again there is a high probability that it will have to read more than 50% of the table pages but it may just be lucky with the data distribution that it is less than 100% and as a plus it does not have to check the conditions again as they are assured by the index.

What surprises me: usual understanding is that the optimizer will not even want to use an index if it will lead to fetching ~20% of the table rows, prefering full table scan instead as it is usually faster. Your IGNORE INDEX() hints should not change that, thats what FORCE INDEX() hint is for (telling optimizer that the table scan is extremely costly compared to the suggested index).

But maybe if the insert order was not totally random, the statistics and index dives show that (even when the expected rows numbers do not).


What is the difference between using First..Last and 1..count in Oracle?

first and last returns the first index and last index. count returns the number of elements in the collection. These do not necessarily give a correct way of referencing the elements in the collection, as you can have sparse collections, where index values are not contiguous (see below example).

declare
  type t_numbers is table of number index by pls_integer;
  l_numbers t_numbers;
begin
  l_numbers(1) := 5;
  l_numbers(4) := 6;
  for i in l_numbers.first..l_numbers.last loop
    dbms_output.put_line(i);
  end loop;
end;
/

1
2
3
4

declare
  type t_numbers is table of number index by pls_integer;
  l_numbers t_numbers;
begin
  l_numbers(1) := 5;
  l_numbers(4) := 6;
  for i in 1..l_numbers.count loop
    dbms_output.put_line(i);
  end loop;
end;
/

1
2

I hope you see why both versions fail. These work properly only as long as you have dense collections (where index values are contiguous). With sparse collections, both method fail, and you should use a WHILE loop:

declare
  type t_numbers is table of number index by pls_integer;
  l_index number;
  l_numbers t_numbers;
begin
  l_numbers(1) := 5;
  l_numbers(4) := 6;
  l_index := l_numbers.first;
  while (l_index is not null)
  loop
    dbms_output.put_line(l_index);
    l_index := l_numbers.next(l_index);
  end loop;
end;
/

1
4


Restore only one datafile in SQL Server 2008 from partitioned database

You can do a piecemeal restore of this data file if:

you are in full recovery mode (this is not clear in the question - I think there are many configuration options for BackupExec)
you have more than one filegroup (this is not clear in the question)
this data file is read/write and in its own filegroup (otherwise you will also need all of the other data files in this filegroup). I assume this is the case because, how else could you know that this partition exists only in this one file?
you can restore the primary filegroup and then this filegroup (in other words, the rest of the data is NOT in the primary filegroup - also not clear in the question)
you are on Enterprise Edition (again, unknown)

More details in the following articles on MSDN:

Performing Piecemeal Restores
Example: Piecemeal Restore of Only Some Filegroups (Full Recovery
Model)


MySQL: memory requirements for django app with 1 big table (460M rows)

Looking at the query

SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation
WHERE gene2_id IN (176829, 176519, 176230)
GROUP BY gene1_id ORDER BY NULL

I have four(4) suggestions

SUGGESTION #1

I see three(3) columns. I recommend making a covering index

ALTER TABLE genescorrelation
ADD INDEX gene2_gene1_correlation_idx1 (gene2_id,gene_id,correlation);

The StackOverflow accepted answer already mentioned making this index. That way, the aggregation never touches the table. All the needed data will come from the index.

Since you already made gene2_gene1_idx1 here is the fix

ALTER TABLE genescorrelation
DROP INDEX gene2_gene1_idx1,
ADD INDEX gene2_gene1_correlation_idx1 (gene2_id,gene_id,correlation);

SUGGESTION #2

You probably need more read threads

Add this to my.cnf

[mysqld]
innodb_read_io_threads = 8

Restart of mysql is required

SUGGESTION #3

If the server is just a DB Server, you should reserve up to 75% of RAM for InnoDB Buffer Pool or the sum total of all InnoDB Data and Indexes (SELECT SUM(data_length+index_length) InnoDBDataAndIndex FROM information_schema.tables WHERE engine='InnoDB';), whichever is smaller.

SUGGESTION #4 (OPTIONAL)

Change the Query to see break down by gene2_id

SELECT
    IFNULL(gene2_id,'All Gene2 IDs') gene2,
    IFNULL(gene1_id,'All Gene1 IDs') gene1,
    AVG(correlation) AS avg FROM genescorrelation
WHERE gene2_id IN (176829, 176519, 176230)
GROUP BY gene2_id,gene1_id WITH ROLLUP;


What is a good option to replace a daily SQL server snapshot replication job?

ETL for SQL Server can be accomplished natively using SQL Server Integration Services.  That can be scheduled using SQL Server Agent.

If you have a linked-server setup between the prod server and the reporting server, you could use it to truncate the target table(s), and repopulate them via the linked server.  I would schedule this at the reporting server end.

There are a large number of commercial and open-source products that can be used for ETL, unfortunately we frown upon creating "shopping lists" of products since those lists are subject to going out of date extremely quickly.

It's vastly simpler to setup this kind of scenario if you have two separate databases on the same server. Simply create a SQL Server Agent job that truncates the destination table, then does something like INSERT INTO reportdb.dbo.tablename SELECT * FROM proddb.dbo.tablename.  Don't get me wrong, there are a lot of subtle corner-cases around doing this on a single server, not least of which would be evaluating the impact of running the reporting workload on the same server as your transactional production workload.


T-SQL Daylight Saving lookup table - poorly performing table-valued function

If Tzid and DT_WhenSwitch define a unique row I recommend clustering the dbo.DSTLookup table by those two columns. You can make those columns the primary key if you want or you can just make them be the clustered index.
CREATE TABLE dbo.DSTLookup
(
     [Id] int,
     [Tzid] int,
     [DT_WhenSwitch] datetime,
     [DSTOffSetSeconds] int,
     [GMTOffSetSeconds] int
);

CREATE CLUSTERED INDEX CI_DSTLookup ON dbo.DSTLookup ([Tzid], [DT_WhenSwitch]); -- new

INSERT INTO dbo.DSTLookup
VALUES (29, 2, N'2014-03-30T01:00:00', 3600, 0),
       (30, 2, N'2014-10-26T02:00:00', 0, 0),
       (31, 2, N'2015-03-29T01:00:00', 3600, 0),
       (32, 2, N'2015-10-25T02:00:00', 0, 0),
       (33, 2, N'2016-03-27T01:00:00', 3600, 0),
       (34, 2, N'2016-10-30T02:00:00', 0, 0),
       (35, 2, N'2017-03-26T01:00:00', 3600, 0),
       (36, 2, N'2017-10-29T02:00:00', 0, 0),
       (37, 2, N'2018-03-25T01:00:00', 3600, 0),
       (38, 2, N'2018-10-28T02:00:00', 0, 0);

The reason for doing that is it will allow very fast individual row lookups. For both queries against the table you want to filter on [Tzid] and to find the first [DT_WhenSwitch] value in descending order. With the right clustered index getting that row can be a single clustered index seek.
To get that the plan I want I'm going to simplify the TVF a bit with the APPLY and TOP operators. I also want to make it very obvious to the optimizer that I get back just one row every time. Here's one implementation:
CREATE FUNCTION dbo.FN_GetLocalTime_FromUTC_BasedOnTZId
     (@StartDateTime DATETIME, @EndDateTime DATETIME, @Tzid INT)
/*=========================================================================
*   2017-03-27
*   Returns local time from UTC time based on timeZoneId
*
==========================================================================*/
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
        SELECT
               DATEADD(SECOND, (COALESCE(S.s_DST_OffSet, 0) + COALESCE(S.s_GMT_OffSet, 0)), @StartDateTime) 'LocalStartDateTime'
             , DATEADD(SECOND, (COALESCE(E.e_DST_OffSet, 0) + COALESCE(E.e_GMT_OffSet, 0)), @EndDateTime)   'LocalEndDateTime'
        FROM (SELECT 1 t) t
        OUTER APPLY (
            SELECT TOP 1
                D.DSTOffSetSeconds 's_DST_OffSet',
                D.GMTOffSetSeconds 's_GMT_OffSet'
            FROM dbo.DSTLookup D
            WHERE D.DT_WhenSwitch <= @StartDateTime AND D.Tzid = @Tzid
            ORDER BY D.DT_WhenSwitch DESC
        ) s
        OUTER APPLY (
            SELECT TOP 1
                 D.DSTOffSetSeconds 'e_DST_OffSet',
                 D.GMTOffSetSeconds 'e_GMT_OffSet'
             FROM dbo.DSTLookup D
             WHERE D.DT_WhenSwitch <= @EndDateTime AND D.Tzid = @Tzid
             ORDER BY D.DT_WhenSwitch DESC
        ) e
);

Here is the query plan for your example query in the question:

As expected we only do two seeks against the clustered index:

(1 row(s) affected)
Table 'DSTLookup'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 1 ms.

I wasn't able to test against SQL Server 2008, but I think that syntax will work on that platform. db fiddle for SQL Server 2014.


You need to overcome your scruples and materialize the list of users and allowed clients into a dimension, then apply security to that dimension.  If you have Enterprise Edition you can hide the dimension from the users.


MySQL InnoDB add new instance to existing cluster

The old way of adding a Slave might be best:

Stop mysqld on an existing Slave (Replica)
Copy the disk (or at least all of MySQL's stuff)
Change the server_id
See if something in the Master contains a list of servers in the topology; if so, augment it
Start mysqld on both replicas.


Create folder mysql_tmp on the biggest partition you have.
Add the line tmpdir = /path/to/the/mysql_tmp to the section [mysqld] of the mysql.conf.
restart mysql
perform REPAIR for all the tables in your working base


Splitting a table with large columns for performance?

Generally this can make sense, BUT: you have first to make sure the data is in the table.

If you use large table fields (varbinary(max)) then the data is actually not stored in the table but in separate pages anyway. If the original table as as thin as you indicate (id, date upload, not a lot more fields) Then this is a fallacy, with one exception: EF will gladly load all the data on object access - which makes acess slow thanks to EF inefficiency.

You can avoid that by not using EF for accessing the file field. That does make a TON of sense because you may never want to load the whole file into memory anyway. You want to be able to access the file in a loop reading max x bytes - so you can stream them off.

Welcome to the limitations of EF and the even worse limitations of code first.


MYSQL TABLESPACE = innodb_file_per_table GIVING ERROR 1030 (HY000): Got error 168 from storage engine

If you want the table created in that data directory with .ibd file, do this

chown -R mysql:mysql /home/egonzalez/Documentos

Then, run this

CREATE TABLE t2 (c1 INT PRIMARY KEY)
ENGINE=InnoDB DATA DIRECTORY = '/home/egonzalez/Documentos';

The option innodb_file_per_table is enabled by default in MySQL 5.7

The reason for the error in the first place ?

You need to run CREATE TABLESPACE like this

mysql> CREATE TABLESPACE `ts1`
    ->     ADD DATAFILE '/home/egonzalez/Documentos/mytblspc.ibd'
    ->     ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

Then, you could create any table into be included in the one tablespace like

CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE=InnoDB TABLESPACE ts1;


PL/SQL - how to identify sequences used by tables?

You can query with this one:
SELECT
    TABLE_OWNER, TABLE_NAME,
    REFERENCED_OWNER AS SEQUENCE_OWNER, REFERENCED_NAME AS SEQUENCE_NAME
FROM ALL_DEPENDENCIES d
    JOIN ALL_TRIGGERS t ON TRIGGER_NAME = d.NAME AND t.OWNER = d.OWNER
WHERE REFERENCED_TYPE = 'SEQUENCE';

Note, a sequence might be used by more than one trigger. And a sequence can be used also by function, procedure, package, insert, update, etc, which is not covered by this query.
Also this query does not cover IDENTITY columns and sequences, they are hidden.
It depends what you mean by "identify sequences used by tables"?


Psycopg2 - PGBouncer - Postgresql > Server does not support SSL but SSL was required

The reason that this command worked:

psql --set=sslmode=verify-full -h DBHOST -p DBPORT -U USERNAME DBNAME

Is that --set just creates a user-defined variable inside the psql program with the name of 'sslmode'.  That name is not special to psql, it does nothing with your connection options and you just connect without ssl.


SQL: revoking permissions from public in master

As long as you have given any account that needs explicit permissions to those objects (views/procedures) you should see no issues. The issue I usually see after altering PUBLIC role permissions is applications that assume the permissions are there instead of just giving explicit permissions to them.

You will find that some Microsoft documentation states to not alter the PUBLIC  role permissions. I work with the IASE database checklist and the permissions given to the PUBLIC role is one of the first things we have to remove/revoke. I always keep the script handy to restore the permissions if needed.

EDIT
Query to find public permissions that I use sometimes:

SELECT o.name AS [Object], p.permission_name AS [Type]
FROM sys.all_objects o
INNER JOIN sys.database_permissions p ON o.object_id = p.major_id
INNER JOIN sys.database_principals u ON u.principal_ID = p.grantee_principal_id
WHERE u.name = 'public'


File share witness vs 3rd Node for WSFC + AG with only 2 nodes?

In a 2 node Availability Group (or mirror) a 3rd node is important if you want automatic HA. If your secondary cannot connect to the primary, the secondary has to work out if it can come online as the primary, or if the error is actually with the secondary. Without a 3rd party, this is impossible.

The File Share Witness will be simpler than adding another Windows node just to work as a witness. It can be anywhere, what is important is that both nodes will be able to see the witness if the other node goes offline. You might need to think extra hard if you have a virtual environment, you need to consider if the physical node goes offline will your fileshare still be available.

Additional thoughts for multi data centers

If the 2 nodes are in different data centers (you mentioned DR, so I'm assuming) then having a file share in the cloud may be helpful (you can use Azure Blob Storage as a witness). This means if one of your primary data center goes offline, SQL will automatically failover to the secondary. If you host the fileshare at your secondary and the secondary goes offline, then your primary server will go offline as well (can't contact the secondary or the file share so assumes that it is no longer primary).


sqlserver - Insert from xml generated with `select for xml`

Here is what I ended with.
Assuming that data is read from the table using the following:
select * from someTable where st_id between 100 and 200
for xml path, root('rows')

I get this data from one server and store it as a tuple (schema, table, keys, xml), where:

schema is schema name (e.g. dbo)
table is table name (e.g. someTable)
keys is comma-separated list of "keys" (columns used for <match> in merge ... on <match>, e.g. st_id in example above, if there are more keys they should be passed,like,this)
xml is data as an xml (easy)

This data is then passed to some other server, where there is one function and one procedure defined.
Function tools.GenerateXMLMergerForTable creates body (whole declaration in fact) of procedure used for merging data read from xml into table table in schema schema using parsed string keys as <match>.
Procedure tools.createXMLMergerForTable executes aforementioned function and creates procedure that takes single parameter (of type XML) and merges data read from it into table that it was configured for.
T-SQL follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER FUNCTION tools.GenerateXMLMergerForTable
(
@tableschema nvarchar(max), @tablename nvarchar(max), @matchcols nvarchar(max), @procname nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    declare @str nvarchar(max) = '
    create or alter procedure tools.sp_AutoMergerForTableXXXPROCEDURENAMEXXX @x xml
    as
    begin
        begin try
            DECLARE @handle INT, @PrepareXmlStatus INT

            EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @x

            merge XXXTABLENAMEXXX as tgt
            using (SELECT  * FROM OPENXML(@handle, ''/rows/row'', 2) WITH XXXTABLENAMEXXX)
                as src (XXXFULLCOLUMNLISTXXX)
            on XXXMATCHLISTXXX
            when matched
                then update set
                    XXXSETLISTXXX
            when not matched by target
                then
                    insert (XXXFULLCOLUMNLISTXXX)
                    values (XXXFULLCOLUMNLISTXXX);
            EXEC sp_xml_removedocument @handle
        end try
        begin catch
        end catch
    end
    '

    declare @collist nvarchar(max) = '', @setlist nvarchar(max) = '', @matchlist nvarchar(max) = '', @fullcollist nvarchar(max) = ''

    select @collist = @collist + quotename(COLUMN_NAME) + ','
        , @setlist = @setlist + quotename(column_name) + ' = src.' + quotename(column_name) + ', '
    from INFORMATION_SCHEMA.COLUMNS where
        TABLE_SCHEMA = @tableschema and TABLE_NAME = @tablename and COLUMN_NAME not in (select * from string_split(@matchcols, ','))
    order by ordinal_position

    select @fullcollist = @fullcollist + quotename(COLUMN_NAME) + ','
    from INFORMATION_SCHEMA.COLUMNS where
        TABLE_SCHEMA = @tableschema and TABLE_NAME = @tablename
    order by ordinal_position

    select @matchlist = @matchlist + 'tgt.' + QUOTENAME(trim(value)) + '=src.' + QUOTENAME(trim(value)) from string_split(@matchcols, ',')

    declare @result nvarchar(max) = replace(@str, 'XXXCOLUMNLISTXXX', trim(', ' from @collist))
    set @result = REPLACE(@result, 'XXXSETLISTXXX', trim(', ' from @setlist))
    set @result = REPLACE(@result, 'XXXTABLENAMEXXX', @tablename)
    set @result = REPLACE(@result, 'XXXMATCHLISTXXX', REPLACE(trim(@matchlist), ' ', ' and '))
    set @result = REPLACE(@result, 'XXXPROCEDURENAMEXXX', @procname)
    set @result = REPLACE(@result, 'XXXFULLCOLUMNLISTXXX', trim(', ' from @fullcollist))

    RETURN @result

END
GO

create or alter procedure tools.createXMLMergerForTable @schema nvarchar(max), @table nvarchar(max), @matchcols nvarchar(max), @procsuffix nvarchar(max)
as
begin
    declare @body nvarchar(max) = tools.GenerateXMLMergerForTable(@schema, @table, @matchcols, @procsuffix)
    exec sp_executesql @body
end

go

Java code simply reads tuple defined above, inserts parameters that it knows off, creates random suffix, creates merging procedure, executes it using xml from The Tuple and drops previously created procedure.
It seems to work as advertised, though no guarantees are provided.
NOTE: it is assumed that function, procedure and subsequently created procedures live in schema tools.
NOTE 2: It is possible that you will get information about string_split object not found, if so then just do:
ALTER DATABASE <your database name>
SET COMPATIBILITY_LEVEL = 130;

For reasons see documentation of string_split and how to change compatibility level.


FLIGHTS.destination_citycannot be the same value at the same time, therefore a join of the same table for each destination reference is required like:

SELECT f1.source_city
FROM FLIGHTS f1
JOIN FLIGHTS f2 ON f1.source_city = f2.source_city
WHERE f1.destination_city = 'Honolulu'
  AND f2.destination_city = 'Newark'
 ;


How to use WHERE condition when using ON clause

You can put the filter in your join like this:

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
    ON Users.[Name] = Skills.[Name] AND Users.[Active] = '1'
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
    ON Users.[Name] = WeekTimeslip.[employeename] AND Users.[Active] = '1'
LEFT JOIN [master].[dbo].[Project] AS Project
    ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
    ON Users.[Name] = Utilisation.[employeename] AND Users.[Active] = '1'
ORDER by Users.[Name]

Or just put it in a where clause like this:

SELECT Users.[Name],Users.[JobTitle],Users.[Active],Skills.[Qualification],
WeekTimeslip.[date_week],WeekTimeslip.[Company],
Project.[Billable],WeekTimeslip.[totalhours],
Utilisation.[utilisation]
FROM [master].[dbo].[Users] AS Users
LEFT JOIN [master].[dbo].[Skills] AS Skills
    ON Users.[Name] = Skills.[Name]
LEFT JOIN [master].[dbo].[TIMESLIPS_wk13] AS WeekTimeslip
    ON Users.[Name] = WeekTimeslip.[employeename]
LEFT JOIN [master].[dbo].[Project] AS Project
    ON WeekTimeslip.[Project] = Project.[Project]
LEFT JOIN [master].[dbo].[Utilisation_wk13] AS Utilisation
    ON Users.[Name] = Utilisation.[employeename]
WHERE Users.[Active] = '1'
ORDER by Users.[Name]

Usually, SQL will generate the same execution plan and the optimization process will do pretty much the same thing.


How can I run a PostgreSQL outside the standard directories on Linux, ie outside /var/run/postgresql etc?

pg_ctl allows for a -D option which allows you to point at your data wherever it is.

I do this all the time for development environments and fun things like setting up intracomputer replication environments for testing.  It works fine.

The one thing you really need to be careful about is the configuration.  After unzipping you should probably edit the postgresql.conf appropriately.


I'm not sure if that is exactly what you want. But It gives your expected result. Note you can rename the columns as you please.

SET NOCOUNT ON

declare @t table(
    cid int,
    OverviewText varchar(15),
    RAGStatusText varchar(15)
 )

insert into @t values (153, 'Cost',           'Green');
insert into @t values (154, 'Requirements',   'Yellow');
insert into @t values (155, 'Schedule',       'NULL');
insert into @t values (156, 'Technical',      'NULL');
insert into @t values (157, 'Testing',        'NULL');

Select
    [153|1] as [xyz],
    [153|2],
    [154|1],
    [154|2],
    [155|1],
    [155|2],
    [156|1],
    [156|2],
    [157|1],
    [157|2]

FROM (
select CAST(cid as varchar) + '|1' type, OverviewText  text from @t
Union
select CAST(cid as varchar) + '|2' type, RAGStatusText text from @t
) as SourceTable
Pivot(
    min(text)
    for type in ([153|1],[153|2], [154|1],[154|2], [155|1],[155|2], [156|1],[156|2], [157|1],[157|2])
) as PivotTable;


You need to deactivate all active databases first, in your case

db2 deactivate db

before stopping the instance. db2stop force does that for you, so I'm not sure what's wrong with simply using the force option.


Indexes: integer vs string performance if the number of nodes is the same

Short answer: integer is faster than varchar or text in every aspect. Won't matter much for small tables and / or short keys. The difference grows with the length of the keys and the number of rows.

  string ... 20 characters long, which in memory is roughly 5x that of
  the integer (if an integer is 4 bytes, and the strings are pure ASCII
  at 1 byte per character, then this holds)

To be precise, character types (text or varchar) occupy exactly 21 bytes for 20 ASCII characters on disk and 23 bytes in RAM. Detailed assessment:

What is the overhead for varchar(n)?

Also important: COLLATION rules can make sorting character data more expensive - unlike numeric data types:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

Index size is probably responsible for the lion share of performance difference in most cases. Consider the overhead per index tuple (basically the same as for a table): 4 bytes for the item identifier and 8 bytes for the index tuple header. So the index tuple for integer would amount to 20 bytes (including 4 bytes of alignment padding) and for varchar(20) with 20 ASCII characters it would be 36 bytes (also incl. padding). Details:

Configuring PostgreSQL for read performance

All the theory aside: it's best to just test:

Measure the size of a PostgreSQL table row

Postgres 9.5 introduced an optimization for sorting long strings of character data (key word "abbreviated keys"). But a bug in some C library functions on Linux forced the project to disable the feature for non-C collations in Postgres 9.5.2. Details in the release notes.

However, if you actually use Postgres enum types, most of these considerations are irrelevant, since those are implemented with integer values internally anyway. The manual:

  An enum value occupies four bytes on disk.

Aside: varchar(255) used to make sense for early versions of SQL Server, which could use a more efficient data type internally up to the limit of 255 characters. But the odd length restriction of 255 characters has no special meaning in Postgres at all.


Instead of using the SSMS GUI interface to restore databases, you should create a T-SQL script for the restore statement.

This serves two purposes:

You can confirm the target database name, the location of the restored database files, etc prior to the start of the restore.
You can re-run the restore statement as needed in future without re-using the GUI.  This ensures you do the restore the same way each time.

You can create a script from the "Restore Database" dialog-box as show in this screenshot:

This will generate a restore statement like the one below, which I've wrapped for readability:

USE [master]
RESTORE DATABASE [test_master_restore]
FROM  DISK = N'D:\SQLServer\Backups\master_db.bak' WITH  FILE = 1
    ,  MOVE N'master' TO N'D:\SQLServer\MV\MSSQL10_50.MV\MSSQL\DATA\test_master_restore.mdf'
    ,  NOUNLOAD
    ,  STATS = 5


calculate running balance of debit credit using union all

Try this (you may need to adjust data types or play with clustered index on temporary table, may be use identity column):
CREATE TABLE #x(
VoucherNo int,
DocumentDate datetime,
Description varchar(255),
Debit numeric(18,2),
Credit numeric(18,2),
Balance numeric(18,2)
);
CREATE UNIQUE CLUSTERED INDEX cx_x ON #x(DocumentDate, VoucherNo);

INSERT INTO #x (VoucherNo, DocumentDate, Description, Debit, Credit)
SELECT '1'AS VoucherNo,''AS DocumentDate,'Opening' AS Description,isnull(SUM(JournalDetials.Debit),0)AS Debit,
    isnull(SUM(JournalDetials.Credit),0)AS Credit
    FROM JournalDetials INNER JOIN JournalMains ON JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId=GLMainAccount.Id
    where(JournalDetials.AccountId='001653')AND(JournalDetials.Date<'03/31/2019')

    union all

    select(JournalMains.VoucherNumberPrefix+JournalMains.VoucherNumber)AS VoucherNo,JournalDetials.Date as DocumentDate,
    isnull(JournalDetials.Description,''),isnull(JournalDetials.Debit,0),isnull(JournalDetials.Credit,0)
    from JournalDetials inner join JournalMains on JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId = GLMainAccount.Id
where(JournalDetials.AccountId='001653' and JournalDetials.Date BETWEEN '03/31/2019' AND '04/09/2020')

DECLARE @Balance numeric(18,2), @DocumentDate datetime, @amt numeric(18,2), @VoucherNo int;
SET @Balance = 0;

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
  FOR SELECT DocumentDate, VoucherNo, Debit - Credit FROM #x ORDER BY DocumentDate, VoucherNo;

OPEN c;

FETCH c INTO @DocumentDate, @VoucherNo, @amt;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @Balance = @Balance + @amt;
  UPDATE #x SET Balance = @Balance WHERE DocumentDate = @DocumentDate AND VoucherNo = @VoucherNo;
  FETCH c INTO @DocumentDate, @VoucherNo, @amt;
END

CLOSE c; DEALLOCATE c;

SELECT VoucherNo, DocumentDate, Description, Debit, Credit, Balance, '' AS JournalTransationStatus
  FROM #x
  ORDER BY DocumentDate, VoucherNo;

DROP TABLE #x;