How to Schedule Recurring Queries in SQL Server and PostgreSQL
💡 Concept Name
Recurring Query Scheduling – Automating the execution of SQL queries at fixed intervals using built-in or external tools.
📘 Quick Intro
Whether for reports, maintenance, or ETL processes, running SQL automatically saves time and reduces errors. SQL Server uses SQL Server Agent, while PostgreSQL relies on cron or pg_cron.
🧠 Analogy / Short Story
Think of recurring SQL jobs like setting your coffee maker to brew every morning. Once scheduled, it works automatically without you lifting a finger, delivering fresh data when you need it.
🔧 Technical Explanation
- 🛠️ SQL Server uses SQL Server Agent to create jobs with steps and schedules.
- 🧩 PostgreSQL lacks a built-in scheduler, but pg_cron or system cron fill that gap.
- 🕐 Schedules can be set by frequency (daily, hourly, etc.).
- 🔗 Jobs can execute queries, stored procedures, or scripts.
- 📤 Output can be logged or sent to reporting tools.
💻 Real Code Example
-- SQL Server Agent Job Example (T-SQL to call stored proc)
EXEC msdb.dbo.sp_add_job @job_name = 'DailyReportJob';
-- Add steps, schedule, and link them via sp_add_jobstep and sp_add_schedule
-- PostgreSQL using cron (Linux):
0 6 * * * psql -d mydb -c "CALL daily_summary();"
-- PostgreSQL using pg_cron extension:
SELECT cron.schedule(
'daily_summary_job',
'0 6 * * *',
$$CALL daily_summary();$$
);
❓ Interview Q&A
Q1: How do you schedule recurring SQL tasks in SQL Server?
A: By creating jobs in SQL Server Agent with defined steps and schedules.
Q2: Does PostgreSQL include a built-in job scheduler?
A: No, but you can use system cron or extensions like pg_cron.
Q3: What is pg_cron?
A: A PostgreSQL extension that allows cron-like scheduling from inside SQL.
Q4: How does cron work with PostgreSQL?
A: It uses command-line calls to invoke psql with SQL scripts at scheduled times.
Q5: Can SQL Server Agent run scripts other than SQL?
A: Yes, it can run PowerShell, SSIS packages, and command-line utilities.
Q6: What permissions are needed to use SQL Server Agent?
A: Users need to be in the SQLAgentOperatorRole or higher.
Q7: Is pg_cron available by default?
A: No, it must be installed and added to shared_preload_libraries.
Q8: How do you monitor job history in SQL Server Agent?
A: Use Job History logs via SSMS or query msdb tables.
Q9: What format does cron use for scheduling?
A: It uses 5 fields: minute, hour, day, month, and weekday.
Q10: What are some use cases for scheduled SQL jobs?
A: Automating reports, syncing data, cleanup tasks, or daily data snapshots.
📝 MCQs
Q1. Which tool schedules SQL jobs in SQL Server?
- Task Manager
- SQL Scheduler
- SQL Server Agent
- SSMS
Q2. What is pg_cron?
- Backup tool
- JSON parser
- A PostgreSQL scheduling extension
- CLI utility
Q3. How many fields are in a cron schedule expression?
- 3
- 4
- 5
- 6
Q4. Which PostgreSQL tool runs SQL queries at fixed times?
- agentd
- cron
- timerctl
- scheduler
Q5. What function registers a pg_cron job?
- pg.schedule()
- cron.add_job()
- cron.schedule()
- pg_run()
Q6. Which role grants SQL Server Agent permissions?
- db_owner
- admin
- SQLAgentOperatorRole
- AgentManager
Q7. Can SQL Server Agent run PowerShell scripts?
- No
- Only T-SQL
- Yes
- Only in Express Edition
Q8. Which method is used for scheduling in Linux systems?
- sshd
- psql_scheduler
- cron
- sqlwatch
Q9. What does the psql command do in a cron job?
- Backup DB
- Start server
- Monitor logs
- Runs a SQL command
Q10. What output options are available for SQL jobs?
- Only emails
- Only logs
- Only backups
- Logs, reports, exports
💡 Bonus Insight
Always monitor scheduled jobs using logs or alerts. A failed job might silently break reports or ETL chains unless monitored properly. Consider retry mechanisms and alerts for critical jobs.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!