AI is a part of every developer’s job these days. On my own, I prefer to use ChatGPT, to lookup things and ask questions. I would certainly like to have an AI tool that’s integrated with my query tool, like Copilot for SSMS, or using DBeaver. But, as with a lot of places, the place I work at dictates the tools we use. The company’s AI tool of choice is Claude Code, so that’s what I use there.
I was going to share a few things I’ve set up to help me with working with SQL Server databases. It’s been a struggle at times working with Claude and SQL Server.
*) For the most part, I’m running Claude and using my local .claude directory as the default directory. I know most devs are working in a project directory, but my work tends to be a lot of ad-hoc data work. We do keep tables, procs, etc. in source control, but most work is one-time updates to data. We usually start from scratch with these scripts, although we created Claude skills for situations that do recur.
*) For accessing SQL Server, I have Claude use SQLCMD.exe to execute any SQL that it needs to run. We do have a SQL Server MCP at work that someone put together, but it seems more straightforward to use SQLCMD. I have a Claude skill set up to enforce all queries going through this tool, as well adding other rules around running scripts.
I use other MCPs (Like for Notion) but SQLCMD gives me an interface to the database, so I don’t need any more than that
*) The main thing I’ve done is to force Claude to write any SQL that it wants to run to a file, and then execute the script. At first, I did this because Claude had a lot of trouble running inline SQL through SQLCMD. Using Bash to pass inline SQL to SQLCMD seemed to cause a lot of issues, mainly around escaping characters in the inline SQL. Writing SQL to a script and then running it took care of this issue. Now, I like having a history of the SQL that Claude needs to run. I like to review it periodically, and then I can add an optimized query to a skill if there’s something that gets run over and over.
By specifying this one path for database access, I feel like I’m better able to enforce permissions and style rules for SQL. With SQLCMD, I’m using Windows authentication by default. I have read permission for Prod and QA, and read and update/alter permissions for Dev, so I can’t do much damage with my account. If I had wider permissions, where I could change things in Prod, I probably wouldn’t use Windows authentication.
*) For these ad-hoc scripts, Claude writes them to a ClaudeTemp directory. At first, I had the directory under the .claude directory, but then I had to approve Claude writing these files each time. Turns out Claude doesn’t like working with things inside the .claude directory, so everything comes up for approval, even when you’ve granted Claude use of the Write tool. Once I moved the temp directory alongside .claude in my user directory, Claude could write there without me having to approve each time.
*) With the WRITE permission, I’ve given Claude permission to write files to the ClaudeTemp directory. I created a second hook to prevent Claude from using WRITE with existing files. I didn’t know this until recently, but Claude can use WRITE to overwrite an existing file, if there are a lot of changes to be made. I assumed that WRITE is for new files, and EDIT is used for existing files, but that’s not the case. I approve each EDIT call before it happens, so I can see what’s being changed.
*) I added a hook to prevent Claude from running anything but a SELECT statement. If we need to add, update, or delete data, I have it write a script that I review first. I’m probably being paranoid, because I only have permission to change data in Dev, not in QA or Prod. But even in Dev, I don’t want to risk having Claude mess something up that may take hours to rebuild. Plus, I want a script, because I’ll end up performing the same operation in multiple environments. The hook prevents INSERT, UPDATE, DELETE, ALTER, TRUNCATE, MERGE, DROP commands, plus EXEC or EXECUTE for stored procedures (Although I have a couple of read-only procs on a whitelist).
This does prevent the use of temp tables, but so far Claude has been able to work around this.
*) I started off with a rule around creating SQL scripts, specifically around formatting the SQL and writing a script header in a particular format. A rule gets loaded in full at each startup, as opposed to a skill, where the header or description is always loaded, and then the rest of the skill is loaded when it is needed. Although the bulk of my work is around creating SQL scripts, I do occasionally work in C#, or in other situations where I don’t need a SQL script rule loaded. It seems most rules get created with a filter, so that they get loaded when working with certain projects or types of files. I ended up converting this rule into a skill, so that it gets loaded when I’m specifically working on SQL scripts.
This SQL Script skill contains basically two types of rules: There are formatting rules that I want to use, like having all commands in upper case, ending each statement in a semicolon, etc, and then there are a few rules that the company wants to enforce. If we create a new table or index, they want those created with table compression, for example.
It’s useful to split these types of rules up. If I want to use Claude to help review someone else’s scripts, I want to enforce the company rules, but not my personal formatting rules. I still review every script myself, but I’ve found it useful to have Claude review them as well, to make sure that I didn’t miss anything.
I also have a specific header to add to each script with things like date created, the request number, a short description, things of that nature. I also add SET NOCOUNT ON to each script, and a USE statement with the database name.
*) Claude has created a few sub-agents for me. One is a SQL script reviewer. This can enforce anything I’ve specified in my SQL script format skill (other than my personal formatting items), plus run checks on the script logic. This works well in a sub-agent, since it can go off in its own context to review scripts, and then return to my main context with only the list of items to review.
It has also created a schema explorer sub-agent, when it needs to go explore a database that it isn’t familiar with.
*) I have a docs directory with a few lists. I keep a list of the servers that we use for our client databases, as well as which environment they are for (Dev, QA, Prod). We have around 200 clients, so I also keep a list of each client, and which Prod server their database is on. Claude can use these lists to find things for me easily.
These items aren’t database-related, but are things I found useful with Claude Code.
*) I find the Claude output to be very verbose. I’ve added entries to have Clause try to curb this, with little luck.
Recently, Claude did add a Concise output style, which has helped some. You can set this under Config – Output Style = Concise.
*) I also asked Claude to set up a status line for me. This is one the bottom line of the terminal, and can keep some information in constant view. I asked Claude to display the current model and effort level, as well as displaying the percentage of context filled. This is helpful to know when the context may need to be compacted. You can also display other things, like the cost of the current session, or the working directory.
*) I started with writing my own Claude skills, but lately I’ve had Claude write the skill itself. I tell it what I want, and it does a good job of asking me questions to clarify my intent, as well as making suggestions. I also have Claude review all of my skills periodically, to make sure that I haven’t given conflicting commands across other skills, or for entries that need clarification.
I’d like to be able to share some of the files I’ve set up, but since I’ve created these at work, I can’t post these. I doubt my exact setup would benefit most people, but I tried to share the ideas behind them. One thing I’ve learned is that Claude is good at writing for itself. At first, I created the skills and other entries myself, but there are plenty of times that I wouldn’t be clear, would give conflicting directions, or would leave gaps. Now, I give Claude the general idea, and let it write what it needs, and ask me to fill in any gaps.
I’m certainly not at a point where I’m letting a LLM do things on its own. As I mentioned, I don’t let it change data on its own, it writes a script that I review. I don’t sign off on a PR unless I’ve read everything myself. A LLM is good to get things started, and to review your project for issues that may have been overlooked, but I think people still have to have the final review on everything that’s produced. There are too many mistakes that are made for it to work autonomously.
